MySQL WHILE Loop
Summary: in this tutorial, you will learn how to use the MySQL WHILE loop statement to execute one or more statements repeatedly as long as a condition is true.
Introduction to MySQL WHILE loop statement
The WHILE loop is a loop statement that executes a block of code repeatedly as long as a condition is true.
Here is the basic syntax of the WHILE statement:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Code language: SQL (Structured Query Language) (sql)
In this syntax:
First, specify a search condition after the WHILE keyword.
The WHILE checks the search_condition at the beginning of each iteration.
If the search_condition evaluates to TRUE, the WHILE executes the statement_list as long as the search_condition is TRUE.
The WHILE loop is called a pretest loop because it checks the search_condition before the statement_list executes.
Second, specify one or more statements that will execute between the DO and END WHILE keywords.
Third, specify optional labels for the WHILE statement at the beginning and end of the loop construct.
The following flowchart illustrates the MySQL WHILE loop statement:
MySQL WHILE loop statement example
First, create a table namedcalendars which stores dates and derived date information such as day, month, quarter, and year:
CREATE TABLE calendars(
id INT AUTO_INCREMENT,
fulldate DATE UNIQUE,
day TINYINT NOT NULL,
month TINYINT NOT NULL,
quarter TINYINT NOT NULL,
year INT NOT NULL,
PRIMARY KEY(id)
);
Code language: SQL (Structured Query Language) (sql)
Second, create a new stored procedure to insert a date into the calendars table:
DELIMITER $$BEGIN
INSERT INTO calendars(
fulldate,
day,
month,
quarter,
year
)
VALUES(
dt,
EXTRACT(DAY FROM dt),
EXTRACT(MONTH FROM dt),
EXTRACT(QUARTER FROM dt),
EXTRACT(YEAR FROM dt)
);
END$$
Code language: SQL (Structured Query Language) (sql)
Third, create a new stored procedure LoadCalendars() that loads a number of days starting from a start date into the calendars table.
DELIMITER $$startDate DATE,
day INT
)
BEGIN
DECLARE dt DATE DEFAULT startDate;
CALL InsertCalendar(dt);
SET counter = counter + 1;
SET dt = DATE_ADD(dt,INTERVAL 1 day);
END WHILE;
Code language: SQL (Structured Query Language) (sql)
The stored procedure LoadCalendars() accepts two arguments:
startDateis the start date inserted into thecalendarstable.dayis the number of days that will be loaded starting from thestartDate.
In the LoadCalendars() stored procedure:
First, declare a counter and dt variables for keeping immediate values. The default values of counter and dt are 1 and startDate respectively.
Then, check if the counter is less than or equal day, if yes:
- Call the stored procedure
InsertCalendar()to insert a row into thecalendarstable. - Increase the
counterby one. Also, increase thedtby one day using theDATE_ADD()function.
The WHILE loop repeatedly inserts dates into the calendars table until the counter is equal to day.
The following statement calls the stored procedure LoadCalendars() to load 31 days into the calendars table starting from January 1st 2019.
CALL LoadCalendars('2019-01-01',31);
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL WHILE loop to execute one or more statements repeatedly as long as a condition is true.