The CREATE TYPE (array) statement defines
an array type. The elements of an array type are based on one of the
built-in data types or a user-defined distinct type.
Invocation
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).
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- IMPLICIT_SCHEMA authority on the database, if the schema name
of the array type does not refer to an existing schema
- CREATEIN privilege on the schema, if the schema
name of the array type refers to an existing schema
- DBADM authority
Syntax
>>-CREATE--+------------+--TYPE--type-name--AS--| data-type |--->
'-OR REPLACE-'
.-2147483647-------.
>--ARRAY--[--+------------------+--]---------------------------><
+-integer-constant-+
'-| data-type2 |---'
data-type
|--+-| built-in-type |------+-----------------------------------|
+-| anchored-data-type |-+
+-row-type-name----------+
'-array-type-name--------'
data-type2
|--+-+-INTEGER-+------------------------------------------------+--|
| '-INT-----' |
+-+-VARCHAR----------------+--(--integer--+-------------+--)-+
| '-+-CHARACTER-+--VARYING-' +-OCTETS------+ |
| '-CHAR------' '-CODEUNITS32-' |
'-| anchored-non-row-data-type |-----------------------------'
anchored-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--|
+-table-name.column-name----------------+
| .-OF-. |
'-ROW--+----+--+-table-name-----------+-'
+-view-name------------+
'-cursor-variable-name-'
built-in-type
|--+-+-SMALLINT----+----------------------------------------------------------------------+--|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
+-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+
| | '-DEC-----' | | .-,0-------. | |
| '-+-NUMERIC-+-' '-(integer-+----------+-)-' |
| '-NUM-----' '-,integer-' |
| .-(53)------. |
+-+-FLOAT--+-----------+--+------------------------------------------------------------+
| | '-(integer)-' | |
| +-REAL------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+-' |
| .-(1)------------------------. |
+-+-+-+-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)-' |
'-BOOLEAN------------------------------------------------------------------------------'
anchored-non-row-data-type
|--ANCHOR--DATA TYPE--TO--+-variable-name----------+------------|
'-table-name.column-name-'
Notes:
- The
FOR BIT DATA clause can be specified in any order with the other column
constraints that follow. The FOR BIT DATA clause cannot be specified
with string units CODEUNITS32 (SQLSTATE 42613).
Description
- OR REPLACE
- Specifies to replace the definition for the data type if one exists
at the current server. The existing definition is effectively dropped
before the new definition is replaced in the catalog, with the exception
that functions and methods are invalidated instead of dropped when
they have parameters or a return value defined with the data type
being replaced. The existing definition must not be a structured type
(SQLSTATE 42809). This option is ignored if a definition for the data
type does not exist at the current server.
- type-name
- Names the type. The name, including the implicit or explicit qualifier,
must not identify any other type (built-in or user-defined) that
already exists at the current server. The unqualified name must
not be the same as the name of a built-in data type or BOOLEAN, BINARY
or VARBINARY (SQLSTATE 42918).
A number of names used as keywords
in predicates are reserved for system use, and cannot be used as a 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 type-name is specified,
the schema name must not begin with the characters 'SYS' (SQLSTATE
42939).
- data-type
- Specifies the data type of the array elements.
- built-in-type
- Specifies a built-in data type. See "CREATE TABLE" for the description
of built-in data types. Built-in types include the data types described
in "CREATE TABLE", other than reference, SYSPROC.DB2SECURITYLABEL, XML,
or user-defined types (SQLSTATE 429C2).
- row-type-name
- Specifies the name of a user-defined row
type. If a row-type-name is specified without
a schema name, the row-type-name is resolved
by searching the schemas in the SQL path. Row
types can be nested as elements in other array types with a maximum
nesting level of sixteen.
- array-type-name
- Specifies an array type. If an array-type-name is
specified without a schema name, the array-type-name is
resolved by searching the schemas in the SQL path. Array types can
be nested as elements in other array types with a maximum nesting
level of sixteen.
- anchored-data-type
- Identifies another object used to determine the data type. The
data type of the anchor object is bound by the same limitations that
apply when specifying the data type directly, or in the case of a
row, to creating a row type.
- ANCHOR DATA TYPE TO
- Indicates that an anchored data type is used to specify the data
type.
- variable-name
- Identifies a global variable. The data type of the global variable
is used as the data type for the array elements.
- table-name.column-name
- Identifies a column name of an existing table or view. The data
type of the column is used as the data type for the array elements.
- ROW OF table-name or view-name
- Specifies a row of fields with names and data types that are based
on the column names and column data types of the table identified
by table-name or the view identified by view-name.The data type of the array elements is an unnamed
row type.
- ROW OF cursor-variable-name
- Specifies a row of fields with names
and data types that are based on the field
names and field data types of the cursor variable identified by cursor-variable-name.
The specified cursor variable must be one of
the following elements (SQLSTATE 428HS):
- A global variable with a strongly typed cursor data type
- A global variable with a weakly typed cursor data type that was
created or declared with a CONSTANT clause specifying a select-statement where
all the result columns are named.
If the cursor type of the cursor variable is not strongly-typed
using a named row type, the data type of the array elements is an unnamed row type.
- anchored-non-row-data-type
- Identifies another object used to determine the data type. The
data type of the anchor object is bound by the same limitations that
apply when specifying the data type directly.
- ANCHOR DATA TYPE TO
- Indicates that an anchored data type is used to specify the data
type.
- variable-name
- Identifies a global variable with a data type that is an INTEGER or VARCHAR data type. The data type
of the global variable is used as the data type for the array index.
- table-name.column-name
- Identifies a column name of an existing table or view with a data
type that is an INTEGER or VARCHAR data
type. The data type of the column is used as the data type for the
array index.
- ARRAY [integer-constant]
- Specifies
that the type is an array with a maximum cardinality of integer-constant. The value must be a positive integer (not zero) and less than
the largest positive integer value (SQLSTATE 42820). The default
is the largest positive integer value (2 147 483 647).
The cardinality of an array value is determined by the highest element
position assigned to the array value.
The maximum cardinality of an array on a given system is
limited by the total amount of memory available to database applications.
As such, although arrays of large cardinalities can be created, not
all elements might be available for use.
- ARRAY[data-type2]
- Specifies that the type is an associative array that is indexed
with values of data type data-type2. The
data type must be either the INTEGER or VARCHAR data type (SQLSTATE
429C2). The values specified as the index when assigning an array
element must be assignable to a value of data-type2.
The cardinality of an array value is determined by the number of
unique index values used when assigning array elements.
Rules
- Use of anchored data types: An anchored
data type cannot refer to the following objects (SQLSTATE 428HS):
a nickname, typed table, typed view, statistical view that is associated
with an expression-based index, declared temporary table, row definition
that is associated with a weakly typed cursor, object with a code
page or collation that is different from the database code page or
database collation.
Notes
- Array type usage: An array type
can only be used as the data type of:
- A local variable in a compound SQL (compiled) statement
- A parameter of an SQL routine
- A parameter of a Java™ procedure (non-nested ordinary arrays only)
- The returns type of an SQL function
- A global variable
- A variable or parameter defined with
an array type can only be used in compound SQL (compiled) statements
Examples
Example 1: Create an array
type named PHONENUMBERS with a maximum of 50 elements that are of
the DECIMAL(10, 0) data type.
CREATE TYPE PHONENUMBERS AS DECIMAL(10,0)
ARRAY[50]
Example 2: Create
an array type named NUMBERS with
the default number of elements
in the schema GENERIC.
CREATE TYPE GENERIC.NUMBERS AS DECFLOAT(34)
ARRAY[]
Example
3: Create an
associative array named
PERSONAL_PHONENUMBERS with elements that are
DECIMAL(16, 0) that is indexed by strings like
'Home',
'Work', or 'Mom'.
CREATE TYPE PERSONALPHONENUMBERS AS DECIMAL(16, 0) ARRAY[VARCHAR(8)]
Example
4: Create an associative array type where the indexes are province
, territory, or country names and the elements
are capital cities:
CREATE TYPE CAPITALSARRAY AS VARCHAR(30) ARRAY[VARCHAR(20)]
Example 5: Create an associative array type
for product descriptions of up to 40 characters long, where the indexes
are the product numbers, which are a maximum of 12 characters long:
CREATE TYPE PRODUCTS AS VARCHAR(40) ARRAY[VARCHAR(12)]
DB2 10.5 for Linux, UNIX, and Windows