Oracle PL/SQL Programming, 5th Edition

The Cursor FOR Loop

A cursor FOR loop is a loop that is associated with (and actually defined by) an explicit cursor or a SELECT statement incorporated directly within the loop boundary. Use the cursor FOR loop only if you need to fetch and process each and every record from a cursor, which is often the case with cursors.

The cursor FOR loop is one of my favorite PL/SQL features. It leverages fully the tight and effective integration of the procedural constructs with the power of the SQL database language. It reduces the volume of code you need to write to fetch data from a cursor. It greatly lessens the chance of introducing loop errors in your programming—and loops are one of the more error-prone parts of a program. Does this loop sound too good to be true? Well, it isn’t—it’s all true!

Here is the basic syntax of a cursor FOR loop:

FOR record IN { cursor_name | (explicit SELECT statement) }
LOOP
   executable statement(s)
END LOOP;

where record is a record declared implicitly by PL/SQL with the %ROWTYPE attribute against the cursor specified by cursor_name.

Warning

Don’t declare a record explicitly with the same name as the loop index record. It is not needed (PL/SQL declares one for its use within the loop implicitly) and can lead to logic errors. For tips on accessing information about a cursor FOR loop’s record outside or after loop execution, see Obtaining Information About FOR Loop Execution.

You can also embed a SELECT statement directly in the cursor FOR loop, as shown in this example: ...