Re: Fine Grained Acces Control & cursor

gnuoytr_at_rcn.com (robert) wrote in message news:<da3c2186.0406041150.67f109a9_at_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;

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; Received on Sun Jun 06 2004 - 17:25:01 CEST