Cursor variables

A cursor variable enables a cursor handler to be associated with multiple SQL queries. With respect to functionality, it is similar to an explicit cursor but with certain implementation changes. One of the fundamental differences is that, unlike a cursor, it is a variable of a cursor type. Therefore, it can potentially be referenced in a similar way to other program variables.

As a variable, it can be passed as a parameter to subprograms or used as a return type of a PL/SQL function. Cursor variables can be quite handy when sharing result sets between two subprograms or when a client pulls a data set from the database.

Cursor variables are created by defining a variable of the REF CURSOR type variable or an SYS_REFCURSOR type variable.

Note

Cursor FOR loop does not support cursor variables

The REF CURSOR syntax is as follows:

TYPE [CURSOR VARIABLE NAME] IS REF CURSOR [RETURN (return type)]

In the preceding syntax, the RETURN type of a cursor variable must be a record type. It...