Open In App

Loops in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

The MySQL LOOP statement could be used to run a block of code or set of statements, again and again, depending on the condition. Stored procedures are a subset of SQL statements that are kept in the SQL catalog as subroutines. These procedures may have both IN and OUT parameters. If you use SELECT statements, they might return result sets or multiple result sets. In MYSQL, functions can also be made.

IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT are examples of flow control statements supported by MySQL, much like in other programming languages. These statements can be used in stored programs (procedures), and stored functions can use RETURN. One Flow Control Statement may be used inside another.

Syntax:

[labelname:] LOOP

statements

END LOOP [labelname]

Parameters:

  • Label name: 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

Let’s see this is a stored procedure written in MySQL that creates a procedure called “GeekLoop”.This procedure uses a loop to output the numbers from 1 to 5 and then outputs the final value of the variable “no”.

We should also be well versed with this that the code sets the delimiter to “$$” instead of the default “;” delimiter, and that it resets the delimiter back to “;” at the end of the procedure. Additionally, the user should have the necessary permissions to create and execute stored procedures in the MySQL database.

Query:

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 –

 

Example 2 

This is a MySQL user-defined function called “Geekdemo”. The function takes one integer parameter “value1” and returns an integer value.

Here’s a breakdown of what each line does:

  1. DELIMITER: “Changesthedelimiterto” instead of the default “;” delimiter.
  2. CREATE FUNCTION Geekdemo (value1 INT): Creates a user-defined function called “Geekdemo” that takes one integer parameter called “value1”.
  3. RETURNS INT: Indicates that the function returns an integer value.
  4. BEGIN: Begins the block of code for the function.
  5. DECLARE value2 INT: Declares a local variable called “value2” of type integer and initializes it to 0.
  6. label: LOOP: Defines a loop called “label”.
  7. SET value 2= value2 + value1 Calculates the sum of “value2” and “value1” and stores the result in “income”. However, the “income” variable is not defined before this line, so it should be “value2”.
  8. IF value2 < 4000 THEN: If “value2” is less than 4000, the loop will continue.
  9. ITERATE label: Skips to the next iteration of the loop.
  10. END IF: Ends the conditional statement.
  11. LEAVE label: Exits the loop labeled “label” when “value2” is greater than or equal to 4000.
  12. END LOOP label: Ends the loop.
  13. RETURN value2: Returns the final value of “value2”.
  14. END $$: Ends the block of code for the function.
  15. DELIMITER: Changes the delimiter back to “;”.

Query:

DELIMITER $$
CREATE FUNCTION Geekdemo (value1 INT)
RETURNS INT
BEGIN
 DECLARE value2 INT;
 SET value2 = 0;
 label: LOOP
  SET value2 = 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 :

 


Last Updated : 11 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads