Types of cursor data types

There are two main types of cursor data types: weakly-typed cursor data types and strongly-typed cursor data types. The property of being strongly or weakly typed is defined when the data type is created. This property is maintained in variables created of each type.

The characteristics of strongly-typed cursor data types and weakly typed cursor data types are provided here:

Strongly-typed cursor data types

A strongly-typed cursor data type is one that is created with a result set definition specified by a row data structure. These data types are called strongly typed, because when result set values are assigned to them the data types of the result sets can be checked. Cursor data type result set definitions can be defined by providing a row type definition. Only result sets that match the definition can be assigned to and stored in a strongly typed cursor data type. Strong type checking is performed at assignment time and if there are any data type mismatches, an error is raised.

Result set definitions for strongly-typed cursor data types can be provided by a row data type definition or an SQL statement definition.

The following is an example of a cursor data type definition that is defined to return a result set with the same row format as the rowType data type:

	CREATE TYPE cursorType AS rowType CURSOR@

Only result sets that contain columns of data with the same data type definition as the rowType row definition can be successfully assigned to variables declared to be of the cursorType cursor data type.

The following is an example of a cursor data type definition that is defined to return a result set with the same row format as that which defines table T1:

	CREATE TABLE T1 (C1 INT)

  CREATE TYPE cursorType AS ANCHOR ROW OF t1 CURSOR;

Only result sets that contain columns of data with the same data type definition as the column definition for the table t1 can be successfully assigned to variables declared to be of the cursorType cursor data type.

The row definition associated with a strongly typed cursor can be referenced as the definition of an anchored data type. The following example illustrates this:

CREATE TYPE r1 AS ROW (C1 INT);
CREATE TYPE c1 AS RTEST CURSOR;

DECLARE c1 CTEST;
DECLARE r1 ANCHOR ROW OF CV1;

A row data type named r1 is defined, a cursor type named c1 associated with the row definition of r1 is defined. The subsequent SQL statements are examples of variable declarations might appear in an SQL procedure. The second variable declaration is for a variable named r1 which is defined to be of the anchored data type - it is anchored to the row type that was used to define the cursor cv1.

Weakly-typed cursor data types

A weakly typed cursor type is not associated with any row data type definition. No type checking is performed when values are assigned to weakly typed cursor variables.

There is a built-in weakly typed cursor data type named CURSOR that can be used to declare weakly typed cursor variables or parameters. The following is an example of a weakly typed cursor variable declaration based on the built-in weakly typed cursor data type CURSOR:

DECLARE cv1 CURSOR;

Weakly typed cursor variables are useful when you must store a result set with an unknown row definition.

To return a weakly typed cursor variable as an output parameter, the cursor must be opened.

In this version, variables based on weakly typed cursor data types cannot be referenced as anchored data types.

User-defined weakly typed cursor data types can be defined.

All other characteristics of cursor variables are common for each type of cursor variable.