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

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 Thu Jun 17 1999 - 09:10:40 CDT