Purpose
Repeats the execution of one or more statements embedded within the defined iteration statement.
Invocation
Executable.
Stored procedures only.
Syntax
[ label_name : ] LOOP statement [...] END LOOP [ label_name ] ;
- label_name
- An optional label for the LOOP statement.
- statement
- A statement list to be processed unconditionally. The list can contain any of the following:
- SQL DML, DDL or DCL statements, including dynamic SQL.
- Control statements, including BEGIN … END.
ANSI Compliance
LOOP is ANSI/ISO SQL:2011-compliant.
Authorization
None.
Causes of LOOP-Terminating Errors
- If a statement in the LOOP raises an exception condition and a CONTINUE handler has been declared for that condition, then the stored procedure execution continues.
- If an EXIT handler has been declared, then the statement terminates the stored procedure execution.
- If a statement within the loop raises an error condition and its associated SQLSTATE code is not defined for a handler, then both the loop and the stored procedure terminate.
Rules
- You can qualify LOOP with a statement label.
A LEAVE statement specified within the LOOP breaks the iteration statement, passing control to the next statement following the statement with that label.
- You must specify a LEAVE statement inside the LOOP statement to ensure normal termination of the statement.
If you do not, the loop iterates continuously and can only be stopped by an asynchronous abort.
Example: The LOOP Statement
The following LOOP statement is valid:
L1: LOOP INSERT INTO transaction (trans_num, account_num) VALUES (hCounter, hAccountNum); SET hCounter = hCounter - 1; IF hCounter = 0 THEN LEAVE L1; END IF; END LOOP L1;