LOOP | Teradata Vantage - LOOP - 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
previous
Product Category
Teradata Vantage™

Purpose

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

Invocation

Executable.

Stored procedures only.

Syntax

[ label_name : ] LOOP
  statement [...]
  END LOOP [ label_name ] ;
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.

ANSI Compliance

LOOP is ANSI/ISO SQL:2011-compliant.

Authorization

None.

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.

Rules

  • 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.

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;