FirebirdSQL logo

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.

Examples Using Named Cursors

  1. Declaring a named cursor in a trigger.

    CREATE OR ALTER TRIGGER TBU_STOCK
      BEFORE UPDATE ON STOCK
    AS
      DECLARE C_COUNTRY CURSOR FOR (
        SELECT
          COUNTRY,
          CAPITAL
        FROM COUNTRY
      );
    BEGIN
      /* PSQL statements */
    END
  2. Declaring a scrollable cursor

    EXECUTE BLOCK
      RETURNS (
        N INT,
        RNAME CHAR(63))
    AS
      - Declaring a scrollable cursor
      DECLARE C SCROLL CURSOR FOR (
        SELECT
          ROW_NUMBER() OVER (ORDER BY RDB$RELATION_NAME) AS N,
          RDB$RELATION_NAME
        FROM RDB$RELATIONS
        ORDER BY RDB$RELATION_NAME);
    BEGIN
      / * PSQL statements * /
    END
  3. A collection of scripts for creating views with a PSQL block using named cursors.

    EXECUTE BLOCK
    RETURNS (
      SCRIPT BLOB SUB_TYPE TEXT)
    AS
      DECLARE VARIABLE FIELDS VARCHAR(8191);
      DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
      DECLARE VARIABLE RELATION RDB$RELATION_NAME;
      DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
      DECLARE VARIABLE CUR_R CURSOR FOR (
        SELECT
          RDB$RELATION_NAME,
          RDB$VIEW_SOURCE
        FROM
          RDB$RELATIONS
        WHERE
          RDB$VIEW_SOURCE IS NOT NULL);
      -- Declaring a named cursor where
      -- a local variable is used
      DECLARE CUR_F CURSOR FOR (
        SELECT
          RDB$FIELD_NAME
        FROM
          RDB$RELATION_FIELDS
        WHERE
          -- the variable must be declared earlier
          RDB$RELATION_NAME = :RELATION);
    BEGIN
      OPEN CUR_R;
      WHILE (1 = 1) DO
      BEGIN
        FETCH CUR_R
        INTO :RELATION, :SOURCE;
        IF (ROW_COUNT = 0) THEN
          LEAVE;
    
        FIELDS = NULL;
        -- The CUR_F cursor will use the value
        -- of the RELATION variable initiated above
        OPEN CUR_F;
        WHILE (1 = 1) DO
        BEGIN
          FETCH CUR_F
          INTO :FIELD_NAME;
          IF (ROW_COUNT = 0) THEN
            LEAVE;
          IF (FIELDS IS NULL) THEN
            FIELDS = TRIM(FIELD_NAME);
          ELSE
            FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
        END
        CLOSE CUR_F;
    
        SCRIPT = 'CREATE VIEW ' || RELATION;
    
        IF (FIELDS IS NOT NULL) THEN
          SCRIPT = SCRIPT || ' (' || FIELDS || ')';
    
        SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
        SCRIPT = SCRIPT || SOURCE;
    
        SUSPEND;
      END
      CLOSE CUR_R;
    END