The REPEAT statement executes SQL statements in a loop until the specified condition is satisfied. The loop ends with the condition being checked, i.e. it is executed at least once.
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 REPEAT 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 REPEAT statement is a non-atomic SQL statement, i.e. further (atomic or non-atomic) SQL statements can occur in it.
If the REPEAT 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 :]
REPEAT routine_sql_statement; [ routine_sql_statement; ]...
UNTIL search_condition
END REPEAT [ label ]
label
The label in front of the REPEAT 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 REPEAT 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
The search condition is the stop criterion for the loop.
routine_sql_statement
SQL statement which is to be executed in the REPEAT 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 REPEAT statement is a non-atomic statement:
If the REPEAT statement is part of a COMPOUND statement, the rules described there apply, in particular the exception routines defined there.
If the REPEAT 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 REPEAT 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 the value.
DECLARE i INTEGER DEFAULT 0;
...
label:
REPEAT
SET i= i+2;
...
UNTIL i >1000
END REPEAT
label;
See also
CREATE PROCEDURE, CREATE FUNCTION, ITERATE, LEAVE