The CONTINUE Statement

Oracle Database 11g offers a new feature for loops: the CONTINUE statement. Use this statement to exit the current iteration of a loop, and immediately continue on to the next iteration of that loop. This statement comes in two forms, just like EXIT: the unconditional CONTINUE and the conditional CONTINUE WHEN.

Here is a simple example of using CONTINUE WHEN to skip over loop body execution for even numbers:

BEGIN
   FOR l_index IN 1 .. 10
   LOOP
      CONTINUE WHEN MOD (l_index, 2) = 0;
      DBMS_OUTPUT.PUT_LINE ('Loop index = ' || TO_CHAR (l_index));
   END LOOP;
END;
/

The output is:

Loop index = 1
Loop index = 3
Loop index = 5
Loop index = 7
Loop index = 9

Of course, you can achieve the same effect with an IF statement, but CONTINUE may offer a more elegant and straightforward way to express the logic you need to implement.

CONTINUE is likely to come in handy mostly when you need to perform “surgery” on existing code, make some very targeted changes, and then immediately exit the loop body to avoid side effects.

You can also use CONTINUE to terminate an inner loop and continue immediately on to the next iteration of an outer loop’s body. To do this, you will need to give names to your loops using labels. Here is an example:

BEGIN <<outer>> FOR outer_index IN 1 .. 5 LOOP DBMS_OUTPUT.PUT_LINE ( 'Outer index = ' || TO_CHAR (outer_index)); <<inner>> FOR inner_index IN 1 .. 5 LOOP DBMS_OUTPUT.PUT_LINE ( ' Inner index = ' || TO_CHAR (inner_index)); CONTINUE outer; END LOOP inner; END ...

Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.