The WHILE statement is used to construct conditional loop statements. The statement list is executed repeatedly as long as the condition is True.
Syntax
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Parameters
Parameter |
Note |
|---|---|
| begin_label | Optional. The name of the tag at the start of the loop. |
| search_condition | The conditional expression is evaluated at the beginning of each loop iteration. If the result is True, then execute the statement_list; if it is False, the loop exits. |
| statement_list | List of SQL/PL statements to be executed repeatedly. Each statement ends with a semicolon (;). |
| end_label | Optional. The tag name at the end of the loop must be consistent with the begin_label The same. |
As long as the search_condition expression is true, the statement_list within the WHILE statement will be executed repeatedly. The statement_list consists of one or more SQL statements, each ending with a semicolon (;). You can use a label to mark the WHILE statement.
Examples
Example 1: A 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: Use a 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: Use 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