Repeats the execution of one or more statements embedded within the defined iteration statement.
ANSI Compliance
LOOP is ANSI/ISO SQL:2011-compliant.
Required Privileges
None.
Invocation
Executable.
Stored procedures only.
Syntax
[ label_name : ] LOOP statement [...] END LOOP [ label_name ] ;
Syntax Elements
- 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.
Usage Notes
- 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.
- 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.
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;