The CREATE TYPE (Distinct) statement defines a distinct type. The distinct type is always sourced on one of the built-in data types and can be defined to use strong type or weak type rules..
Successful execution of the statement that defines a strongly typed distinct type 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. Successful execution of the statement that defines a weakly typed distinct type does not generate any functions.
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 |----> .-WITH STRONG TYPE RULES------------------------------. >--+-----------------------------------------------------+----->< '-WITH WEAK TYPE RULES--+---------------------------+-' '-| data-type-constraints |-' 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------' '-(integer-+-------------+-)-' | | (1) | | | | | | +-OCTETS------+ | '-FOR BIT DATA-----' | | | | | '-CODEUNITS32-' | | | | | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | | | | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | | | | '-CHAR------' '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-+-CLOB------------------------+--+----------------------------------+------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' | | '-CHAR------' +-K-+ +-OCTETS------+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)------------------------. | +-+-GRAPHIC--+----------------------------+------+-------------------------------------+ | | '-(integer-+-------------+-)-' | | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | +-VARGRAPHIC--(integer-+-------------+-)-------+ | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-DBCLOB--+----------------------------------+-' | | '-(integer-+---+-+-------------+-)-' | | +-K-+ +-CODEUNITS16-+ | | +-M-+ '-CODEUNITS32-' | | '-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-' data-type-constraints |----+----------+----+------------------------------+-----------| '-NOT NULL-' '-CHECK--(--check-condition--)-'
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 |
SMALLINT | distinct-type-name | INTEGER | distinct-type-name |
SMALLINT | SMALLINT | distinct-type-name | SMALLINT |
INTEGER | distinct-type-name | INTEGER | distinct-type-name |
INTEGER | INTEGER | distinct-type-name | INTEGER |
BIGINT | distinct-type-name | BIGINT | distinct-type-name |
BIGINT | BIGINT | distinct-type-name | BIGINT |
DECIMAL | distinct-type-name | DECIMAL (p,s) | distinct-type-name |
DECIMAL | DECIMAL | distinct-type-name | DECIMAL (p,s) |
NUMERIC | distinct-type-name | DECIMAL (p,s) | distinct-type-name |
NUMERIC | DECIMAL | distinct-type-name | DECIMAL (p,s) |
REAL | distinct-type-name | REAL | distinct-type-name |
REAL | distinct-type-name | DOUBLE | distinct-type-name |
REAL | REAL | distinct-type-name | REAL |
FLOAT(n) where n<=24 | distinct-type-name | REAL | distinct-type-name |
FLOAT(n) where n<=24 | distinct-type-name | DOUBLE | distinct-type-name |
FLOAT(n) where n<=24 | REAL | distinct-type-name | REAL |
FLOAT(n) where n>24 | distinct-type-name | DOUBLE | distinct-type-name |
FLOAT(n) where n>24 | DOUBLE | distinct-type-name | DOUBLE |
FLOAT | distinct-type-name | DOUBLE | distinct-type-name |
FLOAT | DOUBLE | distinct-type-name | DOUBLE |
DOUBLE | distinct-type-name | DOUBLE | distinct-type-name |
DOUBLE | DOUBLE | distinct-type-name | DOUBLE |
DOUBLE PRECISION | distinct-type-name | DOUBLE | distinct-type-name |
DOUBLE PRECISION | DOUBLE | distinct-type-name | DOUBLE |
DECFLOAT | distinct-type-name | DECFLOAT(n) | distinct-type-name |
DECFLOAT | DECFLOAT | distinct-type-name | DECFLOAT(n) |
CHAR | distinct-type-name | CHAR (n) | distinct-type-name |
CHAR | CHAR | distinct-type-name | CHAR (n) |
CHAR | distinct-type-name | VARCHAR (n) | distinct-type-name |
VARCHAR | distinct-type-name | VARCHAR (n) | distinct-type-name |
VARCHAR | VARCHAR | distinct-type-name | VARCHAR (n) |
CLOB | distinct-type-name | CLOB (n) | distinct-type-name |
CLOB | CLOB | distinct-type-name | CLOB (n) |
GRAPHIC | distinct-type-name | GRAPHIC (n) | distinct-type-name |
GRAPHIC | GRAPHIC | distinct-type-name | GRAPHIC (n) |
GRAPHIC | distinct-type-name | VARGRAPHIC (n) | distinct-type-name |
VARGRAPHIC | distinct-type-name | VARGRAPHIC (n) | distinct-type-name |
VARGRAPHIC | VARGRAPHIC | distinct-type-name | VARGRAPHIC (n) |
DBCLOB | distinct-type-name | DBCLOB (n) | distinct-type-name |
DBCLOB | DBCLOB | distinct-type-name | DBCLOB (n) |
BLOB | distinct-type-name | BLOB (n) | distinct-type-name |
BLOB | BLOB | distinct-type-name | BLOB (n) |
DATE | distinct-type-name | DATE | distinct-type-name |
DATE | DATE | distinct-type-name | DATE |
TIME | distinct-type-name | TIME | distinct-type-name |
TIME | TIME | distinct-type-name | TIME |
TIMESTAMP | distinct-type-name | TIMESTAMP(p | distinct-type-name |
TIMESTAMP | 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 preceding table and the comparison operator functions 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 strongly typed distinct types until the CREATE FUNCTION statement is used to register user-defined functions for the strongly typed 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 strongly typed distinct type is created, system-generated comparison operators are created when the source type supports comparisons . 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
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
This will also result in the creation of comparison operators (=, <>, <, =, >, >=) and cast functions DOUBLE(MILES) returning DOUBLE and MILES(DOUBLE) returning MILES.
CREATE TYPE BONUS AS INTEGER WITH WEAK TYPE RULES
CHECK(VALUE >= 0 AND VALUE <= 100)
Because it is defined with weak type rules, comparison and cast functions are not generated for the weakly typed distinct type called BONUS.
CREATE TYPE SALARY AS DOUBLE WITH WEAK TYPE RULES
NOT NULL CHECK(VALUE < 100000)