FirebirdSQL logo

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.