CREATE TYPE (row) statement
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
- SCHEMAADM authority on the schema, if the schema name of the row type refers to an existing schema
- DBADM authority
Syntax
Notes:
- 1 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, cannot identify any other type
(built-in, structured, array, row, or distinct) already described in the catalog. The unqualified
name cannot be the same as the name of a built-in data type (SQLSTATE 42918).
Several names used as keywords in predicates are reserved for system use, and cannot be used as a type name (SQLSTATE 42939). These 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 name is specified, the schema name cannot begin with the characters SYS (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).
- 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
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))