What is REF CURSOR? A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The main purpose of the ref cursor is, we can return the query result in the front end(Java, .Net, VB, reporting tools etc). For instance, if we want to return the query result set in Cognos reporting tool, then we can use the ref cursor to return the values. The primary use of ref cursor is to pass the result set back to calling application. Ref cursor can be passed as a variable to another subroutine. The powerful cabability in ref cursor is, cursor can be opened and passed to another block for processing, then returned to original block to be closed.
This article is tested in oracle9i and it should work in oracle10g and further versions... All the below PLSQL code in this article is successfully tested in oracle 9.2.0.8. I tested the code in Scott schema with emp and dept tables.
What is the difference between REF CURSOR and Normal Cursor?
1. The cursor concept is same between regular cursor(PLSQL cursor) and ref cursor. But normal cursor, we can declare and define the cursor in the declaration part. In the declaration itself, the select statement is tied up with the cursor. So the cursor structure is known in the compile time. It is static in definition.
In ref cursor, we just declare the variable as SYS_REFCURSOR data type. We are not tying with any select statement in the declaration. But inside the procedure, we can tie up the same ref cursor variable with any number of select statement. It is dynamic and dynamically opened in the procedure, based on the logic or condition.
Let us demonstrate this... I have stored procedure and i am passing input 1 or 2. If i pass 1, then i wanted to display emp table records. If i pass input 2, then i wanted to display dept table. I am demonstrating this in normal cursor and ref cursor.
Here is the way to accomplish this in Normal cursor.
create or replace procedure Test_refcursor(p_choice NUMBER) is
cursor c1 is select * from emp;
cursor c2 is select * from dept;
begin
if p_choice = 1 then
for i in c1 loop
dbms_output.put_line(i.ename);
end loop;
elsif p_choice = 2 then
for i in c2 loop
dbms_output.put_line(i.dname);
end loop;
end if;
END;
/
Here is the way to accomplish this in ref cursor.
Create or replace procedure test_refcursor(p_choice number) is
c1 sys_refcursor;
v_ename emp.ename%type;
v_dname dept.dname%type;
procedure gen_cur(chc IN number,b IN OUT sys_refcursor) is
str varchar2(1000);
begin
if chc = 1 then
str:= 'select ename from emp';
elsif chc = 2 then
str:= 'select dname from dept';
end if;
open b for str;
end;
begin
gen_cur(p_choice,c1);
if p_choice = 1 then
loop
fetch c1 into v_ename;
exit when c1%notfound;
dbms_output.put_line(v_ename);
end loop;
elsif p_choice = 2 then
loop
fetch c1 into v_dname;
exit when c1%notfound;
dbms_output.put_line(v_dname);
end loop;
end if;
end;
/
2. Ref cursor output can be returned to client(java, .Net, VB, reporting tool etc) application. But normal cursor(PLSQL cursor) output can not be returned to client application.
3. Normal cursor can be global. For example, we can declare the normal cursor in the package specification. It can be used in all procedure/functions in the same package as well as outside the package. But ref cursor can not be declared outside of the procedure.
4. Normal cursor can not be passed from one subroutine to another subroutine. But ref cursor can be passed from one subroutine to another subroutine.
How do we declare ref cursor? There are two type of ref cursor variable declaration. One is weak type declaration and another one is strong type declaration. Weak typed declaration does not tell us return data structure. Strong type declaration will tell us what type of data will be returned. Strongly typed cursor has less flexibilities and less prone to programming errors. Weakly typed cursors has more flexibilities and it can return different structure of of the data.
type emp_cursor is ref cursor; -- Weak typed declaration
type emp_cursor is ref cursor
returning emp%rowtype; -- Strong typed declaration
Once cursor type is defined, then cursor variable can be assigned to cursor type.
c1 emp_cursor;
Sample program for ref cursor... Pass the employee name as a input to procedure and return all subordinates.
CREATE OR REPLACE PROCEDURE testrefcursor(
p_ename IN VARCHAR2,
curename OUT SYS_REFCURSOR) IS
sql_text VARCHAR2(4000);
BEGIN
sql_text := 'SELECT ENAME
FROM EMP
WHERE ENAME != :1
START WITH ENAME = :2
CONNECT BY PRIOR EMPNO = MGR';
IF curename%ISOPEN THEN
CLOSE curename;
END IF ;
OPEN curename
FOR sql_text USING p_ename,p_ename;
END;
/
Let us accomplish the same above task through Function.
CREATE OR REPLACE FUNCTION testrefcursor(p_ename IN VARCHAR2)
RETURN SYS_REFCURSOR IS
sql_text VARCHAR2(4000);
curename SYS_REFCURSOR;
BEGIN
sql_text := 'SELECT ENAME
FROM EMP
WHERE ENAME != :1
START WITH ENAME = :2
CONNECT BY PRIOR EMPNO = MGR';
IF curename%ISOPEN THEN
CLOSE curename;
END IF ;
OPEN curename
FOR sql_text USING p_ename,p_ename;
RETURN curename;
END;
/
How do we display ref cursor result through SQL*PLUS? It is simple. Let us go with some sample code and demonstrate how to display the output in SQL*PLUS? Let us display the output for the above procedure testrefcursor.
SQL> DECLARE
2 refcursor SYS_REFCURSOR;
3 v_ename EMP.ENAME%TYPE;
4 BEGIN
5 testrefcursor('KING',refcursor);
6 loop
7 fetch refcursor into v_ename;
8 exit when refcursor%notfound;
9 dbms_output.put_line(v_ename);
10 end loop;
11 end;
12 /
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
PL/SQL procedure successfully completed.
SQL>
Here is the way, we can display the above function output....
SQL> select testrefcursor('KING') from dual;
TESTREFCURSOR('KING'
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
ENAME
----------
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
13 rows selected.
We can also define SQL*PLus variables of type REFCURSOR...
SQL> create or replace function getemplist(dno in number)
2 return sys_refcursor
3 is
4 return_value sys_refcursor;
5 begin
6 open return_value for
7 select ename from emp where deptno = dno;
8 return return_value;
9 end;
10 /
Function created.
SQL> var rc refcursor
SQL> exec :rc := getemplist(30)
PL/SQL procedure successfully completed.
SQL> print rc
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
6 rows selected.
SQL>
When and what circumstances we should use ref cursor? Ref cursor is classic option when we want to return the query output into any front end application. We can use ref cursor when you are not able to accomplish the task through normal cursor. REF CURSOR is flexibility feature, but not performance feature. Ref cursor is bad choice when performance is a concern and able to accomplish the task by using regular cursor. Ref cursor always consumes time to process the query compared to normal cursor. So ref cursor should be used only place where it is required.
Performance comparision between ref cursor and regular cursor? Ref cursor is not a good option if performance is a concern. but ofcourse, there are place you can not avoid ref cursor. Ref cursor is not an option when you want to process the records inside the PLSQL procedure. Ref cursor is always slower then explicit cursor and implicit cursor. Let me demonstrate how process time between ref cursor, explicit cursor and implicit cursor...
As per the below example, the ref cursor consumes more time then the explicit cursor and implicit cursor.... So in netshell, use ref cursor only if it is required. otherwise, try to use regular cursor..
Note : For some reason, in the below code, pipe symbol is not appearing in the blog. So i replaced the pipe symbol with # symbol. In case if you want to run the below code in your database, please replace back # with pipe sign.
SQL> CREATE OR REPLACE PROCEDURE stp_refcursor_comparison AS
2 l_loops NUMBER := 10000;
3 l_dummy dual.dummy%TYPE;
4 l_start NUMBER;
5 CURSOR c_dual IS
6 SELECT dummy
7 FROM dual;
8 l_cursor SYS_REFCURSOR;
9 BEGIN
10 -- Time explicit cursor.
11 l_start := DBMS_UTILITY.get_time;
12 FOR i IN 1 .. l_loops LOOP
13 OPEN c_dual;
14 FETCH c_dual
15 INTO l_dummy;
16 CLOSE c_dual;
17 END LOOP;
18 DBMS_OUTPUT.put_line('Explicit: ' #
19 (DBMS_UTILITY.get_time - l_start));
20 -- Time ref cursor.
21 l_start := DBMS_UTILITY.get_time;
22 FOR i IN 1 .. l_loops LOOP
23 OPEN l_cursor FOR SELECT dummy FROM dual;
24 FETCH l_cursor
25 INTO l_dummy;
26 CLOSE l_cursor;
27 END LOOP;
28 DBMS_OUTPUT.put_line('REF CURSOR: ' #
29 (DBMS_UTILITY.get_time - l_start));
30 -- Time implicit cursor.
31 l_start := DBMS_UTILITY.get_time;
32 FOR i IN 1 .. l_loops LOOP
33 SELECT dummy
34 INTO l_dummy
35 FROM dual;
36 END LOOP;
37 DBMS_OUTPUT.put_line('Implicit: ' #
38 (DBMS_UTILITY.get_time - l_start));
39 END stp_refcursor_comparison;
40 /
Procedure created.
SQL> set serveroutput on
SQL> execute stp_refcursor_comparison;
Explicit: 53
REF CURSOR: 67
Implicit: 35
PL/SQL procedure successfully completed.
SQL>
No comments:
Post a Comment