MySQL: WHILE Statement

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

Description

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

[ label_name: ] WHILE condition DO
   {...statements...}
END WHILE [ label_name ];

Parameters or Arguments

label_name

Optional. It is a name associated with the WHILE loop.

condition

Te condition is test 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 MySQL:

DELIMITER //

CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT

BEGIN

   DECLARE income INT;

   SET income = 0;

   label1: WHILE income = 3000 DO
     SET income = income + starting_value;
   END WHILE label1;

   RETURN income;

END; //

DELIMITER ;

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

label1: WHILE income = 3000 DO

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