Example: Creating an SQL Procedure Using Condition and Iteration Statements - Teradata Vantage - Analytics Database

This procedure sets the background color of a rectangle to blue if its area is equal to that of the input rectangle and its background color is not blue. The rectangle and old background color are logged into a table.

    CREATE PROCEDURE SP1(IN p1 rectangle)
      BEGIN
        DECLARE var1 rectangle;             /* UDT local variable */
        DECLARE var2 VARCHAR(20);
        DECLARE var3 INTEGER;
        DECLARE var4 FLOAT;
        DECLARE rect_cursor CURSOR WITHOUT RETURN FOR
          SELECT rect_col, background_color, id 
          FROM table1 FOR UPDATE;
        SET var4 = p1.area();                    /* UDT expression */
    /* DDL */
     CREATE TABLE LogTab(
       id         INTEGER, 
       Rect       rectangle, 
       OldBGColor VARCHAR(20));
     L1: LOOP
      FETCH RectCursor INTO var1, var2, var3;   /* Fetch into UDT var */
       IF (SQLCODE <> 0) THEN
        LEAVE L1;
       END IF;
       IF (var1.area() = var4  AND  var2 <> 'Blue') THEN 
        BEGIN TRANSACTION;
          INSERT INTO LogTab 
           VALUES (:var3, :var1, :var2); 
          UPDATE Employee 
           SET BackgroundColor = 'Blue'
           WHERE CURRENT OF RectCursor;
        END TRANSACTION;
      END IF;
     END LOOP L1;
    END;