Oracle PL/SQL Best Practices

Name

SQL-10: Never use a cursor FOR loop to fetch just one row.

Synopsis

If you have a single-row query, you can use a cursor FOR loop, but it’s misleading. A cursor FOR loop is designed to fetch all (multiple) rows from a cursor. The only rationale for using a cursor FOR loop for a single-row query is that you don’t have to write as much code, and that is both dubious and a lame excuse.

Example

Doesn’t this look silly:

CREATE OR REPLACE FUNCTION book_title (
   isbn_in IN book.isbn%TYPE)
RETURN book.title%TYPE
IS
   CURSOR title_cur IS
   SELECT title INTO l_title   
     FROM book                 
    WHERE isbn =isbn_in; 

   l_rec title_cur%ROWTYPE;
BEGIN
   FOR rec IN title_cur
   LOOP
      l_rec := rec;
   END LOOP;
   RETURN l_rec.title;
END;      

Instead, use a SELECT INTO or explicit cursor; for example:

CREATE OR REPLACE FUNCTION book_title (
   isbn_in IN book.isbn%TYPE)
RETURN book.title%TYPE
IS
   CURSOR title_cur IS
   SELECT title INTO l_title   
     FROM book                 
    WHERE isbn =isbn_in; 

   l_rec title_cur%ROWTYPE;
BEGIN
   OPEN title_cur;
   FETCH title_cur INTO l_rec;
   CLOSE title_cur;
   RETURN l_rec.title;
END;      

Benefits

Your code doesn’t look silly. It satisfies the requirement in the most direct and understandable way.

A cursor FOR loop is less efficient than either a SELECT INTO or an explicit cursor fetch.

Resources

explimpl.pkg and explimpl.sql : Scripts that compare the performance of cursor FOR loops to other fetching methods for a single row.