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

[Quoted] 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).

[Quoted] 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 Thu Jun 17 1999 - 16:10:40 CEST