Oracle PL/SQL Best Practices

Name

SQL-07: Fetch into cursor records, never into a hard-coded list of variables.

Synopsis

Whenever you fetch data from a cursor, whether it’s an explicit cursor or a cursor variable, you should fetch into a record defined from that cursor with %ROWTYPE.

Example

Suppose I have declared a cursor in a package as follows:

PACKAGE book_pkg
IS
   CURSOR books_by_category (
      category_in IN book.category%TYPE)
   IS
      SELECT title, author FROM book
       WHERE category = category_in;
END book_pkg;

Now I want to fetch information from this cursor. If I fetch into individual variables like this:

DECLARE
   l_title book.title%TYPE; 
   l_author book.author%TYPE; 
BEGIN
   OPEN book_pkg.books_by_category ('SCIFI');
   FETCH book_pkg.books_by_category INTO 
      l_title, l_author;

then I am hard-coding the number of values returned by a cursor, as well as the datatypes of the individual variables. (I could use %TYPE, but I am more likely to be lazy.)

This is a dangerous assumption to make. What if the owner of the book_pkg package decides to add another column to the SELECT list? My code will then fail to compile.

If, on the other hand, I write my code like this:

DECLARE
   scifi_rec book_pkg.books_by_category%ROWTYPE;
BEGIN
   OPEN book_pkg.books_by_category ('SCIFI');
   FETCH book_pkg.books_by_category INTO 
      scifi_rec;

then, if the cursor ever changes, my code will/can be recompiled, and it will automatically adapt to the new cursor structure.

Benefits

Code adapts automatically to changes in the underlying cursor structure.

You write less ...