Assigning values to cursor variables

Result sets can be assigned to cursor variables at different times and in multiple ways using the SET statement.

About this task

Assigning a query result set to a cursor variable

A result set of a select query can be assigned to a cursor variable by using the SET statement and the CURSOR FOR keywords. The following is an example of how the result set associated with a query on a table named T is assigned to a cursor variable named c1 that has an identical row definition as the table.

If T is defined as:

CREATE TABLE T  (C1 INT, C2 INT, C3 INT);

If C1 is a strongly-typed cursor variable that was defined as:

CREATE TYPE simpleRow AS ROW (c1 INT, c2 INT, c3 INT);         
CREATE TYPE simpleCur AS CURSOR RETURNS simpleRow;
DECLARE c1 simpleCur;

The assignment can be done as follows:

SET c1 = CURSOR FOR SELECT * FROM T;

The strong type checking will be successful since c1 has a compatible definition to table T. If c1 was a weakly-typed cursor this assignment would also be successful, because no data type checking would be performed.

Assigning literal values to a cursor variable

Let T be a table defined as:

CREATE TABLE T  (C1 INT, C2 INT, C3 INT);

Let simpleRow be a row type and simpleCur be a cursor type that are respectively created as:

CREATE TYPE simpleRow AS ROW (c1 INT, c2 INT, c3 INT);         
CREATE TYPE simpleCur AS CURSOR RETURNS simpleRow;

Let c1 be a strongly-typed cursor variable that is declared within a procedure as:

DECLARE c1 simpleCur;

The assignment of literal values to cursor c1 can be done as follows:

SET c1 = CURSOR FOR VALUES (1, 2, 3);

The strong type checking will be successful since the literal values are compatible with the cursor definition. The following is an example of an assignment of literal values that will fail, because the literal data types are incompatible with the cursor type definition:

SET c1 = CURSOR FOR VALUES ('a', 'b', 'c');

Assigning cursor variable values to cursor variable values

A cursor variable value can be assigned to another cursor variable only if the cursor variables have identical result set definitions. For example:

If c1 and c2 are strongly-typed cursor variable that was defined as:

CREATE TYPE simpleRow AS ROW (c1 INT, c2 INT, c3 INT);

CREATE TYPE simpleCur AS CURSOR RETURNS simpleRow

DECLARE c1 simpleCur;

DECLARE c2 simpleCur;

If c2 has been assigned values as follows:


SET c2 = CURSOR FOR VALUES (1, 2, 3);

The assignment of the result set of c2 to cursor variable c1 can be done as follows:


 SET c1 = c2;

Once cursor variables have been assigned values, the cursor variables and cursor variables field values can be assigned and referenced.