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.

Comments

  1. Hello Steven,

    to enable the developer to make a "quick" evolution of the loop code your first example with CONTINUE can be rewritten a little bit:
    ----------------------------------------------------------
    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
    END IF;

    CONTINUE WHEN new_condition;

    ... more of the same here
    END LOOP;
    END;
    ----------------------------------------------------------
    By doing so the developer don't need to fully evaluate the inner code of the IF but can clearly (and quickly) see that the rest of the code should be skipped when the condition is true.

    Kind regards, Niels Hecker

    ReplyDelete
  2. Unfortunately, I've found that Oracle doesn't validate labels as well as maybe it should (at least I've not found a way) and this can lead to confusion or bugs:

    BEGIN
    << outer_loop >>
    FOR o_index IN 1 .. my_collection.COUNT
    LOOP
    << outer_loop >> -- DUPLICATE LOOP NAME
    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 lorem_ipsum_dolor_loop; -- LABEL NOT VALIDATED

    ... more outer loop logic

    END LOOP outer_loop;
    END;

    ReplyDelete
  3. Thanks for pointing this out, Kevan. Question: when you say "bug" do you mean "The developer gets confused and puts a bug in his or her code." or "There is a bug in PL/SQL" (besides not validating label usages)?

    ReplyDelete
    Replies
    1. The developer puts a bug in their code (not realising that they had forgotten to correct their copy-and-paste of the loop, say)

      Delete
  4. Hello Steven & Kevan,

    While I know that labels are not completely validated ...
    still remember that tricky PL/SQL Challenge quiz having:

    LOOP
    ...
    END LOOP -- no ending semicolon here ...

    COMMIT;


    However, performing a small check for the duplicate label,
    we get the following compilation error:

    PLS-00373: EXIT/CONTINUE label 'OUTER_LOOP' must label a LOOP statement


    But, if the CONTINUE statement does not reference the label,
    then there is no error issued, probably because
    the duplicate label is considered "passive".


    We have a similar case with a GOTO statement:

    begin
    GOTO A;
    << a >>
    DBMS_OUTPUT.PUT_LINE('A');
    << a >>
    DBMS_OUTPUT.PUT_LINE('A-2');
    end;
    /

    ORA-06550: line 2, column 8:
    PLS-00371: at most one declaration for 'A' is permitted

    But, if we remove the GOTO statement, there is no error.



    Thanks a lot & Best Regards,
    Iudith



    ReplyDelete
    Replies
    1. Hi Iudith

      I can't reproduce that error message. I tried enabling all PL/SQL warnings too.

      Here's my example on LIVE SQL -https://livesql.oracle.com/apex/livesql/s/flfzukhc2xkgt6yddgr4xmsha

      Regards,

      Delete
  5. Hello Kevan,

    Here is the block I used, quickly crafted out of the block from your first post above:

    DECLARE
    type tt is table of number;
    my_collection tt := tt(10,20,30);
    your_collection tt := tt(1,2,3);

    BEGIN
    << outer_loop >>
    FOR o_index IN 1 .. my_collection.COUNT
    LOOP
    << outer_loop >> -- DUPLICATE LOOP NAME
    DBMS_OUTPUT.put_line('This is outer loop - o_index='||o_index);
    FOR i_index IN your_collection.FIRST .. your_collection.LAST
    LOOP
    --lots of code
    DBMS_OUTPUT.put_line('This is inner loop - i_index='||i_index);

    /* Skip the rest of this and the outer loop if condition is met. */
    CONTINUE outer_loop WHEN MOD( o_index + i_index, 3) = 0 ;

    -- more inner loop logic
    DBMS_OUTPUT.put_line('More inner loop logic');

    END LOOP lorem_ipsum_dolor_loop; -- LABEL NOT VALIDATED

    -- more outer loop logic
    DBMS_OUTPUT.put_line('More outer loop logic');

    END LOOP outer_loop;
    END;
    /

    This produces the error below:

    ORA-06550: line 19, column 5:
    PLS-00373: EXIT/CONTINUE label 'OUTER_LOOP' must label a LOOP statement


    Just now, looking at it, I realized that I added a DBMS_OUTPUT.put_line call between the inner label and the start of the FOR loop ....... and this is probably what "helped" the compiler to behave correctly :)


    But, yes ... if I switch position of that statement with the label, like below, then there is no error, and it looks like the CONTINUE statement is "silently" using the "outer_loop" label that is "closest" to it, aka the label of the inner loop:


    DECLARE
    type tt is table of number;
    my_collection tt := tt(10,20,30);
    your_collection tt := tt(1,2,3);

    BEGIN

    << outer_loop >>
    FOR o_index IN 1 .. my_collection.COUNT
    LOOP
    DBMS_OUTPUT.put_line('This is outer loop - o_index='||o_index);
    << outer_loop >> -- DUPLICATE LOOP NAME
    -- DBMS_OUTPUT.put_line('This is outer loop - o_index='||o_index);
    FOR i_index IN your_collection.FIRST .. your_collection.LAST
    LOOP
    --lots of code
    DBMS_OUTPUT.put_line('This is inner loop - i_index='||i_index);

    /* Skip the rest of this and the outer loop if condition is met. */
    CONTINUE outer_loop WHEN MOD( o_index + i_index, 3) = 0 ;

    -- more inner loop logic
    DBMS_OUTPUT.put_line('More inner loop logic');

    END LOOP lorem_ipsum_dolor_loop; -- LABEL NOT VALIDATED

    -- more outer loop logic
    DBMS_OUTPUT.put_line('More outer loop logic');

    END LOOP outer_loop;
    END;
    /

    This is outer loop - o_index=1
    This is inner loop - i_index=1
    More inner loop logic
    This is inner loop - i_index=2
    This is inner loop - i_index=3
    More inner loop logic
    More outer loop logic
    This is outer loop - o_index=2
    This is inner loop - i_index=1
    This is inner loop - i_index=2
    More inner loop logic
    This is inner loop - i_index=3
    More inner loop logic
    More outer loop logic
    This is outer loop - o_index=3
    This is inner loop - i_index=1
    More inner loop logic
    This is inner loop - i_index=2
    More inner loop logic
    This is inner loop - i_index=3
    More outer loop logic


    On the other hand, by this logic, in the first block above we could have expected that the CONTINUE statement will use the other (outer) "outer_loop" label, which is in fact the only one that indeed is a label of a loop,
    without interfering at all with the other duplicated one ...


    In any case, we should expect the compiler behavior to be "much cleaner" and do raise a compilation error whenever a duplicate label is found.

    As we know, the compiler is also "very generous" when using declaring a duplicate variable name in the same block.
    It only complains if that variable is effectively used.


    So, yes, indeed, we do have a PL/SQL compiler problem in these cases.


    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts