- 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 :)
