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, place one or more statements that will execute between the DO and END WHILE.

Third, define 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 called calendars that stores the date, month, quarter, and year:

DROP TABLE IF EXISTS calendars;

CREATE TABLE calendars(
    date DATE PRIMARY KEY,
    month INT NOT NULL,
    quarter INT NOT NULL,
    year INT NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, create a new stored procedure loadDates() that insert dates into the calendars table:

DELIMITER $$

CREATE PROCEDURE loadDates(
    startDate DATE, 
    day INT
)
BEGIN
    
    DECLARE counter INT DEFAULT 0;
    DECLARE currentDate DATE DEFAULT startDate;

    WHILE counter <= day DO
        CALL InsertCalendar(currentDate);
        SET counter = counter + 1;
        SET currentDate = DATE_ADD(currentDate ,INTERVAL 1 day);
    END WHILE;

END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The stored procedure loadDates() accepts two arguments:

• startDate is the start date that we insert into the calendars table.
• day is the number of days that will be inserted starting from the startDate.

Examining the loadDates() stored procedure

In the loadDates() stored procedure:

First, declare a counter and currentDate variables for keeping immediate values. The default values of counter and currentDate are 0 and startDate respectively.

Then, check if the counter is less than day, if yes:

• Insert the current date into the calendars table.
• Increase the counter by one and the currentDate by one day using the DATE_ADD() function.

The WHILE loop repeatedly executes the INSERT statement to insert dates into the calendars table until the counter is less than or equal to day.

Calling the stored procedure

First, call the stored procedure loadDates() to insert 365 rows into the calendars table starting from January 1st 2024.

CALL loadDates('2024-01-01',365);Code language: SQL (Structured Query Language) (sql)

Second, retrieve rows from the calendars table to verify the inserts:

SELECT 
  * 
FROM 
  calendars 
ORDER BY 
  date DESC ;Code language: SQL (Structured Query Language) (sql)

Partial Output:

+------------+-------+---------+------+
| date       | month | quarter | year |
+------------+-------+---------+------+
| 2024-12-31 |    12 |       4 | 2024 |
| 2024-12-30 |    12 |       4 | 2024 |
| 2024-12-29 |    12 |       4 | 2024 |
| 2024-12-28 |    12 |       4 | 2024 |
| 2024-12-27 |    12 |       4 | 2024 |
...Code language: plaintext (plaintext)

Summary

• Use MySQL WHILE loop to execute one or more statements repeatedly as long as a condition is true.