Loops

A lot many times, situation arises that a set of commands have to be repeatedly executed. This is achievable by using looping constructs. MySQL provides us three types of looping constructs :

• Simple Loops
• Repeat Loops
• While Loops

Simple Loops

Its the most basic construct used in MYSQL. The set of statements to be executed in the loop are enclosed in LOOP...END LOOP. For managing the execution of statements in a loop, two commands used are:

• ITERATE
• LEAVE

As the name suggests LEAVE enforces that the loop is stopped when a condition has been satisfied, whereas ITERATE ensures that the loop is continued till the time a condition is not met.But whatever the case LEAVE is always a part of the syntax of the loop.
Let us make it more easy with help of an example. But before we proceed with that we need to understand is that the LOOP is a labeled construct. For labeling conventions please refer here.

DELIMITER $$
CREATE PROCEDURE TestLoop() 
BEGIN
    DECLARE num INT DEFAULT 0;
    loop1: LOOP
       SET num := num + 1;
   IF num >= 10 THEN
      LEAVE loop1;
   END IF;
END LOOP loop1;
SELECT CONCAT('We Looped ',num,' Times.');
END $$

This is a simple loop labeled as loop1, this continues on its own till the time variable l_loop does not reach a value greater than or equal to 10. As soon as condition is satisfied loop is forced to LEAVE.
The output of command

call  TestLoop()

We Looped 10 Times.

Lets check how the same can be achieved by using ITERATE

DELIMITER $$

CREATE PROCEDURE `TestLoop`()
BEGIN
    DECLARE num INT DEFAULT 0;
    loop1: LOOP
       SET num := num + 1;
   IF num < 10 THEN
      ITERATE loop1;
   END IF;
LEAVE loop1;
END LOOP loop1;
SELECT CONCAT('We Looped ',num,' Times.');
END$$

DELIMITER ;

In the above example :

   IF num < 10 THEN
      ITERATE loop1;
   END IF;

ensures that the the loop is "iterated" till "num" is less than 10 The output is same as first example i.e. without ITERATE.

While loops

This loop construct consists of WHILE...DO...END WHILE; where WHILE...DO consists of the condition to be checked and DO...END WHILE; consists of the statements to be executed in the loop. In this loop, condition is checked first and if satisfied then it executes the loop.

The following example will help in understanding the syntax :

DELIMITER $$

CREATE PROCEDURE `TestLoop`()
BEGIN
    DECLARE num INT DEFAULT 0;
    WHILE num < 5 DO
        SET num:=num+1;
    END WHILE;
SELECT CONCAT('We Looped ',num,' Times.');
END$$

DELIMITER ;

The statement :

SET num;=num+1;

will be executed till the variable "num" is less then 5.

Repeat Loop

This loop construct consists of REPEAT <statements> UNTIL <condition> END REPEAT; In this loop, the statements are executed at-least once as the condition is checked after the execution.

DELIMITER $$

CREATE PROCEDURE `TestLoop`()
BEGIN
    DECLARE num INT DEFAULT 0;
    REPEAT
        SET num:=num+1;
    UNTIL num >= 5
    END REPEAT;
SELECT CONCAT('We Looped ',num,' Times.');
END$$

DELIMITER ;

The above loop will continue until value of "num" does not reach 5

Few points to remember:

• The conditions should be checked correctly else execution will be faltered. It will either not enter the loop or might result in endless loop for example
  • If condition is num > 5 in WHILE or ITERATE it will never enter the loop.
  • If condition is num < 5 in LEAVE then the loop will be executed only once.
• The value of the variable being checked as loop condition should change, else the loop might become endless and only alternative will be to stop MYSQL.