While Loop in MySQL

Loops are the most basic part of full-stack development. Loops are very useful when we need to perform similar tasks again and again under specific criteria.

In this article, we are going to see how we can use the while loop in MySQL, and also we will see a relevant example with a proper explanation to make the topic easier.

Use while Loop in MySQL

The general syntax for the while loop.

WHILE condition
DO
...
END WHILE

In this example, we will learn how to use the while loop in MySQL. Have a look at the below example query.

Example:

CREATE PROCEDURE MyLoop()
BEGIN
      DECLARE x INT default 1;
      DECLARE str Varchar(30) default '';
      WHILE x < 10 DO
         SET str = CONCAT(str,x,' ,');
         SET x = x+1;
      END While;
      SELECT str;
   END

In our above example, we create a procedure in MySQL named MyLoop(). Inside the function, we first declare a variable named x and assign its default value as 1 through the line DECLARE x INT default 1;.

Also, declare a variable str in the varchar() type and assign its default value using the line DECLARE str Varchar(30) default '';. After that, we created a while loop where we concatenate the value of x to the str and increment the value of x.

After the print the value of str by using the line SELECT str;.

Now we are going to call our procedure by the following command:

CALL MyLoop();

Which provides you with the following output.

Output:

1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9

Please note that the query we used in this article is written in MySQL.