MySQL: LOOP Statement

This tutorial explains how to use the LOOP statement in MySQL with syntax and examples.

Description

In MySQL, the LOOP statement is used when you are not sure how many times you want the loop body to execute and you want the loop body to execute at least once.

Syntax

The syntax for the LOOP statement in MySQL is:

[ label_name: ] LOOP
   {...statements...}
END LOOP [ label_name ];

Parameters or Arguments

label_name

Optional. It is a name associated with the LOOP. You use the label_name when executing an ITERATE statement or LEAVE statement.

statements

The statements of code to execute each pass through the LOOP.

Note

• You would use a LOOP statement when you are unsure of how many times you want the loop body to execute.
• You can terminate a LOOP statement with either a LEAVE statement or a RETURN statement.

Example

Let's look at an example that shows how to use the LOOP statement in MySQL:

DELIMITER //

CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT

BEGIN

   DECLARE income INT;

   SET income = 0;

   label1: LOOP
     SET income = income + starting_value;
     IF income  4000 THEN
       ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;

   RETURN income;

END; //

DELIMITER ;

In this MySQL LOOP example, the ITERATE statement would cause the loop to repeat while income is less than 4000. Once income is greater than or equal to 4000, the LEAVE statement would terminate the LOOP.