Creating cursor data types using the CREATE TYPE statement

Creating a cursor data type is a prerequisite to creating a cursor variable. Cursor data types are created using the CREATE TYPE (cursor) statement.

Before you begin

To perform this task you require:
  • Privileges to execute the CREATE TYPE (cursor) statement.
  • If creating a strongly typed cursor data type, you must either prepare a row specification or base it on an existing row from a table, view, or cursor.

The CREATE TYPE (cursor) statement defines a cursor data type that can be used in SQL PL to declare parameters and local variables of the cursor data type. A strongly typed cursor data type is created if the row-type-name clause is specified in the CREATE TYPE (cursor) statement. A weakly defined cursor data type is created when the row-type-name clause is omitted.

As an alternative to creating a weakly defined cursor data type, the built-in weakly defined cursor data type CURSOR can be used when declaring cursor variables.
CREATE TYPE weakCursorType AS CURSOR@
If you want to create a strongly-typed cursor data type, a row data type definition must exist that will define the result set that can be associated with the cursor. A row data type definition can be derived from an explicitly defined row data type, a table or view, or strongly typed cursor. The following is an example of a row type definition: 
CREATE TYPE empRow AS ROW (name varchar(128), ID varchar(8))@
The following is an example of a table definition from which a row type definition can be derived: 
CREATE TABLE empTable AS ROW (name varchar(128), ID varchar(8))@

About this task

To define a strongly-typed cursor data type within a database you must successfully execute the CREATE TYPE (CURSOR) statement from any Db2® interface that supports the execution of SQL statements.

Procedure

  1. Formulate a CREATE TYPE (CURSOR) statement:
    1. Specify a name for the type.
    2. Specify a row definition by doing one of: referencing the name of a row data type, specifying that the type should be anchored to a table or view, or anchored to the result set definition associated with an existing strong cursor type.
  2. Execute the CREATE TYPE statement from a supported Db2 interface.

Results

If the CREATE TYPE statement executes successfully, the cursor data type is created in the database.

Example

The following is an example of how to create a weakly typed cursor data type that can be associated with result sets with the same format as the empRow row data type: 
CREATE TYPE cursorType AS empRow CURSOR@
The following is an example of how to create a cursor data type that can be associated with result sets with the same format as the table empTable : 
CREATE TYPE cursorType AS ANCHOR ROW OF empTable@

What to do next

Once the cursor data type is created, cursor variables can be declared based on this data type.