MySQL 5.0 gives the developer access to features that earlier versions of MySQL do not support. These include stored procedures and stored functions. This article, the third in a series, continues our exploration of the potential of these two features. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).
REPEAT is another useful looping construct. Itís somewhat easier to use in some circumstances thanLOOP , because it has a terminating condition built into its syntax, as shown here:
[label:] REPEAT statement-block UNTIL condition END REPEAT [label];
The block of statements appearing between the REPEAT and UNTIL keywords is executed repeatedly until the condition indicated by UNTIL is met. Hereís an example of a stored procedure (named repeat_sum) that gets the sum of an integer and all positive integers less than itself using a REPEAT ... UNTIL loop:
In this procedure, we start by declaring a counter equal to 0, then, each time we go through the REPEAT loop, we increment it, adding it to the output parameter output, and then checking to see if itís yet equal to the original value. When counter and value are equal, we exit the loop. We can test it as shown here:
CAUTION When writing REPEAT loops, make sure that the condition following UNTIL will be met at some point. Otherwise youíll have an endless loop, which means youíll quite likely have to kill the thread and start over.
The WHILE loop in some ways can be regarded as the inverse of a REPEAT loop. With a REPEAT loop, statements are executed until some condition is met. In a WHILE loop, execution of the statement block following the DO keyword continues only so long as the specified condition is true.
Hereís the formal syntax for WHILE:
[label:] WHILE condition DO statement-block END WHILE [label];
In the next example, we use a WHILE loop in a stored function to obtain the factorial of an integer:
The factorial of an integer is defined as the product of that integer and all the positive integers less than itself and greater than 1, so the factorial of 5 (often written as 5!) is 5 X 4 X 3 X 2 = 120, as you can see from our test of the factorialfunction above. 0! (zero factorial) is defined as 1 and the factorial of a negative number is undefined. With these additional conditions in mind, you could write a somewhat improved version of the stored function. This better_factorial function uses a CASE block to distinguish the cases where the input value is equal to or less than zero, and to act accordingly as shown here:
CREATE FUNCTION better_factorial(value INT ) RETURNS INT BEGIN DECLARE temp INT; DECLARE output INT DEFAULT 1; SET temp = value; CASE WHEN value < 0 THEN SET output = 0; WHEN value = 0 OR value = 1 THEN SET output = 1; ELSE WHILE temp > 1 DO SET output = output * temp; SET temp = temp - 1; END WHILE; END CASE; RETURN output; END
This example also illustrates how you can nest multiple flow-control con structs inside one another within a single stored function or stored procedure.