Oracle PL/SQL IF ELSE IF is the conditional control structure that lets your blocks make decisions at runtime — choosing which code to run based on whether a condition is TRUE, FALSE, or unknown. In PL/SQL the "else if" keyword is written as ELSIF (not ELSEIF or ELSE IF), and it forms a ladder of mutually exclusive branches that Oracle evaluates from top to bottom until one condition succeeds. Mastering this structure, along with its NULL behavior, nesting rules, and its relationship to the CASE statement, is one of the fastest ways to write cleaner, faster, and more reliable database logic.
What Is the IF ELSE IF Statement in Oracle PL/SQL?
Conditional statements are the decision-making engine of any procedural language. Inside an anonymous block, a stored procedure, a function, a trigger, or a package body, the IF statement allows your code to branch: if this is true, do that; otherwise, do something else.
PL/SQL supports three forms:
- IF – THEN — runs code
only when the condition is TRUE; if FALSE or NULL, nothing happens.
2. 3. IF – THEN – ELSE — provides a fallback path when the condition is FALSE or NULL.
IF – THEN – ELSIF – ELSE — builds a ladder of checks, letting you choose one branch among many.
The third form is the focus of this article. Its syntax is:
IF condition1 THEN
statements;
ELSIF condition2 THEN
statements;
ELSIF condition3 THEN
statements;
...
ELSE
statements;
END IF;
Oracle evaluates condition1 first. This process repeats until a TRUE condition is found. Because of that, if condition1 is FALSE or NULL, control moves to the next ELSIF. If it is TRUE, the first branch runs and the rest of the ladder is skipped. If none of the conditions evaluate to TRUE, the optional ELSE branch executes. If there is no ELSE and no condition is TRUE, no action is taken That's the whole idea..
A common pitfall is writing ELSEIF or ELSE IF instead of the correct single-word ELSIF. Oracle will raise a compilation error for misspelled keywords, so pay attention to the exact spelling Turns out it matters..
NULL Behavior in Conditions
In PL/SQL, a condition is a Boolean expression that can be TRUE, FALSE, or NULL. The NULL state arises when the expression contains an uninitialised variable, a comparison with a NULL value, or an arithmetic operation involving NULL. Take this: salary > 5000 returns NULL if salary itself is NULL.
The IF‑ELSIF ladder treats NULL exactly like FALSE: the branch is not taken. So in practice, if your data contains NULLs, you must decide explicitly how to handle them. Typically you either test for NULL first (using IS NULL or IS NOT NULL) or you provide an ELSE clause that captures all remaining cases, including NULL.
IF v_salary > 5000 THEN
v_grade := 'A';
ELSIF v_salary > 3000 THEN
v_grade := 'B';
ELSIF v_salary > 1000 THEN
v_grade := 'C';
ELSE
v_grade := 'D'; -- catches NULL and low salaries
END IF;
In this example, a NULL salary would fall into the ELSE branch and be graded 'D', which may or may not be the desired business rule. Always verify that the logic matches the intended handling of missing data Not complicated — just consistent. Took long enough..
Nesting IF Statements
PL/SQL allows you to nest any IF construct inside another. Each complete IF‑THEN‑[ELSIF‑...In real terms, ]‑[ELSE]‑END IF must be closed before the outer block continues. Proper indentation is essential for readability and for spotting mismatched keywords.
IF v_department = 'SALES' THEN
IF v_salary > 5000 THEN
v_bonus := 1000;
ELSE
v_bonus := 500;
END IF;
ELSIF v_department = 'HR' THEN
v_bonus := 750;
END IF;
Deeply nested ladders can become hard to follow. If you find yourself nesting more than two or three levels, consider refactoring into a separate procedure, a CASE statement, or a lookup table Worth keeping that in mind..
IF‑ELSIF vs. CASE
Oracle’s CASE statement offers a compact alternative for multi‑branch selection:
v_grade := CASE
WHEN v_salary > 5000 THEN 'A'
WHEN v_salary > 3000 THEN 'B'
WHEN v_salary > 1000 THEN 'C'
ELSE 'D'
END;
CASE evaluates each WHEN condition in order and returns the result of the first TRUE branch. It is particularly useful when you need to assign a single value
CASE evaluates each WHEN condition in order and returns the result of the first TRUE branch. And it is particularly useful when you need to assign a single value based on conditional logic. Unlike nested IF statements, CASE avoids the indentation nightmare and often performs slightly better due to optimized evaluation. Also worth noting, CASE can be used in SQL statements, making it versatile across PL/SQL and pure SQL contexts Simple, but easy to overlook..
Understanding when to use IF-ELSIF versus CASE is a fundamental skill for writing clean, maintainable PL/SQL. While IF excels when conditions involve complex logic, side effects, or multiple statements per branch, CASE shines in value assignment and simple comparisons. Both constructs are indispensable, and choosing the right one depends on the specific requirements of the code block.
In this article, we explored the structure and nuances of PL/SQL conditional control, from the basic IF statement to the multi-branch IF-ELSIF ladder, the critical handling of NULL values, and the pitfalls of nested constructs. That's why we also compared IF-ELSIF with the streamlined CASE statement, highlighting their respective strengths and appropriate use cases. In practice, by paying attention to keyword spelling, explicitly managing NULLs, and keeping nesting depth reasonable, developers can write strong PL/SQL code that is both efficient and easy to maintain. Whether assigning grades, calculating bonuses, or routing logic flow, mastering these conditional tools is essential for any Oracle professional.