LOOP Statement

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

Description

In MariaDB, 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 MariaDB is:

[ label_name: ] LOOP

   {...statements...}

END LOOP [ label_name ];

Parameters or Arguments

label_name

Optional. The 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 MariaDB:

DELIMITER //

 

CREATE FUNCTION CalcValue ( starting_value INT )

RETURNS INT DETERMINISTIC

 

BEGIN

 

   DECLARE total_value INT;

 

   SET total_value = 0;

 

   label1: LOOP

     SET total_value = total_value + starting_value;

     IF total_value < 850 THEN

       ITERATE label1;

     END IF;

     LEAVE label1;

   END LOOP label1;

 

   RETURN total_value;

 

END; //

 

DELIMITER ;

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