/******************************************************************************
  3 Examples of Loops & Iterations
******************************************************************************/
/*====================================================================
  This is an example of a simple LOOP
=====================================================================*/
DROP   PROCEDURE loops1_proc;
/
CREATE PROCEDURE loops1_proc( )
BEGIN
    DECLARE  i      int          default 0; 
    DECLARE  result varchar(100) default '';

L1: LOOP                                    -- begin a loop
        IF mod(i, 2) = 0 then               -- if number is even
            SET result = concat(result, i, ', '); 
        END IF; 
        SET i = i+1; 
        IF i > 50 then 
            LEAVE  L1;                      -- very important
        END IF;
    END LOOP; 

    SELECT result; 
END; 
/

CALL loops1_proc();
/



/*====================================================================
  This is an example of a WHILE DO loop
=====================================================================*/
DROP   PROCEDURE loops2_proc;
/
CREATE PROCEDURE loops2_proc( )
BEGIN
    DECLARE  i      int          default 0; 
    DECLARE  result varchar(100) default '';

    WHILE  i<=50  DO                        -- begin a loop
        IF mod(i, 2) = 0 then               -- if number is even
            SET result = concat(result, i, ', '); 
        END IF; 
        SET i = i+1;                        -- very important
    END WHILE; 

    SELECT result; 
END; 
/

CALL loops2_proc();
/



/*====================================================================
  This is an example of a REPEAT UNTIL loop
=====================================================================*/
DROP   PROCEDURE loops3_proc;
/
CREATE PROCEDURE loops3_proc( )
BEGIN
    DECLARE  i      int          default 0; 
    DECLARE  result varchar(100) default '';
    REPEAT                                  -- begin a loop
        IF mod(i, 2) = 0 then               -- if number is even
            SET result = concat(result, i, ', '); 
        END IF; 
        SET i = i+1;                        -- very important
    UNTIL i > 50
    END REPEAT; 
    SELECT result; 
END; 
/

CALL loops3_proc();