The CREATE TYPE (cursor) statement defines a user-defined
cursor type.
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:
- IMPLICIT_SCHEMA authority on the database, if the schema name
of the cursor type does not refer to an existing schema
- CREATEIN privilege on the schema, if the schema name of the cursor
type refers to an existing schema
- DBADM authority
Syntax
>>-CREATE--+------------+--TYPE--type-name--AS--+----------------------------+--CURSOR-><
'-OR REPLACE-' +-| anchored-row-data-type |-+
'-row-type-name--------------'
anchored-row-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--|
| .-OF-. |
'-ROW--+----+--+-table-name-----------+-'
+-view-name------------+
'-cursor-variable-name-'
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).
- anchored-row-data-type
- Identifies row information from another object used to determine
the row type associated with the cursor type. The data type of the
anchor object has the same limitations that apply to creating a row
type.
- 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 and is used as the row type associated
with the cursor 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. The row type associated with the cursor
type 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 (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 row type associated with the cursor type is an unnamed row type.
- row-type-name
- Specifies the row type that will be used to check the row type
of the result table of the select-statement assigned
to a variable of the cursor type. The assignment fails if the type
check fails (SQLSTATE 42821). If row-type-name is
specified without a schema name, the row type is resolved by searching
the schemas in the SQL path.
Rules
- Use of anchored data
types: An anchored data type cannot refer to (SQLSTATE 428HS):
a nickname, typed table, typed view, declared temporary table, row
definition 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
- Cursor type usage: A cursor 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
- A global variable
- A variable or parameter defined with
a cursor type can only be used in compound SQL (compiled) statements
- A variable or parameter that has a strongly-typed cursor type
must not be used to assign cursor values that are based on a statement-name instead
of a select-statement
- A user-defined cursor type with an associated
row type is a strongly-typed cursor type; otherwise, it is a weakly-typed
cursor type.
Examples
Example 1: Create
a cursor type that can be used with any cursor.
CREATE TYPE EMPCURSOR AS CURSOR
Example
2: Create a strongly-typed cursor type that is based on the row
data type
DEPTROW:
CREATE TYPE DEPTCURSOR AS DEPTROW CURSOR