Home / MySQL – Tutorial / MySQL – Loop Statements

MySQL – Loop Statements

A stored procedure is a segment of a SQL statement that is stored in the SQL catalog as a subroutine. There may be both IN and OUT parameters in these procedures. If you use SELECT statements, they may return multiple results sets. It is also possible to create functions in MySQL.

Like other programming languages, MySQL supports flow control statements such as IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT. These statements can be used in stored programs (procedures), and RETURN can be used in stored functions. It is possible to use one flow control statement within another.

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

Syntax

begin_label: LOOP
statement_list
END LOOP end_label

Where statement_list refers to a single or set of statements that are to be repeated. The labels begin_label and end_label are optional.

The statement(s) in the LOOP are repeated until the loop is terminated. Using the LEAVE statement, you can terminate the LOOP.

The LOOP can also be terminated using the RETURN statement when used within a function. The LOOP ends with a semicolon (or the current delimiter).

Example

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;