PL/SQL Datatypes  «Prev  Next»

Lesson 7 Operators in PL/SQL
Objective Use operators within PL/SQL

Operators in PL/SQL

The operators n PL/SQL are similar to those used in SQL. Expressions are constructed by using operands and operators. An operand is a variable, constant, or literal. PL/SQL evaluates an expression by combining the values of the operands in ways specified by the operators.
  • Logical Operators
    The logical operators AND, OR, and NOT operate according to the value returned by the truth table. AND and OR are binary operators; NOT is a unary operator.
  • Comparison Operators
    These operators let you compare one expression to another. They are used in conditional control statements and SQL data manipulation statements. The result is always TRUE, FALSE, or NULL.
  • Relational Operators
    Relational operators are =, !=, <, >, <=, and >=. They allow for arbitrary comparisons of complex expressions.
  • IS NULL Operator
    The IS NULL operator returns a boolean value TRUE if its operand is null or FALSE if it is not null. Comparisons involving nulls always yield NULL. For example, 
    IF value IS NULL THEN
  value := 0;
END IF;
  • LIKE Operator
    You can use the LIKE operator to compare a character value to a pattern. Case is significant. For example, 
    SELECT * FROM CUSTOMER 
WHERE FIRSTNAME LIKE ‘Am%’;
  • BETWEEN Operator
    This operator tests whether a value lies within a specified range. For example, 
    SELECT * FROM PRODUCT 
WHERE SALE_PRICE BETWEEN 40 AND 45;
  • IN Operator
    The IN operator tests the existence of a value within a set of value. For example, 
    DELETE FROM CUSTOMER 
WHERE STATE IN ('FL', ‘NY’);
  • Concatenation Operators
    These operators let you manipulate strings by appending one string to another. For example, 
    SELECT * FROM PRODUCT 
WHERE PRODUCT_NAME = ‘F’ || ‘ish’;


Boolean Expressions

PL/SQL supports the comparison of variables and constants in SQL and PL/SQL statements. These comparisons, called boolean expressions, generally consist of simple expressions separated by relational operators. Boolean expressions are often connected by logical operators NOT, AND, and OR. In PL/SQL, a boolean expression always evaluates to TRUE, FALSE, or NULL.
Here are examples of PL/SQL Boolean expressions using the logical operators NOT, AND, and OR:
  1. Using NOT: 
    DECLARE
  v_age NUMBER := 25;
BEGIN
  IF NOT (v_age < 18) THEN
    DBMS_OUTPUT.PUT_LINE('You are an adult.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('You are a minor.');
  END IF;
END;

    In this example, NOT (v_age < 18) checks if the person's age is not less than 18, which effectively means checking if they are 18 or older.
  2. Using AND: 
    DECLARE
  v_salary NUMBER := 50000;
  v_years_of_service NUMBER := 5;
BEGIN
  IF v_salary > 40000 AND v_years_of_service >= 5 THEN
    DBMS_OUTPUT.PUT_LINE('Eligible for bonus.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not eligible for bonus.');
  END IF;
END;

    Here, v_salary > 40000 AND v_years_of_service >= 5 checks if both conditions (salary over 40,000 and at least 5 years of service) are true before determining eligibility for a bonus.
  3. Using OR: 
    DECLARE
  v_department VARCHAR2(20) := 'Sales';
BEGIN
  IF v_department = 'Sales' OR v_department = 'Marketing' THEN
    DBMS_OUTPUT.PUT_LINE('You are in a client-facing department.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('You are not in a client-facing department.');
  END IF;
END;

    This example uses OR to check if the department is either 'Sales' or 'Marketing', indicating that the employee works in a client-facing role if either condition is true.
  4. Combining NOT, AND, OR: 
    DECLARE
  v_employee_id NUMBER := 1001;
  v_department VARCHAR2(20) := 'HR';
  v_salary NUMBER := 75000;
BEGIN
  IF NOT (v_department = 'HR') AND (v_salary >= 70000 OR v_employee_id < 1000) THEN
    DBMS_OUTPUT.PUT_LINE('Special compensation review needed.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Standard compensation review.');
  END IF;
END;

    In this complex example, we combine all three operators: NOT (v_department = 'HR') checks if the employee is not in HR. v_salary >= 70000 OR v_employee_id < 1000 checks if the salary is high enough or if the employee's ID is below 1000.

These examples illustrate how Boolean expressions can be constructed and used in PL/SQL to control flow based on conditions.

Order of Operations

The operations within an expression are executed in a particular order depending on their precedence. Parentheses control the order of evaluation. The following table lists the order of operations.
Operator Operation
**, NOT Exponentiation, logical negation
+, - Identity, negation
*, / Multiplication, division
+, -, || Addition, subtraction, concatenation
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN Comparison
AND Conjunction
OR Inclusion

Operators in PL/SQL - Exercise

Click the Exercise link below to create a PL/SQL block that uses identifiers, literals, operators, and conversion functions.
Operators in PL/SQL - Exercise

SEMrush Software