15.00 - SQL Procedure Support For UDTs - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

SQL Procedure Support For UDTs

For correct operation of a UDT within a procedure, the UDT must have its mandatory ordering and transform functionality defined. Additionally, the tosql and fromsql transform routines must be duplicated by an equivalent set of predefined data type‑to‑UDT and UDT‑to‑predefined data type implicit cast definitions. You can do this by referencing the same routines in both the CREATE TRANSFORM and CREATE CAST statements.

For distinct UDTs, if you plan to use the system‑generated functionality, no additional work is required because the transform and implicit casting functionality have already been defined. For structured UDTs, you must explicitly define the transform and cast functionality, as shown by the following examples.

/* Transform Functionality */
    CREATE TRANSFORM FOR address ClientIO (
      TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress,
      FROM SQL WITH SPECIFIC METHOD toString);
 
/* Implicit Cast To Back Up The ToSql Functionality */
    CREATE CAST ( Varchar(100) AS address )
      WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress 
      AS ASSIGNMENT;
 
/* Implicit Cast To Back Up The FromSql Functionality */
    CREATE CAST (address AS Varchar(100))
      WITH SPECIFIC METHOD  ToString 
      AS ASSIGNMENT; 

You can declare an input parameter to have the VARIANT_TYPE UDT data type only within the body of the procedure definition, which means that the system passes the dynamic UDT data type to this parameter position during runtime using the NEW VARIANT_TYPE expression (see “NEW_VARIANT TYPE” in SQL Functions, Operators, Expressions, and Predicates). You cannot declare an IN parameter data type for the procedure itself to have the VARIANT_TYPE data type.

You cannot declare the data type for any parameter to be TD_ANYTYPE.

Details about SQL procedure support for the following DML statements are documented in SQL Stored Procedures and Embedded SQL.

  • SELECT INTO a UDT local variable.
  • You can SELECT INTO a UDT local variable. No expressions are allowed in the INTO list.

  • FETCH INTO a UDT local variable.
  • You can FETCH INTO a UDT local variable. No expressions are allowed in the INTO list.

    You cannot perform a FETCH INTO operation in which you attempt to fetch into a predefined type nor can you perform a fetch into operation that involves UDT types that are not 100 percent identical. In other words, no implicit casts are applied for these operations.

  • INSERT INTO a UDT local variable.
  • You can INSERT INTO a UDT local variable in a table, but you cannot invoke methods on such a local variable if those methods are invoked within the INTO clause.