Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts

Cursor Variables

Cursor variables are a special PL/SQL type that allows a procedure to return the rows from a cursor to a calling application. This can be useful when you want to switch cursors dynamically, as you will see in Listing 16.1, or to return multiple rows from a procedure. The cursor variable designates an area of memory that stores the returned rows from the procedure.

Two steps are needed to create a cursor variable:

• Declare the cursor as a REF CURSOR type.

• Define a cursor variable using the type.

Here is a syntax example of the declaration:

				Type
				type_name
				is ref cursor return
				return_type;
				Cursor_variable_name         type_name;
			

The return type can be a data type or a PL/SQL record. When the return data type is included, the cursor variable ...