Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Stored Procedures and Functions in MySQL- Part 6

    • 0
    • 4
    • 4
    • 1
    • 0
    • 0
    • 0
    • 0
    • 361
    Comment on it

    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()
    will be

    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.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: