MySQL - LOOP Statement

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. These procedures contain IN and OUT parameters, or both. They may return result sets in case you use SELECT statements; they can return multiple result-sets. You can also create functions in MYSQL.

Similar to other programming languages MySQL provides support for the flow control statements such as IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT. You can use these statements in the stored programs (procedures), and RETURN in stored functions. You can use one Flow Control Statement with in another.

MySQL LOOP Statement

The LOOP is a compound MySQL statement which is used to execute a single or set of statements repeatedly.

Syntax

Following is the syntax of the loop statement is MySQL −

begin_label: LOOP
statement_list
END LOOP end_label

Where, statement_list is a single or set of statements that are to be repeated. begin_label and end_label are the optional labels of the LOOP statement.

The statement(s) in the LOOP are executed repeatedly till the loop is terminated. You can terminate the LOOP using the LEAVE statement.

When used in a function the LOOP can also be terminated using the RETURN statement. Each statement in the LOOP ends with a semi colon (or. the current delimiter).

Example 1

Following query demonstrates the usage of the LOOP statement with a procedure −

Delimiter //
CREATE procedure loopDemo()
   label:BEGIN
      DECLARE val INT ;
      DECLARE result VARCHAR(255);
      SET val =1;
      SET result = '';
         loop_label: LOOP
         IF val > 10 THEN 
            LEAVE loop_label;
         END IF;
         SET result = CONCAT(result,val,',');
         SET val = val + 1;
         ITERATE loop_label;
      END LOOP;
      SELECT result;
      END//

You can call the above procedure as follows −

call loopDemo;//

Output

Following is the output of the above query −

Example 2

Following query demonstrates how to use the LOOP statement with in a function.

DELIMITER //
CREATE FUNCTION Sample (bonus INT)
   RETURNS INT
   BEGIN
      DECLARE income INT;
      SET income = 0;
      myLabel: LOOP
         SET income = income + bonus;
         IF income < 10000 THEN
            ITERATE myLabel;
         END IF;
         LEAVE myLabel;
      END LOOP myLabel;
   RETURN income;
END; //
DELIMITER ;

You can call the above created function as shown below −

SELECT Sample(1000);

Output

The above query generates the following output −