IF-THEN-ELSIF-ELSE evaluates multiple boolean conditions in sequence and runs the statements for the first condition that is TRUE. It is equivalent to nested IF-THEN-ELSE statements but requires only one END IF.
Syntax
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements ] ...]
[ ELSE
statements ]
END IF;How it works
Conditions are evaluated top to bottom. The first boolean-expression is always evaluated. Each subsequent boolean-expression is evaluated only if all preceding conditions are FALSE.
When a condition evaluates to TRUE, the associated statements run and execution skips all remaining conditions. If no condition is TRUE, the ELSE branch runs (if present).
ELSIF vs. nested IF-THEN-ELSE
IF-THEN-ELSIF-ELSE is easier to read than logically equivalent nested IF-THEN-ELSE statements, and requires only one END IF:
-- Using IF-THEN-ELSIF-ELSE
IF condition_1 THEN
statements_1;
ELSIF condition_2 THEN
statements_2;
ELSIF condition_3 THEN
statements_3;
END IF;
-- Equivalent nested IF-THEN-ELSE (requires one END IF per IF)
IF condition_1 THEN
statements_1;
ELSE
IF condition_2 THEN
statements_2;
ELSE
IF condition_3 THEN
statements_3;
END IF;
END IF;
END IF;Examples
Classify a numeric value
This example classifies a number as positive, negative, or zero. The ELSE branch handles the remaining case — a NULL value.
IF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSIF number = 0 THEN
result := 'zero';
ELSE
-- The only remaining possibility: number is NULL
result := 'NULL';
END IF;Count employees by compensation range
This example uses a cursor to iterate over the emp table and counts employees by yearly compensation in USD 25,000 increments.
DECLARE
v_empno emp.empno%TYPE;
v_comp NUMBER(8,2);
v_lt_25K SMALLINT := 0;
v_25K_50K SMALLINT := 0;
v_50K_75K SMALLINT := 0;
v_75K_100K SMALLINT := 0;
v_ge_100K SMALLINT := 0;
CURSOR emp_cursor IS SELECT empno, (sal + NVL(comm,0)) * 24 FROM emp;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno, v_comp;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_comp < 25000 THEN
v_lt_25K := v_lt_25K + 1;
ELSIF v_comp < 50000 THEN
v_25K_50K := v_25K_50K + 1;
ELSIF v_comp < 75000 THEN
v_50K_75K := v_50K_75K + 1;
ELSIF v_comp < 100000 THEN
v_75K_100K := v_75K_100K + 1;
ELSE
v_ge_100K := v_ge_100K + 1;
END IF;
END LOOP;
CLOSE emp_cursor;
DBMS_OUTPUT.PUT_LINE('Number of employees by yearly compensation');
DBMS_OUTPUT.PUT_LINE('Less than 25,000 : ' || v_lt_25K);
DBMS_OUTPUT.PUT_LINE('25,000 - 49,9999 : ' || v_25K_50K);
DBMS_OUTPUT.PUT_LINE('50,000 - 74,9999 : ' || v_50K_75K);
DBMS_OUTPUT.PUT_LINE('75,000 - 99,9999 : ' || v_75K_100K);
DBMS_OUTPUT.PUT_LINE('100,000 and over : ' || v_ge_100K);
END;The output:
Number of employees by yearly compensation
Less than 25,000 : 2
25,000 - 49,9999 : 5
50,000 - 74,9999 : 6
75,000 - 99,9999 : 0
100,000 and over : 1Each ELSIF threshold is tested only if all preceding conditions are FALSE. For example, when v_comp is 30,000: the first condition (v_comp < 25000) is FALSE, so the second condition (v_comp < 50000) is evaluated and is TRUE — v_25K_50K is incremented and no further conditions are tested.