Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 7.3 The Numeric FOR LoopChapter 7
Next: 7.5 The WHILE Loop

7.4 The Cursor FOR Loop

A cursor FOR loop is a loop that is associated with (actually defined by) an explicit cursor or a SELECT statement incorporated directly within the loop boundary. Use the cursor FOR loop whenever (and only if) you need to fetch and process each and every record from a cursor, which is a high percentage of the time 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_index IN cursor_name
   <executable statement(s)>

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

The following table summarizes the properties of the cursor FOR loop where record_index is a record declared implicitly by PL/SQL with the %ROWTYPE attribute against the cursor specified by cursor_name:



How the loop is terminated

The cursor FOR loop terminates unconditionally when all of the records in the associated cursor have been fetched. You can also terminate the loop with an EXIT statement, but this is not recommended.

When the test for termination takes place

After each execution of the loop body, PL/SQL performs another fetch. If the %NOTFOUND attribute of the cursor evalutes to TRUE, then the loop terminates. If the cursor returns no rows, then the loop never executes its body.

Reason to use this loop

Use the cursor FOR loop when you want to fetch and process every record in a cursor.

Let's take a look at how you can use the cursor FOR loop to streamline your code and reduce opportunities for error.

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:

2     CURSOR occupancy_cur IS
3        SELECT pet_id, room_number
4        FROM occupancy WHERE occupied_dt = SYSDATE;
5     occupancy_rec occupancy_cur%ROWTYPE;
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:

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

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:

2  CURSOR occupancy_cur IS
3     SELECT pet_id, room_number
4       FROM occupancy WHERE occupied_dt = SYSDATE;
5   occupancy_rec occupancy_cur%ROWTYPE;
8   FOR occupancy_rec IN occupancy_cur
9   LOOP
10    update_bill
11       (occupancy_rec.pet_id, occupancy_rec.room_number);
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:

2     CURSOR occupancy_cur IS
3        SELECT pet_id, room_number
4          FROM occupancy WHERE occupied_dt = SYSDATE;
5     pet_count INTEGER := 0;
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.

Previous: 7.3 The Numeric FOR LoopOracle PL/SQL Programming, 2nd EditionNext: 7.5 The WHILE Loop
7.3 The Numeric FOR LoopBook Index7.5 The WHILE Loop

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference