Delving Deeper into MySQL 5.0

Looping in Procedures

One major enhancement that stored procedures and stored functions provide to you in your SQL programming is that they give you the ability to execute code in loops. MySQL 5.0 supports three looping constructs: LOOP , REPEAT , and WHILE . They are largely equivalent, and which one you use depends on both the situation you’re in and your own style or preferences. Each looping construct allows you to perform an action or set of actions a specified number of times, while a condition is true, or until a desired condition has been met. We’ll examine these and provide you with some working examples in the next three sections of this chapter.

---

NOTE  While there is no FOR or FOREACH loop in MySQL procedures, controlling loops by means of a counter is not difficult to accomplish using any of the three looping constructs that are available, as you’ll see from some of the examples in the following sections.

---

LOOP, LEAVE, ITERATE

The most basic looping construct of the three offered in MySQL 5.0 and newer is LOOP, which, together with a set of labels, identifies a block of SQL code that is to be repeated one or more times. The labels must appear at both the beginning and end of the loop, and must match. A beginning label must be followed with a colon ( : ) and precedes the LOOP keyword; an ending label follows the END LOOP keywords but precedes the final semicolon ( ; ) marking the end of the loop. A label can be any valid MySQL identifier.

---

TIP A LEAVE statement (see the text that follows) won’t function without a label. Therefore, you should label every LOOP construct that you use in a stored procedure or stored function. Labels aren’t quite so necessary with REPEAT and WHILE (see the next two sections), but using them in longer procedures will help make your code easier to read and maintain.

---

The syntax for a loop with a label is shown here:

label: LOOP
END LOOP label;

Once begun, a LOOP will repeat indefinitely until something happens to cause execution to pass out of it. In order for it to be useful, MySQL provides a statement for doing just that: the LEAVE statement:

LEAVE label;

If you’re familiar with the break keyword in programming languages such as Java, Perl, C, or PHP, then you’ll find that LEAVE works in much the same fashion; it terminates the loop at the point where LEAVE is encountered.

There’s also a statement that causes execution to pass back to the beginning of a loop. This is the ITERATE statement, which, like LEAVE , also uses a label:

ITERATE label;

In this example, we create a stored function using LOOP , LEAVE , and ITERATE . This function, which we’ve named loop_example, is basically a reimplementation of the integer division ( DIV ) operator that became available in MySQL 4.1; it takes two integers as arguments, and returns the number of times that the first one will go into the second without regard to any fraction or remainder:

Here’s the loop_example function in use:

Let’s look more closely at how the LOOP construct in this function works, by repeating the code for it with some comments:

myloop:  # marks the beginning of the loop "myloop"
LOO P
  SET temp = temp - start;   # perform subtraction
  SET counter = counter + 1; # increment counter
  IF temp >= start # is the new value greater than the input value?
    THEN ITERATE myloop;  # if it is, go back to the "myloop" label
  END IF;
  LEAVE myloop;  # (otherwise) break out of the loop
END LOOP myloop; # marks the end of the loop "myloop"

We could also have written this loop as

myloop:
LOOP
  SET temp = temp - start;
  SET counter = counter + 1;
  IF temp < start
    THEN LEAVE myloop;
  END IF;
END LOOP myloop;

In this instance, the ITERATE wasn’t really necessary; however, in more complex loops, you may want to return to the beginning of the loop from one of several dif ferent points, depending on the circumstances; ITERATE makes this possible.