Loops in MySQL Stored Procedure

By Avinash

May 30, 2012Tutorial1 Comment

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:

  1. REPEAT
  2. WHILE
  3. 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:

REPEAT Statements; UNTIL expression END REPEAT
  REPEAT
    Statements;
  UNTIL expression
  END REPEAT

Here is the basic demo for how you can use this loop in your MySQL Stored Procedure.

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;
  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;

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:

WHILE expression DO Statements; END WHILE
  WHILE expression DO
    Statements;
  END WHILE

Here is the basic demo for this WHILE loop in MySQL Stored Procedure.

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;
  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;

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.

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;
  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;

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.

Share This Article

Tags: