Forum OpenACS Development: Cursors
I encountered some pl/sql code which makes use of cursors. One of the functions uses the cursors to loop through the result set and to perform different actions based on some logic. However, there are several functions which use cursors to perform a simple select. They will open the cursor as a select, then in the body of the function it has something like this:
OPEN this_cursor; FETCH this_cursor INTO this_variable; CLOSE this_cursor; OPEN that_cursor; FETCH that_cursor INTO that_variable; CLOSE that_cursor; return this_variable + that_variable;
Would there be any reason to use a cursor instead of select statement? If not, would it make sense to remove the cursors and make the function call a select statement(s) instead? Thanks.
The Oracle docs indicate that the NOT FOUND case *should* work just fine with simple selects, BTW, but in practice I found that not to be true and digging in ACS code a year or so ago found that they'd run into the same sort of problem, apparently (you can catch the exception raised when no row's returned but the NOT FOUND example in the Oracle 8i doc doesn't work).
As Dan says, this isn't a problem in PG. And as Gilbert's noted, the ACS use of cursors often doesn't check for the NOT FOUND case anyway.
"My rule of thumb is always to use an explicit cursor for all SELECT statements in my applications, even if an implicit cursor might run a little faster...
"By setting and following this clear-cut rule, I give myself one less thing to think about. I do not have to determine if a particular SELECT statement will return only one row...I do not have to wonder about the conditions under which a single-row query might suddenly return more than one row, thus requiring a TOO_MANY_ROWS exception handler. I am guaranteed to get vastly improved programmatic control over the data access and more finely-tuned exception handling for the cursor."
From page 166 of O'Reilly's _Oracle PL/SQL Programming_
Maybe the ACS team read the same, and put it into practice.
But ... in doing so it will be masking what is very likely a buggy query! This is not good! The query writer in this case has either miswritten the query or didn't understand the datamodel or the data contained within it.
PG does support cursors, BTW - it is PL/pgSQL which doesn't. The current, eventually-to-be 7.2, version of PL/pgSQL actually does but the version of PL/pgSQL contained in PG 7.1 and previous versions do not.
case when sum(price_charged) is null then 0::numeric else sum(price_charged) end
Is that the same as:
By the way, the Oracle code used cursors. Thanks.
Seems like it could mask all sorts of errors. The query could be correct, but the data model could have been setup incorrectly allowing duplicate data to get into the system. Or a uniqueness constraint could be dropped by accident and go undiscovered for a very long time. This is similar to assuming a catch placed around your code is going to help you find a bug.
My purpose in posting the above quote was in hopes that someone with real world experience would explain what was wrong with this reasoning. Thanks Don!