The For Loop in MySQL

In this tutorial, we will learn how to use the for loop in MySQL.

A for loop is generally used to query through a particular condition. In simpler words, these loops keep on executing in MySQL until and unless a particular predefined condition is met.

Once the set condition is met, the for loop execution under consideration is terminated. Upon termination of this for loop, we can check the loop results printed in the console or stored in a data structure.

Let us understand more about the details and implementation of this loop.

To run or execute a for loop in MySQL, one must write a stored procedure. This procedure is sometimes known as a collection of MySQL statements written together to avoid rewriting MySQL queries for execution repeatedly.

There are multiple ways to write a stored procedure in MySQL, which we will learn more about in a separate tutorial. Let us understand how to write a stored procedure for the for loop in a particular database in MySQL.

Let us create a dummy database. We can name this database boatdb, which refers to the different boats set to sail for a particular company. We can do this using the following query.

CREATE DATABASE boatdb;

Once we do this, we can see that we will have a new database named boatdb in our directory.

We can now execute queries and procedures in this database by enabling its use. We can use this newly created database with the following query.

USE boatdb;

This would change our current database under consideration.

Now let us write a stored procedure with a for loop. Particularly, let us try to print values from -5 to 0 with comma separation.

-- Start of our procedure
delimiter //

CREATE procedure boatdb.for_loop_example()
wholeblock:BEGIN
  DECLARE x INT;
  DECLARE str VARCHAR(255);
  SET x = -5;
  SET str = '';
  loop_label: LOOP
    IF x > 0 THEN
      LEAVE loop_label;
    END IF;
    SET str = CONCAT(str,x,',');
    SET x = x + 1;
    ITERATE loop_label;
  END LOOP;
  SELECT str;
END//
-- End of Procedure

As we can see in the procedure above, we have a variable x, initially set to -5. We update the value of x with every iteration in our loop and print the value of x with a comma to separate the neighboring values.

Once we have the procedure written and set to use, we can call this procedure using the CALL statement in MySQL. It can be illustrated as follows.

call for_loop_example();

As we can see in the statement above, we call the for_loop_example(), our stored procedure in the boatdb database.

Output:

str
-5,-4,-3,-2,-1,0,

As we had aimed before, we have our values ranging from -5 to 0 printed with the help of the for loop using the stored procedure.

Therefore, with the help of a stored procedure in MySQL, we can efficiently write a for loop that can query through data and generate meaningful results as expected.