FOR - Execute SQL statements in a loop

The FOR-statement executes SQL statements in a loop over all records of an implicitly defined cursor. Cursor operations (e.g. FETCH) are not required here. Nor may they be used for the implicitly defined cursor. The implicitly defined cursor is automatically closed when processing has been concluded.

The ITERATE statement enables you to switch immediately to the next loop pass. The loop can be aborted by means of a LEAVE statement.

The FOR statement may only be specified in a routine, i.e. in the context of a CREATE PROCEDURE or CREATE FUNCTION statement. Routines and their use in SESAM/SQL are described in detail in chapter "Routines".

The FOR statement is a non-atomic SQL statement, i.e. further (atomic or non-atomic) SQL statements can occur in it.

If the FOR statement is part of a COMPOUND statement, in the case of corresponding exception handling routines the loop can also be left when a particular SQLSTATE (e.g. no data, class '02xxx') occurs.

---

[ label :]

FOR [ forloopname AS] [ cursor CURSOR FOR] query_expression

   DO routine_sql_statement; [ routine_sql_statement; ]...

END FOR [ label ]

forloopname ::= unqual_name

---

label

The label in front of the FOR statement (start label) indicates the start of the loop. It may not be identical to another label in the loop.

The start label need only be specified when the next loop pass is to be switched to using ITERATE or when the loop is to be left using a LEAVE statement. However, it should always be used to permit SESAM/SQL to check that the routine has the correct structure (e.g. in the case of nested loops).

The label at the end of the FOR statement (end label) indicates the end of the loop. If the end label is specified, the start label must also be specified. Both labels must be identical.

forloopname

Name of the FOR loop. It can be used to qualify the names of the columns of the subsequent cursor description.
forloopname may be up to 31 characters long.

cursor

Optional name for the cursor defined by query_expression.
This name must be specified if UPDATE ... WHERE CURRENT OF ... or DELETE ... WHERE CURRENT OF ... is to be used for the cursor or function.

query_expression

Definition of the cursor which is to be processed by the FOR statement.
The cursor must have unambiguously named columns. This can always be achieved by using correlation names.
The data types of the cursor’s output values may not be multiple. However, individual occurrences of a multiple field can be used.

routine_sql_statement

SQL statement which is to be executed in the FOR statement.
An SQL statement is concluded with a ";" (semicolon).
Multiple SQL statements can be specified one after the other. They are executed in the order specified.
No privileges are checked before an SQL statement is executed.
An SQL statement in a routine may access the parameters of the routine and (if the statement is part of a COMPOUND statement) local variables, but not host variables.

The syntax and meaning of routine_sql_statement are described centrally in section "SQL statements in routines". The SQL statements named there may not be used.

The SQL statements update_positioned_statement and delete_positioned_statement can also be executed for the corresponding cursor if cursor is specified and the query_expression is updatable (see section "Rules for updatable query expressions").

Execution information

The FOR statement is a non-atomic statement:

• • • If the FOR statement is part of a COMPOUND statement, the rules described there apply, in particular the exception routines defined there.

    • If the FOR statement is not part of a COMPOUND statement and one of the SQL statements reports an SQLSTATE, it is possible that only the updates of this statement will be undone. The FOR statement and the routine in which it is contained are aborted. The SQL statement in which the routine was used returns the SQLSTATE concerned.

Areas of validity and precedence rules for names

• • • In the case of an unqualified name (unqual_name), first an existing routine parameter or an existing local variable is used with this name. Otherwise the name is searched for in the current statement. If this name also does not exist there, the name (in the case of nested FOR statements) is searched for in the higher-ranking FOR statements “from the inside out”.

    • It is recommended that you define a name for the FOR loop (forloopname), see below. This makes name references within FOR loops clear. Precedence rules need not then be observed.

Name for a FOR loop:

In the SQL statements of the FOR statement, the current values can be referred to using the column names of the cursor description.

However, it is clearer if a name is defined for the FOR loop (forloopname). This name can be used to qualify the columns of the current row:

FOR F1 AS SELECT C001, C002 FROM T1 WHERE P < 127 
DO
   UPDATE TU
   SET COLX = COLX + F1.C001 WHERE COLY = F1.C002;
END FOR

This becomes apparent in a nested FOR statement:

FOR F1 AS SELECT C001 FROM T1 WHERE P < 127
DO
    FOR F2 AS SELECT C001, C002 FROM T2 WHERE Q < 875
    DO
        UPDATE TU
               SET COLX = COLX + F1.C001 + F2.C001
               WHERE COLY < F2.C002;
    END FOR;
END FOR

See also

CREATE PROCEDURE, CREATE FUNCTION, ITERATE, LEAVE