The WHILE statement is used to construct conditional loops that repeat a list of statements as long as the condition is true.
Syntax
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Parameters
Parameter |
Description |
|---|---|
| begin_label | Optional. The label name at the start of the loop. |
| search_condition | A condition expression that is evaluated at the beginning of each loop iteration. If the result is true, the statement_list is executed; if false, the loop exits. |
| statement_list | A list of SQL/PL statements to be repeated. Each statement is terminated with a semicolon (;). |
| end_label | Optional. The label name at the end of the loop, which must match begin_label. |
The statement_list in the WHILE statement is repeated as long as the search_condition expression evaluates to true. The statement_list consists of one or more SQL statements, each terminated with a semicolon (;). You can use label to mark the WHILE statement.
Examples
Example 1: Basic WHILE loop.
obclient> DELIMITER //
obclient> CREATE PROCEDURE while_proc()
BEGIN
DECLARE v1 INT DEFAULT 10;
WHILE v1 > 0 DO
SET v1 = v1 - 1;
END WHILE;
SELECT v1;
END //
obclient> DELIMITER ;
obclient> CALL while_proc();
The query result is as follows:
+------+
| v1 |
+------+
| 0 |
+------+
1 row in set
Example 2: WHILE loop with labels.
obclient> DELIMITER //
obclient> CREATE PROCEDURE labeled_while()
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE sum_result INT DEFAULT 0;
while_label: WHILE counter < 10 DO
SET counter = counter + 1;
SET sum_result = sum_result + counter;
END WHILE while_label;
SELECT sum_result;
END //
obclient> DELIMITER ;
obclient> CALL labeled_while();
The query result is as follows:
+------------+
| sum_result |
+------------+
| 55 |
+------------+
1 row in set
Example 3: Using WHILE in a stored function.
obclient> DELIMITER //
obclient> CREATE FUNCTION factorial(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result INT DEFAULT 1;
DECLARE i INT DEFAULT 1;
WHILE i <= n DO
SET result = result * i;
SET i = i + 1;
END WHILE;
RETURN result;
END //
obclient> DELIMITER ;
obclient> SELECT factorial(5);
The query result is as follows:
+------------+
| factorial(5) |
+------------+
| 120 |
+------------+
1 row in set