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 to a particular LOOP statement. 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.

Example 4-19. Infinite loop (don’t try this at home!)

Infinite_loop: LOOP
    SELECT 'Welcome to my infinite 
 loop from hell!!';
END LOOP inifinite_loop;

Obviously we almost never want to program an infinite loop, and therefore we need some way to terminate the loop. We can do this with the LEAVE statement, so let’s move on to this statement without delay....

LEAVE Statement

The LEAVE statement allows us to terminate a loop. The general syntax for the LEAVE statement is:

    LEAVE label;

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.

Example 4-20. Using LEAVE to terminate a loop

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

The 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:

    ITERATE label;

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. ITERATE is used to repeat the loop if the number we have is not odd. LEAVE is used to terminate the loop once we reach 10.

Example 4-21. Using ITERATE to return to the start of a loop

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 LEAVE and 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 using a 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 WHILE loop, 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 the LOOP, LEAVE, and ITERATE statements. However, in practice these “manual” loops are awkward when compared to some of the alternatives we are about to consider. The WHILE and REPEAT statements described in the following sections allow us to create loops that are easier to write, read, and maintain.

REPEAT ... UNTIL Loop

The REPEAT and UNTIL statements can be used to create a loop that continues until some logical condition is met. The syntax for REPEAT...UNTIL is:

    [label:] REPEAT
        statements
    UNTIL expression
    END REPEAT [label]

A REPEAT loop continues until the expression defined in the UNTIL clause evaluates to TRUE. In essence, a REPEAT loop is logically equivalent to a LOOP-LEAVE-END LOOP block like this one:

    some_label:LOOP
        statements
        IF expression THEN LEAVE some_label; END IF;
    END LOOP;

The 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 UNTIL statement is always associated with the END 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 using REPEAT to print out odd numbers less than 10. Compare this syntax with that of our previous example using the LOOP and LEAVE statements.

Example 4-22. Example of a REPEAT loop

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:

• A 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).

• Using 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 REPEAT loop.

WHILE Loop

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.

The WHILE loop has the following syntax:

    [label:] WHILE expression DO
        statements
    END WHILE [label]

A WHILE loop is functionally equivalent to a simple LOOP-LEAVE-END LOOP construction that has a LEAVE clause as its very first statement, as described in the “LEAVE Statement” section. Example 4-23 demonstrates the LOOP-LEAVE-END-LOOP.

Example 4-23. LOOP-END LOOP that implements same functionality as WHILE loop

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 WHILE.

Example 4-24. Odd numbers less than 10 implemented as a WHILE loop

SET i=1;
loop1: WHILE i<=10 DO
    IF MOD(i,2)<>0 THEN /*Even number - try again*/
       SELECT CONCAT(i," is an odd number");
    END IF;
    SET i=i+1;
END WHILE loop1;

Nested Loops

We often want to nest loops. In the simple code in Example 4-25, we print out the elementary “times table” using a nested LOOP-LEAVE-END LOOP structure.

Example 4-25. Example of nesting loops

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.

Parting Comments on Loops

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.