Monday, September 11, 2017

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.

7 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