The Undeclared Cursor

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

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:

The OPEN, FETCH and CLOSE statements cannot be applied to a cursor surfaced by the AS CURSOR clause
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
The optional FOR UPDATE clause in the SELECT statement is not required for a positioned update

Examples using `FOR SELECT`

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

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.

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

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

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

The Undeclared Cursor

The Undeclared Cursor

Examples using FOR SELECT

Examples using `FOR SELECT`