IF-THEN-ELSE Statements in Oracle PL/SQL

Conditional logic is the foundation of any procedural program. In Oracle PL/SQL, the IF statement evaluates a Boolean condition and executes one sequence of statements or another depending on the result. Three forms of the IF statement are available — IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF — each suited to a different level of conditional complexity. This lesson covers all three forms, short-circuit evaluation, nested IF statements, and the Oracle 23ai enhancements that affect how Boolean conditions interact with SQL.

IF-THEN

The IF-THEN form executes its statement block only when the condition evaluates to TRUE. When the condition evaluates to FALSE or NULL, the block is skipped entirely and execution continues at the first statement after END IF. No alternative action is taken.

IF-THEN-ELSE

The ELSE clause provides an alternative block that executes when the condition evaluates to FALSE or NULL. Exactly one of the two branches always executes — either the THEN block or the ELSE block, never both and never neither.

IF condition THEN
  -- executes when condition is TRUE
ELSE
  -- executes when condition is FALSE or NULL
END IF;

The ELSE clause guarantees that the program always takes an explicit action regardless of the condition result. This is particularly important when the NULL case must be handled — a plain IF-THEN silently does nothing when the condition is NULL, which can leave variables in an undefined state.

IF-THEN-ELSIF

When multiple mutually exclusive conditions must be tested, the ELSIF clause extends the IF statement with additional branches. Conditions are evaluated sequentially from top to bottom. The first branch whose condition evaluates to TRUE executes, and all remaining branches — including any subsequent ELSIF clauses and the final ELSE — are skipped.

Note the spelling carefully — Oracle PL/SQL uses ELSIF, not ELSEIF. Using ELSEIF produces a compile-time error. An IF statement can contain any number of ELSIF clauses. The final ELSE clause is optional but recommended whenever a default action should apply to all unmatched cases.

Boolean Conditions and NULL

PL/SQL uses three-valued logic — a condition can evaluate to TRUE, FALSE, or NULL. NULL in a condition behaves as "unknown" — it is neither true nor false. The practical consequence is that IF NULL THEN does not execute the THEN block. The ELSE block executes instead, or nothing executes if there is no ELSE.

DECLARE
  v_value NUMBER := NULL;
BEGIN
  IF v_value = 10 THEN
    DBMS_OUTPUT.PUT_LINE('Equal to 10');
  ELSIF v_value IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Value is NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not equal to 10');
  END IF;
END;
/

The condition v_value = 10 evaluates to NULL when v_value is NULL — not FALSE. To test for NULL explicitly, use the IS NULL predicate rather than an equality comparison.

Short-Circuit Evaluation

Oracle PL/SQL uses short-circuit evaluation when processing logical expressions with AND and OR. The engine stops evaluating as soon as the overall result is determined, without examining the remaining operands. For OR, evaluation stops as soon as any operand is TRUE. For AND, evaluation stops as soon as any operand is FALSE or NULL.

Short-circuit evaluation prevents runtime errors that would occur if all operands were always evaluated. The following example demonstrates how a potential divide-by-zero error is avoided — when on_hand is zero, the left operand of the OR expression is TRUE, so PL/SQL never evaluates the right operand:

DECLARE
  on_hand  INTEGER := 0;
  on_order INTEGER := 100;
BEGIN
  -- Short-circuit prevents divide-by-zero:
  -- when on_hand = 0, the left operand is TRUE
  -- and the right operand is never evaluated
  IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN
    DBMS_OUTPUT.PUT_LINE('On hand quantity is zero.');
  END IF;
END;
/

Output:

On hand quantity is zero.

If PL/SQL evaluated both operands before applying OR, on_order / on_hand would raise ORA-01476: divisor is equal to zero. Short-circuit evaluation guarantees this never occurs. The same protection applies to AND — if the left operand is FALSE, the right operand is never evaluated regardless of what it contains.

Nested IF Statements

An IF statement can be nested inside the THEN or ELSE block of another IF statement. Nesting allows secondary conditions to be evaluated only when a primary condition is satisfied. Each nested IF requires its own END IF:

IF order_total > 500 THEN
  IF customer_type = 'PREMIUM' THEN
    discount := 0.15;
  ELSE
    discount := 0.10;
  END IF;
ELSE
  discount := 0.05;
END IF;

Deep nesting makes code harder to read. When more than two levels of nesting are required, consider refactoring the logic into a CASE statement or a separate procedure. Each IF clause can have only one ELSE clause — additional alternatives require ELSIF rather than nested IF-ELSE chains.

IF-THEN-ELSIF vs CASE — Choosing the Right Construct

When a single variable or expression is compared against several fixed values, a CASE statement is more readable than a chain of ELSIF clauses. When conditions involve different variables, range checks, or complex Boolean expressions, IF-THEN-ELSIF is more appropriate because each ELSIF clause can evaluate an entirely independent condition.

-- CASE is cleaner when comparing one variable against fixed values
CASE order_status
  WHEN 'PENDING'   THEN process_pending(order_id);
  WHEN 'APPROVED'  THEN process_approved(order_id);
  WHEN 'CANCELLED' THEN process_cancelled(order_id);
  ELSE log_unknown_status(order_id);
END CASE;

-- IF-THEN-ELSIF is more appropriate for independent conditions
IF order_total > 1000 AND customer_type = 'PREMIUM' THEN
  apply_premium_discount(order_id);
ELSIF ship_date < SYSDATE THEN
  flag_overdue_shipment(order_id);
ELSE
  process_standard_order(order_id);
END IF;

Oracle 23ai — BOOLEAN in Conditional Expressions

In Oracle 23ai, BOOLEAN is a native SQL data type. Conditional expressions that produce a BOOLEAN result can now be used directly in SQL contexts — not just inside PL/SQL blocks. This affects how IF conditions interact with SQL queries. A PL/SQL function returning BOOLEAN can now be called directly from SQL without a wrapper:

-- Oracle 23ai -- BOOLEAN return type callable directly from SQL
CREATE OR REPLACE FUNCTION is_premium_customer(
  p_customer_id IN NUMBER
) RETURN BOOLEAN IS
  v_type customers.customer_type%TYPE;
BEGIN
  SELECT customer_type INTO v_type
  FROM   customers
  WHERE  customer_id = p_customer_id;
  RETURN v_type = 'PREMIUM';
END;
/

-- Call directly in SQL without a wrapper function
SELECT customer_id, customer_name
FROM   customers
WHERE  is_premium_customer(customer_id);

Before Oracle 23ai, the WHERE clause above would have raised an error because SQL could not handle a BOOLEAN return type. A wrapper returning VARCHAR2('Y'/'N') or NUMBER(1/0) was required. Oracle 23ai eliminates this workaround entirely.

Summary

The three forms of the PL/SQL IF statement — IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF — provide conditional branching for all levels of complexity. Conditions follow three-valued logic: TRUE, FALSE, and NULL. Test for NULL explicitly with IS NULL rather than equality comparisons. Short-circuit evaluation prevents runtime errors in complex Boolean expressions by stopping evaluation as soon as the result is determined. Use ELSIF — not ELSEIF — for multiple branches, and prefer CASE when a single variable is compared against fixed values. In Oracle 23ai, functions returning BOOLEAN can be called directly from SQL, eliminating the wrapper functions that were previously required for SQL-PL/SQL Boolean interoperability. The next lesson covers building a PL/SQL block with the IF clause through an evaluative simulation.