The CREATE TYPE (row) statement defines a row type. A row
type includes one or more fields with associated data types that make
up a row of data.
Invocation
This statement can be embedded
in an application program or issued interactively. It is an executable
statement that can be dynamically prepared.
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 row type does not refer to an existing schema
- CREATEIN privilege on the schema, if the schema name of the row
type refers to an existing schema
- DBADM authority
Syntax
>>-CREATE--+------------+--TYPE--type-name--AS ROW-------------->
'-OR REPLACE-'
.-,---------------------.
V |
>--+-(----| field-definition |-+--)-+-------------------------><
'-| anchored-row-data-type |------'
field-definition
|--field-name--| data-type |------------------------------------|
data-type
|--+-| built-in-type |--------------+---------------------------|
+-| anchored-non-row-data-type |-+
+-| anchored-row-data-type |-----+
+-row-type-name------------------+
+-array-type-name----------------+
'-distinct-type-name-------------'
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)-' |
'-BOOLEAN------------------------------------------------------------------------------'
anchored-non-row-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name----------+----|
'-table-name.column-name-'
anchored-row-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--|
| .-OF-. |
'-ROW--+----+--+-table-name-----------+-'
+-view-name------------+
'-cursor-variable-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, structured, array, row,
or distinct) already described in the catalog. The unqualified name
must not be the same as the name of a built-in data type or BOOLEAN
(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 cannot begin with 'SYS'; otherwise, an error is returned
(SQLSTATE 42939).
- field-definition
- Defines the fields of the row type.
- field-name
- Specifies the name of a field within the row type. The name cannot
be the same as any other field of this row type (SQLSTATE 42711).
- data-type
- Specifies the data type of the field.
- 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 field types of a row type 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 field types of a row type with a maximum nesting level
of sixteen.
- distinct-type-name
- Specifies a user-defined distinct data type. The specified distinct type cannot have any
data type constraints (SQLSTATE 429C5).
- anchored-non-row-data-type
- Identifies another object used to determine the data type. The
data type of the anchor object is has 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 a supported
row field data type. The data type of the global variable is used
as the data type for the field.
- table-name.column-name
- Identifies a column name of an existing table or view with a data
type that is a built-in-type or a distinct type. The data type of
the column is used as the data type for the field.
- anchored-row-data-type
- Identifies row information from another object to use as the fields
of the row.
- ANCHOR DATA TYPE TO
- Indicates an anchored data type is used to specify the data type.
- variable-name
- Identifies a global variable. The data type of the referenced
variable must be a row type.
- 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 types of the anchor object columns have the same limitations
that apply to field data types.
- 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 objects (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.
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
- Row type usage: A row 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
- The returns type of an SQL function
- The element of an array type
- A user-defined cursor type
- A global variable
- A variable or parameter defined with
a row type can only be used in compound SQL (compiled) statements
Example
- Create a row type based on the columns of the DEPARTMENT table.
CREATE TYPE DEPTROW AS ROW (DEPTNO VARCHAR(3),
DEPTNAME VARCHAR(29),
MGRNO CHAR(6),
ADMRDEPT CHAR(3),
LOCATION CHAR(16))
DB2 10.5 for Linux, UNIX, and Windows