LOOP - Execute SQL statements in a loop

The LOOP statement executes SQL statements in a loop.

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 LOOP 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 LOOP statement is a non-atomic SQL statement, i.e. further (atomic or non-atomic) SQL statements can occur in it.

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

---

[ label :]

LOOP

      routine_sql_statement; [ routine_sql_statement; ]...

END LOOP [ label ]

---

label

The label in front of the LOOP 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 procedure has the correct structure (e.g. in the case of nested loops).

The label at the end of the LOOP 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.

routine_sql_statement

SQL statement which is to be executed in the LOOP 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.

Execution information

The LOOP statement is a non-atomic statement:

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

  • If the LOOP 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 LOOP statement and the routine in which it is contained are aborted. The SQL statement in which the routine was used returns the SQLSTATE concerned.

Example

A loop is canceled after 1000 passes by means of LEAVE.

DECLARE i INTEGER DEFAULT 0; 
   ...
   label: 
   LOOP
       SET i = i+1; 
       IF i > 1000 THEN LEAVE label; 
       ...
   END LOOP label;

See also

CREATE PROCEDURE, CREATE FUNCTION, ITERATE, LEAVE