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.
x
is printed first. Then the comma is generated, and so based on the terminating condition that x > 0
, we have an additional comma at the end of our output.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.
Related Article - MySQL Query
- How to Sort MySQL Data in Alphabetical Order
- How to Enable Slow Query Log in MySQL
- How to Calculate Percentage in MySQL
- Where vs Having in MySQL
- Nested Select Statements in MySQL for Enhanced Query
- Tiny Integer in MySQL