Oracle PL/SQL Best Practices

Name

CTL-06: Use a simple loop to avoid redundant code required by a WHILE loop.

Synopsis

Generally, you should use a simple loop if you always want the body of the loop to execute at least once. You use a WHILE loop if you want to check before executing the body the first time. Since the WHILE loop performs its check “up front,” the variables in the boundary expression must be initialized. The code to initialize is often the same code needed to move to the next iteration in the WHILE loop. This redundancy creates a challenge in both debugging and maintaining the code: how do you remember to look at and update both?

If you find yourself writing and running the same code before the WHILE loop and at end of the WHILE loop body, consider switching to a simple loop.

Example

I write a procedure to calculate overdue charges for books; the maximum fine to be charged is $10, and I will stop processing when there are no overdue books for a given date. Here is my first attempt at the procedure body:

DECLARE
   l_fine PLS_INTEGER := 0;
   l_date DATE := SYSDATE;
   l_overdue_count NUMBER;
BEGIN
   l_overdue_count := 
      overdue_pkg.countem (
         borrower_id => borrower_in,
         l_date);
   
   WHILE (l_overdue_count > 0 AND l_fine < 10)
   LOOP
      update_fine_info (l_date, l_one_day_fine);

      l_fine := l_fine + l_one_day_fine;
      l_date := l_date + 1;
      l_overdue_count :=
         overdue_pkg.countem (
            borrower_id => borrower_in,
            l_date);
   END LOOP;

As is readily apparent, I duplicate the assignments of values to l_overdue_count. I would be far better ...