MySQL - WHILE Statement

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. These procedures contain IN and OUT parameters, or both. They may return result sets in case you use SELECT statements; they can return multiple result-sets. You can also create functions in MYSQL.

Similar to other programming languages MySQL provides support for the flow control statements such as IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT. You can use these statements in the stored programs (procedures), and RETURN in stored functions. You can use one Flow Control Statement with in another.

MySQL WHILE Statement

The WHILE is a compound MySQL statement which is used to execute a single or set of statements repeatedly as long as the specified condition is TRUE.

Syntax

Following is the syntax of the WHILE statement is MySQL −

begin_label: WHILE search_condition DO
statement_list
END WHILE end_label

Where, statement_list is a single or set of statements that are to be repeated. begin_label and end_label are the optional labels of the WHILE statement. Each statement in the WHILE ends with a semi colon (or, the current delimiter).

Example 1

Following query demonstrates the usage of the WHILE statement with a procedure −

DELIMITER //
CREATE PROCEDURE while_loop()
   BEGIN
      DECLARE num INT default 1;
      DECLARE res Varchar(50) default '';
      WHILE num < 78125 DO
         SET res = CONCAT(res,num,',');
         SET num = num*5;
      END While;
      SELECT res;
   END //
DELIMITER ;

You can call the above procedure as shown below −

call while_loop;

Output

The above mysql query produces the following output −

Example 2

Following query is another example of the WHILE statement −

Delimiter //
CREATE PROCEDURE While_Loop()
   BEGIN
   DECLARE num INT default 1;
   DECLARE res Varchar(50) default '';
   SET num = 1;
   SET res = '';
   WHILE num <=10 DO
   SET res = CONCAT(res, num, ',');
   SET num = num + 1;
   END WHILE;
   SELECT res;
END //

You can call the above procedure as shown below −

call While_Loop//

Output

Following is the output of the above program −