Today we are going to discuss about system reference cursor
or ref cursor. I have been working in IT industry for last 7 years and I have
seen many developers who avoid using this very powerful tool as their understanding
of ref cursor is not as clear as simple cursor. So in this edition we will try
to explore various aspects of ref cursor and how can we use those features in
daily programming which will make our life much easier.
First question which I
would like to answer before going in detailed discussion is what is ref cursor.
System reference cursors
or Ref cursors are pointers to the Executed Query in an area called process global area (PGA).
Second question is what
is cursor variable? Cursor variable is a variable that points to the reference
of a Cursor. A cursor variable (especially weak cursor variable, discussed
later) can be opened for any number of queries. User can use a cursor variable
multiple times within a same execution unit.
Some of the biggest benefit of cursor variable is written below
Programmer
can use the ref cursor as a parameter to other pl/sql units.
Programmer can assign the pointer of one cursor
to another cursor variable. Like other variables cursor variables can be
assigned values using an assignment operator (See the last example).
But before using ref cursor in program all programmers should keep
in mind the below points
A ref cursor involves additional database
round tip. While the ref cursor is returned to the client, the actual data is not returned
until client opens the ref cursor and try to read it.
Secondly, you cannot update the database by ref
cursor.
Lastly,
Ref cursor is not backward scrollable. Data can be fetched only in forward
directions.
Types of Ref Cursor:-
The Syntax for creating
a system Referenced Cursor or Ref Cursor is as follows
TYPE cursor_name IS REF CURSOR [ RETURN
return_type ].
The portion which exists
within the brace is an optional part of ref cursor declaration.
Based on the existence of return clause there are two types of Ref
Cursor
1>
Strong Ref Cursor :- This type
of ref cursors have always a return type.
For example
TYPE v_emp IS REF CURSOR RETURN emp%ROWTYPE;
v_emp_rec
v_emp;
This type of Ref Cursor can only fetch emp%rowtype
data structure. This cursor will not work on any other data structure. The
advantage of a strong type is that the compiler can determine whether or not
the developer has properly matched up the cursor variable FETCH statements with
it’s cursor objects query lists.
For Example:-
DECLARE
TYPE v_emp
IS REF CURSOR RETURN emp%ROWTYPE;
v_emp_ref
v_emp;
emp_rec emp%ROWTYPE;
BEGIN
OPEN
v_emp_ref FOR SELECT * FROM emp;
LOOP
FETCH
v_emp_ref INTO emp_rec;
EXIT WHEN
v_emp_ref%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);
END LOOP;
END;
2> Weak Ref Cursor :- This type of
ref cursors does not have return type.
For example
TYPE v_emp_ref IS
REF CURSOR;
OR
v_emp_ref SYS_REFCURSOR; (SYS_REFCURSOR IS a
predefined weak REF CURSOR type).
This type of Ref Cursor can be associated with any query.
As it does not return any kind of particular data structure it can be
associated with different queries which have different kind of result set. It
is more flexible than the Strong Ref Cursor.
For example
DECLARE
v_emp_ref SYS_REFCURSOR;
emp_rec
emp%ROWTYPE;
dept_rec
dept%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
DBMS_OUTPUT.PUT_LINE('----- Fetching Data
from emp table-----');
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
OPEN v_emp_ref FOR SELECT * FROM emp;
LOOP
FETCH v_emp_ref INTO emp_rec;
EXIT WHEN v_emp_ref%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);
END LOOP;
CLOSE v_emp_ref; -- Good practice code
--
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
DBMS_OUTPUT.PUT_LINE('----- Fetching Data
from Dept table-----');
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
--
OPEN v_emp_ref FOR SELECT * FROM dept;
LOOP
FETCH v_emp_ref INTO dept_rec;
EXIT WHEN v_emp_ref%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||'---'||dept_rec.dname||'--'||dept_rec.loc);
END LOOP;
END;
And the
result we got is
----------------------------------------
-----
Fetching Data from emp table-----
----------------------------------------
7400--SMITH--CLERK--7902
7369--SMITH--CLERK--7902
7499--ALLEN--SALESMAN--7698
7566--JONES--MANAGER--7839
7654--MARTIN--SALESMAN--7698
7698--BLAKE--MANAGER--7839
7782--CLARK--MANAGER--7839
7788--SCOTT--ANALYST--7566
7839--KING--PRESIDENT--
7844--TURNER--SALESMAN--7698
7876--ADAMS--CLERK--7788
7900--JAMES--CLERK--7698
7902--FORD--ANALYST--7566
7934--MILLER--CLERK—7782
----------------------------------------
-----
Fetching Data from Dept table-----
----------------------------------------
25---MANUFACTURING--KOLKATA
10---ACCOUNTING--NEW
YORK
20---RESEARCH--DALLAS
30---SALES--CHICAGO
40---OPERATIONS--BOSTON
Please observe that the single ref cursor is used to query
two different tables in two different queries. Secondly, when you are
associating a new result set with a cursor variable that was previously used in
an OPEN FOR statement and you did not explicitly close the cursor variable,
then the underlying cursor remain open. It is not mandatory but you should
always close the cursor variable before reusing them with another result set.
Lastly, declaration of cursor variable does not create a cursor object.
Programmer must use the OPEN FOR syntax to create a new cursor object and
assign it to cursor variable.
Similarities
with Static Cursor:-
While using Ref Cursor programmer can always use the basic
four attributes with exactly the same syntax as for a static cursor. They are
as follows
1> REF_CUR%ISOPEN
2> REF_CUR%FOUND
3> REF_CUR%NOTFOUND
4> REF_CUR%ROWCOUNT
With the following example the points will be
clarified. We will modify our previous example a little bit.
SET
SERVEROUTPUT ON SIZE 20000;
DECLARE
TYPE v_emp IS REF CURSOR RETURN emp%ROWTYPE;
v_emp_ref v_emp;
emp_rec
emp%ROWTYPE;
BEGIN
OPEN v_emp_ref FOR SELECT * FROM emp;
------
IF v_emp_ref%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('v_emp_ref cursor
OPEN');
END IF;
------
DBMS_OUTPUT.PUT_LINE('Rowcount--->'||v_emp_ref%ROWCOUNT);
------
LOOP
FETCH v_emp_ref INTO emp_rec;
EXIT WHEN v_emp_ref%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);
DBMS_OUTPUT.PUT_LINE('Rowcount--->'||v_emp_ref%ROWCOUNT);
IF
v_emp_ref%FOUND THEN
DBMS_OUTPUT.PUT_LINE('v_emp_ref
cursor found');
END
IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Final
Rowcount--->'||v_emp_ref%ROWCOUNT);
END;
RESULT:-
anonymous
block completed
v_emp_ref
cursor OPEN
Rowcount--->0
7400--SMITH--CLERK--7902
Rowcount--->1
v_emp_ref
cursor found
7369--SMITH--CLERK--7902
Rowcount--->2
v_emp_ref
cursor found
7499--ALLEN--SALESMAN--7698
Rowcount--->3
v_emp_ref
cursor found
7566--JONES--MANAGER--7839
Rowcount--->4
v_emp_ref
cursor found
7654--MARTIN--SALESMAN--7698
Rowcount--->5
v_emp_ref
cursor found
7698--BLAKE--MANAGER--7839
Rowcount--->6
v_emp_ref
cursor found
7782--CLARK--MANAGER--7839
Rowcount--->7
v_emp_ref
cursor found
7788--SCOTT--ANALYST--7566
Rowcount--->8
v_emp_ref
cursor found
7839--KING--PRESIDENT--
Rowcount--->9
v_emp_ref
cursor found
7844--TURNER--SALESMAN--7698
Rowcount--->10
v_emp_ref
cursor found
7876--ADAMS--CLERK--7788
Rowcount--->11
v_emp_ref
cursor found
7900--JAMES--CLERK--7698
Rowcount--->12
v_emp_ref
cursor found
7902--FORD--ANALYST--7566
Rowcount--->13
v_emp_ref
cursor found
7934--MILLER--CLERK--7782
Rowcount--->14
v_emp_ref
cursor found
Final
Rowcount--->14
Please observe one thing
that at the very beginning of the execution the ROWCOUNT is 0 because at that
time no row were fetched from the underlying table.
ROWTYPE_MISMATCH EXCEPTION:-
Some
time programmer who is accustomed to use Strong Ref Cursor may get it. It is an
exception which tell programmer that the data structure of query is different
than the return type of the Ref Cursor itself.
DECLARE
--v_emp_ref
SYS_REFCURSOR;
TYPE v_emp
IS REF CURSOR RETURN emp%ROWTYPE;
v_emp_ref
v_emp;
emp_rec emp%ROWTYPE;
PROCEDURE Return_Dept_Row(v_emp
IN OUT SYS_REFCURSOR)
IS
BEGIN
OPEN
v_emp FOR SELECT * FROM dept;
END
Return_Dept_Row;
BEGIN
Return_Dept_Row(v_emp_ref);
LOOP
FETCH
v_emp_ref INTO emp_rec;
EXIT WHEN
v_emp_ref%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);
END LOOP;
EXCEPTION
WHEN
ROWTYPE_MISMATCH THEN
DBMS_OUTPUT.PUT_LINE('Within ROWTYPE_MISMATCH: Reason for
Error-->'||SQLERRM);
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Within Others: Reason for Error-->'||SQLERRM);
END;
Assigning one Ref Cursor to another Ref
Cursor variable :-
As
ref cursor is just another kind of variable programmer can assign the reference
of one ref cursor variable to another just using an assignment operator.
For
example
DECLARE
v_emp_ref SYS_REFCURSOR;
v_emp_sec_ref SYS_REFCURSOR;
emp_rec emp%ROWTYPE;
emp_rec_sec emp%ROWTYPE;
BEGIN
OPEN
v_emp_ref FOR SELECT * FROM emp;
LOOP
FETCH
v_emp_ref INTO emp_rec;
EXIT WHEN
v_emp_ref%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);
--
v_emp_sec_ref := v_emp_ref;
FETCH
v_emp_ref INTO emp_rec_sec;
DBMS_OUTPUT.PUT_LINE(emp_rec_sec.empno||'--'||emp_rec_sec.ename||'--'||emp_rec_sec.job||'--'||emp_rec_sec.mgr);
CLOSE
v_emp_sec_ref;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Final Rowcount--->'||v_emp_ref%ROWCOUNT);
END;
OutPut is:-
ORA-01001: invalid cursor
ORA-06512: at line 9
01001. 00000 -
"invalid cursor"
*Cause:
*Action:
7400--SMITH--CLERK--7902
7369--SMITH--CLERK—7902
Please observe that even
v_emp_ref and v_emp_ref_sec is two different ref Cursor variable and both ref
Cursor variable has fetched two different set of data from emp table because
the pointer to the current record stays with the cursor object. It does not
stays with any ref cursor variable.
During execution when we
have close v_emp_ref_sec cursor variable it also closed the ref cursor
v_emp_ref.
No comments:
Post a Comment