MySQL loop example – MySQL For Loop Example

MySQL loop example: In this article we will discuss about For Loop in MySQL with examples.

Loops in MySQL

Loop is a order of instructions that is continually replicated until a given condition is reached. MySQL also provides loop functionality like other languages.

Syntax:

label_for_loop: LOOP
IF <condition> THEN
LEAVE label_for_loop;
END IF;
<statements to be executed - separated by ';' >
ITERATE label_for_loop;
END LOOP;

Parameters:

1.labelname:It is an non-mandatory label at the start and end.

2.loop:For start and end loop.

3.statements:Condition which you give for execution

Flow of executions in For Loop:

MySQL For Loop Examples

Let’s understand it by example,

Example 1: Loop through and display the ‘%’ five times

DROP PROCEDURE IF EXISTS for_loop_star;
DELIMITER $$
CREATE procedure for_loop_star()
BEGIN
  DECLARE x INT;
  DECLARE percentage VARCHAR(50);
  DECLARE f_output VARCHAR(50);
  SET x = 1;
  SET star= "%";
  SET f_output ="";
  forloop: LOOP
    IF x > 5 THEN
    LEAVE forloop;
    END IF;
   SET x = x + 1;
   set f_output = CONCAT(f_output, percentage);
  END LOOP;
SELECT f_output;
END $$
DELIMITER ;
CALL for_loop_percentage();

In above code you can see that we have created a strategy for_loop_percentage() to run for loop to print the ‘%’ five times .Initial x value is 1,after every loop it increment by 1.Percentage concatenates with f_output in every iteration,after that checks the value of x, and if it is greater than five the loop will terminate.

Using SELECT f_output statement we will get our output.

Output:

f_output-
%%%%%

Example2:CREATE TABLE Mytable (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 Mytable;

Output:

1
2
3
4
5
6
7
8
9
10

Example3:

DELIMITER $$
CREATE FUNCTION Mydemo (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 Mydemo();

Input –

value1: 3500

Output –

value2: 3500

Conclusion:

In this article we have discussed about For Loop in MySQL with different examples.Thank you!