Conditional Logic: IF and CASE

Conditional statements let your PL/SQL code make decisions — executing different branches based on the values of variables or query results.

IF ... THEN ... END IF

The simplest form — run code only when a condition is true:

IF v_salary > 50000 THEN
  DBMS_OUTPUT.PUT_LINE('High earner');
END IF;

IF ... THEN ... ELSE ... END IF

IF v_score >= 70 THEN
  DBMS_OUTPUT.PUT_LINE('Pass');
ELSE
  DBMS_OUTPUT.PUT_LINE('Fail');
END IF;

IF ... ELSIF ... ELSE ... END IF

Use ELSIF (note: one word, no extra 'e') for multiple branches:

IF v_score >= 90 THEN
  v_grade := 'A';
ELSIF v_score >= 80 THEN
  v_grade := 'B';
ELSIF v_score >= 70 THEN
  v_grade := 'C';
ELSE
  v_grade := 'F';
END IF;

Comparison Operators

Operator	Meaning
=	Equal
<> or !=	Not equal
<, >	Less than, greater than
<=, >=	Less/greater than or equal
IS NULL	Value is NULL
IS NOT NULL	Value is not NULL

Logical Operators

Combine conditions with AND, OR, and NOT:

IF v_age >= 18 AND v_has_id = TRUE THEN
  DBMS_OUTPUT.PUT_LINE('Entry permitted');
END IF;

NULL in Conditions

NULL is never equal to anything, including itself. Always use IS NULL / IS NOT NULL — never = NULL:

-- Wrong:
IF v_name = NULL THEN ...

-- Correct:
IF v_name IS NULL THEN ...

CASE Statement

The CASE statement is cleaner than a long ELSIF chain when matching a single variable:

CASE v_day
  WHEN 'MON' THEN DBMS_OUTPUT.PUT_LINE('Monday');
  WHEN 'TUE' THEN DBMS_OUTPUT.PUT_LINE('Tuesday');
  WHEN 'WED' THEN DBMS_OUTPUT.PUT_LINE('Wednesday');
  ELSE             DBMS_OUTPUT.PUT_LINE('Other day');
END CASE;

Searched CASE Statement

A searched CASE evaluates conditions rather than matching a single value:

CASE
  WHEN v_temp < 0  THEN DBMS_OUTPUT.PUT_LINE('Freezing');
  WHEN v_temp < 15 THEN DBMS_OUTPUT.PUT_LINE('Cold');
  WHEN v_temp < 25 THEN DBMS_OUTPUT.PUT_LINE('Mild');
  ELSE                  DBMS_OUTPUT.PUT_LINE('Hot');
END CASE;

CASE Expression (in assignments)

CASE can also be used as an expression on the right side of an assignment:

v_label := CASE
              WHEN v_score >= 70 THEN 'Pass'
              ELSE 'Fail'
            END;

Practical Example