O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required