FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

DECLARE .. CURSOR

Declares a named cursor

Syntax
DECLARE [VARIABLE] cursor_name
  [[NO] SCROLL] CURSOR
  FOR (<select>);
Table 1. DECLARE …​ CURSOR Statement Parameters
Argument Description

cursor_name

Cursor name

select

SELECT statement

The DECLARE …​ CURSOR …​ FOR statement binds a named cursor to the result set obtained by the SELECT statement specified in the FOR clause.In the body code, the cursor can be opened, used to iterate row-by-row through the result set, and closed.While the cursor is open, the code can perform positioned updates and deletes using the WHERE CURRENT OF in the UPDATE or DELETE statement.

Note

Syntactically, the DECLARE …​ CURSOR statement is a special case of [fblangref50-psql-declare-variable].

Forward-Only and Scrollable Cursors

The cursor can be forward-only (unidirectional) or scrollable.The optional clause SCROLL makes the cursor scrollable, the NO SCROLL clause, forward-only.By default, cursors are forward-only.

Forward-only cursors can — as the name implies — only move forward in the dataset.Forward-only cursors only support the FETCH [NEXT FROM] statement, other fetch options raise an error.Scrollable cursors allow you to move not only forward in the dataset, but also back, as well as N positions relative to the current position.

Warning

Scrollable cursors are materialized as a temporary dataset, as such, they consume additional memory or disk space, so use them only when you really need them.

Cursor Idiosyncrasies

  • The optional FOR UPDATE clause can be included in the SELECT statement, but its absence does not prevent successful execution of a positioned update or delete

  • Care should be taken to ensure that the names of declared cursors do not conflict with any names used subsequently in statements for AS CURSOR clauses

  • 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 does this automatically.

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

  • The SELECT statement may contain parameters. For instance:

    SELECT NAME || :SFX FROM NAMES WHERE NUMBER = :NUM

    Each parameter has to have been declared beforehand as a PSQL variable, or as input or output parameters.When the cursor is opened, the parameter is assigned the current value of the variable.

Warning
Unstable Variables and Cursors

If the value of the PSQL variable used in the SELECT statement of the cursor changes during the execution of the loop, then its new value may — but not always — be used when selecting the next rows.It is better to avoid such situations.If you really need this behaviour, then you should thoroughly test your code and make sure you understand how changes to the variable affect the query results.

Note particularly that the behaviour may depend on the query plan, specifically on the indexes being used.Currently, there are no strict rules for this behaviour, and this may change in future versions of Firebird.