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

In article <BnVb3.1343$el4.45133_at_ord-read.news.verio.net>, Genesis <rindner_at_wwa.com> writes
>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.
>

Ooo... I always try to avoid the 'for update'! So much locking...

How is this affected by the open/fetch/close compared to the for cursor loop?

Andy
>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
>>> ===============================================================
>>
>
>

--
Andy Hardy. PGP key available on request