SQL WHILE Statement in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 12

Division 5

Forward: In this part of the series, we look at the WHILE SQL statement in MySQL. We are dealing with MySQL 5.1.

By: Chrysanthus Date Published: 7 Aug 2012

Introduction

This is part 12 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at the WHILE SQL statement in MySQL. We are dealing with MySQL 5.1.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Illustration
Run (type and press Enter) the following code (see explanation below):

Delimiter //
CREATE PROCEDURE pExample ()
BEGIN

    DECLARE n INTEGER DEFAULT 1;

    WHILE n <= 5 DO
        INSERT INTO Invoice () VALUES ();
        SET n = n + 1;
    END WHILE;

END;
//

Now run the following statements:

    CALL pExample ();
    SELECT * FROM Invoice;

You should realize that 5 new rows have been inserted into the Invoice table.

Explanation
A variable, n is created and initialized to 1. After that you have the WHILE statement. The syntax of the WHILE statement is:

WHILE search-condition WHILE
statement-list
END WHILE;

You have the reserved words, WHILE and DO. The WHILE statement ends with END WHILE and a semicolon. statement-list is a block of SQL statements. So while the search-condition is true, the block of statements are executed repeatedly until the search condition is false.

In the first pass of the WHILE statement above, the two SQL statements are executed. The first one inserts a row in the Invoice table. The second one increments the variable by 1. In this second statement, the right hand side of the assignment operator (=) takes whatever value n had and adds 1 to it. Because of the assignment operator and the left hand side, the sum becomes the new value of n. So after the first run of the LOOP, the value of n becomes 2.

In the second pass of the WHILE loop a second row is inserted into the Invoice table again; the value of n becomes 3. The WHILE loop continues to repeat until the 5th pass. At the 5th pass the 5th row is inserted and the value of n is 6.

Before the loop passes each time, the search-condition checks if n is less than or equal to5 (n<=5). After n becomes 6 in the 5th pass, the search-condition is checked again. This time it is false and the loop will not pass anymore. The search-condition is always checked until it is false. Each time it is true, the loop is passed.

Note that by first assigning the value of 1 to the variable n, and making the search-condition, (n<=5), we made the loop to repeat 5 times. You can make the loop to repeat any number of times following the same ideas. The search-condition can be anything that you know will finally become false within the loop.

Well, we shall look at another example in one of the following parts of the series. Let us take a break now and continue in the next part.

Chrys

Broad Network

Related Articles

SQL WHILE Statement in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 12

Division 5

Introduction

Related Links

Comments