UDT Data Type Examples | Data Types and Literals | Teradata Vantage - UDT Data Type Examples - Advanced SQL Engine - Teradata Database

Example: UDT Data Type

Consider the following statement that creates a distinct UDT named euro:

CREATE TYPE euro
AS DECIMAL(8,2)
FINAL;

The following statement creates a table that defines a euro column named sales:

CREATE TABLE european_sales
  (region INTEGER
  ,sales euro);

Example: Distinct UDT Parameter in a Java UDF

CREATE TYPE MONEY AS numeric(10,2) FINAL

REPLACE FUNCTION MyMoney (A1 MONEY)
RETURNS MONEY
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mymoney';

public static java.math.BigDecimal mymoney(java.math.BigDecimal a)
                                              throws SQLException

Alternatively, you can define the function as follows:

REPLACE FUNCTION MyMoney (A1 MONEY)
RETURNS MONEY
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mymoney(java.math.BigDecimal) returns java.math.BigDecimal';
	
public static java.math.BigDecimal mymoney(java.math.BigDecimal a) 
                                              throws SQLException

Example: Structured UDT Parameter in a Java UDF

CREATE TYPE CIRCLE AS (x double, y double, r double)…

REPLACE FUNCTION MyCircle(A1 CIRCLE)
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mycircle';

public static int mycircle(java.sql.Struct s) throws SQLException

Alternatively, you can define the function as follows:

REPLACE FUNCTION MyCircle(A1 CIRCLE)
RETURNS INTEGER
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mycircle(java.sql.Struct) returns int';
	
public static int mycircle(java.sql.Struct s) throws SQLException

Example: Distinct UDT Parameter in a Java External Stored Procedure

CREATE TYPE MONEY AS numeric(10,2) FINAL

REPLACE PROCEDURE MyMoney(IN A1 MONEY, OUT A2 MONEY)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mymoney';

public static void MyMoney(java.lang.BigDecimal A1, java.lang.BigDecimal[] A2) throws SQLException

Example: Structured UDT Parameter in a Java External Stored Procedure

CREATE TYPE CIRCLE AS (x double, y double, r double)

REPLACE PROCEDURE MyCircle(IN A1 CIRCLE, OUT A2 INTEGER)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA 
EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mycircle';

public static void(java.sql.Struct A1, int[] A2) throws SQLException