Conditional Statements (IF/CASE)

PL/SQL provides IF statements and CASE statements (and expressions) to branch execution based on conditions. Both support NULL-aware three-valued logic.

IF / THEN / ELSIF / ELSE / END IF

DECLARE
    v_salary  employees.salary%TYPE;
    v_grade   VARCHAR2(10);
BEGIN
    SELECT salary INTO v_salary
    FROM   employees
    WHERE  employee_id = 107;

    IF v_salary >= 15000 THEN
        v_grade := 'Senior';
    ELSIF v_salary >= 8000 THEN
        v_grade := 'Mid';
    ELSIF v_salary >= 4000 THEN
        v_grade := 'Junior';
    ELSE
        v_grade := 'Entry';
    END IF;

    DBMS_OUTPUT.PUT_LINE('Salary: $' || v_salary || ' — Grade: ' || v_grade);
END;
/

Rules:

• ELSIF (one word, no space) chains additional conditions.
• ELSE handles everything that did not match.
• End every IF block with END IF;.
• Conditions evaluate left-to-right; the first TRUE branch executes, the rest are skipped.

Nested IF Statements

DECLARE
    v_dept_id   employees.department_id%TYPE := 80;
    v_salary    employees.salary%TYPE        := 12000;
BEGIN
    IF v_dept_id = 80 THEN
        IF v_salary > 10000 THEN
            DBMS_OUTPUT.PUT_LINE('Sales — High performer');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Sales — Standard');
        END IF;
    ELSIF v_dept_id = 90 THEN
        DBMS_OUTPUT.PUT_LINE('Executive office');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Other department');
    END IF;
END;
/

NULL Semantics in Conditionals

In three-valued logic, NULL comparisons do not yield TRUE or FALSE — they yield NULL, which is treated as FALSE in a condition:

DECLARE
    v_bonus  NUMBER := NULL;
BEGIN
    IF v_bonus > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Has bonus');  -- never prints
    ELSIF v_bonus = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Zero bonus'); -- never prints
    ELSE
        DBMS_OUTPUT.PUT_LINE('No bonus or NULL');  -- this branch runs
    END IF;

    -- Correct NULL check
    IF v_bonus IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('Bonus is NULL');
    END IF;
END;
/

Simple CASE Statement

Compares a single selector against a list of values:

DECLARE
    v_job_id  employees.job_id%TYPE;
    v_category VARCHAR2(30);
BEGIN
    SELECT job_id INTO v_job_id
    FROM   employees WHERE employee_id = 100;

    CASE v_job_id
        WHEN 'AD_PRES'  THEN v_category := 'Executive';
        WHEN 'AD_VP'    THEN v_category := 'Vice President';
        WHEN 'IT_PROG'  THEN v_category := 'Technical';
        WHEN 'SA_REP'   THEN v_category := 'Sales';
        ELSE                 v_category := 'Other';
    END CASE;

    DBMS_OUTPUT.PUT_LINE(v_job_id || ' → ' || v_category);
END;
/

Searched CASE Statement

Evaluates independent Boolean conditions — more flexible than simple CASE:

DECLARE
    v_salary  NUMBER := 17000;
    v_region  VARCHAR2(20) := 'EMEA';
    v_result  VARCHAR2(50);
BEGIN
    CASE
        WHEN v_salary > 20000 AND v_region = 'US' THEN
            v_result := 'US Top Earner';
        WHEN v_salary > 15000 THEN
            v_result := 'High Earner';
        WHEN v_salary BETWEEN 8000 AND 15000 THEN
            v_result := 'Mid Earner';
        ELSE
            v_result := 'Standard';
    END CASE;

    DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

CASE Expression vs CASE Statement

A CASE expression returns a value and can appear anywhere a value is expected (assignment, SQL query):

DECLARE
    v_salary  NUMBER := 12000;
    v_grade   VARCHAR2(10);
BEGIN
    -- CASE expression on the right side of assignment
    v_grade := CASE
                   WHEN v_salary >= 15000 THEN 'Senior'
                   WHEN v_salary >=  8000 THEN 'Mid'
                   ELSE 'Junior'
               END;

    DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
END;
/

CASE expressions can also appear directly in SQL:

SELECT
    first_name,
    salary,
    CASE
        WHEN salary >= 15000 THEN 'Senior'
        WHEN salary >=  8000 THEN 'Mid'
        ELSE 'Junior'
    END AS grade
FROM employees
ORDER BY salary DESC;

Real-World Example: Salary Adjustment Logic

CREATE OR REPLACE PROCEDURE adjust_salary(
    p_employee_id IN employees.employee_id%TYPE
) IS
    v_salary    employees.salary%TYPE;
    v_job_id    employees.job_id%TYPE;
    v_increase  NUMBER(5,2);
BEGIN
    SELECT salary, job_id
    INTO   v_salary, v_job_id
    FROM   employees
    WHERE  employee_id = p_employee_id;

    -- Searched CASE for raise percentage
    v_increase := CASE
                      WHEN v_job_id LIKE 'AD_%' THEN 5
                      WHEN v_job_id LIKE 'SA_%' AND v_salary < 8000 THEN 10
                      WHEN v_job_id LIKE 'IT_%' THEN 8
                      ELSE 3
                  END;

    IF v_salary * (1 + v_increase/100) > 25000 THEN
        DBMS_OUTPUT.PUT_LINE('Cap reached — no raise for emp ' || p_employee_id);
    ELSE
        UPDATE employees
        SET    salary = salary * (1 + v_increase/100)
        WHERE  employee_id = p_employee_id;

        DBMS_OUTPUT.PUT_LINE(
            'Emp ' || p_employee_id ||
            ' raised by ' || v_increase || '%'
        );
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || p_employee_id || ' not found.');
END adjust_salary;
/

Summary

• IF / ELSIF / ELSE / END IF for general multi-way branching.
• Simple CASE for a single variable vs. a list of values.
• Searched CASE for independent conditions — also usable as a value-returning expression.
• NULL comparisons always return NULL (not TRUE/FALSE) — use IS NULL / IS NOT NULL.
• A CASE statement without an ELSE raises CASE_NOT_FOUND if no WHEN matches.