DB2 10.5 for Linux, UNIX, and Windows

CREATE TYPE (cursor) statement

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 authorities:

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 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.

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 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

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
```