Declaring local variables of type cursor

Local variables of type cursor can be declared once a cursor data type has been created.

Before you begin

A cursor data type definition must exist in the database. Cursor data types are created by successfully executing the CREATE TYPE (CURSOR) statement. The following is an example of a strongly-typed cursor type definition:

CREATE TYPE cursorType AS empRow CURSOR;

About this task

In this version, cursor variables can only be declared as local variables within SQL procedures. Both strongly-typed and weakly-typed cursor variables can be declared.

Procedure

Formulate a DECLARE statement:
1. Specify a name for the variable.
2. Specify the cursor data type that will define the variable. If the cursor variable is to be weakly-typed, a user-defined weakly typed cursor data type must be specified or the built-in weakly-typed cursor data type CURSOR. If the cursor variable is to be based on a strongly-typed cursor data type, you can initialize the variable immediately.
The following is an example of how to formulate a DECLARE statement that will define a cursor variable of type cursorType that is not initialized:
```
DECLARE Cv1 cursorType@
```
The following is an example of how to formulate a DECLARE statement that will define a cursor variable Cv2 with a type that is anchored to the type of the existing cursor variable named Cv1:
```
DECLARE Cv2 ANCHOR DATA TYPE TO Cv1@
```
The following is an example of how to formulate a DECLARE statement that will define a weakly-typed cursor variable:
```
DECLARE Cv1 CURSOR@
```
Execute the DECLARE statement within a supported context.

Results

If execution of the DECLARE statement is successful, the cursor variable is created.

What to do next

Once this cursor variable is created, the cursor variable can be assigned values, referenced, or passed as a parameter.