Firebird Cursors - Why would you use one

Question

In the documentation here, the following code example is given for using a cursor:

execute block
returns (
  relation char(31), 
  sysflag int)
as
declare cur cursor for 
  (select rdb$relation_name, rdb$system_flag from rdb$relations);
begin
  open cur;
  while (1=1) do
  begin
    fetch cur into relation, sysflag;
    if (row_count = 0) then leave;
    suspend;
  end
  close cur;
end

But this can also be done as follows:

execute block
returns (
  relation char(31), 
  sysflag int)
as
begin
  for select rdb$relation_name, rdb$system_flag 
  from rdb$relations 
  into relation, sysflag 
  do begin
    suspend;
  end
end

So why would I want to use one? Ultimately the above example doesn't even need execlute block as it's just a simple select statement. So I suppose the example is just too simple to showcase a benefit of this.

The current version of the documentation contains a little more complex example: http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-declare-cursor — Mark Rotteveel, May 26 '16 at 07:20

Mark Rotteveel · Accepted Answer · 2020-12-31T16:35:55.573

The documentation you link to (and its newer 2.5 counterpart) already includes most of the reasons why you would (or would not) use a cursor (emphasis mine):

If the cursor is needed only to walk the result set, it is nearly always easier and less error-prone to use a FOR SELECT statement with the AS CURSOR clause. Declared cursors must be explicitly opened, used to fetch data and closed. The context variable ROW_COUNT has to be checked after each fetch and, if its value is zero, the loop has to be terminated. A FOR SELECT statement checks it automatically.

Nevertheless, declared cursors provide a high level of control over sequential events and allow several cursors to be managed in parallel.

So in short, you should usually use FOR SELECT, except when you need access to multiple cursors at the same time, or maybe need some more complicated logic than just a simple loop. It also makes it possible to reuse the same cursor definition in multiple parts of your code (although that might indicate you need to break up your code in multiple stored procedures).

Presence of a tool does not mean that it should be used for everything.

As an aside, a FOR SELECT is also a cursor, except you don't have explicit control over it (it hides most of the ugliness ;)).

score 1 · Answer 2 · answered Jun 22 '16 at 14:46

Another situation one might use cursors is when it's needed to update retrieved rows, and finding or repositioning (determining the exact WHERE clause) the rows could be an issue. In this case, you can open cursors with FOR UPDATE clause, and update (or delete) rows using WHERE CURRENT OF clause.

Firebird Cursors - Why would you use one

2 Answers2