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:
ELSIFvsELSE IF: This is the most common trap for developers coming from Java, C#, or JavaScript. In PL/SQL, it is one word:ELSIF. WritingELSE IFcreates a nestedIFstatement requiring an additionalEND IF, changing the logic flow entirely.THENis Mandatory: EveryIFandELSIFclause must be immediately followed by theTHENkeyword.END IFTermination: The entire structure must close withEND IF;(two words, followed by a semicolon).- Semicolons: The
END IFrequires a semicolon. Individual statements inside the blocks require semicolons. The keywordsIF,ELSIF,ELSE, andTHENdo 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: