Surgical strike on spaghetti code with CONTINUE statement

It can be incredibly painful to make changes to an existing program that has the markings of spaghetti code. And isn't it positively terrifying to make those changes when you don't have a regression test for the program that you can run afterward to ensure that no bugs were inadvertently introduced?

Yet that is what we are often called upon to do.

When faced with this situation, the smart thing to do is to make the smallest, most isolated change possible, thereby minimizing the ripple effect.

Suppose the code you have to modify looks like this:

PROCEDURE someone_elses_mess
/*
|| Author: Long-Gone Consultant 
|| Maintained by: Terrified Employee
*/
IS
BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here 

      ... more of the same here    
     
   END LOOP;
END;

and you need to add some code between "hard-to-understand logic here" and "more of the same here." If a certain condition is met, you want to execute some new code and then skip over the rest of the loop body and move on to the next iteration.

You can accomplish this in a few ways:

• Adding an IF statement
• Using a GOTO statement
• Using a CONTINUE statement

Here's what the loop body might look like with an IF statement:

BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here

      IF new_condition
      THEN
         ... new code here
      ELSE
         ... more of the same here
      END IF;
   END LOOP;
END;

Here's the approach with GOTO:

BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here

      IF new_condition
      THEN
         ... new code here

         GOTO end_of_loop;
      END IF;

      ... more of the same here
      <<end_of_loop>>
      NULL; -- Placeholder
   END LOOP;
END;

And, finally, here's the approach with CONTINUE (new to Oracle Database 11g):

BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here

      IF new_condition
      THEN
         ... new code here

         CONTINUE;
      END IF;

      ... more of the same here

   END LOOP;
END;

Now, with code this simple, all three of these approaches look reasonable and get the job done. But if you are dealing with an extremely complex, convoluted program, the IF statement gets tricky. You have to make sure you set up the ELSE clause properly and enclose the correct logic. Which means that you have to find the END LOOP statement for this loop, which could be hundreds of lines later in the program, with many other END LOOPs in between.

The GOTO allows you to simply branch to the end of the loop, but, again, you must find the end of that loop and then add both the label and the placeholder "NULL;" statement so that the GOTO has someplace executable to go.

No, the best solution to this problem is to simply tell the PL/SQL runtime that you want to continue with the loop execution, skipping the rest of the body for this iteration. Clean, simple, and declarative.

And, by the way, just as with the EXIT statement, you can use CONTINUE in a WHEN clause and also specify an END label (helpful with nested loops).

Here is an example of CONTINUE WHEN:

BEGIN
   ... lots and lots of convoluted code

   FOR index IN 1 .. my_collection.COUNT
   LOOP
      ... hard-to-understand logic here

      /* I want to execute this new logic and then "escape." */
      ... new code here

      CONTINUE WHEN new_condition;

      ... more of the same here

   END LOOP;
END;

The following block shows how you can skip not only the rest of the inner loop but also the outer loop by specifying a label with CONTINUE:

BEGIN
   <<outer_loop >>
   FOR o_index IN 1 .. my_collection.COUNT
   LOOP
      <<inner_loop>>
      FOR i_index
            IN your_collection.FIRST ..
                your_collection.LAST
      LOOP
         ... lots of code

         /* Skip the rest of this and the outer loop if condition is met. */
         CONTINUE outer_loop WHEN condition_is_met;

         ... more inner loop logic

      END LOOP inner_loop;

      ... more outer loop logic

   END LOOP outer_loop;
END;

So: you like CONTINUE, right? Nice, clear, easy-to-understand syntax and code structure. You'll start using it, right? Right!

In that case, the following resources may be of assistance:

The Oracle Dev Gym offers a number of quizzes on CONTINUE. Just head to the home page and search on "CONTINUE".

And here's a LiveSQL script that demonstrates CONTINUE, comparing it to using GOTOs and exceptions to achieve the same result (please don't!).

Note: Much of this content originally appeared in Oracle Magazine.