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
Basic Usage
The general syntax for implementing a simple MySQL loop is:
statement_list
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:
statement_list;
IF condition THEN
LEAVE [label];
END IF;
END LOOP [end_label];
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:
DELIMITER $$
CREATE PROCEDURE loopMe()
BEGIN
DECLARE i INT;
SET i = 1;
SET @str = '';
iterateMe: LOOP
IF i > 10 THEN
LEAVE iterateMe;
END IF;
SET i = i + 1;
IF (i mod 2) THEN
ITERATE iterateMe;
ELSE
SET @str = CONCAT(@str,i,' ');
END IF;
END LOOP;
SELECT @str AS EVEN;
END$$
DELIMITER ;
CALL loopMe()
Once we call the loop, we get the values of even numbers separated by spaces as specified in the CONCAT() function.
Conclusion
In this tutorial, we covered the basics of using a MySQL loop to iterate through a set of instructions. MySQL loops perform similarly to loops in other programming languages such as Python, C++, and JavaScript.
