Re: Implicit vs. Explicit cursors

Kenneth C Stahl wrote in message <3933BF84.46C57CCC_at_Unforgettable.com>...
>Dave Wotton wrote:
>>
>> Kenneth C Stahl wrote in message <392E9441.FE8CAB23_at_Unforgettable.com>...
>>
>> >Also, completely forget about OPEN/FETCH/CLOSE for explicit cursors.
>> >Always use a cursor FOR loop. Once you get used to writing them and
>> >handling all of the possible exceptions/errors you'll never want to go
>> >back to the OPEN/FETCH/CLOSE syntax. Steve Feuerstein got this wrong in
>> >his book and too many people follow his advice without thinking through
>> >all of the consequences (he claims that there are no adverse
>> >consequences and tries to lead the reader to believe that there are
>> >inherent problems with the cursor FOR loop and trusts that the reader
>> >won't know better).
>>
>> Not in the second edition of his "Oracle PL/SQL Programming" book
>> O'Reilley, ISBN 1-56592-335-9) he doesn't. On page 33 he says:
>>
>> "The cursor FOR loop is one of my favourite PL/SQL constructs. It leverages
>> fully the tight and effective integration of the Ada-like programming
>> language with the power of the SQL database language. It reduces the volume
>> of the code you need to write to fetch data from a cursor. It greatly
>> lessens the chance of introducing errors in your programming - and loops
>> are one of the more error-prone parts of a program. Does this loop sound
>> too good to be true? Well, it isn't - it's all true."
>>
>> What book of Steven's were you reading?
>
>In the original book (copyright 1995) on page 213 the autor goes into a
>discussion about "When To Use The Cursor FOR Loop". It is in that
>section he gets it wrong because he makes the assumption that it is
>wrong to exit from a cursor for loop before fetching all rows in its
>range. He apparently didn't think this through very well or else he has
>never worked in a situation where this might be a normal and frequent
>occurring.

He *is* rather dogmatic about when to use the cursor FOR loop (he continues to argue, in the second edition, that a cursor FOR loop should only be used if you are going to process ALL the rows returned by the cursor, and not exit prematurely from the loop).

So, after making the claim that they're the greatest thing since sliced bread, Steve recommends constraints on cursor FOR loops which actually make them rarely useable in real-life, I'll agree.

Most of his arguments are not based on technical reasons, but on the grounds of "standards" and code legibility, and are akin to the books on writing C code which recommend not using for(...) loops if you will be terminating the loop prematurely. The argument is that the for loop (both the PL/SQL cursor one, and the C one) is asserting that the loop will be executed a number of times determined by the condition in the loop statement, and yet later in the code, there is an exit statement which contradicts this. Like Kenneth, I'm not so dogmatic to see this as an overwhelming reason to use cursor FOR loops more.

With regard to the efficiency of explicit vs implicit cursors, Feuerstein's book refers to the fact that implicit cursors are potentially less efficient than explicit cursors because of the requirement to implicitly fetch a second row to check that a second row is not present for a single row query. However, it is worth noting that this discussion is in the context of single-row queries (SELECT ... INTO ) and not queries used to return multiple rows. A single additional fetched row, when reading hundreds or thousands of rows will have little impact. He also states that PL/SQL release 2.3 and above has been optimised (somehow, he doesn't specify) to minimise this impact further.

Interestingly, he also states (page 166, second edition):

   "Always use Explicit cursors! ...
    With explicit cursors, you have complete control over how to access     information in the database. You decide when to OPEN the cursor,     when to FETCH records from the cursor (and therefore from the tables     in the SELECT statement of the cursor) how many rows to fetch and when     to CLOSE the cursor. Information about the current state of your cursor     is available through the examination of the cursor attributes. This     granularity of control makes the explicit cursor an invaluable tool for     your development effort."

Which is in stark contrast to my previous quote from his book.

I note that the book is written by Steve Feuerstein *with Bill Pribyl*. I wonder if we're seeing different opinions from the two authors?

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.