FirebirdSQL logo

The Undeclared Cursor

The optional AS CURSOR clause surfaces the result set of the FOR SELECT structure as an undeclared, named cursor that can be operated on using the WHERE CURRENT OF clause inside the statement or block following the DO command, to delete or update the current row before execution moves to the next row.In addition, it is possible to use the cursor name as a record variable (similar to OLD and NEW in triggers), allowing access to the columns of the result set (i.e. cursor_name.columnname).

Rules for Cursor Variables
  • When accessing a cursor variable in a DML statement, the colon prefix can be added before the cursor name (i.e. :cursor_name.columnname) for disambiguation, similar to variables.

    The cursor variable can be referenced without colon prefix, but in that case, depending on the scope of the contexts in the statement, the name may resolve in the statement context instead of to the cursor (e.g. you select from a table with the same name as the cursor).

  • Cursor variables are read-only

  • In a FOR SELECT statement without an AS CURSOR clause, you must use the INTO clause.If an AS CURSOR clause is specified, the INTO clause is allowed, but optional;you can access the fields through the cursor instead.

  • Reading from a cursor variable returns the current field values.This means that an UPDATE statement (with a WHERE CURRENT OF clause) will update not only the table, but also the fields in the cursor variable for subsequent reads.Executing a DELETE statement (with a WHERE CURRENT OF clause) will set all fields in the cursor variable to NULL for subsequent reads

Other points to take into account regarding undeclared cursors:

  1. The OPEN, FETCH and CLOSE statements cannot be applied to a cursor surfaced by the AS CURSOR clause

  2. The cursor_name argument associated with an AS CURSOR clause must not clash with any names created by DECLARE VARIABLE or DECLARE CURSOR statements at the top of the module body, nor with any other cursors surfaced by an AS CURSOR clause

  3. The optional FOR UPDATE clause in the SELECT statement is not required for a positioned update

Examples using FOR SELECT

  1. A simple loop through query results:

    CREATE PROCEDURE SHOWNUMS
    RETURNS (
      AA INTEGER,
      BB INTEGER,
      SM INTEGER,
      DF INTEGER)
    AS
    BEGIN
      FOR SELECT DISTINCT A, B
          FROM NUMBERS
        ORDER BY A, B
        INTO AA, BB
      DO
      BEGIN
        SM = AA + BB;
        DF = AA - BB;
        SUSPEND;
      END
    END
  2. Nested FOR SELECT loop:

    CREATE PROCEDURE RELFIELDS
    RETURNS (
      RELATION CHAR(32),
      POS INTEGER,
      FIELD CHAR(32))
    AS
    BEGIN
      FOR SELECT RDB$RELATION_NAME
          FROM RDB$RELATIONS
          ORDER BY 1
          INTO :RELATION
      DO
      BEGIN
        FOR SELECT
              RDB$FIELD_POSITION + 1,
              RDB$FIELD_NAME
            FROM RDB$RELATION_FIELDS
            WHERE
              RDB$RELATION_NAME = :RELATION
            ORDER BY RDB$FIELD_POSITION
            INTO :POS, :FIELD
        DO
        BEGIN
          IF (POS = 2) THEN
            RELATION = ' "';
    
          SUSPEND;
        END
      END
    END
    Tip
    Instead of nesting statements, this is generally better solved by using a single statements with a join.
  3. Using the AS CURSOR clause to surface a cursor for the positioned delete of a record:

    CREATE PROCEDURE DELTOWN (
      TOWNTODELETE VARCHAR(24))
    RETURNS (
      TOWN VARCHAR(24),
      POP INTEGER)
    AS
    BEGIN
      FOR SELECT TOWN, POP
          FROM TOWNS
          INTO :TOWN, :POP AS CURSOR TCUR
      DO
      BEGIN
        IF (:TOWN = :TOWNTODELETE) THEN
          -- Positional delete
          DELETE FROM TOWNS
          WHERE CURRENT OF TCUR;
        ELSE
          SUSPEND;
      END
    END
  4. Using an implicitly declared cursor as a cursor variable

    EXECUTE BLOCK
     RETURNS (o CHAR(63))
    AS
    BEGIN
      FOR SELECT rdb$relation_name AS name
        FROM rdb$relations AS CURSOR c
      DO
      BEGIN
        o = c.name;
        SUSPEND;
      END
    END
  5. Disambiguating cursor variables within queries

    EXECUTE BLOCK
      RETURNS (o1 CHAR(63), o2 CHAR(63))
    AS
    BEGIN
      FOR SELECT rdb$relation_name
        FROM rdb$relations
        WHERE
          rdb$relation_name = 'RDB$RELATIONS' AS CURSOR c
      DO
      BEGIN
        FOR SELECT
            -- with a prefix resolves to the cursor
            :c.rdb$relation_name x1,
            -- no prefix as an alias for the rdb$relations table
            c.rdb$relation_name x2
          FROM rdb$relations c
          WHERE
            rdb$relation_name = 'RDB$DATABASE' AS CURSOR d
        DO
        BEGIN
          o1 = d.x1;
          o2 = d.x2;
          SUSPEND;
        END
      END
    END

FOR EXECUTE STATEMENT

Executes dynamically created SQL statements and loops over its result set

Syntax
[label:]
FOR <execute_statement> DO <compound_statement>
Table 1. FOR EXECUTE STATEMENT Statement Parameters
Argument Description

label

Optional label for LEAVE and CONTINUE.Follows the rules for identifiers.

execute_stmt

An EXECUTE STATEMENT statement

compound_statement

A single statement, or statements wrapped in BEGIN …​ END, that performs all the processing for this FOR loop

The statement FOR EXECUTE STATEMENT is used, in a manner analogous to FOR SELECT, to loop through the result set of a dynamically executed query that returns multiple rows.

FOR EXECUTE STATEMENT Examples

Executing a dynamically constructed SELECT query that returns a data set
CREATE PROCEDURE DynamicSampleThree (
   Q_FIELD_NAME VARCHAR(100),
   Q_TABLE_NAME VARCHAR(100)
) RETURNS(
  LINE VARCHAR(32000)
)
AS
  DECLARE VARIABLE P_ONE_LINE VARCHAR(100);
BEGIN
  LINE = '';
  FOR
    EXECUTE STATEMENT
      'SELECT T1.' || :Q_FIELD_NAME ||
      ' FROM ' || :Q_TABLE_NAME || ' T1 '
    INTO :P_ONE_LINE
  DO
    IF (:P_ONE_LINE IS NOT NULL) THEN
      LINE = :LINE || :P_ONE_LINE || ' ';
  SUSPEND;
END