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.

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)

Advertisements

Share on Google+Share on Google+

SQL Simple LOOP

Posted on: December 20, 2008 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

Ads
 

Discuss: SQL Simple LOOP  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0

Ads

 

Ads