Loops (LOOP, WHILE, FOR)

PL/SQL provides three loop constructs: the basic LOOP, WHILE LOOP, and numeric FOR LOOP. Each has a natural use case.

Basic LOOP

The simplest form — repeats until an explicit EXIT or EXIT WHEN is reached:

DECLARE
    v_counter PLS_INTEGER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 5;  -- condition checked at end of loop body
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Loop finished. Counter: ' || v_counter);
END;
/

Output:

Iteration: 1
Iteration: 2
Iteration: 3
Iteration: 4
Iteration: 5
Loop finished. Counter: 6

EXIT vs EXIT WHEN

LOOP
    -- EXIT unconditionally
    EXIT;  -- immediately leaves the loop

    -- EXIT WHEN is more readable than IF ... EXIT
    EXIT WHEN v_counter > 10;
END LOOP;

⚠

A basic LOOP with no EXIT condition is an infinite loop. Always ensure the exit condition will eventually become true.

WHILE LOOP

Checks the condition before each iteration — may execute zero times:

DECLARE
    v_salary  NUMBER := 3000;
    v_years   PLS_INTEGER := 0;
BEGIN
    -- How many years until salary exceeds 10,000 with 8% annual raises?
    WHILE v_salary <= 10000 LOOP
        v_salary := v_salary * 1.08;
        v_years  := v_years + 1;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(
        'Reaches $10,000 after ' || v_years || ' years. Final: $' ||
        ROUND(v_salary, 2)
    );
END;
/

Output:

Reaches $10,000 after 16 years. Final: $10,264.11

Numeric FOR LOOP

Iterates over a fixed integer range. The loop variable is declared implicitly — do not declare it in DECLARE:

BEGIN
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('i = ' || i);
    END LOOP;
END;
/

REVERSE

Counts down from upper bound to lower bound:

BEGIN
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Countdown: ' || i);
    END LOOP;
END;
/

Output:

Countdown: 5
Countdown: 4
Countdown: 3
Countdown: 2
Countdown: 1

ℹ

The numeric FOR loop counter is read-only inside the loop body. You cannot assign to it: i := i + 1 causes a compile error.

CONTINUE and CONTINUE WHEN

Skip the rest of the current iteration and jump to the next:

BEGIN
    FOR i IN 1..10 LOOP
        CONTINUE WHEN MOD(i, 2) = 0;  -- skip even numbers
        DBMS_OUTPUT.PUT_LINE('Odd: ' || i);
    END LOOP;
END;
/

Output:

Odd: 1
Odd: 3
Odd: 5
Odd: 7
Odd: 9

Nested Loops with Labels

Labels identify which loop an EXIT or CONTINUE targets:

BEGIN
    <<outer_loop>>
    FOR dept IN 10..30 LOOP
        FOR emp_rank IN 1..5 LOOP
            IF dept = 20 AND emp_rank = 3 THEN
                -- Exit the OUTER loop, not just the inner one
                EXIT outer_loop;
            END IF;
            DBMS_OUTPUT.PUT_LINE('Dept ' || dept || ', Rank ' || emp_rank);
        END LOOP;
    END LOOP outer_loop;

    DBMS_OUTPUT.PUT_LINE('Done.');
END;
/

Practical Example: Process Employees in Batches

DECLARE
    v_batch_size  PLS_INTEGER := 10;
    v_start_id    PLS_INTEGER := 100;
    v_end_id      PLS_INTEGER := 120;
    v_processed   PLS_INTEGER := 0;
    v_name        VARCHAR2(100);
BEGIN
    FOR v_id IN v_start_id..v_end_id LOOP
        BEGIN
            SELECT first_name || ' ' || last_name
            INTO   v_name
            FROM   employees
            WHERE  employee_id = v_id;

            v_processed := v_processed + 1;
            DBMS_OUTPUT.PUT_LINE('Processed: ' || v_name);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;  -- skip missing IDs silently
        END;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Total processed: ' || v_processed);
END;
/

GOTO Statement

GOTO jumps to a labeled statement. Its use is generally discouraged — it makes code hard to follow. Limited legal uses: jumping out of a deeply nested construct or bypassing code after an error check without restructuring:

DECLARE
    v_count PLS_INTEGER := 0;
BEGIN
    <<start_over>>
    v_count := v_count + 1;

    IF v_count < 3 THEN
        DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
        GOTO start_over;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Done at: ' || v_count);
END;
/

⚠

GOTO cannot jump into an IF, loop body, or sub-block. It can only jump forward in the same block or to an enclosing block. Prefer loops and EXIT — GOTO in production code is a red flag.

Which loop type to choose?

Basic LOOP:

You need to test the exit condition inside the body (after some work).
Number of iterations is unknown and the condition involves mid-body state.

WHILE LOOP:

You need to test before the first iteration.
Zero iterations is a valid outcome.
Classic "keep processing until done" pattern.

Numeric FOR LOOP:

You know the exact iteration range at the start of the loop.
Processing each item in a cursor result set (cursor FOR loop — see Explicit Cursors).
Counting, generating sequences, processing arrays by index.

Summary

LOOP ... EXIT WHEN ... END LOOP — most flexible, runs at least once.
WHILE cond LOOP ... END LOOP — condition tested first; may run zero times.
FOR i IN low..high LOOP — range is fixed; i is implicit and read-only.
REVERSE counts down; CONTINUE/CONTINUE WHEN skips to the next iteration.
Label loops with <<name>> to target EXIT and CONTINUE in nested loops.
Avoid GOTO — use structured loops and EXIT instead.