Loops in MySQL

In MySQL, loops are powerful constructs used to repeatedly execute a block of code or a set of statements until a specified condition is satisfied. MySQL supports various types of loop constructs, including the basic LOOP, WHILE and REPEAT loops along with flow control statements such as IF, CASE, ITERATE and LEAVE.

These constructs and statements provide flexibility and control over the execution flow within stored programs. In this article, We will learn about Loops in MySQL in detail and so on.

Loops in MySQL

In MySQL, loops are used to repeatedly execute a block of code or set of statements until a specified condition is met. They are particularly useful in stored procedures and functions for performing repetitive tasks. MySQL provides several types of loop constructs such as:

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 Geektable 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:

Conclusion

Loops in MySQL facilitate repetitive task execution, enhancing the efficiency of database operations. By leveraging constructs like LOOP, WHILE, and REPEAT, along with flow control statements such as IF, CASE, ITERATE, and LEAVE, developers can create complex stored procedures and functions that automate tasks and process data iteratively. The examples provided illustrate how to use these constructs effectively, demonstrating their application in various scenarios, from simple number iteration to more complex data manipulation tasks.