Hi all, Here we reach to the Loops in MySQL Stored Procedure. But before starting, I hope you are aware of previous articles. If not let me give falshback first.
Flashback
Upto now we have covered the basic Introduction For Stored Procedure in MySQL, then we have compeleted the Types of Parameter in MySQL Stored Procedure. After parameter we have covered the Conditional Controls in MySQL Stored Procedure.
Now I am going to covers the different types of Loops in MySQL Stored Procedure.
Types of Loop – MySQL Stored Procedure
We can have three diffrent types of Loops in MySQL Stored Procedure, which are as below:
- REPEAT
- WHILE
- LOOP, LEAVE & ITERATE
Let’s dive into each one by one :)
REPEAT
This loop also known as POST-TEST loop as this loop will execute the statement first then check for the condition. This loop will keep executing untill the conditions gets false. Here is the basic syntax for this loop:
[cc lang=”mysql”]
REPEAT
Statements;
UNTIL expression
END REPEAT
[/cc]
Here is the basic demo for how you can use this loop in your MySQL Stored Procedure.
[cc lang=”mysql”]
DECLARE num INT;
DECLARE my_string VARCHAR(255);
REPEAT
SET my_string = CONCAT(my_string,num,’,’);
SET num = num + 1;
UNTIL num > 5
END REPEAT;
[/cc]
WHILE
This is reverse of the WHILE loop, as this loop will check the condition first before executing the statement. Due to this reason, this loop is also known as PRE-TEST loop in MySQL Stored Procedure.
Let’s have a look at basic syntax of this loop:
[cc lang=”mysql”]
WHILE expression DO
Statements;
END WHILE
[/cc]
Here is the basic demo for this WHILE loop in MySQL Stored Procedure.
[cc lang=”mysql”]
DECLARE num INT;
DECLARE my_string VARCHAR(255);
SET num = 1;
SET str = ”;
WHILE num <= 10 DO
SET my_string = CONCAT(my_string,num,',');
SET num = num + 1;
END WHILE;
[/cc]
LOOP, LEAVE & ITERATE
This loop is similar to break and continue in other programming lanaguage. LOOP will behave as a body of the loop, while LEAVE is used to leave out of the loop and ITERATE is used to continue with the loop.
Let’s have a look at the sample procedure which uses the LOOP, LEAVE and ITERATE in it.
[cc lang=”mysql”]
DECLARE num INT;
DECLARE str VARCHAR(255);
SET num = 1;
SET my_string = ”;
loop_label: LOOP
IF num > 10 THEN
LEAVE loop_label;
END IF;
SET num = num + 1;
IF (num mod 3) THEN
ITERATE loop_label;
ELSE
SET my_string = CONCAT(my_string,num,’,’);
END IF;
END LOOP;
[/cc]
Now we are done with the all three types of loops in MySQL Stored Procedure. Now in upcoming article we will cover Transaction (Rollback & Commit) in Stored Procedure. Subscribe to our RSS Feed to don’t miss that article. And Don’t forget to follow us on Twiiter and to Like us on Facebook.
