MySQL REPEAT Loop

 MySQL REPEAT Loop

Summary: in this tutorial, you will learn how to use the MySQL REPEAT statement to execute one or more statements until a search condition is true.

The REPEAT the statement executes one or more statements until a search condition is true.

Here is the basic syntax of the REPEAT loop statement:

[begin_label:] REPEAT
    statement
UNTIL search_condition
END REPEAT [end_label]

The REPEAT executes the statement until the search_condition evaluates to true.

The REPEAT checks the search_condition after the execution of statement, therefore, the statement always executes at least once. This is why the REPEAT is also known as a post-test loop.

The REPEAT the statement can have labels at the beginning and at the end. These labels are optional.

The following flowchart illustrates the REPEAT loop:

MySQL REPEAT loop example

This statement creates a stored procedure called RepeatDemo  that uses the REPEAT statement to concatenate numbers from 1 to 9:

DELIMITER $$

CREATE PROCEDURE RepeatDemo()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result VARCHAR(100) DEFAULT '';
    
    REPEAT
        SET result = CONCAT(result,counter,',');
        SET counter = counter + 1;
    UNTIL counter >= 10
    END REPEAT;
    
    -- display result
    SELECT result;
END$$

DELIMITER ;

In this stored procedure:

First, declare two variables counter and result and set their initial values to 1 and blank.

The counter the variable is used for counting from 1 to 9 in the loop. And the result the variable is used for storing the concatenated string after each loop iteration.

Second, append counter value to the result variable using the CONCAT() function until the counter is greater than or equal to 10.

The following statement calls the RepeatDemo() stored procedure:

CALL RepeatDemo();

Here is the output:

+--------------------+
| result             |
+--------------------+
| 1,2,3,4,5,6,7,8,9, |
+--------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

In this tutorial, you will learn how to use the MySQL REPEAT statement to execute one or more statements until a search condition is true.