Today we will learn how can we use loops in a MySQL stored procedure. We will see three different examples in which we will use different types of loops in the stored procedure. First, we will see the syntax of loops, and then we will use them inside the stored procedure. We will use only a single problem statement throughout the tutorial and solve it using multiple loops in the stored procedure. So, let’s get started!
Also read: Create MySQL Stored Procedure [With Examples]
Introduction to Loops in Stored Procedure
There are three types of loops available in MySQL- LOOP, WHILE and REPEAT. All of these are used for the same purpose which is iterating the block repetitively but in different situations.
Examples of Loops in the Stored Procedure
In this tutorial, we will see the syntaxes of all loops first and use them inside the stored procedure. You can read our detailed guides on these MySQL loops here.
Example 1. LOOP in the Stored Procedure
We will see the first example of the LOOP statement in MySQL. In the LOOP statement, you don’t need to specify the condition when you define it as we do in the WHILE loop. Let’s see the syntax of the LOOP statement first.
[begin_label:] LOOP
statement_list
END LOOP [end_label]Code language: SQL (Structured Query Language) (sql)The LOOP block is repeated until a certain condition meets and the loop terminates. To terminate the loop, the LEAVE statement is used. To demonstrate the use of the LOOP in the stored procedure we will solve the following example.
Problem Statement – Print the numbers from 1 to 10 using the LOOP loop in the stored procedure.
DELIMITER //
CREATE PROCEDURE loopDemo()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE outp VARCHAR(100);
SET outp= "";
iterates:LOOP
SET outp = CONCAT(outp,i,',');
IF i = 10 THEN LEAVE iterates;
END IF;
SET i = i+1;
END LOOP;
SELECT outp;
END //
DELIMITER ;Code language: SQL (Structured Query Language) (sql)
In the above stored procedure, we have first declared two variables, i and outp for incrementing the value and holding the result respectively.
Then we used the LOOP statement and set the “iterates” variable as a label for that loop. The “outp” variable will hold the concatenated string of all numbers from 1 to 10.
To concatenate the numbers, we have used the CONCAT function in MySQL. In the loop, we have applied the condition that if the value of i becomes 10 then leave the loop. And finally, we have used the SELECT statement to print the result.
Now let’s call the procedure to check if it works correctly.
CALL loopDemo();Code language: SQL (Structured Query Language) (sql)
As you can see, we have received 1 to 10 numbers.
Example 2. WHILE Loop in the Stored Procedure
The WHILE loop is very much similar to the while loops in other programming languages. The WHILE loop iterates as long as a certain condition is true. As soon as the specified condition becomes false, the loop breaks and control is handed over to the next statement outside the loop. Check the syntax of the WHILE loop below.
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]Code language: SQL (Structured Query Language) (sql)As you can see in the syntax, we must specify a condition to make the WHILE loop work. Let’s see an example to demonstrate how to use the WHILE loop inside the stored procedure.
Problem Statement- Print the digits from 1 to 9 using the LOOP loop in the stored procedure.
DELIMITER //
CREATE PROCEDURE whileDemo()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE outp VARCHAR(100);
SET outp= "";
WHILE i < 10 DO
SET outp = CONCAT(outp,i,',');
SET i = i +1;
END WHILE;
SELECT outp;
END //
DELIMITER ;Code language: SQL (Structured Query Language) (sql)
Similar to the previous stored procedure, we have declared two variables for incrementing a value and storing a result. Then we used the WHILE loop and set a condition i<10 which means iterating the loop as long as the value of i is less than 10 and we increment the value of i.
Finally, we have used the SELECT statement to print the result.
Let’s test the stored procedure to check if the loop is working perfectly.
CALL whileDemo();Code language: SQL (Structured Query Language) (sql)
As you can see, we have received the expected output.
Example 3. REPEAT UNTIL Loop in the Stored Procedure
The REPEAT UNTIL loop, you can say, is the opposite of the WHILE loop. Unlike the WHILE loop which iterates again and again as long as the certain condition is true, the REPEAT loop iterates itself until the certain condition becomes true. That means, when the specified condition becomes true, the REPEAT loop breaks.
Following is the syntax of the REPEAT UNTIL loop.
[ label_name: ] REPEAT
{...statements...}
UNTIL condition
END REPEAT [ label_name ];Code language: SQL (Structured Query Language) (sql)Let’s understand it by solving the below example.
Problem Statement – Print the digits from 1 to 9 using the LOOP loop in the stored procedure.
DELIMITER //
CREATE PROCEDURE repeatDemo()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE outp VARCHAR(100);
SET outp= "";
REPEAT
SET outp = CONCAT(outp,i,',');
SET i = i +1;
UNTIL i=10
END REPEAT;
SELECT outp;
END //
DELIMITER ;Code language: SQL (Structured Query Language) (sql)
In the above procedure, we have used the condition UNTIL i=10 which means as long as the value of i is not 10, repeat the loop. When the value of i becomes 10, break the loop.
Let’s test the loop by calling the procedure.
CALL repeatDemo();Code language: SQL (Structured Query Language) (sql)
As you can see, we have got the result of digits from 1 to 9.
Note that, when the value of i becomes 9, it will be concatenated to the output string and immediately the value of i is incremented. As soon as the value of i becomes 10, the condition is checked and the loop breaks. Therefore, the concatenated string will not contain the number 10.
Summary
We have seen-
- The introduction to loops in MySQL stored procedure.
- Information and Syntax of the LOOP loop.
- Example of the LOOP in the stored procedure.
- Information and Syntax of the WHILE loop.
- Example of the WHILE in the stored procedure.
- Information and Syntax of the REPEAT UNTIL loop.
- Example of the REPEAT UNTIL in the stored procedure.
This was an interesting tutorial where we learned three loops in one place. If you find this tutorial helpful, don’t forget to share it with your friends. See you in the next tutorial!