The CREATE TYPE (Distinct) statement defines a distinct type. The distinct type is always sourced on one of the built-in data types. Successful execution of the statement also generates functions to cast between the distinct type and its source type and, optionally, generates support for the comparison operators (=, <>, <, <=, >, and >=) for use with the distinct type.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
>>-CREATE TYPE--distinct-type-name--AS--| source-data-type |----> >--+----------------------+------------------------------------>< | (1) | '-WITH COMPARISONS-----' source-data-type |--+-| built-in-type |------+-----------------------------------| '-| anchored-data-type |-' built-in-type |--+-+-SMALLINT----+-----------------------------------------------------------+--| | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)-------------------. | +-+-+-DECIMAL-+-+--+-------------------------+------------------------------+ | | '-DEC-----' | | .-,0-------. | | | '-+-NUMERIC-+-' '-(integer-+----------+-)-' | | '-NUM-----' '-,integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+-------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+--------------------------------------------------------+ | '-(16)-' | | .-(1 BYTE)------------. | +-+-+-+-CHARACTER-+--+---------------------+----------+--+--------------+-+-+ | | | '-CHAR------' | .-BYTE-. | | '-FOR BIT DATA-' | | | | | '-(integer-+------+-)-' | | | | | | .-BYTE-. | | | | | '-+-VARCHAR----------------+--(integer-+------+-)-' | | | | '-+-CHARACTER-+--VARYING-' | | | | '-CHAR------' | | | | .-(1M)-------------. | | | '-+-CLOB------------------------+--+------------------+-----------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-)-' | | '-CHAR------' +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+------------------------------------------+ | | '-(integer)-' | | | +-VARGRAPHIC--(integer)--------+ | | | .-(1M)-------------. | | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-+-+-NCHAR-------------------+--+-----------+------+-------+-------------+ | | | '-NATIONAL--+-CHAR------+-' '-(integer)-' | | | | | | '-CHARACTER-' | | | | | '-+-NVARCHAR-------------------------+--(integer)-' | | | | +-NCHAR VARYING--------------------+ | | | | '-NATIONAL--+-CHAR------+--VARYING-' | | | | '-CHARACTER-' | | | | .-(1M)-------------. | | | '-+-NCLOB---------------------------+--+------------------+-' | | +-NCHAR LARGE OBJECT--------------+ '-(integer-+---+-)-' | | '-NATIONAL CHARACTER LARGE OBJECT-' +-K-+ | | +-M-+ | | '-G-' | | .-(1M)-------------. | +-+-BLOB----------------+--+------------------+-----------------------------+ | '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | '-+-DATE-------------------------+------------------------------------------' +-TIME-------------------------+ | .-(--6--)-------. | '-TIMESTAMP--+---------------+-' '-(--integer--)-' anchored-data-type |--ANCHOR--+-----------+--+----+--+-variable-name----------+----| '-DATA TYPE-' '-TO-' '-table-name.column-name-'
In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.
A number of names used as keywords in predicates are reserved for system use, and cannot be used as a distinct-type-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators.
If a two-part distinct-type-name is specified, the schema name must not begin with the characters 'SYS' (SQLSTATE 42939).
EXECUTE privilege on all functions automatically generated during the CREATE TYPE (Distinct) statement is granted to PUBLIC.
CREATE FUNCTION source-type-name (distinct-type-name)
RETURNS source-type-name ...
CREATE FUNCTION distinct-type-name (source-type-name)
RETURNS distinct-type-name ...
CREATE TYPE T_SHOESIZE AS CHAR(2)
WITH COMPARISONS
CREATE TYPE T_MILES AS DOUBLE
WITH COMPARISONS
FUNCTION CHAR (T_SHOESIZE) RETURNS CHAR (2)
FUNCTION T_SHOESIZE (CHAR (2))
RETURNS T_SHOESIZE
FUNCTION DOUBLE (T_MILES) RETURNS DOUBLE
FUNCTION T_MILES (DOUBLE) RETURNS T_MILES
The schema of the generated cast functions is the same as the schema of the distinct type. No other function with this name and with the same signature may already exist in the database (SQLSTATE 42710).
The following table gives the names of the functions to convert from the distinct type to the source type and from the source type to the distinct type for all predefined data types.
Source Type Name | Function Name | Parameter | Return-type |
---|---|---|---|
SMALLINT | distinct-type-name | SMALLINT | distinct-type-name |
distinct-type-name | INTEGER | distinct-type-name | |
SMALLINT | distinct-type-name | SMALLINT | |
INTEGER | distinct-type-name | INTEGER | distinct-type-name |
INTEGER | distinct-type-name | INTEGER | |
BIGINT | distinct-type-name | BIGINT | distinct-type-name |
BIGINT | distinct-type-name | BIGINT | |
DECIMAL | distinct-type-name | DECIMAL (p,s) | distinct-type-name |
DECIMAL | distinct-type-name | DECIMAL (p,s) | |
NUMERIC | distinct-type-name | DECIMAL (p,s) | distinct-type-name |
DECIMAL | distinct-type-name | DECIMAL (p,s) | |
REAL | distinct-type-name | REAL | distinct-type-name |
distinct-type-name | DOUBLE | distinct-type-name | |
REAL | distinct-type-name | REAL | |
FLOAT(n) where n<=24 | distinct-type-name | REAL | distinct-type-name |
distinct-type-name | DOUBLE | distinct-type-name | |
REAL | distinct-type-name | REAL | |
FLOAT(n) where n>24 | distinct-type-name | DOUBLE | distinct-type-name |
DOUBLE | distinct-type-name | DOUBLE | |
FLOAT | distinct-type-name | DOUBLE | distinct-type-name |
DOUBLE | distinct-type-name | DOUBLE | |
DOUBLE | distinct-type-name | DOUBLE | distinct-type-name |
DOUBLE | distinct-type-name | DOUBLE | |
DOUBLE PRECISION | distinct-type-name | DOUBLE | distinct-type-name |
DOUBLE | distinct-type-name | DOUBLE | |
DECFLOAT | distinct-type-name | DECFLOAT(n) | distinct-type-name |
DECFLOAT | distinct-type-name | DECFLOAT(n) | |
CHAR | distinct-type-name | CHAR (n) | distinct-type-name |
CHAR | distinct-type-name | CHAR (n) | |
distinct-type-name | VARCHAR (n) | distinct-type-name | |
VARCHAR | distinct-type-name | VARCHAR (n) | distinct-type-name |
VARCHAR | distinct-type-name | VARCHAR (n) | |
CLOB | distinct-type-name | CLOB (n) | distinct-type-name |
CLOB | distinct-type-name | CLOB (n) | |
GRAPHIC | distinct-type-name | GRAPHIC (n) | distinct-type-name |
GRAPHIC | distinct-type-name | GRAPHIC (n) | |
distinct-type-name | VARGRAPHIC (n) | distinct-type-name | |
VARGRAPHIC | distinct-type-name | VARGRAPHIC (n) | distinct-type-name |
VARGRAPHIC | distinct-type-name | VARGRAPHIC (n) | |
DBCLOB | distinct-type-name | DBCLOB (n) | distinct-type-name |
DBCLOB | distinct-type-name | DBCLOB (n) | |
BLOB | distinct-type-name | BLOB (n) | distinct-type-name |
BLOB | distinct-type-name | BLOB (n) | |
DATE | distinct-type-name | DATE | distinct-type-name |
DATE | distinct-type-name | DATE | |
TIME | distinct-type-name | TIME | distinct-type-name |
TIME | distinct-type-name | TIME | |
TIMESTAMP | distinct-type-name | TIMESTAMP(p | distinct-type-name |
TIMESTAMP | distinct-type-name | TIMESTAMP(p) | |
Note: NUMERIC and FLOAT are not
recommended when creating a user-defined type for a portable application.
DECIMAL and DOUBLE should be used instead.
|
The functions described in the above table are the only functions that are generated automatically when distinct types are defined. Consequently, none of the built-in functions (AVG, MAX, LENGTH, and so on) are supported for distinct types until the CREATE FUNCTION statement is used to register user-defined functions for the distinct type, and those user-defined functions are sourced on the appropriate built-in functions. In particular, note that it is possible to register user-defined functions that are sourced on the built-in column functions.
When a distinct type is created using the WITH COMPARISONS clause, system-generated comparison operators are created. Creation of these comparison operators will generate entries in the SYSCAT.ROUTINES catalog view for the new functions.
The schema name of the distinct type must be included in the SQL path or the FUNCPATH BIND option for successful use of these operators and cast functions in SQL statements.
CREATE TYPE SHOESIZE AS INTEGER WITH COMPARISONS
This will also result in the creation of comparison operators (=, <>, <, <=, >, >=) and cast functions INTEGER(SHOESIZE) returning INTEGER and SHOESIZE(INTEGER) returning SHOESIZE.
CREATE TYPE MILES AS DOUBLE WITH COMPARISONS
This will also result in the creation of comparison operators (=, <>, <, =, >, >=) and cast functions DOUBLE(MILES) returning DOUBLE and MILES(DOUBLE) returning MILES.