User-defined types

A user-defined type is a data type that is defined to the database using a CREATE TYPE statement. There are two types of user-defined type: distinct types and array types.

Distinct types

A distinct type is a user-defined data type that shares its internal representation with a built-in data type (its "source type"). Start of change A distinct type is defined to use either strong typing or weak typing rules. Strong typing rules are the default. End of change

A distinct type is created using CREATE TYPE (distinct).

The name of a distinct type is qualified with a schema name. The implicit schema name for an unqualified name depends upon the context in which the distinct type appears. If an unqualified distinct type name is used:

In a CREATE TYPE statement or the object of the DROP, COMMENT, LABEL, GRANT, or REVOKE statement, the database manager uses the normal process of qualification by authorization ID to determine the schema name. For more information about qualification rules, see Unqualified function, procedure, specific name, type, and variables.
In any other context, the database manager uses the SQL path to determine the schema name. The database manager searches the schemas in the path, in sequence, and selects the first schema that has a distinct type that matches. For a description of the SQL path, see SQL path.

A distinct type is subject to the same restrictions as its source type. For example, a table can only have one ROWID column. Therefore, a table with a ROWID column cannot also have a column with distinct type that is sourced on a ROWID.

Strongly typed distinct type

strongly typed End of change

distinct type is considered to be a separate and incompatible type for most operations. For example, the semantics for a picture type, a text type, and an audio type that all use the built-in data type BLOB for their internal representation are quite different.

For example, the following statement creates a distinct type named AUDIO:

  CREATE TYPE AUDIO AS BLOB (1M)

Although AUDIO has the same representation as the built-in data type BLOB, it is considered to be a separate type that is not comparable to a BLOB or to any other type. This inability to compare AUDIO to other data types allows functions to be created specifically for AUDIO and assures that these functions cannot be applied to other data types (such as pictures or text).

strongly typed End of change

distinct type does not automatically acquire the functions and operators of its source type, since these may not be meaningful. (For example, the LENGTH function for an AUDIO type might return the length of its object in seconds rather than in bytes.) Strong typing ensures that only the functions and operators that are explicitly defined for a distinct type can be applied to that distinct type. However, a function or operator of the source type can be applied to the distinct type by creating an appropriate user-defined function. The user-defined function must be sourced on the existing function that has the source type as a parameter. For example, the following series of SQL statements shows how to create a distinct type named MONEY based on data type DECIMAL(9,2), how to define the + operator for the distinct type, and how the operator might be applied to the distinct type:

CREATE TYPE MONEY AS DECIMAL(9,2)

CREATE FUNCTION "+"(MONEY,MONEY)
   RETURNS MONEY
   SOURCE "+"(DECIMAL(9,2),DECIMAL(9,2))

CREATE TABLE SALARY_TABLE
   (SALARY MONEY,
    COMMISSION MONEY)

SELECT "+"(SALARY, COMMISSION) FROM SALARY_TABLE

The database manager automatically generates functions for a distinct type that support casting from the source type to the distinct type and from the distinct type to the source type. For example, for the AUDIO type created above, these are the generated cast functions:

Name of generated cast function	Parameter list	Returns data type
schema-name.BLOB	schema-name.AUDIO	BLOB
schema-name.AUDIO	BLOB	schema-name.AUDIO

Weakly typed distinct type

A weakly typed distinct type is considered to be the same as its source type for all operations including function resolution. A weakly typed distinct type provides an alternative method of referring to a built-in data type. End of change

The following example illustrates the creation of a distinct type named LAST_NAME:

CREATE TYPE LAST_NAME AS VARCHAR(50)
  WITH WEAK TYPE RULES

Weak typing means that LAST_NAME operates in the same way as its source type of VARCHAR(50). End of change

Array types

An array is a structure that contains an ordered collection of data elements. All elements in an array have the same data type. The cardinality of the array is equal to the number of elements in the array.

The entire array can be referenced or an individual element of the array can be referenced by its ordinal position in the collection. If N is the cardinality of an array, the ordinal position associated with each element is an integer value greater than or equal to 1 and less than or equal to N.

An array type is a user-defined data type that is defined as an array. An SQL variable or SQL parameter can be defined as a user-defined array data type. Additionally, the result of an invocation of the TRIM_ARRAY function, or the result of a CAST specification, can be a user-defined array data type. An element of a user-defined array type can be referenced anywhere an expression returning the same data type as an element of that array can be used.

An unnamed array type is an array without an associated user-defined data type. The result of an invocation of the ARRAY_AGG aggregate function or an ARRAY constructor is an array without an associated user-defined data type. An element of an array without an associated user-defined array type cannot be directly referenced.

An array value can be empty (cardinality zero), null, or the individual elements in the array can be null or not null. An empty array is different than an array value of null, or an array for which all elements are the null value.

An array value cannot be stored in the database or be returned to an external application other than Java™.