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 $$
CREATE PROCEDURE InsertCalendar(dt DATE)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$$
DELIMITER ;
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 $$
CREATE PROCEDURE LoadCalendars(startDate DATE,
day INT
)
BEGIN
DECLARE counter INT DEFAULT 1;
DECLARE dt DATE DEFAULT startDate;
WHILE counter <= day DO
CALL InsertCalendar(dt);
SET counter = counter + 1;
SET dt = DATE_ADD(dt,INTERVAL 1 day);
END WHILE;
END$$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
The stored procedure LoadCalendars()
accepts two arguments:
startDate
is the start date inserted into thecalendars
table.day
is 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 thecalendars
table. - Increase the
counter
by one. Also, increase thedt
by 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.