Example: Creating SQL Procedures Using UDTs - Advanced SQL Engine - Teradata Database

The following examples and example excerpts show various applications of UDTs within SQL procedures:

Cursor Fetching:

    OPEN cursor1;
    LSUB1:
    LOOP
      FETCH cursor1 INTO predefinedcol, distincttype, structuredtype;
      IF(SQLSTATE <> 0) THEN  LEAVE LSUB1;
      END IF;
      SET rowcount = rowcount +1;
      INSERT INTO resultstable VALUES(:rowcount, :predefinedtype,      :distincttype,
     :structuredtype.attribute1(), 'FETCH');
    END LOOP;
    CLOSE Cursor1;

Declaring a Structured Type:

    CREATE PROCEDURE sp1()
     BEGIN
      DECLARE var1 INTEGER;
      DECARE var2  VARCHAR(300);
      DECLARE structuredtype  STATEUDT;
      SET var1 = 92069;
      SET var2 = 'California";
     INSERT INTO T1 VALUES (var1, structuredtype.state(var2));

Using a Mutator Method in a SET Expression:

    UPDATE resultstable SET structuredcol=:structuredtype.attribute1();

Create Procedure:

    CREATE PROCEDURE ups02(IN pi udtint)
     BEGIN
      FOR fcode AS b_code CURSOR WITHOUT RETURN FOR
       SELECT udt2 FROM tab2 WHERE col1 = :pi.method1()
      DO
       UPDATE tab2 
       SET col2 = :fcode.udt2.method1() 
       WHERE col1  = :pi.method1()
      ELSE
       INSERT INTO tab2(:pi, :fcode.udt2.method1());
      END FOR;
    END;

Create Procedure:

    CREATE PROCEDURE cas01 (OUT p1 insv_structured_date)
     BEGIN
      DECLARE vudt insv_structured_date AS NEW insv_structured_date();
       SET p1= NEW insv_structured_date(CAST('02/02/02' AS DATE));
    END;

See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL , B035-1148 for details about how UDTs interact with procedure control language features.