​
Synopsis

[label :] REPEAT
    statements
UNTIL condition
END REPEAT

​
Description

The REPEAT UNTIL statement is an optional construct in SQL routines to allow processing of a block of statements as long as a condition is met. The condition is validated as a last step of each iteration.

The block of statements is processed at least once. After the first, and every subsequent processing the expression condidtion is validated. If the result is true, processing moves to END REPEAT and continues with the next statement in the function. If the result is false, the statements are processed again.

The optional label before the REPEAT keyword can be used to name the block.

Note that a WHILE statement is very similar, with the difference that for REPEAT the statements are processed at least once, and for WHILE blocks the statements might not be processed at all.

​
Examples

The following routine shows a routine with a REPEAT statement that runs until the value of a is greater or equal to 10.

FUNCTION test_repeat(a bigint)
  RETURNS bigint
  BEGIN
    REPEAT
      SET a = a + 1;
    UNTIL a >= 10
    END REPEAT;
    RETURN a;
  END

Since a is also the input value and it is increased before the check the routine always returns 10 for input values of 9 or less, and the input value

  • 1 for all higher values.

Following are a couple of example invocations with result and explanation:

SELECT test_repeat(5); -- 10
SELECT test_repeat(9); -- 10
SELECT test_repeat(10); -- 11
SELECT test_repeat(11); -- 12
SELECT test_repeat(12); -- 13

Further examples of varying complexity that cover usage of the REPEAT statement in combination with other statements are available in the SQL routines examples documentation.

​
See also

LOOPRETURN