What are the explicit cursors good for?

Geoff Worboys wrote:

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?

Roger Vellacott answers:

We use cursors quite a lot for next 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.