How to Use the LOOP Statement in MySQL
In this article, you will learn how to use the LOOP statement in MySQL.
Using the LOOP statement in MySQL
MySQL provides the LOOP statement to create iterative operations. The LOOP statement allows us to perform a set of instructions repeatedly until a specific condition is met.
The syntax of the LOOP statement in MySQL is as follows:
LOOP -- Statements to execute repeatedly -- Exit the loop using the LEAVE statement END LOOP;
Using the LOOP statement
Let’s consider an example where we want to print the numbers from 1 to 10 using the LOOP statement:
DELIMITER // CREATE PROCEDURE print_numbers() BEGIN DECLARE i INT DEFAULT 1; my_loop: LOOP IF i > 10 THEN LEAVE my_loop; END IF; SELECT i; SET i = i + 1; END LOOP; END// DELIMITER ; CALL print_numbers();
In the above example, we first define a stored procedure called “print_numbers”. We declare an integer variable “i” with a default value of 1. We then define a label “my_loop” for the LOOP statement.
Inside the LOOP statement, we use an IF statement to check if the value of “i” is greater than 10. If it is, we use the LEAVE statement to exit the LOOP. Otherwise, we print the value of “i” using a SELECT statement, and increment the value of “i” by 1.
Finally, we call the “print_numbers” procedure using the CALL statement, which executes the procedure and prints the numbers from 1 to 10.
Using the LOOP statement for iterative calculations
Let’s consider another example where we want to calculate the sum and average of sales for each unique salesperson in a table called “sales”, using the LOOP statement:
DROP TABLE IF EXISTS temp_sales_summary; CREATE TEMPORARY TABLE temp_sales_summary ( name VARCHAR(50), total_sales DECIMAL(10,2), average_sales DECIMAL(10,2), number_of_sales INT ); SET @counter = 0; my_loop: LOOP SELECT name, SUM(amount) AS total_sales, AVG(amount) AS average_sales, COUNT(*) AS number_of_sales FROM sales GROUP BY name LIMIT @counter, 1 INTO @name, @total_sales, @average_sales, @number_of_sales; IF @name IS NULL THEN LEAVE; END IF; INSERT INTO temp_sales_summary (name, total_sales, average_sales, number_of_sales) VALUES (@name, @total_sales, @average_sales, @number_of_sales); SET @counter = @counter + 1; END LOOP; SELECT * FROM temp_sales_summary;
In the above example, we first drop any existing temporary table called “temp_sales_summary”. We then create a new temporary table with three columns, “name”, “total_sales”, “average_sales”, and “number_of_sales”. We initialize a variable, @counter, to 0. We then use the LOOP statement to iterate over each unique salesperson in the “sales” table, and calculate their total sales, average sales, and number of sales. Finally, we insert this information into the temporary table. We increment the value of @counter by 1, until all salespeople have been processed.
We then print the contents of the temporary table using a SELECT statement.