Examples Using

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

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

`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.

Examples Using

Examples using FOR SELECT

FOR EXECUTE STATEMENT

Examples using `FOR SELECT`

`FOR EXECUTE STATEMENT`