7.6.11. FOR SELECT

Used for

Looping row-by-row through a selected result set

Available in

PSQL

Syntax

FOR <select_stmt> [AS CURSOR cursorname]
  DO <compound_statement>

A FOR SELECT statement

• retrieves each row sequentially from the result set and executes the statement or block of statements on the 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 carry 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 that is located at the end of the SELECT …​ FROM …​ specification. In each iteration of the loop, the field values in 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 LEAVE statement

The Undeclared Cursor

The optional AS CURSOR clause surfaces the set in 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, in order to delete or update the current row before execution moves to the next iteration.

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 body code, 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

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

See also

DECLARE CURSOR, LEAVE, SELECT, UPDATE, DELETE