MySQL LOOP Statement with Examples


How to Use the MySQL LOOP Statement

In this tutorial, we'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, that allows us to do what a LOOP statement can.

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 are repeated until the loop is terminated.
  • To terminate a LOOP statement, we can use either a LEAVE statement or a RETURN statement.
  • To start a loop again, we use a ITERATOR statement.

Examples

Example 1

The following statement creates a stored procedure named SP_LOOP:

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 this 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 terminated the LOOP.

Result:

Total
5050

Example 2

The following statement creates a stored procedure named SP_LOOP:

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 this 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 terminated the LOOP.

Result:

Total
5050

In this tutorial, we'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. A REPEAT or RETURN statement can be used to terminate a LOOP statement. And a ITERATOR statement is used to start a loop again.

Leave a Reply

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