MySQL WHILE Loop

 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 the 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]

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 the 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)
);

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 ;

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 ;

The stored procedure LoadCalendars() accepts two arguments:

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

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 the calendars table.
• Increase the counter by one. Also, increase the dt by one day using the DATE_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);

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.