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

Repeats the execution of one or more statements embedded within the defined iteration statement.

ANSI Compliance

LOOP is ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Stored procedures only.

Syntax

[ label_name : ] LOOP
  statement [...]
  END LOOP [ label_name ] ;

Syntax Elements

label_name

An optional label for the LOOP 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 LOOP statements, but can be reused for different non-nesting iteration statements.

statement

A statement list to be processed unconditionally. The list can contain any of the following:

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

For details, see statement in FOR.

Usage Notes

• You can qualify LOOP with a statement label.

  A LEAVE statement specified within the LOOP breaks the iteration statement, passing control to the next statement following the statement with that label.

• You must specify a LEAVE statement inside the LOOP statement to ensure normal termination of the statement.

  If you do not, the loop iterates continuously and can only be stopped by an asynchronous abort.

• Causes of LOOP-Terminating Errors
  • If a statement in the LOOP raises an exception condition and a CONTINUE handler has been declared for that condition, then the stored procedure execution continues.
  • If an EXIT handler has been declared, then the statement terminates the stored procedure execution.
  • If a statement within the loop raises an error condition and its associated SQLSTATE code is not defined for a handler, then both the loop and the stored procedure terminate.

Example: The LOOP Statement

The following LOOP statement is valid:

L1:
LOOP
  INSERT INTO transaction (trans_num, account_num)     VALUES (hCounter, hAccountNum);
  SET hCounter = hCounter - 1;
  IF hCounter = 0 THEN
     LEAVE L1;
  END IF;
END LOOP L1;