Mysql Loop in Procedure

Loop in Procedure is a simple loop construct that enables an repeated execution of the statement list, which consists of one or more statements.Each loop is terminated by a semicolon(;) or statement delimiter. The statement within a loop are repeated untill your loop is exited; this is normally terminated by a LEAVE statement .

Understand with Example

The Tutorial helps you to understand an example from 'Mysql Loop in Procedure'.To grasp this example we create a procedure 'myloop'.The Procedure starts with the BEGIN-END pair statement that declare a variable counter and set to default value of 0.The statement within a loop are repeated untill the counter reached to 50.

The Loop will be terminated once the counter is assigned to 50.This is normally accompanied with LEAVE statement.

Query to Create Procedure named myloop:-

mysql> CREATE PROCEDURE myloop()
    ->        BEGIN
    ->        DECLARE counter INT DEFAULT 0;
    ->        simple_loop: LOOP
    ->          SET counter=counter+10;
    ->          select counter;
    ->          IF counter=50 THEN
    ->             LEAVE simple_loop;
    ->          END IF;
    ->        END LOOP simple_loop;
    -> ENDGG
Query OK, 0 rows affected (0.00 sec)

Output showing the use if Loop in procedure:-

mysql> call myloop();
    -> GG
+---------+
| counter |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

+---------+
| counter |
+---------+
|      20 |
+---------+
1 row in set (0.05 sec)

+---------+
| counter |
+---------+
|      30 |
+---------+
1 row in set (0.11 sec)

+---------+
| counter |
+---------+
|      40 |
+---------+
1 row in set (0.19 sec)

+---------+
| counter |
+---------+
|      50 |
+---------+
1 row in set (0.25 sec)

Query OK, 0 rows affected (0.31 sec)