Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to use FOR LOOP in MySQL Stored Procedure?
The following is the syntax to work with FOR LOOP in MySQL stored procedure −
delimiter //
CREATE procedure yourProcedureName()
wholeblock:BEGIN
DECLARE anyVariableName1 INT ;
Declare anyVariableName3 int;
DECLARE anyVariableName2 VARCHAR(255);
SET anyVariableName1 =1 ;
SET anyVariableName3 =10;
SET anyVariableName2 = '';
loop_label: FORLOOP
IF anyVariableName1 > anyVariableName3 THEN
LEAVE loop_label;
END IF;
SET anyVariableName2 = CONCAT(anyVariableName2 ,anyVariableName1 ,',');
SET anyVariableName1 = anyVariableName1 + 1;
ITERATE loop_label;
END FORLOOP;
SELECT anyVariableName2;
END
//Now you can implement the above syntax. The for loop query is as follows −
mysql> delimiter // mysql> CREATE procedure ForLoop() -> wholeblock:BEGIN -> DECLARE start INT ; -> Declare maxLimit int; -> DECLARE result VARCHAR(255); -> SET start =1 ; -> SET maxLimit=10; -> SET result = ''; -> loop_label: LOOP -> IF start > 10 THEN -> LEAVE loop_label; -> END IF; -> SET result = CONCAT(result,start,','); -> SET start = start + 1; -> ITERATE loop_label; -> END LOOP; -> SELECT result; -> END -> // Query OK, 0 rows affected (0.37 sec) mysql> delimiter ;
The above for loop prints 1 to 10 i.e. in the following form 1,2,3,4,.....10. Call the stored procedure using CALL command. The syntax is as follows −
call yourStoredProcedureName();
The query to call is as follows −
mysql> call ForLoop();
Output
+-----------------------+ | result | +-----------------------+ | 1,2,3,4,5,6,7,8,9,10, | +-----------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
Updated on: 30-Jul-2019
2K+ Views
Advertisements