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.
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.