Name

SQL-09: Use a cursor FOR loop to fetch all rows in a cursor unconditionally.

Synopsis

The cursor FOR loop construct is a wonderful addition to the PL/SQL language, reflecting the tight integration between SQL and PL/SQL. Use it whenever you need to fetch every single row identified by the cursor, but don’t use it if you have to conditionally exit from the loop.

Example

I need to display the total number of books sold for each of my PL/SQL texts. That’s easy:

DECLARE
   CURSOR sef_books_cur IS
       SELECT title, total_count 
         FROM book_sales
        WHERE author = 'FEUERSTEIN, STEVEN';
BEGIN
   FOR rec IN sef_books_cur
   LOOP
      pl (rec.title || ': ' ||
         rec.total_count || ' copies');
   END LOOP;
END;

Perfect use of a cursor FOR loop! Suppose, on the other hand, the requirement was this: “Display all the books and their numbers sold until the total reaches 100,000; then quit.” In this case, I should use a WHILE loop with an EXIT WHEN statement. Here’s an example:

DECLARE
   total_sold  PLS_INTEGER := 0;

   CURSOR sef_books_cur IS
       SELECT title, total_count 
         FROM book_sales
        WHERE author = 'FEUERSTEIN, STEVEN';

   rec  sef_books_cur%ROWTYPE;
stop_loop BOOLEAN;
BEGIN
    OPEN sef_books_cur;
    LOOP
       FETCH sef_books_cur INTO rec;
            stop_loop := sef_books_cur%NOTFOUND;
            IF NOT stop_loop
            THEN
               pl (rec.title || ': ' ||
              rec.total_count || ' copies');
          total_sold := total_sold + rec.total_count;
          stop_loop := total_sold >= 100000;
       END IF;
       EXIT WHEN stop_loop;
    END LOOP;
    CLOSE sef_books_cur;
END;

Benefits

The cursor FOR loop saves you coding effort ...

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.