Re: Trigger error (implicit vs explicit cursors)

mcstock wrote:

><snipped>
>
>an possible alternative to an explicit cursor when the TOO_MANY_ROWS must be
>handled (i.e., >1 row returned when only the first row is required) would be
>to code a 'non-looping cursor loop' -- i.e., put the implicit cursor in a
>FOR loop and exit immediately after saving the fetched value(s) into a
>variable(s0 -- this performs comparably to the explicit cursor for all 3
>cases (0, 1, >1 rows) but may be considered bad programming style by some
>
>

Why bother with the loop at all?

OPEN cursor
FETCH
CLOSE cursor

>regarding the arrticle's suggestions, i respectuflly disagree with the
>assertion that implicit cursors are always better and always easier to code
>and debug. i also disagree some of the reasoning and examples used in the
>article. since the article is over two years old, i'm not posting to the
>article itself, but since it was included as a refrence on this thread, i'll
>include my comment here:
>
>[_] the observation in the article that the statement 'close c' is hard to
>figure out when reviewing code is not a problem with explicit cursors, but
>with poor naming conventions
>

On this one I'll agree. I rarely find value in arguments that things are hard to figure out. I keep wanting
to say "only if you don't know what you are doing." Though I'd certainly not say that to Tom. ;-)

>[_] avoiding open/fetch/close because of the possiblility of forgetting to
>close the cursor is like avoiding PL/SQL blocks because of the possibility
>of forgetting to code an exception handler -- these things are handled by
>decent standards and QA
>

Same comment as above.

>[_] use explicitly declared cursors in all but very trivial cases (which
>often become non-trivial before too long)
>

Can't agree. As you acknowledged above. There is a performance hit. And however small it is
real and should not be ignored. I'd rather have a developer spend and extra hour writing something
once than have a hit on my system for the next 10 years.

>[_] only use implicit cursors (SELECT INTO) for single row-selects do not
>need to handle the TOO_MANY_ROWS exception
>

I'll agree except in the case of CAST(MULTISET( ... at least tentatively. I haven't time to fully
consider this.

>[_] use implicit cursor processing (i.e. for loops vs the explicit
>open/fetch/close) when multi-row result sets are expected
>

You are mixing metaphors. If you are refering to SELECT INTO you have no choice. If you are
referring to CAST(MULTISET( you are dead wrong. And if you are referring to something that
could be accomplished with an IMPLICIT CURSOR I disagree further. Once again you are
ignoring preformance and scalability issues and they are not trivial.

>[_] depending on your coding style, consider a non-looping FOR loop
>(immediate exit after setting a variable) for single row selects where there
>is a possibility of a discardable 2nd row. if that seems like a bad
>construct (due to coding style preferences) go with the explicit
>open/fetch/close to avoid the overhead of handling the TOO_MANY_ROWS
>exception
>

As I pointed out above, why write a loop that doesn't loop? Serves no useful purpose except
to waste ticks on the clock.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)