REPEAT | Teradata Vantage - REPEAT - Teradata Vantage - Analytics Database

Repeats the execution of one or more statements until the specified condition evaluates to true.

ANSI Compliance

REPEAT is ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Stored procedures only.

Syntax

[ label_name : ] REPEAT statement [...]
  UNTIL conditional_expression
  END REPEAT [ label_name ] ;

Syntax Elements

label_name

An optional label for the REPEAT statement.

If an ending-label is specified, you must specify a beginning-label that is equivalent to the ending-label. The beginning-label must be terminated by a colon character (:).

The label name of the BEGIN END compound statement cannot be reused in an iteration statement. One label name cannot be reused within one group of nested REPEAT statements, but can be reused for different non-nesting iteration statements.

statement

A statement list to be executed.

The list can contain any of the following:

• DML, DDL or DCL statements, including dynamic SQL.
• Control statements, including BEGIN END.

For details, see statement in FOR.

conditional_expression

A boolean condition used to evaluate whether a statement or statements embedded within the REPEAT loop should be executed.

You cannot use IN and NOT IN operators if the conditional list contains any local variables, parameters, or cursor aliases.

OUT parameters are not allowed in conditional_expression.

Usage Notes

• You can qualify the REPEAT statement with a label name. If a label name is provided for REPEAT, then:
  • A LEAVE statement inside the block can use that label name to leave the REPEAT statement.
  • If an ITERATE statement is specified within the block, and it refers to the label associated with REPEAT, the execution is continued from the beginning of the REPEAT statement without evaluating the conditional expression specified with the UNTIL clause.
• Exception Handling

  If a statement within the REPEAT statement, or the conditional expression of the UNTIL clause raises an exception and the stored procedure contains a handler to handle that exception condition, the behavior is identical to exceptions occurring within a WHILE statement.

• Difference Between REPEAT and WHILE

  REPEAT – END REPEAT is similar to the WHILE – END WHILE statement, with some differences.

  REPEAT…	WHILE …
  makes the first iteration unconditional. REPEAT always executes the sequence of statements at least once.	makes the first iteration and subsequent iterations only if a specified condition is true.
  executes statements until a specified condition is met.	executes statements as long as a specified condition is met.

Example: Using a REPEAT Statement

The following example shows the use of a REPEAT statement:

CREATE PROCEDURE ProcessTrans(IN pAcctNum INTEGER
                              IN pStartTrans INTEGER,
                              IN pEndTrans INTEGER )
BEGIN
    DECLARE vTransNum INTEGER;
    SET vTransNum = pStartTrans;
    ...;
    REPEAT
        INSERT INTO trans (trans_num, acct_nbr)
               VALUES (vTransNum, pAcctNum);
        SET vTransNum = vTransNum + 1;
      UNTIL vTransNum > pEndTrans
    END REPEAT;
    ...;
END;

Related Information

For more information about rules governing exceptions, see LEAVE.