LOOP

Oracle PL/SQL Loops: Iterative Control Structures

Iterative control structures, commonly known as loops, are fundamental constructs in any programming language. In PL/SQL, loops allow developers to execute a sequence of statements multiple times, enabling the automation of repetitive tasks and the processing of large datasets (Oracle, 2025a). Understanding the different types of loops and their appropriate use cases is essential for writing efficient and maintainable database code.

PL/SQL provides four distinct types of loop statements: the basic (or infinite) loop, the WHILE loop, the numeric FOR loop, and the cursor FOR loop (Oracle, 2025a). This article examines each type, their syntax, and the mechanisms for controlling loop execution.

1. The Basic LOOP

The simplest form of loop is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP. With each iteration, the sequence of statements is executed, and then control resumes at the top of the loop (Oracle, 2025a). Because the loop has no inherent termination condition, it must be exited explicitly using an EXIT, GOTO, or RAISE statement.

LOOP
    sequence_of_statements;
    EXIT WHEN condition;
END LOOP;

The EXIT WHEN statement provides conditional termination. When the Boolean expression in the WHEN clause evaluates to TRUE, the loop is exited immediately, and control passes to the statement following END LOOP (Oracle, 2005). This construct is logically equivalent to an IF condition THEN EXIT; END IF; statement but is more concise and readable.

It is important to note that the condition must be modified within the loop body; otherwise, the loop will never terminate, resulting in an infinite loop (Oracle, 2005).

2. The WHILE LOOP

The WHILE loop associates a Boolean expression with a sequence of statements. Before each iteration, the expression is evaluated. If it yields TRUE, the statements are executed, and control returns to the top of the loop. If the expression yields FALSE or NULL, the loop is bypassed entirely, and control passes to the next statement (Oracle, 2025a).

WHILE condition LOOP
    sequence_of_statements;
END LOOP;

Unlike the numeric FOR loop, the number of iterations through a WHILE loop is unknown until the loop completes. Because the condition is tested at the top of the loop, the sequence of statements may execute zero times if the initial condition is FALSE (Oracle, 2005).

Note: Some programming languages provide a LOOP UNTIL or REPEAT UNTIL structure that tests the condition at the bottom, ensuring the loop executes at least once. In PL/SQL, the equivalent is a basic LOOP with an EXIT WHEN condition (Oracle, 2005).

3. The Numeric FOR LOOP

The numeric FOR loop iterates over a specified range of integers. Unlike the WHILE loop, the number of iterations is known before the loop is entered. The range is evaluated once when the loop is first entered and is never re-evaluated (Oracle, 2025a).

FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;

The loop index (in this example, i) is implicitly declared as a PLS_INTEGER. Its scope is limited to the loop itself, meaning it cannot be referenced outside the loop (Oracle, 2025a). Inside the loop, the index is treated as a constant; it can appear in expressions but cannot be assigned a value.

The REVERSE keyword can be used to iterate from the upper bound down to the lower bound. In a reverse loop, the index is decremented after each iteration (Oracle, 2005).

FOR i IN REVERSE 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || i); -- Prints 10, 9, 8...
END LOOP;

4. The Cursor FOR LOOP

The cursor FOR loop is specifically designed for processing result sets from SQL queries. It implicitly declares its loop index as a %ROWTYPE record, opens the cursor, repeatedly fetches rows into the record, and closes the cursor automatically when all rows have been processed (Oracle, 2025a).

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

The cursor FOR loop is one of the most common and convenient constructs in PL/SQL. It eliminates the need for explicit OPEN, FETCH, and CLOSE statements, reducing boilerplate code. Furthermore, Oracle Database automatically optimizes cursor FOR loops to execute similarly to BULK COLLECT, making them efficient for read-only operations (Kyte, 2008).

However, for large datasets where the loop body performs DML operations (such as UPDATE or DELETE), the row-by-row processing of a cursor FOR loop can become inefficient. In such cases, developers should consider using BULK COLLECT and FORALL to minimize context switches between the PL/SQL and SQL engines (Cleverence, 2026).

5. Loop Labels and Nested Loops

PL/SQL allows loops to be labeled using an undeclared identifier enclosed in double angle brackets (<<label_name>>). Labels are particularly useful when dealing with nested loops, as they allow an EXIT statement to terminate not just the current loop, but any enclosing loop (Oracle, 2025a).

<<outer_loop>>
LOOP
    <<inner_loop>>
    LOOP
        EXIT outer_loop WHEN condition; -- Exits both loops
    END LOOP inner_loop;
END LOOP outer_loop;

When nesting labeled loops, using the ending label name (as shown above) improves readability and helps prevent errors (Oracle, 2005).

6. Performance Considerations: Row-by-Row vs. Bulk Processing

While loops are essential for procedural logic, they are not always the most efficient way to process data. When a loop iterates over many rows and performs a DML operation for each row, the PL/SQL engine must switch to the SQL engine for every iteration. This row-by-row processing, sometimes called "slow-by-slow" processing, can be orders of magnitude slower than a single set-based SQL statement (Kyte, 2008).

For scenarios where a loop is necessary, Oracle provides BULK COLLECT and FORALL to bridge the gap between procedural and set-based processing. BULK COLLECT fetches multiple rows into a collection in a single operation, and FORALL executes a DML statement against all elements of a collection in a single batch (Oracle, 2005).

-- Row-by-row (slower for large datasets)
FOR rec IN (SELECT id, qty FROM t_src) LOOP
    UPDATE t_dst SET qty = qty + rec.qty WHERE id = rec.id;
END LOOP;

-- Bulk processing (faster)
DECLARE
    TYPE t_ids IS TABLE OF t_src.id%TYPE;
    TYPE t_qtys IS TABLE OF t_src.qty%TYPE;
    l_ids  t_ids;
    l_qtys t_qtys;
BEGIN
    SELECT id, qty BULK COLLECT INTO l_ids, l_qtys FROM t_src;
    FORALL i IN 1..l_ids.COUNT
        UPDATE t_dst SET qty = qty + l_qtys(i) WHERE id = l_ids(i);
END;
/

Developers should always measure performance with realistic data and choose the appropriate technique. For simple read-only loops or small datasets, a cursor FOR loop is often sufficient. For large-scale DML operations, bulk processing is essential for acceptable performance (Cleverence, 2026).

References

Cleverence. (2026). Oracle PL/SQL FOR LOOP statement: Syntax, examples, and best practices. https://www.cleverence.com/articles/oracle-documentation/14-33-for-loop-statement-database-5829/

Kyte, T. (2008). On cursor FOR loops. Oracle Magazine. https://asktom.oracle.com/Misc/oramag/on-cursor-for-loops.html

Oracle. (2005). PL/SQL user's guide and reference: LOOP statements (10g). Oracle Help Center. https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/loop_statement.htm

Oracle. (2025a). PL/SQL user's guide and reference: LOOP statements (Release 9.0.1). Oracle Help Center. https://docs.oracle.com/cd/A91202_01/901_doc/appdev.901/a89856/13_elems29.htm

No comments:

Post a Comment

Subscribe to: Posts (Atom)