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.