Loops in MySQL

The MySQL LOOP statement could be used to run a block of code or set of statements, again and again, depends on the condition.

Syntax :

[labelname:] LOOP
   statements
END LOOP [labelname]

Parameters –

• labelname : It is an optional label at the start and end.
• statements : They could have one or multiple statements, each ended by a semicolon (;) and executed by LOOP.

Syntax of the LOOP statement with LEAVE statement :

[labelname]: LOOP
     -- terminate the loop
   IF condition THEN
       LEAVE [labelname];
   END IF;
END LOOP;

Example-1 :

DROP PROCEDURE IF EXISTS GeekLoop();

DELIMITER $$ 
CREATE PROCEDURE GeekLoop()
 BEGIN
DECLARE no INT;
  SET no = 0;
  loop: LOOP
    SET no = no +1;
    select no ;
    IF no =5 THEN
     LEAVE loop;
    END IF;
 END LOOP loop;
SELECT no;
END $$
DELIMITER ;

Statement to check the output :

CALL GeekLoop();

Output –

0, 1, 2, 3, 4, 5

Example-2 :

DELIMITER $$
CREATE FUNCTION Geekdemo (value1 INT)
RETURNS INT
BEGIN
 DECLARE value2 INT;
 SET value2 = 0;
 label: LOOP
  SET income = value2 + value1 ;
  IF value2 < 4000 THEN
    ITERATE label;
  END IF;
  LEAVE label;
 END LOOP label;
 RETURN value2 ;
END $$
DELIMITER ;

Queries to check the output :

CALL Geekdemo();

Input –

value1: 3500

Output –

value2: 3500

Example-3 :

CREATE TABLE Geektable (value VARCHAR(50) NULL DEFAULT NULL);

DELIMITER $$ 
CREATE PROCEDURE ADD()
 BEGIN
  DECLARE a INT Default 1 ;
  simple_loop: LOOP     
    insert into table1 values(a);
    SET a=a+1;
    IF a=11 THEN
      LEAVE simple_loop;
    END IF;
 END LOOP simple_loop;
END $$

Queries to check the output –

CALL ADD();
Select value 
from Geektable;

Output –

1
2
3
4
5
6
7
8
9
10