MySQL LOOP

MySQL LOOP Statement

The LOOP statement in MySQL provides a way to execute a block of code repeatedly within a stored procedure, function, or trigger. It is a basic loop construct that executes indefinitely unless explicitly terminated using a LEAVE statement or similar control flow mechanisms.

Syntax

[loop_label:] LOOP
    -- Statements to execute
END LOOP [loop_label];

• loop_label: An optional identifier for the loop, which is helpful when using nested loops or exiting the loop with a LEAVE statement.
• The loop continues indefinitely unless terminated by:
  • A LEAVE statement.
  • Logic conditions that redirect flow outside the loop.

Key Features of LOOP

1. Indefinite Execution: The LOOP statement by itself does not evaluate any conditions to stop. You must explicitly control when it stops.
2. Control with LEAVE: Use the LEAVE statement to exit the loop based on a specific condition.
3. Nested Loops: The LOOP construct supports nesting, and each loop can have its own label for better control.

Examples of Using LOOP

1. Simple Loop with Termination

DELIMITER $$

CREATE PROCEDURE simple_loop_example()
BEGIN
    DECLARE counter INT DEFAULT 0;

    my_loop: LOOP
        SET counter = counter + 1;

        -- Print the current counter value (for demonstration purposes)
        SELECT CONCAT('Counter: ', counter);

        -- Exit the loop when the counter reaches 5
        IF counter = 5 THEN
            LEAVE my_loop;
        END IF;
    END LOOP;

    SELECT 'Loop terminated successfully.';
END$$

DELIMITER ;

Execution

CALL simple_loop_example();

Output

Counter: 1
Counter: 2
Counter: 3
Counter: 4
Counter: 5
Loop terminated successfully.

2. Nested Loops

DELIMITER $$

CREATE PROCEDURE nested_loops_example()
BEGIN
    DECLARE outer_counter INT DEFAULT 0;
    DECLARE inner_counter INT DEFAULT 0;

    outer_loop: LOOP
        SET outer_counter = outer_counter + 1;
        SET inner_counter = 0;

        inner_loop: LOOP
            SET inner_counter = inner_counter + 1;

            -- Exit inner loop when inner_counter reaches 3
            IF inner_counter = 3 THEN
                LEAVE inner_loop;
            END IF;

            SELECT CONCAT('Outer: ', outer_counter, ', Inner: ', inner_counter);
        END LOOP;

        -- Exit outer loop when outer_counter reaches 2
        IF outer_counter = 2 THEN
            LEAVE outer_loop;
        END IF;
    END LOOP;

    SELECT 'Nested loops completed.';
END$$

DELIMITER ;

Execution

CALL nested_loops_example();

Output

Outer: 1, Inner: 1
Outer: 1, Inner: 2
Outer: 1, Inner: 3
Outer: 2, Inner: 1
Outer: 2, Inner: 2
Outer: 2, Inner: 3
Nested loops completed.

3. Using LOOP for Data Processing

You can use the LOOP statement to iterate through and process data in a table.

DELIMITER $$

CREATE PROCEDURE loop_process_data()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE current_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM sample_table;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    my_loop: LOOP
        FETCH cur INTO current_id;

        -- Exit loop when no more rows are found
        IF done THEN
            LEAVE my_loop;
        END IF;

        -- Perform an operation (example: update the row)
        UPDATE sample_table
        SET processed = 1
        WHERE id = current_id;
    END LOOP;

    CLOSE cur;

    SELECT 'All rows processed.';
END$$

DELIMITER ;

Execution

CALL loop_process_data();

Use Case

• This example iterates through rows in the sample_table and updates a processed flag for each row.

Best Practices for Using LOOP

1. Always Have an Exit Condition:

   • Use LEAVE to avoid infinite loops.
   • Example: Add an IF condition that triggers the exit.

2. Label Loops Clearly:

   • Use labels like outer_loop, inner_loop for better control in nested loops.

3. Use Loops for Complex Iterations:

   • Loops are ideal for processing large datasets, applying calculations, or performing batch updates.

4. Debugging:

   • Use SELECT statements within loops for debugging during development.

5. Consider Alternatives:

   • For simple iterations, consider using WHILE or REPEAT loops, which are easier to read and manage in many cases.

Comparison of LOOP with Other Loops

Feature	LOOP	WHILE	REPEAT
Exit Condition	Explicit with LEAVE	Evaluated before loop	Evaluated after loop
Execution	Runs indefinitely	Runs while condition true	Runs at least once
Complexity	Simple but manual exit	Built-in condition	Built-in condition

Conclusion

The LOOP statement in MySQL provides flexibility for repeated execution of code blocks. While it requires manual control for termination, its straightforward syntax and utility make it a powerful tool for managing repetitive tasks, especially in stored procedures. Combined with labels and exit conditions, LOOP can handle even complex logic efficiently.

Let me know if you'd like additional examples or help!