Re: PLS/SQL: OK to use EXIT in a cursor FOR LOOP ??

I'm going to guess that if it were really done on 10,000,000 rows that I'd run into a "snapshot too old" long before I ever get to the 10,000,000 count.

Ken

Genesis wrote:
>
> Guys,
>
> Before you put down Steve Feuerstein, try to use "for .... loop" with cursor
> having "select .... for update" option on 10,000,000 rows table.
>
> Regrards,
> Gene
>
> Kenneth C Stahl wrote in message <376901E0.60FDC704_at_lucent.com>...
> >That is a perfectly valid way to do what you describe. Steve Feuerstein,
> the
> >author of the O'Reilly book "Oracle PL/SQL Programming" says that it is
> poor
> >practice and touts the open/fetch/close scenario, but he seems to have a
> >fundamental lack of understanding that the advantages of using cursor "for"
> >loops outways any "computer weenie" complaints that exiting from a cursor
> >"for" loop amounts to a "goto".
> >
> >I notice that one of the other respondees to your question also mentioned
> >the open/fetch/close. I disagree with him. I have been writing PL/SQL since
> >1973 and except for a few instances in the very early months of my
> >experience I have never written the open/fetch/close sequence and have
> >always used cursor "for" loops. I also don't write implicit cursors except
> >in triggers (with the exception that I will initialize a date variable with
> >SYSDATE and that amounts to an implied implicit cursor).
> >
> >You'll never go wrong if you use cursor "for" loops. The biggest reason is
> >that if you ever raise an exception, the PL/SQL engine will automatically
> >close the cursor as soon as execution passes out of scoop for the loop. The
> >same thing goes with an exit - the engine automatically performs the close
> >and any necessary cleanup.
> >
> >IMHO - forget about the open/fetch/close sequence and always write cursor
> >"for" loop. If you need to jump out when a certain condition exists, use
> the
> >'exit' statement.
> >
> >Ken
> >
> >Andy Hardy wrote:
> >
> >> Hello,
> >>
> >> I used a cursor FOR LOOP to work my way through some ordered data. I now
> >> want to limit the number of rows considered using something like ROWNUM.
> >> However, as ROWNUM is considered before ORDER I cannot simply add it to
> >> the query.
> >>
> >> I *could* create a FOR LOOP with an EXIT clause based on the %ROWCOUNT,
> >> but wonder if this is the *correct* way to do this or would it upset the
> >> Oracle cursor management?
> >>
> >> Any thoughts?
> >>
> >> E.G
> >>
> >> CURSOR my_csr
> >> IS
> >> SELECT a, b, C
> >> FROM my_table
> >> ORDER BY d
> >> ;
> >>
> >> FOR v_data IN my_cursor LOOP
> >> EXIT WHEN my_cursor%ROWCOUNT > 5;
> >> -- do the real work
> >> --
> >> END LOOP;
> >>
> >> --
> >> Andy Hardy. PGP key available on request
> >> ===============================================================
> >
Received on Tue Jun 22 1999 - 20:25:33 CDT