Re: Fine Grained Acces Control & cursor

thomas.kyte_at_oracle.com (Thomas Kyte) wrote in message news:<7b0834a8.0406060725.86a2686_at_posting.google.com>...
> 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

lots of useful information. thanks. (if only you were published by o'reilly; since they're again binding with Rep-Kover my prejudice has returned.)

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;
Received on Mon Jun 07 2004 - 15:29:24 CEST