oracle中exit when用法,关于编码风格：在Oracle中遍历CURSOR时使用EXIT WHEN指令是一种不好的做法吗？...

Loops

12. Take advantage of the cursor FOR loop.

The cursor FOR loop is

one of my favorite PL/SQL constructs. It leverages fully the tight and

effective integration of the procedural aspects of the language 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!

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.

DECLARE

CURSOR occupancy_cur IS

SELECT pet_id, room_number

FROM occupancy

WHERE occupied_dt = SYSDATE;

occupancy_rec occupancy_cur%ROWTYPE;

BEGIN

OPEN occupancy_cur;

LOOP

FETCH occupancy_cur

INTO occupancy_rec;

EXIT WHEN occupancy_cur%NOTFOUND;

update_bill

(occupancy_rec.pet_id,

occupancy_rec.room_number);

END LOOP;

CLOSE occupancy_cur;

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-ofdata 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 all I have is:

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 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 integrated with SQL should.