The WHILE statement executes SQL statements in a loop until the specified search condition is satisfied. The loop begins with a check, i.e. it can already be terminated before the first pass.
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 WHILE 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 WHILE statement is a non-atomic SQL statement, i.e. further (atomic or non-atomic) SQL statements can occur in it.
If the WHILE 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 :]
WHILE search_condition
DO routine_sql_statement; [ routine_sql_statement; ]...
END WHILE [ label ]
label
The label in front of the WHILE 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 WHILE 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.
search_condition
Search condition that returns a truth value when evaluated.
routine_sql_statement
SQL statement which is to be executed in the WHILE 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 WHILE statement is a non-atomic statement:
If the WHILE statement is part of a COMPOUND statement, the rules described there apply, in particular the exception routines defined there.
If the WHILE 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 SQL statement will be undone. The WHILE 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
The loop is executed until the variable i has a value < 100.
DECLARE i INTEGER DEFAULT 0;
label:
WHILE i < 100
DO
SET i = i+1;
...
END WHILE label;
See also
CREATE PROCEDURE, CREATE FUNCTION, ITERATE, LEAVE