Oracle PL/SQL Best Practices

Name

CTL-05: Use a single EXIT in simple loops.

Synopsis

This best practice is another variation on “one way in, one way out.” It suggests that, whenever possible, you consolidate all exit logic in your simple loop to a single EXIT (or EXIT WHEN) statement.

In general, use the EXIT WHEN statement in place of code like this:

IF <> THEN EXIT; END IF;

because it’s more intuitive and requires less typing.

Example

Here’s part of a program that compares two files for equality. After reading the next line from each file, it checks for the following conditions:

Did I reach the end of both files?

Are the lines different?

Did I reach the end of just one file?

In each case, set the “return value” for the function and also issue an EXIT statement:

LOOP
   read_line (file1, line1, file1_eof);
   read_line (file2, line2, file2_eof);

   IF (file1_eof AND file2_eof)
   THEN
      retval := TRUE;
      EXIT;
   ELSIF (line1 != line2)
   THEN
      retval := FALSE;
      EXIT;
   ELSIF (file1_eof OR file2_eof)
   THEN
      retval := FALSE;
      EXIT;
   END IF;
END LOOP;

Then rewrite this loop body as follows:

LOOP
   read_line (file1, line1, file1_eof);
   read_line (file2, line2, file2_eof);

   IF (file1_eof AND file2_eof)
   THEN
      retval := TRUE;
      exit_loop := TRUE;
   ELSIF (line1 != line2)
   THEN
      retval := FALSE;
      exit_loop := TRUE;
   ELSIF (file1_eof OR file2_eof)
   THEN
      retval := FALSE;
      exit_loop := TRUE;
   END IF;
   EXIT WHEN exit_loop;
END LOOP;

Sometimes it can be difficult to come up with just one EXIT statement. This usually occurs when you need to check a condition at the beginning ...