MySQL For Loop Example

In this article, we will go through a few examples of For Loop in MySQL.

Like other languages, MySQL also provides LOOP functionality to execute one or more statements multiple times. The statements within the loop repeat until the LOOP gets terminated.

Table of Contents

Advertisements

MySQL For Loop Syntax
MySQL For Loop Examples

MySQL For Loop Syntax

We will get started by looking into the basic syntax of for loop in MySQL.

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;

The syntax is similar to loops in many programming languages.

Frequently Asked:

LOOP: start the loop.
IF: will check the <condition>. It can be positioned anywhere within the loop.
THEN: defines the flow of executions.
LEAVE: to terminate the loop.
END LOOP: to end the loop.
ITERATE: to iterate through the loop.
label_for_loop: label of the loop to direct ITERATE and LEAVE.
statements within the for loop are separated by ‘;’ .

Now let us look into a few examples.

MySQL For Loop Examples

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

Observe the below query for the solution.

DROP PROCEDURE IF EXISTS for_loop_star;
DELIMITER $$
CREATE procedure for_loop_star()
BEGIN
  DECLARE x INT;
  DECLARE star 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, star);
  END LOOP;
SELECT f_output;
END $$
DELIMITER ;

Explanation:

Here we are creating a procedure for_loop_star() to run a loop to print the ‘*’ five times as the loop iterates five times.
The initial value of x is 1, and then in every iteration, it is increment by 1.
star concatenates with f_output in every iteration.
IF checks the value of x, and if it is greater than five, terminate the loop by the LEAVE statement.
Display the output using the SELECT f_output statement.

Let us call the procedure for_loop_star() to check its working.

CALL for_loop_star();

Output:-

Example 2: Loop to decrement value of an integer and display in output

Observe the below query for the solution.

DROP PROCEDURE IF EXISTS for_loop_x;
DELIMITER $$
CREATE procedure for_loop_x()
BEGIN
  DECLARE x INT;
  SET x = 10;
  forloop: LOOP
  IF x < 1 THEN
  LEAVE forloop;
  END IF;
    SET x = x - 1;
    ITERATE forloop;
  END LOOP;
SELECT x;
END $$
DELIMITER ;

Explanation:-

Procedure for_loop_x() is created to execute the statements in loop.
The initial value of x is 10 and is decremented in every iteration.
loop will run until the value of x becomes 0 (x < 1).
Finally, the value of x is displayed with the SELECT statement.

Let us call the procedure for_loop_x.

CALL for_loop_x();