gn*****@rcn.com (robert) wrote in message news:<da**************************@posting.google. com>...
Mr. Kyte's article doesn't use cursors, while Mr. Feuerstein's
book examples do. my recollection of conventional wisdom is
to avoid using cursors.
is this difference merely a question of style, or is one
approach right and the other wrong?
oracle 8.1.7
thanks,
robert
well, we both use cursors -- it is *impossible* to get data otherwise!
The difference is "implicit" cursors vs "explicit" cursors.
There are two basic types of cursors in PL/SQL:
o Implicit cursor With this type of cursor, PL/SQL does most of the
work for you. You don't have to open close, declare, or fetch from an
implicit cursor.
o Explicit cursor With this type of cursor, you do all of the work.
You must open, close, fetch, and control an explicit cursor
completely.
There is a myth that explicit cursors are superior in performance and
usability to implicit cursors. However, the truth is that equivalent
implicit cursors are faster and much easier to code. Does that mean
you'll never use an explicit cursor? No, there are cases where
explicit cursors are used
o When you are performing large bulk operations. (bulk collect).
Note that in 10g, there is an automagical 100 row bulk collect in
place for implicit cursors so even this case is going the way of
mythology in the future...
o Explicit cursors are also necessary when you are using dynamic SQL
and fetching more than a single row. Here, you do not have a choice.
There is no way to dynamically process an implicit cursor. So, when
processing dynamic SQL using ref cursors, you will be using explicit
cursors
there are two cases to be looked at really:
o single row selects
o result sets (0, 1 or more rows)
for single row selects -- select .... INTO .... from .... is the *only
way to go*.
The select into does many things for us -- some people will say to
code this:
cursor c is ....
begin
open c;
fetch c;
close c;
but really, to get the functionality of the select into, you MUST
code:
cursor c is ...
begin
open c;
fetch c;
if c%notfound then raise an error;
fetch c;
if c%found then raise an error;
close c;
because a select into says "you shall get AT LEAST one and AT MOST one
row" -- it has all of that error checking nicely bundled into a single
call (if you want "at least one" but don't care about the "at most"
part -- just add "and rownum = 1" to your query and you are done). I
wrote about this in Effective Oracle By Design - the plsql chapter and
said:
Which should we use (discussingimplict vs explicit for select into
logic)? *Without exception*, we should use SELECT INTO, for the
following reasons:
o It is less code (less chance for bugs) and easier to read.
o It runs faster (more efficient).
o It makes our code safer (more bug-free).
Similar arguments are in place for the explicit vs implicit cursor for
loop construct -- when you are processing row by row.
I prefer to always code:
for x in ( select .... )
loop
(and if the select is heinously big -- well, we do have views! or we
could "cursor it" and
for x in CURSOR_NAME
loop
which is sort of an implicitly explicit cursor...)
This sets up a data structure for me (easy).
This reduces the code I have to write (easy).
This removes the bug I see in way too much code. If I had a penny for
everytime I've seen:
...
open c;
loop
fetch c into ...;
process record;
exit when c%notfound;
end loop;
close c;
the exit when is in the wrong place. I see this time and time and
time again. A mistake not possible with implicit cursors where the
code would just be:
for x in ( select ... )
loop
process record;
end loop;
