3 Ways To Use Loops in MySQL Stored Procedure

Mysql Loops In Stored Procedure

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]

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 ;
Create LoopDemo Procedure
Create LoopDemo Procedure

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();
Call Loopdemo Procedure
Call Loopdemo Procedure

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]

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 ;
Create WhileDemo Procedure
Create WhileDemo Procedure

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();
Call WhileDemo Procedure
Call WhileDemo Procedure

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 ];

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 ;
Create RepeatDemo Procedure
Create RepeatDemo Procedure

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();
Call RepeatDemo Procedure
Call RepeatDemo Procedure

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!