WHILE

You can use the WHILE statement to build a basic conditional statement.

The syntax is as follows:

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

statement_list in the WHILE statement will be executed repeatedly provided that the result of the search_condition expression is True. statement_list consists of one or more SQL statements. Each SQL statement ends with a semicolon (;). You can use label to label a WHILE statement.

Here is an example:

obclient> DELIMITER //

obclient> CREATE PROCEDURE while_proc()
   BEGIN
     DECLARE v1 INT DEFAULT 10;
     WHILE v1 > 0 DO
       SET v1 = v1 - 1;
     END WHILE;
   END //
Query OK, 0 rows affected