REPEAT - MySQL

REPEAT is another useful looping construct. It’s somewhat easier to use in some circumstances than LOOP , 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.

---

WHILE

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 factorial function 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.