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.



yntax 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 
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.