Use of loops in MySQL stored procedures

Loops -> Repetitive tasks

3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT  and UNTIL clauses that runs until a condition is true

Termination of loop -> LEAVE statement.

I will demonstrate the use of loops the simple loop way.

Simple counter from 1 to 10:

Store below stored procedure in a file named my_loop.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS my_loop$$

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

    my_loop: LOOP
        SET counter = counter + 1;

        IF counter = 10 THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    SELECT CONCAT('Done counting up to ', counter) AS result;

END$$

DELIMITER ;

Execute the stored procedure:

mysql> SOURCE my_loop.sql

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL my_loop();

+------------------------+

| result                 |

+------------------------+

| Done counting up to 10 |

+------------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Explaining simply, we are defining a counter that starts with 0 and a LOOP labelled my_loop that increments from 0 onward and a condition inside to check for counter value of 10, when the loop terminates through the use of LEAVE clause. You can give a try to the other loop clauses :)