Declaring a named cursor

    Available in

    PSQL

    Syntax

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

    • 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 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.

    Attention!

    If the value of a PSQL variable used in the SELECT statement changes during the loop, its new value may (but not always) be used for the remaining rows. It is better to avoid having such situations arise unintentionally. If you really need this behaviour, you should test your code carefully to be certain that you know exactly how changes in the variable affect the result.

    Note particularly that the behaviour may depend on the query plan, specifically on the indexes being used. No strict rules are in place for situations like this currently, but that could change in future versions of Firebird.

    Examples Using Named Cursors

    1. Declaring a named cursor in the trigger.

    2. A collection of scripts for creating views with a PSQL block using named cursors.

    See also