Is it wrong to use EXIT WHEN statement when going through CURSOR in Oracle?
This may sound like a silly question, but I hope to make myself clear enough.
- Speaking of Spaghetti Code , it is based on the use of GOTO. I had a peer used to tell if I put a breakpoint at the end of the code and that breakpoint will not be hit every time, something is wrong.
- However, this is a common (and, I would say, the rule) use of
EXIT WHEN
structure in Oracle packages (usually followed by a%NOTFOUND
test).
Whereas using EXIT
breaks the programming flow, isn't there something that doesn't match between 1 and 2?
Are all PL / SQL programmers after bad practice? Does PL / SQL follow this particular pattern for conditionals?
Is there any performance reason in the Oracle cap for using assertions like this?
Sorry if this question has already been asked, I haven't found anything like this.
source to share
Yes, many people follow bad practice.
Bad style
I agree with @Osy that OPEN / FETCH / CLOSE adds completely unnecessary code. I would go even further and say that you will almost never use CURSOR
.
First of all, you usually want to do as much as possible in plain SQL. If you need to use PL / SQL, use an implicit cursor. It will save you a line of code and help you bring related logic closer together.
It is my firm belief that the individual units of code will be as small as possible. At first glance it seems that it CURSOR
can help you with this. You can define your SQL at the top in one place and then execute the PL / SQL loop later.
But in reality, this extra layer of indirection is almost never worth it. Sometimes there is a lot of logic in SQL and sometimes there is a lot of logic in PL / SQL. But in practice, it rarely makes sense to put a lot of complex logic into both. Your code usually ends with one of these:
for records in (<simple SQL>) loop
<complex PL/SQL>
end loop;
or
for records in
(
<complex SQL>
) loop
<simple PL/SQL>;
end loop;
Either way, one of your sections of code will be very small. The difficulty of separating these two sections of code is greater than the complexity of a larger, one section of code. (But this is obviously my opinion.)
Poor performance
Significant performance implications when using OPEN / FETCH / CLOSE. This method is much slower than using a loop cursor or an implicit cursor.
The compiler can automatically use bulk collection in some for loops. But, to quote from Oracle's presentation "PL / SQL-Debunking the Myths Performance" , p. 122:
Don't throw this chance away using an open, sample, closed form
Here's an example:
--Sample data
create table t(a number, b number);
insert into t select level, level from dual connect by level <= 100000;
commit;
--OPEN/FETCH/CLOSE
--1.5 seconds
declare
cursor test_cur is
select a, b from t;
test_rec test_cur%rowtype;
counter number;
begin
open test_cur;
loop
fetch test_cur into test_rec;
exit when test_cur%notfound;
counter := counter + 1;
end loop;
close test_cur;
end;
/
--Implicit cursor
--0.2 seconds
declare
counter number;
begin
for test_rec in (select a, b from t) loop
counter := counter + 1;
end loop;
end;
/
source to share
This is highly recommended to make it easier to write your code, so I can tell you what the PL / SQL guru has to say about it:
NOTE. In some cases it is not recommended to use CURSOR-FOR-LOOP. You may consider another sane way to choose one SELECT-INTO or BULK COLLECT as per your needs.
Source: On Cursor FOR Loops, Oracle Magazine Steven Feuerstein
(Ref: Feuerstein, TOP TWENTY PL / SQL TIPS AND TECHNIQUES ):
Cycles
12. Use the FOR loop cursor.
The cursor FOR loop is one of my favorite PL / SQL constructs. It takes full advantage of the tight and efficient integration of the procedural aspects of the language with the power of the SQL database language. This reduces the amount of code you need to write to retrieve data from the cursor. This greatly reduces the chance of loop errors in your programming - and loops are one of the most error-prone parts of your program. Does this loop sound too good to be true? Well, it's not - it's all true!
Suppose I need to update bills for all pets staying at my pet's hotel, 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 for all passengers in the hotel. The update_bill routine adds any new changes to the pet numbers.
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 a cursor (line 2), you must explicitly declare a record for a cursor (line 5), open a cursor (line 7), start an infinite loop, select a line from the cursor set to a record (line 9), check for end-ofdata with the cursor attribute (line 10) and finally do the update. When it's over, you must remember to close the cursor (line 14). If I convert this PL / SQL block to use a FOR loop cursor, then I am all 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 FOR loop cursor! Gone is the record declaration. Gone - OPEN, FETCH and CLOSE statements. Gone, you need to check the% FOUND attribute. Gone makes sure everything is right. Instead, you say PL / SQL, in effect :: ÒYou and I both know that I want each row, and I want to flush that row into the entry corresponding to the cursor. I take care of this, right? “And PL / SQL will take care of it, like a modern SQL-integrated programming language should.
source to share