REPEAT | Teradata Vantage - REPEAT - Advanced SQL Engine

REPEAT | Teradata Vantage - REPEAT - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product

Advanced SQL Engine

Teradata Database

Release Number

17.05

17.00

Published

June 2020

Language

English (United States)

Last Update

2021-01-23

dita:mapPath

xqq1557098602407.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1148

lifecycle

Product Category

Teradata Vantage™

Purpose

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

Invocation

Executable.

Stored procedures only.

Syntax

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

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.

ANSI Compliance

REPEAT is ANSI/ISO SQL:2011-compliant.

Authorization

None.

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.

Rules

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.

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;