[Chapter 7] 7.7 Tips for PL/SQL Loops

7.7 Tips for PL/SQL Loops

Loops are very powerful and useful constructs, but they are among the most complicated control structures in PL/SQL. The tips in this section will help you select the most efficient and easily maintained loops for your programs.

7.7.1 Naming Loop Indexes

How would you like to try to understand -- much less maintain -- code that looks like this?

FOR i IN start_id .. end_id
LOOP
   FOR j IN 1 .. 7
   LOOP
      FOR k IN 1 .. 24
      LOOP
         build_schedule (i, j, k);
      END LOOP;
   END LOOP;
END LOOP;

It is hard to imagine that someone would write code based on such generic integer variable names (right out of Algebra 101), yet it happens all the time. The habits we pick up in our earliest days of programming have an incredible half-life. Unless you are constantly vigilant, you will find yourself writing the most abominable code. In this case, the solution is simple: use variable names for the loop indexes that are meaningful and therefore self-documenting:

FOR focus_account IN start_id .. end_id
LOOP
   FOR day_in_week IN 1 .. 7
   LOOP
      FOR month_in_biyear IN 1 .. 24
      LOOP
         build_schedule (focus_account, day_in_week, month_in_biyear);
      END LOOP;
   END LOOP;
END LOOP;

Well, that cleared things up! Before I substituted the meaningless loop index names, I would wager that you were fairly sure the statement:

FOR j IN 1 .. 7

meant that "j" stood for the seven days of the week. And I bet further that you were equally confident that:

FOR k IN 1 .. 24

meant that "k" represented the hours in a day.

Now that I have provided descriptive names for those index variables, however, you discover that the innermost loop actually spanned two sets of twelve months (12 × 2 = 24). Your deduction about "k", while reasonable, was wrong, but it would have been completely impossible to determine this without looking at the build_schedule code. Given PL/SQL's ability to hide information within packages, this code might not even be available.

Software programmers should not have to make Sherlock Holmes-like deductions about the meaning of the start and end range values of the innermost FOR loops in order to understand their purpose. Use names that self-document the purposes of variables and loops. That way other people will understand your code and you will remember what your own code does when you review it three months later.

7.7.2 The Proper Way to Say Goodbye

No matter what kind of loop you are using, there is always only one entry point into the loop: the first executable statement following the LOOP keyword. Your loops should also have just one way of leaving the loop. The method of exit, furthermore, should be compatible with the type of loop you use. The following tips will help you write well-structured and easily maintained loops.

7.7.2.1 Premature FOR loop termination

The syntax of the FOR loop states your intent explicitly and should only be a FOR loop if you know in advance how many times the loop needs to execute. For example, the following loop is very clear:

FOR month_count IN 1 .. 12
LOOP
   analyze_month (month_count);
END LOOP;

It states: "I am going to execute the analyze_month procedure 12 times, once for each month in the year." Straightforward and easy to understand.

Now take a look at the next numeric FOR loop:

FOR year_count IN 1 .. years_displayed
LOOP
   IF year_count > 10 AND :store.status = 'CLOSED'
   THEN
      EXIT;
   END IF;
   analyze_month (month_count);
END LOOP;

In this case, the loop boundary states: "Run the loop for the number of years displayed in the form." Yet in the body of the loop, an IF statement allows a premature termination of the loop. If the year count (the loop index) exceeds 10 and the current store status is CLOSED, then an EXIT statement is issued and the loop halts.

This approach is very unstructured and contradictory. The loop boundary states one thing, but the loop body executes something very different.

You should always let a FOR loop (whether numeric or cursor) complete its stated number of iterations. If you do need to conditionally halt loop execution, you should choose either an infinite or a WHILE loop. The above FOR loop could, for example, be easily recoded as follows:

FOR year_count IN 1 .. LEAST (years_displayed, 11)
LOOP
   analyze_month (month_count);
END LOOP;

Similar guidelines apply to the infinite and WHILE loop, as I explore in the next sections.

7.7.2.2 EXIT and EXIT WHEN statements

Neither the FOR loop nor the WHILE loop should use the EXIT and EXIT WHEN statements. You have already seen why this is so in FOR loops. Consider the following WHILE loop:

WHILE more_records
LOOP
   NEXT_RECORD;
   EXIT WHEN :caller.name IS NULL;
END LOOP;

In this case, even though my loop boundary indicates that the body should execute until more_records evaluates to FALSE, the EXIT WHEN in the loop body bypasses that condition. Instead of using EXITs in your WHILE loop, you should always rely exclusively on your loop condition to determine whether the looping should continue. The previous WHILE loop can be redesigned as follows:

WHILE more_records
LOOP
   NEXT_RECORD;
   more_records := :caller.name IS NOT NULL;
END LOOP;

7.7.2.3 RETURN statement

The RETURN statement will cause instant termination of a function and return the specified value back to the calling program. Never use a RETURN statement inside a loop.

Unfortunately, such things have been known to happen. In the following example of terrifically poor programming practice (taken from an Oracle Corporation reference manual, I am sorry to say), the FOR loop is interrupted -- not with an EXIT, which would be unstructured enough, but with a RETURN statement:

BEGIN
   the_rowcount := Get_Group_Row_Count( rg_id );

   FOR j IN 1..the_rowcount
   LOOP

      col_val := Get_Group_Char_Cell( gc_id, j );

      IF UPPER(col_val) = UPPER(the_value)
      THEN
         RETURN j;
      END IF;

   END LOOP;
END;

The author of this program was in a big hurry to return to the calling program!

Once again, if the loop should be conditionally terminated, do not use a FOR loop. Instead, use a WHILE or infinite loop and then issue the RETURN after the loop is completed. The following code replaces the unstructured IF statement shown above:

BEGIN
/* Initialize the loop boundary variables. */
   row_index := 0;
   the_rowcount := Get_Group_Row_Count (rg_id);

   /* Use a WHILE loop. */
   WHILE row_index <= the_rowcount AND
          match_not_found
   LOOP
         row_index := row_index + 1;
         col_val := Get_Group_Char_Cell (gc_id, row_index);
         match_not_found := UPPER (col_val) != UPPER (the_value)
   END LOOP;

   /* Now issue the RETURN statement. */
   RETURN row_index;
END;

7.7.2.4 GOTO statements inside a loop

The same reasons for avoiding a RETURN apply to the GOTO statement. If you use a GOTO to exit from a loop, you bypass the logical structure of the loop. You end up with code that is very difficult to trace, debug, fix, and maintain.

7.7.3 Avoiding the Phony Loop

As I have stated previously, you should not use a numeric FOR loop if you cannot specify in a range scheme of lowest and highest bounds the number of times the loop must execute. Just because you know the number of iterations of some code, however, doesn't mean that you should use a loop.

I have run across a number of programs which execute variations on this kind of FOR loop:

FOR i IN 1 .. 2
LOOP
   IF i = 1
   THEN
      give_bonus (president_id, 2000000);

   ELSIF i = 2
   THEN
      give_bonus (ceo_id, 5000000);
   END IF;
END LOOP;

This loop provides hefty bonuses to the president and CEO of a company that just went deep into debt to acquire a competitor. I need to use the loop so the code executes twice to make sure both the president and CEO receive their just compensation. Right? Wrong. This code should not be inside a loop. It does not need iteration to perform its job; the LOOP syntax just confuses the issue.

The two sections within the IF-THEN-ELSE construct in the previous example both need to be executed all the time; this is straight sequential code and should be written as follows:

give_bonus (president_id, 2000000);
give_bonus (ceo_id, 5000000);

7.7.4 PL/SQL Loops Versus SQL Processing

One of the indicators that a numeric FOR loop is being used incorrectly is that the loop index is not used for anything but traffic control inside the loop. The actual body of executable statements completely ignores the loop index. When that is the case, there is a good chance that you don't need the loop at all.

When should you use standard SQL to accomplish your task and when should you rely on PL/SQL loops? Sometimes the choice is clear: if you do not need to interact with the database, then there is clearly no need for SQL. In addition, SQL can't always provide the necessary flexibility to get the job done. Conversely, if you are performing a single record insert into a table then there is no need for a loop. Often, however, the choice is less obvious. For example, a SELECT statement queries one or more rows from the database. A cursor FOR loop also queries rows from the database based on a SELECT statement. In fact, PL/SQL and native SQL often can both accomplish the task at hand. Given that fact, you will need to choose your implementation according to more subtle issues like performance and maintainability of code.

Before we look at some examples of scenarios which call for one or the other approach, let's review the difference between the implicit looping of the SQL set-at-a-time approach and the PL/SQL loop.

SQL statements such as SELECT, UPDATE, INSERT, and DELETE work on a set of data. That set (actually, a collection of rows from a table or tables) is determined by the WHERE clause (or lack thereof) in the SQL statement. SQL derives much of its power and effectiveness as a database language from this set-at-a-time processing approach. There is, however, a drawback, as I mentioned earlier: SQL often does not give you the flexibility you might need to handle individual records and specialized logic which must be applied differently to different records.

The PL/SQL cursor offers the ability to access a record at a time and to take action based on the contents of that specific record. It is not always clear, however, which language component would best fit the needs of the moment. I have seen a number of programs where developers went overboard in their drive to PL/SQL-ize the SQL access to their data. This happens most frequently when using a cursor FOR loop.

The PL/SQL block in the code below moves checked-out pets from the pet hotel occupancy table to the pets_history table using a cursor FOR loop. For each record fetched (implicitly) from the cursor (representing a pet who has hit the road), the body of the loop first inserts a record into the pet_history table and then deletes the record from the occupancy table:

DECLARE
   CURSOR checked_out_cur IS
      SELECT pet_id, name, checkout_date
        FROM occupancy
       S checkout_date IS NOT NULL;
BEGIN
   FOR checked_out_rec IN checked_out_cur
   LOOP
      INSERT INTO occupancy_history (pet_id, name, checkout_date)
         VALUES (checked_out_rec.pet_id, checked_out_rec.name,
                 checked_out_rec.checkout_date);
      DELETE FROM occupancy WHERE pet_id = checked_out_rec.pet_id;
   END LOOP;
END;

This will work just fine. But do we really need to use a cursor FOR loop to accomplish this task? Suppose 20 animals checked out today. This block of code will then perform 20 distinct inserts and 20 distinct deletes. The same code can be written completely within the SQL language as shown below:

INSERT INTO occupancy_history (pet_id, name, checkout_date)
SELECT pet_id, name, checkout_date
  FROM occupancy
 WHERE checkout_date IS NOT NULL;
DELETE FROM occupancy WHERE checkout_date IS NOT NULL;

Here, a single insert (making use of the INSERT...SELECT syntax) and a single delete (which now checks for the checkout_date and not the employee_id) accomplish the transfer of the data to the history table. This reliance on native SQL, without the help of PL/SQL, allows you to take full advantage of array processing. It significantly reduces network traffic in a client-server environment because only two SQL statements (instead of 40) are passed to the RDBMS.

The cursor FOR loop was not really needed here; the body of the loop did not perform any procedural logic which could not be handled by SQL itself. If, on the other hand, the program needed to selectively reject records for the transfer, or otherwise perform procedural logic not possible within SQL, then either the cursor FOR loop or a WHILE loop would make sense.