Loops -> Repetitive tasks3 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 trueTermination 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.sqlDELIMITER $$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.sqlQuery 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 🙂
via Planet MySQL
Use of loops in MySQL stored procedures