Looping Constructs in MySQL Stored Procedures

MySQL stored procedures offer several looping constructs to execute blocks of code repeatedly. These loops are essential for automating tasks, processing data in sets, and implementing complex logic within your database. This tutorial covers the three primary loop types available in MySQL: LOOP, WHILE, and REPEAT. We’ll explore their syntax, behavior, and practical examples.

1. The `LOOP` Statement

The LOOP statement creates an infinite loop that continues executing until explicitly stopped using the LEAVE statement. It’s a fundamental building block for custom loop control.

Syntax:

loop_label: LOOP
    -- Code to be executed repeatedly
    IF condition THEN
        LEAVE loop_label; -- Exit the loop
    END IF;
    -- Optional: ITERATE loop_label; -- Skip to the next iteration
END LOOP loop_label;

Explanation:

loop_label: A name you assign to the loop, used with LEAVE and ITERATE.
LEAVE loop_label: Immediately exits the loop.
ITERATE loop_label: Skips the remaining code within the current iteration and starts the next iteration.

Example:

This procedure inserts numbers from 1 to 5 into a table named table1 with a column col1.

DROP PROCEDURE IF EXISTS ABC;
DELIMITER $$
CREATE PROCEDURE ABC()
BEGIN
    DECLARE a INT DEFAULT 1;
    simple_loop: LOOP
        INSERT INTO table1 VALUES (a);
        SET a = a + 1;
        IF a > 5 THEN
            LEAVE simple_loop;
        END IF;
    END LOOP simple_loop;
END $$
DELIMITER ;

Important Note: Always include a condition to LEAVE the loop, otherwise it will run indefinitely, potentially causing your database to become unresponsive.

2. The `WHILE` Statement

The WHILE statement executes a block of code as long as a specified condition remains true. It’s useful when you know the number of iterations in advance or when the loop’s continuation depends on a condition.

Syntax:

while_label: WHILE condition DO
    -- Code to be executed as long as the condition is true
    -- Optional: ITERATE while_label;
END WHILE while_label;

Example:

This procedure prints numbers from 1 to 5.

DELIMITER //
CREATE PROCEDURE while_example()
BEGIN
    DECLARE x INT DEFAULT 1;
    SET @str = ''; -- Initialize a variable to store the result

    WHILE x <= 5 DO
        SET @str = CONCAT(@str, x, ',');
        SET x = x + 1;
    END WHILE;

    SELECT @str;
END //
DELIMITER ;

3. The `REPEAT` Statement

The REPEAT statement executes a block of code at least once and continues to execute as long as a specified condition remains false. The condition is checked after each iteration.

Syntax:

repeat_label: REPEAT
    -- Code to be executed at least once
    -- Optional: ITERATE repeat_label;
UNTIL condition END REPEAT repeat_label;

Example:

This procedure prints numbers from 5 down to 1.

DELIMITER //
CREATE PROCEDURE repeat_loop_example()
BEGIN
    DECLARE x INT DEFAULT 5;
    SET @str = '';

    REPEAT
        SET @str = CONCAT(@str, x, ',');
        SET x = x - 1;
    UNTIL x <= 0 END REPEAT;

    SELECT @str;
END //
DELIMITER ;

Choosing the Right Loop

LOOP: Use when you need maximum control over loop execution and want to exit based on complex conditions.
WHILE: Use when the loop’s continuation depends on a condition evaluated before each iteration.
REPEAT: Use when you need to execute the loop body at least once, regardless of the initial condition.

By understanding these looping constructs, you can write powerful and efficient stored procedures in MySQL to automate tasks and manipulate data effectively.