Watch out for redundant code with WHILE loops

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.

Here's an 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;
END;   

As is readily apparent, I duplicate the assignments of values to l_overdue_count. I would be far better off rewriting this code as follows:

DECLARE
   l_fine PLS_INTEGER := 0;
   l_date DATE := SYSDATE;
   l_overdue_count NUMBER;
BEGIN
   LOOP
      EXIT WHEN
        (l_overdue_count <= 0 OR l_fine >= 10)
     
      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;
END;   

By paying close attention to your loop construction, you can avoid redundant code, always bad news in a program, since it increases maintenance costs and the chance of introducing bugs into your code.