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.
Get Oracle PL/SQL Best Practices now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.