MySQL Stored Procedure Programming

Iterative Processing with Loops

In this section we examine the statements that the MySQL stored program language provides for iteratively (repeatedly) processing commands. There are many reasons why a program may need to iterate:

• A program that supports a user interface may run a main loop that waits for, and then processes, user keystrokes (this doesn't apply to stored programs, however).

• Many mathematical algorithms can be implemented only by loops in computer programs.

• When processing a file, a program may loop through each record in the file and perform computations.

• A database program may loop through the rows returned by a SELECT statement.

It's fairly obvious that it is the last case—processing rows returned by a SELECT statement—that will be the most common reason for looping in MySQL stored programs, and we will give this topic a great deal of consideration in Chapter 5. In this chapter, we consider the looping commands in their general form.

LOOP Statement

The simplest possible looping construct is the LOOP statement. The syntax for this statement is as follows:

    [label:] LOOP
        statements
    END LOOP 
 [label];

The statements between the LOOP and END LOOP statements will be repeated indefinitely, until the LOOP is terminated. You can terminate the LOOP using the LEAVE statement, which we will describe shortly.

You can supply labels to the loop, which have the same syntax as those we can add to BEGIN-END blocks. Labels can help you identify the END LOOP statement that corresponds ...