In the documentation here, the following code example is given for using a
declare cur cursor for
(select rdb$relation_name, rdb$system_flag from rdb$relations);
while (1=1) do
fetch cur into relation, sysflag;
if (row_count = 0) then leave;
for select rdb$relation_name, rdb$system_flag
into relation, sysflag
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 SELECTstatement with the
AS CURSORclause. Declared cursors must be explicitly opened, used to fetch data and closed. The context variable
ROW_COUNThas to be checked after each fetch and, if its value is zero, the loop has to be terminated. A
FOR SELECTstatement 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 ;)).