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

Purpose

Terminates the execution of an iterated SQL statement and begins the next iteration of the iterative statement in the loop.

Invocation

Executable

Stored procedures only.

Syntax

ITERATE label_name ;

label_name

The name of the label on which iteration is to be executed.

ANSI Compliance

ITERATE is ANSI/ISO SQL:2011-compliant.

Authorization

None.

Actions

ITERATE executes the following actions depending on the referenced iteration statement.

Rules

• ITERATE is not an independent statement. You can only use it with a FOR, LOOP, REPEAT, or WHILE iteration statement.
• A statement label must follow the ITERATE keyword immediately.
• ITERATE statement cannot reference the label associated with the BEGIN … END compound statement within which the ITERATE is embedded.
• The statement label must be associated with the iteration statement within which the ITERATE is embedded.
• If you specify an ITERATE inside nested FOR loops and it refers to a label associated with an outer iteration statement, then all cursors opened within the outer iteration statement are closed before performing the next iteration.

Example: Using ITERATE to Iterate a WHILE Statement

The following example illustrates a valid use of ITERATE to iterate a WHILE statement.

SELECT minNum INTO hminNum FROM limits
   WHERE LIMIT_TYPE = ’HIGHNUM’;
L1:
WHILE hCounter > 0 
DO
   INSERT INTO transaction (trans_num, account_num)
     VALUES (hCounter, hAccountNum);
   SET hCounter = hCounter - 1;
   IF hCounter >= hminNum THEN
      ITERATE L1;
   END IF;
   -- The following two statements perform only when
   -- hCounter < hminNum
   UPDATE limit SET minNum = hCounter;
   SET hminNum = hCounter;
END WHILE L1;

Example: Using ITERATE to Iterate An Outer Loop

The following example illustrates the use of an ITERATE statement to iterate an outer loop.

LOOP1:
WHILE hCounter > 0 
DO
   SELECT highNum INTO maxNum FROM limits
WHERE LIMIT_TYPE = 'HIGHNUM';
L1:
LOOP
      INSERT INTO transaction (trans_num,
      account_num) VALUES (hCounter, hAccountNum);
    SET hCounter = hCounter - 1;
    IF (hCounter = 10) THEN
      IF (hOnceIterated = 0) THEN
      SET hOnceIterated = 1);
        ITERATE LOOP L1;
      END IF;
    END IF;
    -- The following statement performs only if
    -- hCounter <> 10 or hOnceIterated <> 0
    SET hNum = hNum + 10;
   END LOOP L1;
   IF hCounter >= MaxNum THEN
      ITERATE LOOP1;
   END IF;
   -- The following statement performs only if
   -- hCounter < MaxNum.
      INSERT INTO transaction (trans_num,
      account_num) VALUES (hCounter, hAccountNum);
END WHILE LOOP1;
UPDATE transaction
  SET account_num = hAccountNum + 10;

Example: Using ITERATE to Iterate Outside a FOR Loop

The following example demonstrates the use of ITERATE to iterate outside a FOR loop. When there are no more rows to fetch, the cursor closes and control iterates out of the FOR loop.

L1:
LOOP
   INSERT INTO transaction (trans_num, account_num)
     VALUES (hCounter, hAccountNum);
   SET hCounter = hCounter - 1;
   FOR RowPointer AS c_customer CURSOR FOR
      SELECT CustomerNumber AS Number
            ,CustomerName AS Name
            ,(Amount + 10000) a
        FROM customer
   DO
      SET hCustNum = RowPointer.Number;
      IF hCustNum >= 100 THEN
         ITERATE L1;
      END IF;
      -- The following statements perform only if
      -- hCustNum < 100; else the cursor closes before
      -- iterating outside the FOR loop block.
      SET hCustName = RowPointer.Name;
      SET hAmount = RowPointer.a +
                    RowPointer.a * 0.20;
      INSERT INTO Cust_temp VALUES (hCustNum,
            :hCustName);
       END FOR;
       SET hNum = hNum + 10;
    END LOOP L1;