FirebirdSQL logo

Caveats with EXECUTE STATEMENT

  1. There is no way to validate the syntax of the enclosed statement

  2. There are no dependency checks to discover whether tables or columns have been dropped

  3. Execution is considerably slower than when the same statements are executed directly as PSQL code

  4. Return values are strictly checked for data type to avoid unpredictable type-casting exceptions.For example, the string '1234' would convert to an integer, 1234, but 'abc' would give a conversion error

All in all, this feature is meant to be used cautiously, and you should always take the caveats into account.If you can achieve the same result with PSQL and/or DSQL, it will almost always be preferable.

FOR SELECT

Loops row-by-row through a query result set

Syntax
[label:]
FOR <select_stmt> [AS CURSOR cursor_name]
  DO <compound_statement>
Table 1. FOR SELECT Statement Parameters
Argument Description

label

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

select_stmt

SELECT statement

cursor_name

Cursor name.It must be unique among cursor names in the PSQL module (stored procedure, stored function, trigger or PSQL block)

compound_statement

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

The FOR SELECT statement

  • retrieves each row sequentially from the result set, and executes the statement or block of statements for each row.In each iteration of the loop, the field values of the current row are copied into pre-declared variables.

    Including the AS CURSOR clause enables positioned deletes and updates to be performed — see notes below

  • can embed other FOR SELECT statements

  • can contain named parameters that must be previously declared in the DECLARE VARIABLE statement or exist as input or output parameters of the procedure

  • requires an INTO clause at the end of the SELECT …​ FROM …​ specification if AS CURSOR is absentIn each iteration of the loop, the field values of the current row are copied to the list of variables specified in the INTO clause.The loop repeats until all rows are retrieved, after which it terminates

  • can be terminated before all rows are retrieved by using a BREAK, LEAVE or EXIT statement

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