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]
- 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();
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) );
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 ;
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 ;
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 ;
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.
For more reference, check the official documentation of MySQL.