MySQL LOOP with Examples

How to Use the MySQL LOOP Statement

In this tutorial, you will learn how to use a LOOP statement in MySQL.

A LOOP statement is a simple loop construct that allows you to repeatedly execute a block of SQL code consisting of one or more SQL statements ending with a semicolon (;).

There are two flow control statements in MySQL: REPEAT and WHILE.

Like the LOOP statement, they can be used to execute a block of SQL code repeatedly.

If you want to learn more about MySQL, check out this link: a list of all MySQL tutorials.

Learn more: MySQL case statement

MySQL LOOP Syntax

The syntax of the MySQL LOOP statement is as follows:

[begin_label:] LOOP
statement_list
END LOOP [end_label]

Remarks

• The statement_list in the LOOP statement will be repeated until the loop is terminated
• To terminate the LOOP statement, use a LEAVE or RETURN statement
• To start the loop again, use an ITERATOR statement

Examples

Example 1

The following statement creates a stored procedure named SP_LOOP:

delimiter //

CREATE PROCEDURE SP_LOOP()
BEGIN
DECLARE count INT DEFAULT 100;
DECLARE sum INT DEFAULT 0;
label: LOOP
SET sum = sum + count;
IF count > 0 THEN
SET count = count - 1;
ITERATE label;
END IF;
LEAVE label;
END LOOP label;
SELECT sum AS "Total";
END//

In the above example, the ITERATE statement made the loop repeat while "count" is greater than 0.

When "count" becomes less than or equal to 0, the LEAVE statement terminates the loop.

Output:

CALL SP_LOOP;

Example 2

The following statement creates a stored procedure named SP_LOOP_II:

delimiter //

CREATE PROCEDURE SP_LOOP_II()
BEGIN
DECLARE count INT DEFAULT 100;
DECLARE sum INT DEFAULT 0;
label: LOOP
SET sum = sum + count;
IF count = 0 THEN
LEAVE label;
END IF;
SET count = count - 1;
ITERATE label;
END LOOP label;
SELECT sum AS "Total";
END//

In the above example, the ITERATE statement made the loop repeat while "count" is not equal to 0.

Once "count" reaches 0, the LEAVE statement will terminate the loop.

Output:

CALL SP_LOOP_II;

Example 3

The following SQL creates a stored procedure named SP_LOOP_SUM_ODD_EVEN_NUMBERS and uses the IF and ELSEIF statements in a LOOP statement to calculate the sum of odd and even numbers from 1 to 10:

delimiter //
CREATE PROCEDURE SP_LOOP_SUM_ODD_EVEN_NUMBERS()
BEGIN
DECLARE sum_even INT DEFAULT 0;
DECLARE sum_odd INT DEFAULT 0;
DECLARE start_number INT DEFAULT 0;
DECLARE end_number INT DEFAULT 10;
label: LOOP
IF start_number < end_number THEN
SET start_number = start_number + 1;
IF (start_number % 2 != 0) THEN
SET sum_odd = sum_odd + start_number;
ITERATE label;
ELSEIF start_number % 2 = 0 THEN
SET sum_even = sum_even + start_number;
ITERATE label;
END IF;
END IF;
LEAVE label;
END LOOP label;
SELECT sum_even AS "Total Even Number", sum_odd AS "Total Odd Number";
END//

Output:

CALL SP_LOOP_SUM_ODD_EVEN_NUMBERS;

Example 4

In our example, we will create a table called loop_table_examples, which consists of two columns: id and num.

CREATE TABLE IF NOT EXISTS loop_table_examples (
id INT NOT NULL AUTO_INCREMENT,
num INT NOT NULL,
PRIMARY KEY (id)
) ENGINE = innodb;

Then insert the demo data into the table.

INSERT INTO loop_table_examples (num) VALUE(20);
INSERT INTO loop_table_examples (num) VALUE(90);
INSERT INTO loop_table_examples (num) VALUE(70);
INSERT INTO loop_table_examples (num) VALUE(50);
INSERT INTO loop_table_examples (num) VALUE(120);
INSERT INTO loop_table_examples (num) VALUE(100);
INSERT INTO loop_table_examples (num) VALUE(110);
INSERT INTO loop_table_examples (num) VALUE(160);
INSERT INTO loop_table_examples (num) VALUE(130);
INSERT INTO loop_table_examples (num) VALUE(10);

Now, let's create a stored procedure named SP_LOOP_FIND_LARGEST_NUMBER to find the largest number in the num column, as shown in the following example:

delimiter //
CREATE PROCEDURE SP_LOOP_FIND_LARGEST_NUMBER()
BEGIN
DECLARE count_row INT DEFAULT 0;
DECLARE largest_number INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE tmp INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT t.num FROM loop_table_examples t;
SELECT COUNT(*) INTO count_row FROM loop_table_examples;
OPEN cur;
t_label: LOOP
IF i < count_row THEN FETCH cur INTO tmp;

IF tmp > largest_number THEN
SET largest_number = tmp;
END IF;
SET i = i + 1;
ITERATE t_label;
END IF;
LEAVE t_label;
END LOOP t_label;
CLOSE cur;
SELECT largest_number AS "Largest Number";
END//

Now, call the stored procedure we have just created using the following SQL code:

CALL SP_LOOP_FIND_LARGEST_NUMBER;

Output:

In this tutorial, you have learned how to use a LOOP statement in MySQL.

The LOOP statement is a simple loop construct that allows you to repeatedly execute a block of SQL statements until the loop is terminated.

You can terminate the LOOP statement with a REPEAT or RETURN statement and start the loop again with an ITERATOR statement.