Looping in Mysql is a simple loop construct, that enables the repeated execution of the statement list. This include one or more statements. Each statement is terminated by a semicolon(;). The statement inside a loop are repeated untill the loop is existed. The Leave statement is used to accomplished.
Understand with Example
The Tutorial illustrate an example from 'Mysql Looping'. To understand this example we create a procedure 'ABC' that include BEGIN-END label consists of declare variable. The set keyword is used to set the value of variable 'a'. The loop runs within the block till the value of variable 'a' become 3.The Loop will be terminated once variable become more than 3.The loop is existed from the statement list.
Query to Create Procedure named ABC:
mysql> drop procedure if exists ABC; -> CREATE PROCEDURE ABC() -> BEGIN -> DECLARE a INT Default 0 ; -> simple_loop: LOOP -> SET a=a+1; -> select a; -> IF a=3 THEN -> LEAVE simple_loop; -> END IF; -> END LOOP simple_loop; -> ENDGG Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.06 sec)
mysql> call abc(); -> GG +------+ | a | +------+ | 1 | +------+ 1 row in set (0.02 sec)
+------+ | a | +------+ | 2 | +------+ 1 row in set (0.22 sec)
+------+ | a | +------+ | 3 | +------+ 1 row in set (0.42 sec)
Query OK, 0 rows affected (1.30 sec)
Posted on: January 17, 2009 If you enjoyed this post then why not add us on Google+? Add us to your Circles