If Else If Syntax In Oracle

7 min read

The IF-THEN-ELSIF statement is the cornerstone of conditional logic in Oracle’s PL/SQL procedural language. It allows developers to execute specific blocks of code based on whether a Boolean condition evaluates to TRUE, FALSE, or NULL. Think about it: mastering this control structure is essential for writing dynamic stored procedures, functions, triggers, and anonymous blocks that can handle complex business rules and data validation scenarios. Unlike simple IF-THEN or IF-THEN-ELSE constructs, the ELSIF keyword (note the spelling: no 'E' after the 'L') provides a clean, readable way to chain multiple mutually exclusive conditions together without the nesting nightmare of deeply embedded IF statements.

Understanding the Basic Syntax Structure

Before diving into complex examples, it is critical to internalize the exact syntax rules. Oracle PL/SQL is particular about keywords, semicolons, and block termination. A single missing END IF or a misspelled ELSIF will result in a compilation error (typically PLS-00103).

The standard syntax follows this pattern:

IF condition_1 THEN
    -- Statements executed if condition_1 is TRUE
    statement_1;
    statement_2;
ELSIF condition_2 THEN
    -- Statements executed if condition_1 is FALSE and condition_2 is TRUE
    statement_3;
ELSIF condition_3 THEN
    -- Statements executed if condition_1 and condition_2 are FALSE and condition_3 is TRUE
    statement_4;
ELSE
    -- Statements executed if all previous conditions are FALSE or NULL
    statement_5;
END IF;

Key Syntax Rules to Remember:

  • ELSIF vs ELSE IF: This is the most common trap for developers coming from Java, C#, or JavaScript. In PL/SQL, it is one word: ELSIF. Writing ELSE IF creates a nested IF statement requiring an additional END IF, changing the logic flow entirely.
  • THEN is Mandatory: Every IF and ELSIF clause must be immediately followed by the THEN keyword.
  • END IF Termination: The entire structure must close with END IF; (two words, followed by a semicolon).
  • Semicolons: The END IF requires a semicolon. Individual statements inside the blocks require semicolons. The keywords IF, ELSIF, ELSE, and THEN do not take semicolons.

Practical Example: Employee Bonus Calculation

To illustrate the power of this syntax, consider a realistic HR scenario. Here's the thing — we need to calculate a year-end bonus based on an employee’s performance rating and department. This example demonstrates how ELSIF handles mutually exclusive ranges efficiently Practical, not theoretical..

DECLARE
    v_emp_id       employees.employee_id%TYPE := 101;
    v_rating       NUMBER(3,2) := 4.5;
    v_dept_id      employees.department_id%TYPE;
    v_bonus_pct    NUMBER(4,2) := 0;
    v_bonus_amt    NUMBER(10,2) := 0;
    v_salary       employees.salary%TYPE;
BEGIN
    -- Fetch current salary and department
    SELECT salary, department_id
    INTO v_salary, v_dept_id
    FROM employees
    WHERE employee_id = v_emp_id;

    -- Conditional Logic using IF-THEN-ELSIF
    IF v_rating >= 4.5 THEN
        v_bonus_pct := 0.20; -- 20% for outstanding
        DBMS_OUTPUT.PUT_LINE('Performance: Outstanding');
    ELSIF v_rating >= 4.0 THEN
        v_bonus_pct := 0.15; -- 15% for exceeds expectations
        DBMS_OUTPUT.PUT_LINE('Performance: Exceeds Expectations');
    ELSIF v_rating >= 3.0 THEN
        v_bonus_pct := 0.10; -- 10% for meets expectations
        DBMS_OUTPUT.PUT_LINE('Performance: Meets Expectations');
    ELSIF v_rating >= 2.0 THEN
        v_bonus_pct := 0.05; -- 5% for needs improvement
        DBMS_OUTPUT.PUT_LINE('Performance: Needs Improvement');
    ELSE
        v_bonus_pct := 0;    -- 0% for unsatisfactory
        DBMS_OUTPUT.PUT_LINE('Performance: Unsatisfactory');
    END IF;

    -- Department specific adjustment (Nested IF example)
    IF v_dept_id = 80 THEN -- Sales Department
        v_bonus_pct := v_bonus_pct + 0.05; -- Extra 5% for sales
    END IF;

    v_bonus_amt := v_salary * v_bonus_pct;

    DBMS_OUTPUT.PUT_LINE('Calculated Bonus: 
New Releases

Just In

Readers Also Checked

Familiar Territory, New Reads

Thank you for reading about If Else If Syntax In Oracle. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home