MariaDB: WHILE Statement

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

Description

In MariaDB, the WHILE statement is used when you are not sure how many times you will execute the loop body and the loop body may not execute even once.

Syntax

The syntax for the WHILE statement in MariaDB is:

[ <strong>label_name</strong>: ] WHILE condition DO
   {...statements...}
END WHILE [ <strong>label_name</strong> ];

Parameters or Arguments

label_name

Optional. The name associated with the WHILE loop.

condition

The condition is tested each pass through the WHILE loop. If the condition evaluates to TRUE, the loop body is executed. If the condition evaluates to FALSE, the WHILE loop is terminated.

statements

The statements of code to execute each pass through the WHILE loop.

Note

• You would use a WHILE LOOP statement when you are unsure of how many times you want the loop body to execute.
• Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body may not execute even once.

Example

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

DELIMITER //

CREATE FUNCTION CalcValue ( starting_value INT )
RETURNS INT DETERMINISTIC

BEGIN

   DECLARE total_value INT;

   SET total_value = 0;

   label1: WHILE total_value = 999 DO
     SET total_value = total_value + starting_value;
   END WHILE label1;

   RETURN total_value;

END; //

DELIMITER ;

In this WHILE LOOP example, the loop would terminate once total_value exceeded 999 as specified by:

label1: WHILE total_value = 999 DO

The WHILE LOOP will continue while total_value = 999. And once total_value is > 999, the loop will terminate.