MySQL REPEAT Loop Statement

By /
Repeat Loop Statement In Mysql

In this tutorial, we will learn about the REPEAT statement in MySQL. Note that the REPEAT statement is different from the REPEAT() function, do not get confused between them! We will start with an introduction to the REPEAT statement and then move towards its syntax. Then we will take an example to understand the use of the REPEAT loop effectively.

Introduction to MySQL REPEAT Statement

The REPEAT statement is one of the loops available in MySQL. It is a post-test loop in which body executes until the given condition becomes true. In the REPEAT loop, the body executes at least once before the condition is checked and evaluated.

It is somewhat like other loops which follow the behaviour of repeating itself. However, it is exactly the opposite of the while loop in MySQL. The WHILE loop repeats itself until the certain condition becomes false, whereas the REPEAT loop repeats itself until the given condition becomes true.

Diagramatic Representation

To understand the behaviour of the REPEAT loop, take a glance at the below image.

REPEAT Loop
REPEAT Loop

As you can see in the image,

  • The loop body executes at least once before checking the condition.
  • Condition is checked after the body executes. That’s why it is called the post-test loop.
  • The loop will continue to repeat as long as the given condition is false. When the condition becomes true, the loop ends and control goes outside the loop.

Syntax of MySQL REPEAT Statement

Below is the syntax to write the REPEAT statement in MySQL.

[begin_label:] REPEAT
    loop_body
UNTIL search_condition
END REPEAT [end_label]Code language: SQL (Structured Query Language) (sql)

Where,

  • begin_label – It is the name of the REPEAT loop. It is optional.
  • REPEAT – It is the start of the loop.
  • loop_body – The loop_body contains one or more valid SQL statements.
  • UNTIL search_condition – It specifies the condition for the loop and breaks the loop as soon as the specified condition becomes true.
  • END REPEAT – This statement ends the loop.

Examples of MySQL REPEAT Statement

Let us take an example to understand the working of the REPEAT Statement.

The REPEAT loop is usually and widely used with stored statements such as stored procedures, functions and triggers. So, we will learn to use the REPEAT statement inside the stored procedure.

Example – Display 1-9 Numbers

Here, we will create a stored procedure that contains the REPEAT loop. Inside the loop, we will use a CONCAT() function to concatenate all the digits from 1 to 9 and then display it.

DELIMITER //
CREATE PROCEDURE repeatLoop()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE output VARCHAR(100) DEFAULT '';
REPEAT
SET output = CONCAT(output,i,',');
SET i = i+1;
UNTIL i=10
END REPEAT;
SELECT output;
END //Code language: SQL (Structured Query Language) (sql)
REPEAT LOOP Example 1
REPEAT LOOP Example 1

Here, We have created a stored procedure of the name repeatLoop inside which we have declared two variables; i and output. The “i” is an increment variable that starts from 1 and increments till the condition becomes true. On the other hand, the output variable will store the concatenated string of all the digits and later display it.

Look closely at the condition. We have set the condition i=10, which means repeating the loop until “i” becomes 10 and incrementing the value of “i”. As soon as “i” becomes 10, the REPEAT block will end.

Note that, we perform the concatenate operation first and then increment the value of i. When i is 9, the output variable will hold digits from 1 to 9 and then the next statement will increment the value of i to 10. As soon as i becomes 10, the condition is checked and it becomes true which makes the REPEAT loop stop and break its execution. That’s why the output variable will not consist of the number 10.

Now let’s test our procedure by calling it.

CALL repeatLoop();Code language: SQL (Structured Query Language) (sql)
Example 1 Output
Example 1 Output

As you can see, we have got the expected result here which is a set of digits from 1 to 9.

Conclusion

In this tutorial, we learned about the REPEAT loop statement in MySQL. We have seen the syntax of the REPEAT loop and one example of it. You can use the REPEAT loop in many different ways so we recommend you try it as many times as you can for better understanding.