Using the LOOP, EXIT, CONTINUE, WHILE, and FOR statements, you can arrange for your SPL program to repeat a series of statements.

LOOP

LOOP
    statements
END LOOP;

LOOP defines an unconditional loop that is repeated indefinitely until terminated by an EXIT or a RETURN statement.

EXIT

EXIT [ WHEN expression ];

The innermost loop is terminated and the statement following END LOOP is executed next.

If WHEN is present, loop exit occurs only when the specified condition is TRUE. Otherwise, control passes to the statement after EXIT.

EXIT can be used to cause early exit from all types of loops. It is not limited to use with unconditional loops.

The following simple example shows a loop that iterates ten times and then uses the EXIT statement to terminate:

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;

The following output is generated from this program:

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

CONTINUE

The CONTINUE statement provides a way to proceed with the next iteration of a loop while skipping intervening statements.

When the CONTINUE statement is encountered, the next iteration of the innermost loop is begun, skipping all statements following the CONTINUE statement until the end of the loop. Control is passed back to the loop control expression, if any, and the body of the loop is re-evaluated.

If the WHEN clause is used, the next iteration of the loop is begun only when the specified expression in the WHEN clause evaluates to TRUE. Otherwise, control is passed to the next statement following the CONTINUE statement.

The CONTINUE statement may not be used outside of a loop.

The following example shows a variation of the previous example that uses the CONTINUE statement to skip the display of the odd numbers:

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;

The following output is generated from the above program:

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

WHILE

WHILE expression LOOP
    statements
END LOOP;

The WHILE statement repeats a sequence of statements so long as the condition expression evaluates to TRUE. The condition is checked just before each entry to the loop body.

The following example contains the same logic as in the previous example except the WHILE statement is used to take the place of the EXIT statement to determine when to exit the loop.

Note The conditional expression used to determine when to exit the loop must be altered. The EXIT statement terminates the loop when its conditional expression is true. The WHILE statement terminates (or never begins the loop) when its conditional expression is false.
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;

The same result is generated by this example as in the prior example.

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;

This form of FOR creates a loop that iterates over a range of integer values. The name variable is automatically defined as the INTEGER type and exists only inside the loop. The two expressions giving the loop range are evaluated once when they enter the loop. The iteration step is +1 and name begins with the value of expression to the left of .. and terminates once name exceeds the value of expression to the right of ... Therefore, the two expressions take on the following roles: start-value .. end-value.

The optional REVERSE clause specifies that the loop must iterate in reverse order. The first time name passes through the loop, name is set to the value of the right-most expression. The loop terminates when name is less than the left-most expression.

The following example simplifies the WHILE loop example even further by using a FOR loop that iterates from 1 to 10:

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

The following output is generated from the FOR statement:

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

If the start value is greater than the end value, the loop body is not executed. No error is raised as shown by the following example:

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

There is no output from this example because the loop body is never executed.

Note SPL also supports CURSOR FOR loops.