Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
Repeats the execution of a statement list.
[ statement-label : ] [ WHILE search-condition ] LOOP statement-list END LOOP [ statement-label ]
The WHILE and LOOP statements are control statements that allow you to execute a list of SQL statements repeatedly while a search-condition evaluates to TRUE. The LEAVE statement can be used to resume execution at the first statement after the END LOOP.
If the ending statement-label is specified, it must match the beginning statement-label.
The LOOP/END LOOP statement is part of optional ANSI/ISO SQL Language Feature P002, "Computational completeness". In the ANSI/ISO SQL Standard, the WHILE DO/END WHILE statement is a separate statement that is also part of Language Feature P002. The syntax combination WHILE search-condition LOOP supported in the software is not in the standard.
LOOP is not supported in the Transact-SQL dialect. Looping within Transact-SQL stored procedures is done with the Transact-SQL WHILE statement.
The following example fragment shows a WHILE loop in a procedure.
... SET i = 1; WHILE i <= 10 LOOP INSERT INTO Counters( number ) VALUES ( i ); SET i = i + 1; END LOOP; ...
The following example fragment shows a labeled LOOP in a procedure.
SET i = 1; lbl: LOOP INSERT INTO Counters( number ) VALUES ( i ); IF i >= 10 THEN LEAVE lbl; END IF; SET i = i + 1; END LOOP lbl