Oracle PL/SQL Best Practices

Name

CTL-07: Never declare the FOR loop index.

Synopsis

PL/SQL offers two kinds of FOR loops: numeric and cursor. Both have this general format:

FOR loop index IN loop range
LOOP
   loop body
END LOOP;

The loop index is either an integer or a record; in either case, it’s implicitly declared by the PL/SQL runtime engine. The scope of the loop index variable is restricted to the body of the loop (between the LOOP and END LOOP statements).

You should never declare a variable for the loop. If you do declare the loop index variable, you are actually declaring a completely separate (recordtype or numeric) variable that will (best case) never be used or (worst case) used outside the loop in a way that is confusing and likely to introduce errors.

Example

The developer who worked on the library management system before Jim (a PL/SQL novice) created this procedure to delete books from the collection by title:

CREATE OR REPLACE PROCEDURE remove_titles (
   title_in IN book.title%TYPE,
)
IS
   CURSOR book_cur
   IS
     SELECT isbn, author FROM book
      WHERE title LIKE title_in;
   book_rec book_cur%ROWTYPE;
BEGIN
   FOR book_rec IN book_cur
   LOOP
      te_book.rem (book_rec.isbn);
   END LOOP;
END;

It works just fine (no bugs reported), but Jim has been asked to modify the procedure to display the last book removed. So he adds this code after the FOR loop:

   END LOOP;
   pl (book_rec.isbn || ' - ' || 
       book_rec.author);
END;

The code compiles, but Jim spends the next two hours banging his head against the wall trying to figure out why the ...