How MySQL WHILE loop statement can be used in stored procedure?

As we know that MySQL provides us loop statements that allow us to execute a block of SQL code repeatedly based on a condition. WHILE loop statement is one of such kind of loop statements. Its syntax is as follows −

WHILE expression DO
statements
END WHILE

Actually, the WHILE loop checks the expression at the starting of every iteration. If the expression evaluates to true, MySQL will execute statements between WHILE and END WHILE until the expression evaluates to false. The WHILE loop checks the expression before the statements execute, that is why it is also called the pretest loop.

To demonstrate the use of WHILE loop with stored procedures, the following is an example −

mysql> Delimiter //
mysql> CREATE PROCEDURE While_Loop()
  -> BEGIN
-> DECLARE A INT;
-> DECLARE XYZ Varchar(50);
-> SET A = 1;
-> SET XYZ = '';
-> WHILE A <=10 DO
-> SET XYZ = CONCAT(XYZ,A,',');
-> SET A = A + 1;
-> END WHILE;
-> SELECT XYZ;
-> END //
Query OK, 0 rows affected (0.31 sec)

Now, we can see the result below when we invoke this procedure −

mysql> DELIMITER ;
mysql> call While_Loop();
+-----------------------+
| XYZ |
+-----------------------+
| 1,2,3,4,5,6,7,8,9,10, |
+-----------------------+
1 row in set (0.03 sec)

Ankitha Reddy

Published on 21-Feb-2018 13:04:30