Transact-SQL offers the WHILE loop. The PL/SQL unconditional LOOP and FOR LOOP statements are not supported. The GOTO statement can also be used for looping purposes:
WHILE Boolean_expression
{sql_statement | statement_block}
[BREAK] [CONTINUE]
The WHILE loop tests a Boolean expression for the repeated execution of one or more statements. The statement(s) are executed repeatedly as long as the specified expression evaluates to TRUE. If multiple statements are to be executed, they must be placed within a BEGIN...END block.
| Oracle | SQL Server |
|---|---|
| DECLARE COUNTER NUMBER; BEGIN COUNTER := 0 WHILE (COUNTER <5) LOOP COUNTER := COUNTER + 1; END LOOP; END; |
DECLARE @COUNTER NUMERIC SELECT@COUNTER = 1 WHILE (@COUNTER <5) BEGIN SELECT @COUNTER = @COUNTER +1 END |
Statement execution can be controlled from inside the loop with the BREAK and CONTINUE keywords. The BREAK keyword causes an unconditional exit from the WHILE loop. The CONTINUE keyword causes the WHILE loop to restart, skipping any statements that follow.
| BREAK | CONTINUE |
| Using WHILE...BREAK or CONTINUE | WHILE |