Oracle PL/SQL Best Practices

Name

CTL-04: Never EXIT or RETURN from WHILE and FOR loops.

Synopsis

The WHILE and FOR loops include “boundary conditions” that determine:

• When and if a loop should execute at all

• When a loop should stop executing

If you use the EXIT or RETURN statements inside a WHILE or FOR loop, you cause an unstructured termination from the loop. The resulting code is hard to trace and debug.

Example

Here’s the bottom half of a function that scans the contents of a collection and returns the row in which a match is found.

   l_count := titles.COUNT;
   FOR indx IN 1 .. l_rowcount 
   LOOP
      IF l_match_against = titles(indx)
      THEN
         RETURN indx;
      END IF;
   END LOOP;

   RAISE Exit_Function;
EXCEPTION
   WHEN Exit_Function THEN RETURN NULL;
END;

Now this is some nasty code. You manage to get all the way down to the end of the executable section, and you are punished with an exception! See [MOD-07: Limit functions to a single RETURN statement in the execution section.] for how this violates best practice for a “funnel-shaped” function.

Of course, you’re not supposed to get to the end of the function. Instead, the function finds a match and zooms straight out of the function with a RETURN.

Now imagine a function whose body is 200 lines long with nested loops and several different RETURNs in different parts of the loop. Chaos!

Benefits

By following the maxim “one way in and one way out” for your loops, the resulting code is much easier to understand and debug. If your loop needs to execute at least once (like a Pascal REPEAT statement), ...