Hi Geoff,

We use cursors quite a lot. Reasons...

1. Performance. Particularly when you open a record, read its contents, and update it or delete it.

OPEN MY_CURSOR;
FETCH MY_CURSOR INTO :FIELD_1, :FIELD_2...;
do some stuff.
UPDATE MY_TABLE WHERE CURRENT OF MY_CURSOR;

is much faster than

FOR SELECT KEY_FIELD, FIELD_1,FIELD_2... FROM MY_TABLE
INTO :KEY_FIELD, :FIELD_1,:FIELD_2... DO
BEGIN
do some stuff
UPDATE MY_TABLE SET FIELD_1 = NNN...
WHERE KEY_FIELD = :KEY_FIELD

For example, we have calendar tables running for several years into the future, and daily balances need to be frequently updated to reflect events happening today. Using cursors to run through the calendars to the end is spectacularly faster than a FOR SELECT loop with update queries.

2. Programming flexibility. I can have several cursors open at the same time. I can step each one of them forwards one record whenever I want to, and leave it there until I am ready to update it, or move on. FOR SELECT constructions, on the other hand, run without a pause to the end.

In some circumstances we have two cursors both stepping through the same table, one of them reading one kind of record, and the other other kinds of record. The results of one cursor affect the values of the other cursor. In effect, therefore, we have interleaved (not nested) FOR SELECT loops. The result is vastly more efficient than convoluted nesting of loops.



Roger Vellacott
Passfield Data Systems

--- In firebird-support@yahoogroups.com, Geoff Worboys <geoff@...> wrote:
>
> Hi All,
>
> I've trying to find out what I can about explicit cursors.
> (I've gone for years without ever using explicit cursors,
> so please bear with me.) What remains unclear to me is
> whether they are mostly a syntactic convenience or whether
> they have more explicit advantages.
>
> That is not to knock syntactic convenience, our PSQL code
> could benefit from more of it, but I am looking to try and
> work out whether I should be consciously trying to use
> cursors more often, or whether it's just a matter of what
> produces the clearest looking code.
>
> Take the example from the release notes:
>
> DECLARE RNAME CHAR(31);
> DECLARE FNAME CHAR(31);
> DECLARE C CURSOR FOR ( SELECT RDB$FIELD_NAME
> FROM RDB$RELATION_FIELDS
> WHERE RDB$RELATION_NAME = :RNAME
> ORDER BY RDB$FIELD_POSITION );
> BEGIN
> FOR
> SELECT RDB$RELATION_NAME
> FROM RDB$RELATIONS
> INTO :RNAME
> DO
> BEGIN
> OPEN C;
> FETCH C INTO :FNAME;
> CLOSE C;
> SUSPEND;
> END
> END
>
>
> As I understand it this code could equally well have been
> written using two cursors (replace the FOR SELECT with a
> separate cursor) or no cursors (replace C with simply:
> FOR SELECT ... ROWS 1).
>
> The given example is very neat and reads nicely as it is,
> but is there any other reason why it is better or worse
> than using two cursors or none?
>
> --
> Geoff Worboys
> Telesis Computing
>