I just use Vim's split screen feature to leave the cursor definitions open in the top window, while scrolling the code at the bottom. I'd have to split
the screen anyway if the query in question spans lots of line (as many of
mine do) -- having the LOOP six or seven lines away from the FOR
is a REAL readability disaster for me.
The use of explicit cursors also allows me to define variables and
parameters of the row type. For example:
CREATE PACKAGE BODY mypackage
IS
CURSOR mycur IS SELECT col1, col2, col3 FROM mytable;
PROCEDURE cleanup_row( p_row IN mycur%ROWTYPE )
IS
BEGIN
-- do something with p_row
END cleanup_row;
BEGIN
FOR myrec IN mycur LOOP
cleanup_row( myrec );
END LOOP;
END mypackage;
As another point, on a recent code audit resulting from several key
changes in our data model, I had to look through all our PL/SQL procedures
to find the affected queries. Those that were declared as parameterized
cursors in the header of each program block were LOTS easier to find
and fix than those that made use of program variables (e.g.,
SELECT ... FROM ... WHERE colname = varname).
Basically, I guess I like the fact that explicit, parameterized cursors
allow me to treat queries as functions that return result sets.
As you say, a lot of it's a matter of taste. However, I'm not convinced
that performance should be the sole criterion here. I really value Mr. Kyte's
thoughts on the matter, but he only rarely uses queries of any significant
size in his examples. That, and I don't want to create views for queries that
are used in exactly one program unit.
