Oracle SQL:Ref Cursors and Cursor Variables

7/31/2015 01:29:00 PM

REF CURSORS AND CURSOR VARIABLES

1 This is unconstrained cursor which will return different types depends upon the user input.
2 Ref cursors can not be closed implicitly.
3 Ref cursor with return type is called strong cursor.
4 Ref cursor with out return type is called weak cursor.
5 You can declare ref cursor type in package spec as well as body.
6 You can declare ref cursor types in local subprograms or anonymous blocks.
7 Cursor variables can be assigned from one to another.
8 You can declare a cursor variable in one scope and assign another cursor variable with different scope, then you can use the cursor variable even though the assigned cursor variable goes out of scope.
9 Cursor variables can be passed as a parameters to the subprograms.
10 Cursor variables modes are in or out or in out.
11 Cursor variables can not be declared in package spec and package body (excluding subprograms).
12 You can not user remote procedure calls to pass cursor variables from one server to another.
13 Cursor variables can not use for update clause.
14 You can not assign nulls to cursor variables.
15 You can not compare cursor variables for equality, inequality and nullity.

Ex:
CREATE OR REPLACE PROCEDURE REF_CURSOR(TABLE_NAME IN VARCHAR) IS
type t is ref cursor;
c t;
v_dept dept%rowtype;
type r is record(ename emp.ename%type,job emp.job%type,sal emp.sal%type);
v_emp r;
v_stud student.name%type;
BEGIN
if table_name = 'DEPT' then
open c for select * from dept;
elsif table_name = 'EMP' then
open c for select ename,job,sal from emp;
elsif table_name = 'STUDENT' then
open c for select name from student;
end if;
loop
if table_name = 'DEPT' then
fetch c into v_dept;
exit when c%notfound;
dbms_output.put_line('Deptno = ' || v_dept.deptno || ' Dname = ' ||
v_dept.dname || ' Loc = ' || v_dept.loc);
elsif table_name = 'EMP' then
fetch c into v_emp;
exit when c%notfound;
dbms_output.put_line('Ename = ' || v_emp.ename || ' Job = ' || v_emp.job
|| ' Sal = ' || v_emp.sal);
elsif table_name = 'STUDENT' then
fetch c into v_stud;
exit when c%notfound;
dbms_output.put_line('Name = ' || v_stud);
end if;
end loop;
close c;
END;

Output:
SQL> exec ref_cursor('DEPT')

Deptno = 10 Dname = ACCOUNTING Loc = NEW YORK
Deptno = 20 Dname = RESEARCH Loc = DALLAS
Deptno = 30 Dname = SALES Loc = CHICAGO
Deptno = 40 Dname = OPERATIONS Loc = BOSTON

SQL> exec ref_cursor('EMP')

Ename = SMITH Job = CLERK Sal = 800
Ename = ALLEN Job = SALESMAN Sal = 1600

SQL> exec ref_cursor('STUDENT')

Name = saketh
Name = srinu

There are three types of cursors

1. Strong Ref Cursor
Ref Cursors which has a return type is classified as Strong Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;

Here empcurtyp is a Strong Ref Cursor

2. Weak Ref Cursor
Ref Cursors which has no return type is classified as Weak Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR;
…..
End;

Here empcurtyp is a Weak Ref Cursor

3. System Ref Cursor

This is a system defined Ref Cursor. This also considered weak. System Ref Cursor need not declare explicitly.

Declare
empcurtyp SYS_REFCURSOR;
…..
End;