MySQL REPEAT Loop

MySQL REPEAT Loop

The REPEAT loop in MySQL is a control flow structure that repeatedly executes a block of SQL statements until a specified condition evaluates to TRUE. It is useful when you want to execute a loop at least once, regardless of the condition.

Syntax

[ label: ] REPEAT
    -- Statements to execute
    UNTIL condition
END REPEAT label;

• label (optional) → Used to reference the loop (helpful for nested loops).
• Statements inside the loop execute at least once before checking the condition.
• UNTIL condition → The loop runs until this condition evaluates to TRUE.

Example: Using REPEAT to Print Numbers

Let's create a stored procedure that uses a REPEAT loop to print numbers from 1 to 5.

Stored Procedure with REPEAT Loop

DELIMITER $$

CREATE PROCEDURE repeat_example()
BEGIN
    DECLARE counter INT DEFAULT 1;

    REPEAT
        -- Print the current counter value
        SELECT counter;
        
        -- Increment counter
        SET counter = counter + 1;
    UNTIL counter > 5  -- Exit when counter exceeds 5
    END REPEAT;

END$$

DELIMITER ;

✅ Explanation:

1. A variable counter is initialized to 1.
2. The loop executes once, then increments counter.
3. It stops executing when counter exceeds 5.

Calling the Procedure

CALL repeat_example();

📌 Output:

+---------+
| counter |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
+---------+

Use Case: Factorial Calculation

We can use a REPEAT loop to calculate the factorial of a given number.

DELIMITER $$

CREATE PROCEDURE factorial_example(IN num INT, OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET result = 1;

    REPEAT
        SET result = result * i;
        SET i = i + 1;
    UNTIL i > num
    END REPEAT;

END$$

DELIMITER ;

Calling the Procedure

CALL factorial_example(5, @fact_result);
SELECT @fact_result;

✅ Output for 5! (5 × 4 × 3 × 2 × 1 = 120)

+-------------+
| @fact_result |
+-------------+
| 120         |
+-------------+

Key Takeaways

✔ Ensures at least one execution (even if the condition is already met).
✔ Loops until a condition becomes TRUE (opposite of WHILE).
✔ Useful for simple iterations and incremental calculations.
✔ Must ensure termination to avoid infinite loops.

Would you like an example using nested REPEAT loops? 🚀