Re: Implicit vs. Explicit cursors

Dave Wotton wrote:
>
>
> 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?

The problem with code "standards" is that these days the standards are very different then in the days of FORTRAN or even the early days of C when Ritchie, Thompson and Kernighan had no concept of structured programs and the concept of throwing exceptions wasn't even known.

Exiting out of a loop "prematurely" isn't necessarily a bad idea. It is often a convenient way to handle certain situations. Sure, it can be abused and probably shouldn't be used by amateurs until they really understand what they are doing, but after years and years of writing code I find such shortcuts are just a part of normal coding and when it comes to languages like VB it actually pays off to be able to understand how to perform graceful error recovery by exiting out of a loop when a data error is discovered. The concept is no different in PL/SQL - just the syntax changes.

Keep in mind that Mr. Feurstein is primarily in the business of writing books not code.

..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................
..................................................................