Ref Cursors

[pravin]

Folks,

Can anyone point to a document which talks about
#1. What is a REF CURSOR ?
#2. When should one use it ?
#3. What is the advantage/disadvantage of using it

thx.

Rajesh.

[Naeem]

Hi,

To understand a ref cursor, you need to understand cursors and cursor variables. The following is from the Oracle 8.15 PL/SQL User's Guide & Reference database documentation
Cursor
Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:
DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;
The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria. An explicit cursor "points" to the current row in the result set. This allows your program to process the rows one at a time.
Cursor variable
Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any query. It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database. This gives you more flexibility and a convenient way to centralize data retrieval.
Ref cursor-data type of a cursor variable
Declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR.
To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax
TYPE ref_type_name IS REF CURSOR RETURN return_type;
where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent a record or a row in a database table.
You can declare cursor variables as the formal parameters of functions and procedures. In the following example, you define the REF CURSOR type EmpCurTyp, then declare a cursor variable of that type as the formal parameter of a procedure:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN empROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp)
IS
...
Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. For example, the following packaged procedure opens the cursor variable emp_cv:
CREATE PACKAGE emp_data AS
...
TYPE EmpCurTyp IS REF CURSOR RETURN empROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;
CREATE PACKAGE BODY emp_data AS
..
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
END emp_data;
When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.