In this tutorial, we will discuss how we can implement a MySQL loop within a procedure.
For this tutorial, we assume you are aware of how to work with the MySQL procedures.
Features of MySQL Loop
Before we dive into the “how” of implementing a MySQL loop, let’s list several loop features:
- A loop can contain more than one statement where each statement is terminated with a semicolon.
- All the statements and values inside the loop are continually executed, but only if the set condition is true.
- You can terminate a loop by using the LEAVE statement.
- MySQL allows you to give a name to the loop using the syntax loopName: LOOP
The general syntax for implementing a simple MySQL loop is:
END LOOP [end_label]
The statement list should also include a LEAVE condition that specifies when the loop should terminate.
The label represents the name of the loop.
The syntax below shows the loop implementation with a LEAVE statement:
Example Use Case
In this section, I will try to illustrate how to use a LOOP in a procedure. The purpose of the procedure is to implement a simple loop and will not reflect real-world data.
Consider the procedure below that implements a loop to find even numbers:
CREATE PROCEDURE loopMe()
DECLARE i INT;
SET i = 1;
SET @str = '';
IF i > 10 THEN
SET i = i + 1;
IF (i mod 2) THEN
SET @str = CONCAT(@str,i,' ');
SELECT @str AS EVEN;
Once we call the loop, we get the values of even numbers separated by spaces as specified in the CONCAT() function.