User-defined REF CURSOR type variables (PL/SQL)

The Db2® data server supports the user-defined REF CURSOR data type and cursor variable declarations.

The user-defined REF CURSOR type can be defined by executing the TYPE declaration in a PL/SQL context. After the type has been defined, you can declare a cursor variable of that type.

Restriction: REF CURSOR types can be declared only within a package and are not supported in routines, triggers, or anonymous blocks.

Syntax

Read syntax diagramSkip visual syntax diagramTYPEcursor-type-nameIS REF CURSOR RETURNreturn-type

Description

TYPE cursor-type-name
Specifies an identifier for the cursor data type.
IS REF CURSOR
Specifies that the cursor is of a user-defined REF CURSOR data type.
RETURN return-type
Specifies the return type that is associated with the cursor. If a return-type is specified, this REF CURSOR type is strongly typed; otherwise, it is weakly typed.

Example

The following example shows a cursor variable declaration in a package:
CREATE OR REPLACE PACKAGE my_pkg
AS
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
my_rec emp_cur_type;
END my_pkg