PolarDB:Loops

LOOP

LOOP
    statements
END LOOP;

LOOP defines an unconditional loop that repeats indefinitely. Use EXIT or RETURN to terminate it.

EXIT

EXIT [ WHEN expression ];

EXIT terminates the innermost loop and passes control to the statement after END LOOP.

• With WHEN: the loop exits only when the specified condition is true. If the condition evaluates to false, control passes to the statement after EXIT and the loop continues.

• Without WHEN: the loop exits unconditionally.

EXIT works with all loop types, not just unconditional loops.

The following example iterates ten times and exits using EXIT WHEN:

DECLARE
    v_counter       NUMBER(2);
BEGIN
    v_counter := 1;
    LOOP
        EXIT WHEN v_counter > 10;
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;

Output:

Iteration # 1
Iteration # 2
Iteration # 3
Iteration # 4
Iteration # 5
Iteration # 6
Iteration # 7
Iteration # 8
Iteration # 9
Iteration # 10

CONTINUE

CONTINUE [ WHEN expression ];

CONTINUE skips the remaining statements in the current iteration and begins the next one. Control passes back to the loop control expression (if any), which is re-evaluated before the loop body runs again.

• With WHEN: the skip occurs only when the specified condition is true.

• Without WHEN: the skip is unconditional.

CONTINUE cannot be used outside a loop.

The following example uses CONTINUE WHEN to skip odd numbers and print only even ones:

DECLARE
    v_counter       NUMBER(2);
BEGIN
    v_counter := 0;
    LOOP
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10;
        CONTINUE WHEN MOD(v_counter,2) = 1;
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
    END LOOP;
END;

Output:

Iteration # 2
Iteration # 4
Iteration # 6
Iteration # 8
Iteration # 10

WHILE

WHILE expression LOOP
    statements
END LOOP;

WHILE repeats a sequence of statements as long as the condition is true. The condition is checked before each entry into the loop body — if it is false on the first evaluation, the loop body never runs.

The following example produces the same output as the EXIT WHEN example, using WHILE instead:

DECLARE
    v_counter       NUMBER(2);
BEGIN
    v_counter := 1;
    WHILE v_counter <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;

Output:

Iteration # 1
Iteration # 2
Iteration # 3
Iteration # 4
Iteration # 5
Iteration # 6
Iteration # 7
Iteration # 8
Iteration # 9
Iteration # 10

FOR (integer variant)

FOR name IN [REVERSE] expression .. expression LOOP
    statements
END LOOP;

FOR iterates over a range of integer values. The loop variable name is automatically declared as INTEGER and exists only within the loop — it cannot be referenced outside. The range expressions (start-value .. end-value) are evaluated once when the loop starts. The iteration step is always +1.

With REVERSE, the loop iterates in descending order: name starts at the right-most expression and decrements until it is less than the left-most expression.

If the start value is greater than the end value, the loop body is not executed and no error is raised.

The following example iterates from 1 to 10:

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

Output:

Iteration # 1
Iteration # 2
Iteration # 3
Iteration # 4
Iteration # 5
Iteration # 6
Iteration # 7
Iteration # 8
Iteration # 9
Iteration # 10

The following example shows a loop that does not execute because the start value exceeds the end value:

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

There is no output — the loop body is never executed.