Repeats the execution of one or more statements until the specified condition evaluates to true.
ANSI Compliance
REPEAT is ANSI/ISO SQL:2011-compliant.
Required Privileges
None.
Invocation
Executable.
Stored procedures only.
Syntax
[ label_name : ] REPEAT statement [...] UNTIL conditional_expression END REPEAT [ label_name ] ;
Syntax Elements
- label_name
- An optional label for the REPEAT statement.
- statement
- A statement list to be executed.
- conditional_expression
- A boolean condition used to evaluate whether a statement or statements embedded within the REPEAT loop should be executed.
Usage Notes
- You can qualify the REPEAT statement with a label name. If a label name is provided for REPEAT, then:
- A LEAVE statement inside the block can use that label name to leave the REPEAT statement.
- If an ITERATE statement is specified within the block, and it refers to the label associated with REPEAT, the execution is continued from the beginning of the REPEAT statement without evaluating the conditional expression specified with the UNTIL clause.
- Exception Handling
If a statement within the REPEAT statement, or the conditional expression of the UNTIL clause raises an exception and the stored procedure contains a handler to handle that exception condition, the behavior is identical to exceptions occurring within a WHILE statement.
- Difference Between REPEAT and WHILE
REPEAT – END REPEAT is similar to the WHILE – END WHILE statement, with some differences.
REPEAT… WHILE … makes the first iteration unconditional. REPEAT always executes the sequence of statements at least once.
makes the first iteration and subsequent iterations only if a specified condition is true. executes statements until a specified condition is met. executes statements as long as a specified condition is met.
Example: Using a REPEAT Statement
The following example shows the use of a REPEAT statement:
CREATE PROCEDURE ProcessTrans(IN pAcctNum INTEGER IN pStartTrans INTEGER, IN pEndTrans INTEGER ) BEGIN DECLARE vTransNum INTEGER; SET vTransNum = pStartTrans; ...; REPEAT INSERT INTO trans (trans_num, acct_nbr) VALUES (vTransNum, pAcctNum); SET vTransNum = vTransNum + 1; UNTIL vTransNum > pEndTrans END REPEAT; ...; END;
Related Information
For more information about rules governing exceptions, see LEAVE.