MySQL LOOP
Summary: in this tutorial, you will learn how to use MySQL LOOP statement to run a block of code repeatedly based on a condition.
Introduction to MySQL LOOP statement
The LOOP statement allows you to execute one or more statements repeatedly.
Here is the basic syntax of the LOOP statement:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
Code language: SQL (Structured Query Language) (sql)
The LOOP can have optional labels at the beginning and end of the block.
The LOOP executes the statement_list repeatedly. The statement_list may have one or more statements, each terminated by a semicolon (;) statement delimiter.
Typically, you terminate the loop when a condition is satisfied by using the LEAVE statement.
This is the typical syntax of the LOOP statement used with LEAVE statement:
[label]: LOOP
...
-- terminate the loop
IF condition THEN
LEAVE [label];
END IF;
...
END LOOP;
Code language: SQL (Structured Query Language) (sql)
The LEAVE statement immediately exits the loop. It works like the break statement in other programming languages like PHP, C/C++, and Java.
In addition to the LEAVE statement, you can use the ITERATE statement to skip the current loop iteration and start a new iteration. The ITERATE is similar to the continue statement in PHP, C/C++, and Java.
MySQL LOOP statement example
The following statement creates a stored procedure that uses a LOOP loop statement:
DROP PROCEDURE LoopDemo;CREATE PROCEDURE LoopDemo()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET str = ”;
IF x > 10 THEN
LEAVE loop_label;
END IF;
IF (x mod 2) THEN
ITERATE loop_label;
ELSE
SET str = CONCAT(str,x,‘,’);
END IF;
END LOOP;
SELECT str;
END$$
Code language: SQL (Structured Query Language) (sql)
In this example:
- The stored procedure constructs a string from the even numbers e.g., 2, 4, and 6.
- The
loop_labelbefore theLOOPstatement for using with theITERATEandLEAVEstatements. - If the value of
xis greater than10, the loop is terminated because of theLEAVEstatement. - If the value of the
xis an odd number, theITERATEignores everything below it and starts a new loop iteration. - If the value of the
xis an even number, the block in theELSEstatement will build the result string from even numbers.
The following statement calls the stored procedure:
CALL LoopDemo();Code language: SQL (Structured Query Language) (sql)
Here is the output:
+-------------+
| str |
+-------------+
| 2,4,6,8,10, |
+-------------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)
In this tutorial, you have learned how to use the MySQL LOOP statement to execute a block of code repeatedly based on a condition.