How to Use the LOOP Statement in MySQL

08/06/2021

Contents

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.

Syntax

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;

Examples

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.

#MySQL