Declaring a Cursor Variable:

Reference types in PL/SQL are described using the syntax:

REF type

where type is a previously defined type.

The REF keyword denotes in which the new type will be a pointer to the described type. The type of a cursor variable is thus REF CURSOR. . The complete syntax for defining a cursor variable type is as given below:

TYPE type_name IS REF CURSOR RETURN return_type;

where return_type is a record type denoting the category of the select list and type_name is the name of the new reference type in which will eventually be returned through the cursor variable. The return type for a cursor variable necessary is a record type. It can be defined explicitly as a user-defined record or implicitly using %ROWTYPE.

The variable can be declared once the reference type is defined. The following declarative section displays variant declarations for cursor variables.

DECLARE

TYPE emp_ref IS REF CURSOR RETURN emp%ROWTYPE;

TYPE t_emprecord IS RECORD (name emp.ename%type, desg emp.job%TYPE);

v_emprecord t_emprecord;

TYPE t_emprecordRef IS REF CURSOR RETURN t_empRecord;

TYPE t_empRef2 IS REF CURSOR            RETURN v_emprecord%TYPE;

v_emprecordCV         t_emprecord;

v_emprecord   t_emprecordRef;