While Loop in MySQL – A Complete Reference

In this tutorial, we will study the execution of the WHILE loop in MySQL. While loop is used to execute one or more than one statement till the condition holds true. The condition is evaluated before evaluating the while loop, so if there is a possibility that it may or may not execute. Basically, we can use the WHILE loop when we need to execute a code repeatedly till the condition is true.

Syntax of MySQL WHILE LOOP

[label] WHILE condition DO
    statement
END WHILE [end_label]Code language: SQL (Structured Query Language) (sql)

Where,

• label – It is a name related to WHILE loop, also it is optional.
• condition – The condition which will be tested and if found true, the code will be executed.
• statement – When the condition is found true, this will be executed.

Examples of MySQL WHILE LOOP

The WHILE LOOP is a looping statement that allows you to execute a block of code over and over again while a condition is true. This can be useful for performing repetitive tasks or for checking a condition multiple times.

Example 1. WHILE Loop With a Stored Procedure.

A Stored Procedure is a block of code that a user can reuse again and again.

DELIMITER $$
CREATE procedure some_loop()
BEGIN
 declare value VARCHAR(20) default ' ' ;
 declare a INT default 0;
 SET a = 1;
 WHILE a <= 5 DO
   SET value = CONCAT(value, a ,',' );
   SET a = a + 1;
 END
 WHILE;
 select value;
END $$
DELIMITER ;
call some_loop();Code language: SQL (Structured Query Language) (sql)

Output

In this example, numbers are printed from 1 to 5.

Example 2. While Loop By Creating a Table and Two Stored Procedures

Firstly, we will create a table named Details. The code for this is:

CREATE TABLE Details(
   id INT AUTO_INCREMENT,
   fulldate DATE UNIQUE,
   day TINYINT NOT NULL,
   month TINYINT NOT NULL,
   PRIMARY KEY(id)
);Code language: SQL (Structured Query Language) (sql)

Next, we will create a stored procedure named Insertdata to insert data into the Details table.

DELIMITER $$
CREATE PROCEDURE Insertdata(dt DATE)
BEGIN
   INSERT INTO Details(
       fulldate,
       day,
       month )
   VALUES(dt,  
       EXTRACT(DAY FROM dt),
       EXTRACT(MONTH FROM dt)
     );
END$$
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Now we will create another stored procedure called loading where the number of days will be updated from the starting date in the table.

DELIMITER $$
CREATE PROCEDURE loading(
   startDate DATE,  
   day INT
)
BEGIN
      DECLARE counter INT DEFAULT 1;
   DECLARE dt DATE DEFAULT startDate;
   WHILE counter <= day DO
       CALL Insertdata(dt);
       SET counter = counter + 1;
       SET dt = DATE_ADD(dt,INTERVAL 1 day);
   END WHILE;
END$$
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Here the While Loop inserts the date into the table till the variable counter is the same as the day which we declared above in the code. If the counter is less than or equal to the day then the stored procedure Insertdata will be executed and a row will be inserted into the table Details.

After this, we will check the output using the following command-

CALL loading('2021-01-01',31);
select * from Details where id < 10 ;Code language: SQL (Structured Query Language) (sql)

Output-

Conclusion

In this tutorial, we studied about While loop and how it is used mostly with the stored procedure and to repeat a task until the condition holds true.

References

For more reference, check the official documentation of MySQL.