How to Write Control Structures in PL/SQL for Oracle 23ai

Control structures are the mechanisms that give a PL/SQL program its decision-making and repetition capabilities. Without them, a PL/SQL block executes its statements once, in sequence, from top to bottom. Control structures allow you to branch conditionally, repeat a block of statements while a condition holds, iterate over a fixed range or a cursor result set, and transfer control to a labeled point in the program. This module covers all of these structures in detail. By the end of it you will be able to:

1. Build conditional logic using IF-THEN-ELSE and ELSIF statements
2. Begin and end a simple loop in a PL/SQL block
3. Write FOR and WHILE loops
4. Define uses for labels and GOTO statements

Conditional Control Structures

Conditional control structures evaluate a Boolean expression and execute different code paths depending on whether the expression is true, false, or null. Oracle PL/SQL provides three conditional constructs: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

IF-THEN

The simplest form executes a block of statements only when the condition evaluates to TRUE. If the condition is FALSE or NULL, the block is skipped entirely and execution continues after END IF:

IF condition THEN
  -- statements execute only when condition is TRUE
END IF;

IF-THEN-ELSE

The ELSE branch executes when the condition is FALSE or NULL. Exactly one of the two branches always executes:

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

IF-THEN-ELSIF

Multiple conditions are evaluated in sequence. The first branch whose condition evaluates to TRUE executes, and the remaining branches are skipped. The optional ELSE clause handles all cases not matched by any ELSIF:

IF condition1 THEN
  -- executes when condition1 is TRUE
ELSIF condition2 THEN
  -- executes when condition2 is TRUE
ELSIF condition3 THEN
  -- executes when condition3 is TRUE
ELSE
  -- executes when no condition above is TRUE
END IF;

Note the spelling — Oracle PL/SQL uses ELSIF, not ELSEIF. Using ELSEIF produces a compile-time error.

CASE Expressions and CASE Statements

Oracle PL/SQL supports two forms of CASE: the CASE expression, which returns a value and forms part of a larger statement, and the CASE statement, which executes a block of statements. Both forms exist in simple and searched variants.

Simple CASE

The simple form uses a selector — an expression whose value is compared against each WHEN clause in sequence. The first WHEN value that matches the selector determines the result. Subsequent WHEN clauses are not evaluated. If no match is found, the optional ELSE clause applies:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END;

Searched CASE

The searched form evaluates each WHEN clause as a full Boolean condition rather than comparing against a single selector. This makes it suitable for range checks and complex conditions that a simple selector cannot express:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END;

CASE Expression Example — Grade Appraisal

The following example assigns a text appraisal to a letter grade using a simple CASE expression. The selector is grade, and the first matching WHEN clause determines the value assigned to appraisal:

DECLARE
  grade      CHAR(1)      := 'B';
  appraisal  VARCHAR2(20);
BEGIN
  appraisal :=
    CASE grade
      WHEN 'A' THEN 'Excellent'
      WHEN 'B' THEN 'Very Good'
      WHEN 'C' THEN 'Good'
      WHEN 'D' THEN 'Fair'
      WHEN 'F' THEN 'Poor'
      ELSE 'No such grade'
    END;

  DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || appraisal);
END;
/

Output:

Grade B is Very Good

CASE and BOOLEAN in Oracle 23ai

Before Oracle 23ai, BOOLEAN existed only as a PL/SQL data type. It could not be used as a table column type, could not appear in a SQL SELECT list, and PL/SQL functions returning BOOLEAN could not be called directly from SQL. A wrapper function returning NUMBER (0 or 1) or VARCHAR2 ('Y' or 'N') was required to bridge the gap.

Oracle 23ai promotes BOOLEAN to a native SQL data type. The practical impact on CASE expressions is significant — a CASE expression can now return TRUE or FALSE directly in a SQL context without any wrapper:

-- Oracle 23ai -- BOOLEAN result from CASE is valid in SQL
SELECT product_name,
       CASE
         WHEN unit_price > 100 THEN TRUE
         ELSE FALSE
       END AS is_premium
FROM   products;

Before Oracle 23ai, this query would have required 'Y' and 'N' or 1 and 0 as the THEN and ELSE values. In Oracle 23ai, TRUE, FALSE, and NULL are valid SQL literals, and BOOLEAN columns can be defined in CREATE TABLE. PL/SQL functions with BOOLEAN return types can now be called directly from SQL SELECT statements, eliminating an entire category of wrapper functions that existed solely to work around this limitation.

Iterative Control Structures

Iterative control structures repeat a block of statements. Oracle PL/SQL provides four loop constructs, each suited to a different use case.

Basic LOOP

The basic loop has no built-in termination condition. It repeats indefinitely until an EXIT or EXIT WHEN statement is reached. This form is appropriate when the number of iterations is not known in advance and the exit condition arises from logic inside the loop body:

LOOP
  -- statements
  EXIT WHEN condition;
END LOOP;

WHILE LOOP

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:

WHILE condition LOOP
  -- statements
END LOOP;

FOR LOOP

The FOR loop iterates over a fixed integer range. The loop counter is implicitly declared by Oracle — you do not declare it in the DECLARE section. It is a PLS_INTEGER scoped entirely to the loop and increments by one on each iteration:

FOR counter IN start_value .. end_value LOOP
  -- statements
END LOOP;

The REVERSE keyword iterates from the upper bound down to the lower bound. Note that the range is still written low to high — Oracle handles the reversal internally:

FOR counter IN REVERSE start_value .. end_value LOOP
  -- statements
END LOOP;

Cursor FOR LOOP

The cursor FOR loop is the most concise way to process a query result set in PL/SQL. Oracle opens the cursor, fetches each row into an implicitly declared record variable, and closes the cursor automatically when all rows have been processed:

FOR record IN cursor_name LOOP
  -- access record.column_name
END LOOP;

An inline query can replace the cursor name, eliminating the need for an explicit cursor declaration entirely:

FOR rec IN (SELECT employee_id, last_name FROM employees) LOOP
  DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' ' || rec.last_name);
END LOOP;

Loop Control Statements

Three statements modify loop execution without ending the enclosing block:

EXIT and EXIT WHEN

EXIT terminates the current loop immediately. EXIT WHEN terminates the loop only when the specified condition is TRUE, combining the condition check and the exit into a single readable statement:

EXIT;                  -- unconditional exit
EXIT WHEN condition;   -- conditional exit

CONTINUE and CONTINUE WHEN

CONTINUE skips the remainder of the current iteration and proceeds to the next. CONTINUE WHEN applies the skip conditionally. Both statements were introduced in Oracle 11g and remain fully supported in Oracle 23ai:

CONTINUE;                  -- skip to next iteration unconditionally
CONTINUE WHEN condition;   -- skip to next iteration when condition is TRUE

Transfer of Control — GOTO and Labels

The GOTO statement transfers control unconditionally to a labeled statement within the same block or subprogram. Labels are identifiers enclosed in double angle brackets placed immediately before an executable statement:

GOTO skip_section;

-- This code is bypassed
DBMS_OUTPUT.PUT_LINE('This line is skipped');

<<skip_section>>
DBMS_OUTPUT.PUT_LINE('Resumed after GOTO');

Labels also serve a second purpose — qualifying variable names in nested blocks where the same identifier is declared at multiple scoping levels. This use of labels is well-accepted and does not involve GOTO. The GOTO statement itself is supported in Oracle 23ai but discouraged in new code. Every practical use case it serves is better handled by EXIT, CONTINUE, RETURN, or structured conditional logic. Lesson 6 of this module covers both uses of labels and the full set of GOTO transfer rules in detail.

Summary

Oracle PL/SQL provides a complete set of control structures for conditional branching and iterative processing. Conditional structures — IF-THEN, IF-THEN-ELSE, IF-THEN-ELSIF, and CASE — evaluate Boolean conditions to select which code path executes. Iterative structures — basic LOOP, WHILE, FOR, and cursor FOR — repeat blocks of statements until a termination condition is met. EXIT and CONTINUE provide fine-grained control within loops. In Oracle 23ai, native SQL BOOLEAN support expands the utility of CASE expressions beyond PL/SQL into SQL queries, eliminating the wrapper functions that were previously required. The next lesson covers building conditional logic using IF-THEN-ELSE statements in detail.