Loop in Stored Procedures

Summary: in this tutorial, you will learn how to use various loop statements in MySQL including WHILE, REPEAT and LOOP to run a block of code repeatedly based on a condition.

MySQL provides loop statements that allow you to execute a block of SQL code repeatedly based on a condition. There are three loop statements in MySQL: WHILE, REPEAT and LOOP.

We will examine each statement in more detail in the following section.

WHILE loop

The syntax of the WHILE statement is as follows:

The WHILE loop checks the expression at the beginning of each iteration. If  the expression evaluates to TRUE,  MySQL will executes statements between WHILE and END WHILE until the expression evaluates to FALSE. The WHILE loop is called pretest loop because it checks the expression before the statements execute.

Here is an example of using the WHILE loop statement in stored procedure:

In the stored procedure above:

  • First, we build str string repeatedly until the value of the x variable is greater than 5.
  • Then, we display the final string using the SELECT statement.

Notice that if we don’t initialize x variable, its default value is NULL. Therefore the condition in the WHILE loop statement is always TRUE and you will have a indefinite loop, which is not expected.

REPEAT loop

The syntax of the REPEAT loop statement is as follows:

First MySQL executes the statements, and then it evaluates the expression. If the expression evaluates to TRUE, MySQL executes the statements repeatedly until the expression evaluates to FALSE.

Because the REPEAT loop statement checks the expression after the execution of statements therefore the REPEAT loop statement is also known as post-test loop.

We can rewrite the stored procedure that uses WHILE loop statement above using the REPEAT loop statement:

It is noticed that there is no delimiter semicolon (;) in the UNTIL expression.

LOOP, LEAVE and ITERATE Statements

The LEAVE statement allows you to exit the loop immediately without waiting for checking the condition. The LEAVE statement works like the  break statement in other languages such as PHP, C/C++, Java, etc.

The ITERATE statement allows you to skip the entire code under it and start a new iteration. The ITERATE statement is similar to the continue statement in PHP, C/C++, Java, etc.

MySQL also gives you a LOOP statement that allows you to execute a block of code repeatedly with an additional flexibility of using a loop label.

The following is an example of using the LOOP loop statement.

  • The stored procedure only constructs string with even numbers e.g., 2, 4, 6, etc.
  • We put a loop_label  loop label before the LOOP statement.
  • If the value of  x is greater than 10, the loop is terminated because of the LEAVE statement.
  • If the value of the x is an odd number, the   ITERATE statement ignores everything below it and starts a new iteration.
  • If the value of the x is an even number, the block in the ELSE statement will build the string with even numbers.

In this tutorial, you have learned various MySQL loop statements to execute a block of code repeatedly based on a condition.