Operators in PL/SQL

In PL/SQL, operators are used to construct expressions by combining operands (variables, constants, or literals). PL/SQL evaluates expressions based on the operators' rules and precedence. This lesson covers key operators and demonstrates their use within PL/SQL blocks for practical application.

• Logical Operators: Logical operators AND, OR, and NOT evaluate conditions based on a truth table, returning TRUE, FALSE, or NULL. AND and OR are binary operators, while NOT is unary. These are commonly used in conditional statements like IF.
  Examples:
  

  DECLARE
    v_age NUMBER := 25;
    v_salary NUMBER := 50000;
    v_department VARCHAR2(20) := 'Sales';
  BEGIN
    -- Using NOT
    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;
  -- Using AND
    IF v_salary > 40000 AND v_age >= 25 THEN
      DBMS_OUTPUT.PUT_LINE('Eligible for senior role.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Not eligible for senior role.');
    END IF;  -- Using OR
    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;  -- Combining NOT, AND, OR
    IF NOT (v_department = 'HR') AND (v_salary >= 50000 OR v_age < 30) THEN
      DBMS_OUTPUT.PUT_LINE('Special compensation review needed.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Standard compensation review.');
    END IF;
  END;
  

• Comparison Operators: Comparison operators (=, !=, <, >, <=, >=) compare expressions, returning TRUE, FALSE, or NULL. They are used in conditional control statements and SQL queries within PL/SQL.

DECLARE
  v_price NUMBER := 50;
BEGIN
  IF v_price <= 100 THEN
    DBMS_OUTPUT.PUT_LINE('Price is within budget.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Price exceeds budget.');
  END IF;
END;

• IS NULL Operator: The IS NULL operator returns TRUE if an operand is null, or FALSE otherwise. Comparisons involving nulls typically yield NULL.
  Example:
  

  DECLARE
    v_value NUMBER := NULL;
  BEGIN
    IF v_value IS NULL THEN
      v_value := 0;
      DBMS_OUTPUT.PUT_LINE('Value was NULL, set to 0.');
    END IF;
  END;
  

• LIKE Operator: The LIKE operator compares a character value to a pattern (case-sensitive) within PL/SQL logic.
  Example:
  

  DECLARE
    v_firstname VARCHAR2(50) := 'Amanda';
  BEGIN
    IF v_firstname LIKE 'Am%' THEN
      DBMS_OUTPUT.PUT_LINE('Name starts with Am.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Name does not start with Am.');
    END IF;
  END;
  

• BETWEEN Operator:
  The BETWEEN operator checks if a value lies within a specified range (inclusive).
  Example:
  

  DECLARE
    v_sale_price NUMBER := 42;
  BEGIN
    IF v_sale_price BETWEEN 40 AND 45 THEN
      DBMS_OUTPUT.PUT_LINE('Price is in the target range.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Price is outside the target range.');
    END IF;
  END;
  

• IN Operator: The IN operator tests if a value exists within a set of values.
  Example:
  

  DECLARE
    v_state VARCHAR2(2) := 'FL';
  BEGIN
    IF v_state IN ('FL', 'NY') THEN
      DBMS_OUTPUT.PUT_LINE('Customer is in a priority state.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Customer is not in a priority state.');
    END IF;
  END;
  

• Concatenation Operator: The || operator appends strings to form a new string.
  Example:
  

  DECLARE
    v_product_name VARCHAR2(50) := 'F' || 'ish';
  BEGIN
    IF v_product_name = 'Fish' THEN
      DBMS_OUTPUT.PUT_LINE('Product name is Fish.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Product name is not Fish.');
    END IF;
  END;
  

Operations within an expression are evaluated based on operator precedence. Parentheses can override this order. The following table lists the precedence from highest to lowest:

Operator	Operation
**, NOT	Exponentiation, logical negation
+, -	Identity, negation
*, /	Multiplication, division
+, -, ||	Addition, subtraction, concatenation
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN	Comparison
AND	Conjunction
OR	Inclusion

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