MySQL LOOP Statement with Examples


How to Use the MySQL LOOP Statement

In this tutorial, you'll learn how to use the MySQL LOOP statement. 

LOOP is a simple loop construct that enables repeated execution of a block of SQL code, which consists of one or more statements, each terminated by a semicolon (;).

In MySQL, there are two other flow control statements, REPEAT and WHILE.

Like a LOOP statement, you can use them to execute a block of SQL code repeatedly.

MySQL LOOP Syntax

The syntax of the LOOP statement is as follows:

[begin_label:] LOOP
statement_list
END LOOP [end_label]

Remarks

  • The statement_list within the LOOP statement is repeated until the loop is terminated.
  • To terminate a LOOP statement, you can use either a LEAVE statement or a 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 to repeat while count is greater than 0.

Once count is less than or equal to 0, the LEAVE statement terminates the LOOP.

Output:

CALL SP_LOOP;
Total
5050

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 to repeat while count is not equal to 0. 

Once count is equal to 0, the LEAVE statement terminates the LOOP.

Output:

CALL SP_LOOP_II;
Total
5050

Example 3

The following example creates a stored procedure named SP_LOOP_SUM_ODD_EVEN_NUMBERS to sums odd numbers and even numbers between 1 and 10 using the IF and ELSEIF statements with the LOOP statement:

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;
Total Even NumberTotal Odd Number
3025

Example 4

In this example, let's create a table named loop_table_examples that 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;

And then, insert 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 the following example shows:

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've just created using the following SQL code:

CALL SP_LOOP_FIND_LARGEST_NUMBER;

Output:

Largest Number
160

In this tutorial, you've learned how to use the MySQL LOOP statement. 

LOOP is a simple loop construct that enables repeated execution of a block of SQL statements until the loop is terminated. 

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

Leave a Reply

Your email address will not be published. Required fields are marked *