SQL Simple LOOP

Simple LOOP in Procedure is a series of statement that are executed repeatedly until it push the flow of control to outside of the loop.

Understand with Example

The Tutorial illustrate an example from 'SQL Simple Loop'. To grasp this example, we create a procedure display that accept variable no as input parameter and the data type is int. The Begin include a loop that define a variable counter  and set its data type to int. The Loop run repeatedly until it push the flow of control to outside of the loop. 

Create Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS display$$

CREATE PROCEDURE display(no int)

    BEGIN
	DECLARE counter INT DEFAULT 1;
	simple_loop: LOOP
	SET counter=counter+1;
	
		select counter;

	IF counter=no THEN
		LEAVE simple_loop;
	END IF;

	END LOOP simple_loop;
    END$$

DELIMITER ;

Call Procedure

The call display(5) invoke a procedure display that accept input parameter no as '5'.

call display(5);

Result

+---------+
| counter |
+---------+
| 2       |
+---------+
1 row in set (0.00 sec)

+---------+
| counter |
+---------+
| 3       |
+---------+
1 row in set (0.03 sec)

+---------+
| counter |
+---------+
| 4       |
+---------+
1 row in set (0.08 sec)

+---------+
| counter |
+---------+
| 5       |
+---------+
1 row in set (0.13 sec)

Query OK, 0 rows affected (0.19 sec)