MySQL WHILE Loop

MySQL WHILE Loop

The WHILE loop in MySQL repeatedly executes a block of SQL statements while a specified condition is TRUE. If the condition is initially FALSE, the loop will not execute at all.

Syntax

[ label: ] WHILE condition DO
    -- Statements to execute
END WHILE label;

• label (optional) → A label for the loop, used in case of nested loops.
• condition → The condition that must be TRUE for the loop to continue.
• DO → Specifies the block of statements to execute repeatedly.
• The loop stops when the condition evaluates to FALSE.

Example: Using WHILE to Print Numbers

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

Stored Procedure with WHILE Loop

DELIMITER $$

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

    WHILE counter <= 5 DO
        -- Print the current counter value
        SELECT counter;
        
        -- Increment counter
        SET counter = counter + 1;
    END WHILE;

END$$

DELIMITER ;

✅ Explanation:

1. The loop checks if counter is less than or equal to 5.
2. If the condition is TRUE, the loop prints counter, then increments it by 1.
3. The loop stops when counter exceeds 5.

Calling the Procedure

CALL while_example();

📌 Output:

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

Use Case: Factorial Calculation Using WHILE

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

DELIMITER $$

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

    WHILE i <= num DO
        SET result = result * i;
        SET i = i + 1;
    END WHILE;

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 Points

✔ Loop continues as long as the condition is TRUE.
✔ No execution if the condition is initially FALSE.
✔ Can be used for incremental tasks like calculations or iterative operations.
✔ Termination condition is critical to avoid infinite loops.

Would you like an example of a nested WHILE loop or infinite loop prevention? 🚀