Firebird Cursors - Why You Used One

Question

The documentation here is the following code example for using 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 it 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 like to use it? Ultimately, the above example does not even require an execlute block , as it is just a simple select statement. Therefore, I believe that this example is too simple to demonstrate the advantage of this.

Answer 1

The documentation you are referencing (and its newer version 2.5) already includes most of the reasons why you (or would not want to) use the cursor (highlighted by me):

If the cursor is only needed to go through the result set , it is almost always simpler and less error prone to use the FOR SELECT with the AS CURSOR clause. Declared cursors must be explicitly open, used to retrieve data and close. The context variable ROW_COUNT must be checked after each sample, and if its value is zero, the cycle must be completed. The FOR SELECT checks it automatically.

However, declared cursors provide a high level of control over sequential events and allow multiple cursors to be controlled in parallel.

In short, you should usually use FOR SELECT , unless you need to access multiple cursors at the same time or perhaps need more complex logic than just a simple loop. It also allows you to reuse the same cursor definition in several parts of your code (although this may mean that you need to break the code in multiple stored procedures).

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

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

Answer 2

Another situation where you can use cursors is when you need to update the extracted rows and find or reposition (defining the exact WHERE clause), the rows can be a problem. In this case, you can open cursors with the FOR UPDATE clause and update (or delete) rows using the WHERE CURRENT OF clause.