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
It’s fairly obvious that it is the last case—processing rows
returned by a
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.
The simplest possible looping construct is the
LOOP statement. The syntax for this
statement is as follows:
The statements between the
LOOP statements will be repeated indefinitely, until the
LOOP is terminated. You can
LOOP using the
LEAVE statement, which we will
You can supply labels to the loop, which have the same syntax
as those we can add to
blocks. Labels can help you identify the
END LOOP statement that corresponds to a
Equally important, labels can be used to control execution flow, as
we will see in subsequent sections.
Example 4-19 shows
a very simple (and very dangerous) loop. It will continue forever,
or at least until you manage to somehow terminate it. Because stored
programs run inside of the database server, using Ctrl-C or other
forms of keyboard interrupts will be ineffective—you will only be
able to terminate this loop by issuing a
KILL command against the MySQL session, or
by shutting down the database server. In the meantime, the loop will
consume as much CPU as it can, so we don’t recommend that you run
this example on your mission-critical production systems.
Obviously we almost never want to program an infinite loop,
and therefore we need some way to terminate the loop. We can do this
LEAVE statement, so let’s move on to this statement without
LEAVE statement allows
us to terminate a loop. The general syntax for the
LEAVE statement is:
LEAVE causes the current
loop to be terminated. The label matches the loop to be terminated,
so if a loop is enclosed within another loop, we can break out of
both loops with a single statement.
In the simplest case, we simply execute
LEAVE when we are ready to exit from the
LOOP, as shown in Example 4-20.
SET i=1; myloop: LOOP SET i=i+1; IF i=10 then LEAVE myloop; END IF; END LOOP myloop; SELECT 'I can count to 10';
LEAVE can be used to exit
from any of the alternative looping structures, as we’ll examine in
upcoming sections. In fact, you can also use
LEAVE if you want to break out of a named
BEGIN-END block (introduced
earlier in this chapter).
ITERATE statement is
used to restart execution at the beginning of a loop, without
executing any of the remaining statements in the loop.
ITERATE has the following syntax:
When MySQL encounters the
ITERATE statement, it recommences
execution at the start of the nominated loop. In Example 4-21, we print all odd
numbers less than 10.
used to repeat the loop if the number we have is not odd.
LEAVE is used to terminate the loop once
we reach 10.
SET i=0; loop1: LOOP SET i=i+1; IF i>=10 THEN /*Last number - exit loop*/ LEAVE loop1; ELSEIF MOD(i,2)=0 THEN /*Even number - try again*/ ITERATE loop1; END IF; SELECT CONCAT(i," is an odd number"); END LOOP loop1;
While this loop is useful to illustrate the use of
ITERATE to control a loop, it is a rather
poorly constructed algorithm. We could easily have halved the number
of loop iterations by incrementing the loop variable
i by two rather than by one.
ITERATE causes the
execution of the loop to restart at the top of the loop. If you are
REPEAT loop (see the next section), this means that the loop
will re-execute unconditionally, bypassing the
UNTIL condition that would otherwise terminate the loop.
This may result in unexpected behavior. In a
ITERATE will result in the
WHILE condition being re-evaluated before
the next iteration of the loop.
We can construct just about any conceivable form of loop using
ITERATE statements. However, in practice
these “manual” loops are awkward when compared to some of the
alternatives we are about to consider. The
REPEAT statements described in the
following sections allow us to create loops that are easier to
write, read, and maintain.
UNTIL statements can be used to create a
loop that continues until some logical condition is met. The syntax
expressionEND REPEAT [
REPEAT loop continues
until the expression defined in the
UNTIL clause evaluates to TRUE. In
REPEAT loop is
logically equivalent to a
LOOP block like this one:
statementsIF expression THEN LEAVE some_label; END IF; END LOOP;
REPEAT loop is somewhat
easier to maintain because it is more obvious which conditions will
cause the loop to terminate. The
LEAVE statement in a simple loop could be
anywhere, while the
statement is always associated with the
REPEAT clause at the very end of the loop. Furthermore, we
don’t need to specify a label for the
REPEAT loop since the
UNTIL condition is always specific to the
current loop. However, we still recommend using labels with
REPEAT loops to improve readability,
especially if the loops are nested.
Example 4-22 shows
REPEAT to print out odd
numbers less than 10. Compare this syntax with that of our previous
example using the
SET i=0; loop1: REPEAT SET i=i+1; IF MOD(i,2)<>0 THEN /*Even number - try again*/ Select concat(i," is an odd number"); END IF; UNTIL i >= 10 END REPEAT;
There are a few things worth noting about the
REPEAT loop is always
guaranteed to run at least once—that is, the
UNTIL condition is first evaluated
after the first execution of the loop. For loops that should not
run even once unless some condition is satisfied, use
WHILE (see the next section).
ITERATE in a
REPEAT loop can lead to
unexpected outcomes, since doing so bypasses the
UNTIL test and may result in the loop
executing even though the
UNTIL condition is no longer
satisfied. Therefore, you will probably not want to use
ITERATE in a
WHILE loop executes as
long as a condition is true. If the condition is not true to begin
with, then the loop will never execute—unlike the
REPEAT loop, which is guaranteed to
execute at least once.
WHILE loop has the
statementsEND WHILE [
WHILE loop is
functionally equivalent to a simple
LOOP-LEAVE-END LOOP construction that has
LEAVE clause as its very first
statement, as described in the “LEAVE Statement” section. Example 4-23 demonstrates the
myloop: LOOP IF expression THEN LEAVE myloop; END IF;
other statements;END LOOP myloop;
Example 4-24 shows
our odd-numbers-less-than-10 loop implemented using
We often want to nest loops. In the simple code in Example 4-25, we print out the
elementary “times table” using a nested
DECLARE i,j INT DEFAULT 1; outer_loop: LOOP SET j=1; inner_loop: LOOP SELECT concat(i," times ", j," is ",i*j); SET j=j+1; IF j>12 THEN LEAVE inner_loop; END IF; END LOOP inner_loop; SET i=i+1; IF i>12 THEN LEAVE outer_loop; END IF; END LOOP outer_loop;
When nesting loops, it is particularly useful to label the
start and the end of the loop so as to clearly associate the start
of each loop with its end. Of course, if we need to use
LEAVE, we must label the loop.
We’ve now seen three simple and identical looping algorithms implemented using the three looping constructs available within the MySQL stored program language. Each of the three loop constructs is capable of implementing virtually any loop logic that you might need to implement.
The example loops given in this chapter are fairly simplistic
and have little real-world relevance. We did this partially for the
sake of clarity, but also because the reality is that in stored
programming, almost all your looping constructs will involve
iterating through the rows returned by a
SELECT statement, which is the subject of
the next chapter.