7.4.1 Example of Cursor FOR Loops

Suppose I need to update the bills for all pets staying in my pet hotel, the Share-a-Din-Din Inn. The example below contains an anonymous block that uses a cursor, occupancy_cur, to select the room number and pet ID number for all occupants at the Inn. The procedure update_bill adds any new changes to that pet's room charges:

1  DECLARE
2     CURSOR occupancy_cur IS
3        SELECT pet_id, room_number
4        FROM occupancy WHERE occupied_dt = SYSDATE;
5     occupancy_rec occupancy_cur%ROWTYPE;
6  BEGIN
7     OPEN occupancy_cur;
8     LOOP
9        FETCH occupancy_cur INTO occupancy_rec;
10       EXIT WHEN occupancy_cur%NOTFOUND;
11       update_bill
12          (occupancy_rec.pet_id, occupancy_rec.room_number);
13    END LOOP;
14    CLOSE occupancy_cur;
15 END;

This code leaves nothing to the imagination. In addition to defining the cursor (line 2), you must explicitly declare the record for the cursor (line 5), open the cursor (line 7), start up an infinite loop, fetch a row from the cursor set into the record (line 9), check for an end-of-data condition with the cursor attribute (line 10), and finally perform the update. When you are all done, you have to remember to close the cursor (line 14).

If I convert this PL/SQL block to use a cursor FOR loop, then I have:

DECLARE
   CURSOR occupancy_cur IS
      SELECT pet_id, room_number
        FROM occupancy WHERE occupied_dt = SYSDATE;
BEGIN
   FOR occupancy_rec IN occupancy_cur
   LOOP
      update_bill (occupancy_rec.pet_id, occupancy_rec.room_number);
   END LOOP;
END;

Here you see the beautiful simplicity of the cursor FOR loop! Gone is the declaration of the record. Gone are the OPEN, FETCH, and CLOSE statements. Gone is the need to check the %FOUND attribute. Gone are the worries of getting everything right. Instead, you say to PL/SQL, in effect, "You and I both know that I want each row and I want to dump that row into a record that matches the cursor. Take care of that for me, will you?" And PL/SQL does take care of it, just the way any modern programming language should.

As with all other cursors, you can pass parameters to the cursor in a cursor FOR loop. If any of the columns in the select list of the cursor is an expression, remember that you must specify an alias for that expression in the select list. Within the loop, the only way to access a particular value in the cursor record is with the dot notation (cursor_name.column_name, as in occupancy_rec.room_number), so you need a column name associated with the expression.

7.4.2 The Cursor FOR Loop Record

The record which functions as the index for the cursor FOR loop is, like the loop index of a numeric FOR loop, defined only within the loop itself. You cannot reference that record outside of the loop. Furthermore, PL/SQL declares that record itself. You, the programmer, should never declare a record for use as the index in a cursor FOR loop. If you do include such a declaration, the effect will not be as you intended.

Consider the code shown below. This code contains explicit declarations for both the cursor (line 2) and a record for that cursor (line 5). The block then uses a cursor FOR loop with that cursor and specifies as its index a record with the same name as the declared record:

1  DECLARE
2  CURSOR occupancy_cur IS
3     SELECT pet_id, room_number
4       FROM occupancy WHERE occupied_dt = SYSDATE;
5   occupancy_rec occupancy_cur%ROWTYPE;
6
7  BEGIN
8   FOR occupancy_rec IN occupancy_cur
9   LOOP
10    update_bill
11       (occupancy_rec.pet_id, occupancy_rec.room_number);
12  END LOOP;
13
14  DBMS_OUTPUT.PUT_LINE
15   ('Last pet ID updated: ' || TO_CHAR (occupancy_rec.pet_id));
16 END;

I frequently run across this kind of code. Every time I see it, I shudder because it is fraught with the potential for hard-to-trace bugs. In this example, the declaration of the occupancy_rec record on line 5 is completely unnecessary and completely ignored by the cursor FOR loop. Remember: the record named as the index of the cursor FOR loop index is always declared by PL/SQL -- even if another record of the same name is defined in the declaration section. Inside the loop, any references to occupancy_rec refer to the record declared by the loop, which is completely different from the record defined explicitly in the declaration section.

The danger of this redundant declaration is that an inexperienced developer may try to reference the loop record outside of the loop. In this case, the code will compile anyway because a record is defined in the scope of that PL/SQL block. The contents of that record will, however, come as a big surprise. For example, lines 14-15 of the code above call the DBMS_OUTPUT.PUT_LINE built-in function to display (or so the developer thinks) the ID of that last pet updated by the cursor.

This block compiles successfully because a record called occupancy_rec is declared outside of the loop. The reference to occupancy_rec.pet_id in line 15 does not, however, return the value of the last pet ID from the cursor. It returns NULL, because it refers to the record declared explicitly on line 5, which is not the same record as that used inside the loop to receive fetched rows.

If you really do need to refer to the contents of the loop's record outside of the loop, don't use a cursor FOR loop. Instead, use a WHILE loop or infinite loop. Then the record you defined on line 5 will be the same as the record used inside the loop and will contain the last row fetched by the cursor. If, on the other hand, you need to reference the record only inside the loop, strip out any declarations that match the loop indexes. Leave it to PL/SQL to handle all references correctly.

7.4.3 When to Use the Cursor FOR Loop

As you have seen, the cursor FOR loop offers many advantages when you want to loop through all of the records returned by a cursor. This type of loop is not appropriate, however, when you need to apply conditions to each fetched record to determine if you should halt execution of the loop. Suppose you need to scan through each of the records from a cursor and stop when a total accumulation of a column like the maximum number of pets exceeds a maximum, as shown in the code below. Although you can do this with a cursor FOR loop by issuing an EXIT statement inside the loop, that is an inappropriate use of this construct:

1  DECLARE
2     CURSOR occupancy_cur IS
3        SELECT pet_id, room_number
4          FROM occupancy WHERE occupied_dt = SYSDATE;
5     pet_count INTEGER := 0;
6  BEGIN
7     FOR occupancy_rec IN occupancy_cur
8     LOOP
9        update_bill
10          (occupancy_rec.pet_id, occupancy_rec.room_number);
11       pet_count := pet_count + 1;
12       EXIT WHEN pet_count > :GLOBAL.max_pets;
13    END LOOP;
14 END;

The FOR loop explicitly states: "I am going to execute the body of this loop n times" (where n is a number in a numeric FOR loop, or each record in a cursor FOR loop). An EXIT inside the FOR loop short-circuits this logic. The result is code which is difficult to follow and debug.

If you need to terminate a loop based on information fetched by the cursor FOR loop, you should use a WHILE loop or infinite loop in its place. Then the structure of the code will more clearly state your intentions.