loop in mysql query

loop in mysql query

loop is used to perform repeat work

delimiter $
CREATE PROCEDURE check_loop ()
BEGIN
DECLARE a INT Default 0 ; 

simple_loop: LOOP
SET a=a+1;
select a; 

IF a=11 THEN 

LEAVE simple_loop;
END IF;
END LOOP simple_loop;
END$
delimiter ;

Above loop select 1 to 10. 

LEAVE is used to terminate a loop.

Example of a REPEAT loop 

SET i=0;
loop1: REPEAT
SET i=i+1;
IF MOD(i,2)<>0 THEN /*Even number - try again*/
Select concat(i," is an odd number");
END IF;
UNTIL i >= 10
END REPEAT;

Odd numbers less than 10 implemented as a WHILE loop
SET i=1;
loop1: WHILE i<=10 DO
IF MOD(i,2)<>0 THEN /*Even number - try again*/
SELECT CONCAT(i," is an odd number");
END IF;
SET i=i+1;
END WHILE loop1;