Example: Using ITERATE to Iterate outside a FOR Loop - Teradata Vantage

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;