PL/SQL Control Structures — Module Summary

This module covered the complete set of control structures available in Oracle PL/SQL — the mechanisms that give a program its decision-making and repetition capabilities. The following sections summarize the key concepts from each lesson.

Lesson 1 — Introduction to Control Structures

A PL/SQL block without control structures executes its statements exactly once, in sequence. Control structures change that behavior in two fundamental ways: conditional structures select which statements execute based on a Boolean condition, and iterative structures repeat a block of statements until a termination condition is met.

Oracle PL/SQL provides conditional constructs — IF-THEN, IF-THEN-ELSE, IF-THEN-ELSIF, and CASE — and iterative constructs — basic LOOP, WHILE, FOR, and cursor FOR. Loop control statements — EXIT, EXIT WHEN, CONTINUE, and CONTINUE WHEN — provide fine-grained control within loops. The GOTO statement and block labels complete the set.

A significant Oracle 23ai enhancement runs through this entire module: BOOLEAN is now a native SQL data type. Before 23ai, it existed only in PL/SQL. This means CASE expressions can return TRUE or FALSE directly in SQL queries, and PL/SQL functions with BOOLEAN return types can be called directly from SQL SELECT statements without wrapper functions.

Lesson 2 — IF-THEN-ELSE and ELSIF Statements

The three forms of the IF statement handle increasing levels of conditional complexity. IF-THEN executes its block only when the condition is TRUE — when the condition is FALSE or NULL, the block is silently skipped. IF-THEN-ELSE guarantees that exactly one of two branches always executes. IF-THEN-ELSIF evaluates multiple independent conditions sequentially — the first matching branch executes and all remaining branches are skipped.

PL/SQL uses three-valued logic: conditions evaluate to TRUE, FALSE, or NULL. A NULL condition behaves as unknown — it does not execute the THEN block. Test for NULL explicitly with IS NULL rather than equality comparisons. Short-circuit evaluation stops evaluating a logical expression as soon as the result is determined — this prevents runtime errors such as divide-by-zero that would occur if all operands were always evaluated.

The keyword is ELSIF — not ELSEIF and not ELSE IF. Using either incorrect form produces a compile-time error. Each IF clause can have only one ELSE clause — additional alternatives require ELSIF.

Lesson 3 — Building a PL/SQL Block with an IF Clause

The commission calculator applied IF-THEN-ELSIF to a four-tier business rule: sale values below $100 earn 10%, $100–$499 earn 15%, $500–$999 earn 20%, and $1,000 or above earn 25%. The key insight is that compound AND conditions like v_sale_value > 100 AND v_sale_value < 500 are unnecessary — because conditions are evaluated top to bottom and the first match exits the construct, any value reaching the second ELSIF is already known to be 100 or above.

The searched CASE statement is the preferred alternative when conditional logic assigns a single value from a set of discrete alternatives. The CASE expression assigns the result directly to the variable in a single construct, eliminating the need for a separate assignment statement in each branch:

v_commission_percent :=
  CASE
    WHEN v_sale_value < 100  THEN 10
    WHEN v_sale_value < 500  THEN 15
    WHEN v_sale_value < 1000 THEN 20
    ELSE 25
  END;

Lesson 4 — Beginning and Ending a PL/SQL Loop

The basic LOOP statement repeats indefinitely until an explicit exit condition is reached. Every basic loop must contain at least one reachable EXIT or EXIT WHEN — a loop without one is an infinite loop that Oracle does not detect at compile time.

EXIT terminates the loop unconditionally. EXIT WHEN condition terminates the loop when the condition is TRUE and is the preferred form — it replaces the verbose IF condition THEN EXIT; END IF; pattern with a single readable line. Exit placement within the loop body determines which statements execute on the final iteration — statements after the exit point are bypassed when the condition fires.

The searched CASE expression introduced in this lesson demonstrated that a WHEN clause can call a Boolean function, test independent conditions with OR, or compare a single variable against fixed values — all within a single CASE construct. The correct idiom for negating a Boolean function return is NOT function_name(), not function_name() = FALSE, which is not valid Oracle syntax prior to 23ai.

Lesson 5 — FOR and WHILE Loops

The FOR loop iterates over a fixed integer range. The loop counter is implicitly declared by Oracle, read-only within the loop body, and scoped entirely to the loop. The range is evaluated once at loop entry and never re-evaluated. If the lower bound exceeds the upper bound, the loop body never executes. Use REVERSE to iterate from upper to lower bound — the range is still written low to high.

The cursor FOR loop extends this pattern to query result sets. Oracle opens the cursor, fetches each row into an implicitly declared record, and closes the cursor automatically — eliminating the risk of an unclosed cursor and reducing boilerplate to a minimum.

The WHILE loop evaluates its condition before each iteration. If the condition is FALSE or NULL on the first evaluation, the loop body never executes. The number of iterations is unknown in advance — the programmer must ensure the condition eventually becomes FALSE to prevent an infinite loop. A NULL condition terminates the loop, consistent with three-valued logic, but can produce unexpected early termination if loop variables can become NULL.

Choosing between loop constructs: use FOR when the iteration count is known before the loop begins, WHILE when the termination condition precedes the body, and basic LOOP when exit logic must execute inside the body before the condition can be evaluated.

Lesson 6 — Labels and the GOTO Statement

PL/SQL labels serve two legitimate purposes. The first is variable scoping — when the same identifier is declared in both an enclosing block and a nested block, qualifying the outer variable with the enclosing block's label name makes both accessible simultaneously:

<<outer_block>>
DECLARE
  start_date DATE;
BEGIN
  <<inner_block>>
  DECLARE
    start_date DATE;
  BEGIN
    IF start_date = outer_block.start_date THEN ...
    END IF;
  END inner_block;
END outer_block;

The second purpose is naming loops so that EXIT and CONTINUE can target a specific nesting level in nested loops — a well-accepted use that does not involve GOTO.

The GOTO statement transfers control unconditionally to a labeled statement within the same block or subprogram. Oracle 23ai continues to support it for backward compatibility but its use in new code is strongly discouraged. Every use case GOTO serves is better handled by EXIT, CONTINUE, RETURN, or structured conditional logic. GOTO target validation occurs at compile time — invalid transfers produce compile-time errors, not runtime errors.

Module Glossary

PL/SQL Control Structures — Quiz