3-13. Exiting an Outer Loop Prematurely
Problem
Your code contains a nested loop, and you want the inner loop to have the ability to exit from both loops and stop iteration completely.
Solution
Use loop labels for both loops and then reference either loop within an EXIT statement by following the EXIT keyword with a loop label. The following example prints out a series of numbers. During each iteration, the inner loop will increment until it reaches an odd number. At that point, it will pass control to the outer loop again. The outer loop will be exited when the index for the inner loop is greater than or equal to the number ten.
BEGIN
<<outer>> for idx1 in 1 .. 10 loop
<<inner>> for idx2 in 1 .. 10 loop
dbms_output.put(idx2);
exit inner when idx2 > idx1 * 2;
exit outer when idx2 = 10;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
Results:
123
12345
1234567
123456789
12345678910
PL/SQL procedure successfully completed.
How It Works
Any loop in PL/SQL can be labeled using a similar style to labels for code blocks. The label can be any valid identifier surrounded by angle brackets before the loop, and optionally the identifier can be placed at the end after the END LOOP clause. The result of such a labeling mechanism is that you will have a distinct start and end to the loops and more control over loop execution.
In the solution to this recipe, the label helps identify the outer loop so that it can be terminated with the EXIT clause. Without a label, the EXIT will terminate the innermost FOR loop. However, the label can also be used to help identify the loop's index. In the solution, this is not necessary because the outer loop index was named differently than the inner loop index. If both indexes were named the same, then you could use the loop label along with the index name to fully qualify the index. The following example demonstrates this technique:
BEGIN
<<outer>> FOR idx IN 1 .. 10 LOOP
<<inner>> FOR idx IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT(inner.idx);
EXIT inner WHEN inner.idx > outer.idx * 2;
EXIT outer WHEN inner.idx = 10;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
This code will display the same results as the example given in the solution to this recipe. The only difference is that in this example the index name is the same in both the inner and outer loops. An alternative technique to end the current iteration of an inner loop is to use the CONTINUE statement. A CONTINUE statement can reference the label of a loop that is within the same scope. Therefore, an inner loop can exit its current iteration and proceed to an outer loop, as the following example demonstrates:
BEGIN
<<outer>> for idx1 in 1 .. 10 loop
<<inner>> for idx2 in 1 .. 10 loop
dbms_output.put(idx2);
exit inner when idx2 > idx1 * 2;
exit outer when idx2 = 10;
END LOOP;
CONTINUE outer;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
In this example, the same code that is used in the solution to this recipe is rewritten to incorporate a CONTINUE statement. This statement is used to move control of execution back to the outer loop. When the CONTINUE statement is reached, execution of the current loop is immediately halted, and processing continues to the loop designated by the label.