Difference Between
Cursor And Ref Cursor
Lets first check the basic
example of Cursor and Ref Cursors. In this post Cursor means PL/SQL Cursors
only.
Example of Cursor:
Example of Cursor:
declare
cursor
c1 is select ename, sal from scott.emp;
begin
for c
in c1
loop
dbms_output.put_line('Ename: ' || c.ename || ', Salary: ' || c.sal);
end
loop;
end;
/
Example of Ref Cursor
declare
c1
SYS_REFCURSOR;
ename
varchar2(10);
sal
number;
begin
open c1
for select ename, sal from scott.emp;
LOOP
FETCH c1 into ename, sal;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line('Ename: ' || ename || ', Salary: ' || sal);
END
LOOP;
close
c1;
end;
/
Technically, They are both cursors and can be processed in the same fashion and at the most basic level, they both are same. There are some important differences between regular cursors and ref cursors which are following:
1) A ref cursor cannot be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.
2) A ref cursor is defined at run time and can be opened dynamically but a regular cursor is static and defined at compile time.
3) A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.
4) A ref cursor incurs a parsing penalty because it cannot cache but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.
5) A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.
6) A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.
My recommendation on ref cursors:
Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients and when there is NO other efficient/effective means of achieving the goal.
Ref Cursor: Strongly
Typed VS Weakly Typed
A Ref Cursor is a User Defined Type allows
you to declare the cursor variable which is independent of select statement. A
ref cursor is technically same as cursor and can be processed in the same
fashion at the most basic level. A ref cursor is defined at runtime and acts as
a pointer to the result set of the select query with it is opened dynamically.
It does not contain result of the query. Ref Cursor can be passed/returned to
another PL/SQL routine (function or procedure) or even can be returned to
client from the Oracle Database Server.
Ref Cursors can be further categorized in 2 parts
1) Strongly Typed Ref Cursor
2) Weakly Typed Ref Cursor
when a return type is included while defining Ref Cursor it called Strongly Typed Ref Cursor. Structure of Strongly Typed Ref Cursor is known at the compile time and can only be associated with queries which return result-set of same structure.
Example of Strongly Typed Ref Cursor
Ref Cursors can be further categorized in 2 parts
1) Strongly Typed Ref Cursor
2) Weakly Typed Ref Cursor
when a return type is included while defining Ref Cursor it called Strongly Typed Ref Cursor. Structure of Strongly Typed Ref Cursor is known at the compile time and can only be associated with queries which return result-set of same structure.
Example of Strongly Typed Ref Cursor
SQL> create or replace function
f_get_emp_by_dept (p_deptno in number)
2 return sys_refcursor
3 is
4 type my_row is record
5 (
6 empno
emp.empno%type,
7 ename
emp.ename%type,
8 mgr
emp.mgr%type,
9 sal
emp.sal%type,
10 dname
dept.dname%type
11 );
12 type t_stronge_cursor is ref cursor return my_row;
14 v_cur t_stronge_cursor;
16 begin
17 open v_cur for
18 select empno, ename, mgr, sal, dname
19 from emp, dept
20 where dept.deptno = emp.deptno and
dept.deptno = p_deptno;
21
22 return v_cur;
23 end;
Weakly Typed Ref Cursor do not have return type. Weakly Typed ref cursor gives us great flexibility and can be associated with any query. They can be directly created with predefined SYS_REFCURSOR type.
Example of Weakly Typed Ref Cursor
SQL> create or replace function
f_get_emp_by_dept(p_deptno in number) return sys_refcursor is
2 v_cur sys_refcursor;
3 begin
4 open v_cur for
5 select empno, ename, mgr, sal, dname from emp, dept
7 where
dept.deptno = emp.deptno
8 and
dept.deptno = p_deptno;
10 return v_cur;
11 end;
Passing Parameter to
CURSOR in Oracle
Oracle
does support Parameters with Cursors, like it does in case of function or
procedure. Benefit of "Parameterized Cursors" is of-course re-usability and maintainability.
Let's write some code which opens a Cursor
with Parameter multiple times. In the following example I have used Cursor with Parameter in 4
different ways
1) Parameterized cursor with fetch
2) Parameterized cursor with cursor loop
3) Parameterized cursor with cursor for loop
4) Parameterized cursor with
bulk collect
set serveroutput on
SQL> declare
2 cursor c1 (p_deptno number) is
3 select dname, nvl(sum(sal),0) total_sal
4 from emp e, dept d
5 where d.deptno = e.deptno(+)
6 and d.deptno = p_deptno
7 group by dname;
8
9 l_dname dept.dname%type;
10 l_sal number;
11
12 type t_tab is table of c1%rowtype index by
binary_integer;
13 l_tab t_tab;
14 begin
15
16 -- using parameterized cursor with fetch
17 open c1(10);
18 FETCH c1 INTO l_dname, l_sal;
19 close c1;
20 dbms_output.put_line('Department: ' ||
l_dname || ' Total: ' || l_sal);
21
-- using parameterized cursor with cursor
loop
23 open c1(20);
24 loop
25 fetch c1 into l_dname, l_sal;
26 exit when c1%notfound;
27 dbms_output.put_line('Department:
' || l_dname || ' Total: ' || l_sal);
28 end loop;
29 close c1;
30 -- using parameterized cursor with cursor for
loop
32 for c in c1(30)
33 loop
34 dbms_output.put_line('Department:
' || c.dname || ' Total: ' || c.total_sal);
35 end loop;
36 --
using parameterized cursor with bulk collect
38 open c1(40);
39 fetch c1 bulk collect into l_tab;
40 dbms_output.put_line('Department: ' ||
l_tab(1).dname || ' Total: ' || l_tab(1).total_sal);
41 close c1;
42 end;
43 /
Department: ACCOUNTING Total: 8750
Department: RESEARCH Total: 10875
Department: SALES Total: 9400
Department: OPERATIONS Total: 0
PL/SQL procedure successfully completed.
View results of
refcursor out parameter
check
the results of refcursor cursor which was passed in a stored procedure as a out parameter for
the debugging purpose.
Lets say we have
following oracle stored procedure:
CREATE OR REPLACE
PROCEDURE MYPROC (CR OUT SYS_REFCURSOR)
IS
BEGIN
OPEN CR FOR
SELECT EMPNO, ENAME, DNAME, SAL
FROM SCOTT.EMP E, SCOTT.DEPT D
WHERE E.DEPTNO = D.DEPTNO ORDER BY SAL;
END;
/
Solution 1: The results of refcursor can be easily viewed by making an
anonymous
block as following
SQL> SET
SERVEROUTPUT ON
SQL> DECLARE
2 C
SYS_REFCURSOR;
3
ENAME VARCHAR(50);
4
EMPNO NUMBER;
5
DNAME VARCHAR2(50);
6
SAL NUMBER;
7
BEGIN
8
MYPROC(C);
9
LOOP
10
FETCH C INTO EMPNO, ENAME, DNAME, SAL;
11
EXIT WHEN C%NOTFOUND;
12
DBMS_OUTPUT.PUT_LINE(EMPNO || ' ' || ENAME || ' ' || DNAME || ' ' ||
SAL);
13
END LOOP;
14 END;
15 /
7934 MILLER OPERATIONS
0
7369 SMITH RESEARCH
800
7900 JAMES SALES 950
7876 ADAMS RESEARCH
1100
7521 WARD SALES 1250
7654 MARTIN SALES 1250
7844 TURNER SALES 1500
7499 ALLEN SALES 1600
7782 CLARK ACCOUNTING
2450
7698 BLAKE SALES 2850
7566 JONES RESEARCH
2975
7788 SCOTT RESEARCH
3000
7902 FORD RESEARCH
3000
7839 KING ACCOUNTING
5000
PL/SQL procedure
successfully completed.
Solution 2: But the nice and simple way to get the output of ref-cursor
returned
by an oracle stored
procedure is as following:
SQL> VAR C
REFCURSOR
SQL> EXECUTE
MYPROC(:C);
PL/SQL procedure
successfully completed.
SQL> PRINT C
EMPNO ENAME DNAME SAL
---------- ----------
-------------- ----------
7934 MILLER OPERATIONS 0
7369 SMITH RESEARCH 800
7900 JAMES SALES 950
7876 ADAMS RESEARCH 1100
7521 WARD SALES 1250
7654 MARTIN SALES 1250
7844 TURNER SALES 1500
7499 ALLEN SALES 1600
7782 CLARK ACCOUNTING 2450
7698 BLAKE SALES 2850
7566 JONES RESEARCH 2975
7788 SCOTT RESEARCH 3000
7902 FORD RESEARCH 3000
7839 KING ACCOUNTING 5000
14 rows selected.
Get Results from
REFCURSOR with unknown number of columns in PLSQL
Here is a very simple procedure which takes a SQL
Query as input and returns a REFCURSOR opened with that query.
SQL> CREATE OR REPLACE PROCEDURE MYPROC
(P_SQL VARCHAR2, CR OUT SYS_REFCURSOR)
2 IS
3 BEGIN
4 OPEN CR FOR P_SQL;
5 END;
6 /
Procedure created.
Now the problem is we do not know what will be the SQL passed in procedure, what are the number of columns and their data type. How to fetch data from this REFCURSOR? Oh, Yes, Oracle provides us a very helpful function DBMS_SQL.TO_CURSOR_NUMBER which converts a REFCURSOR variable to a SQL cursor number, which you can pass to DBMS_SQL subprograms.
Now Let's try to write a procedure which takes REFCURSOR as input and simply print it's data, without knowing any information about columns.
SQL> CREATE OR REPLACE PROCEDURE
PRINT_REFCURSOR (CR IN OUT SYS_REFCURSOR)
2 IS
3 l_curid NUMBER;
4 l_col_cnt INTEGER;
5 rec_tab DBMS_SQL.DESC_TAB;
6 l_text VARCHAR2 (4000);
7 l_flag NUMBER;
8 l_varchar2 VARCHAR2 (4000);
9 l_number NUMBER;
10 l_date
DATE;
11
12 BEGIN
13 l_curid := DBMS_SQL.TO_CURSOR_NUMBER(CR);
14
15 -- define columns
16 DBMS_SQL.DESCRIBE_COLUMNS (l_curid,
l_col_cnt, rec_tab);
17 FOR pos IN 1 .. l_col_cnt
18 LOOP
19 CASE rec_tab (pos).col_type
20 WHEN 1 THEN
21 DBMS_SQL.DEFINE_COLUMN
(l_curid,pos,l_varchar2,2000);
22 WHEN 2 THEN
23 DBMS_SQL.DEFINE_COLUMN (l_curid,
pos, l_number);
24 WHEN 12 THEN
25 DBMS_SQL.DEFINE_COLUMN (l_curid,
pos, l_date);
26 ELSE
27 DBMS_SQL.DEFINE_COLUMN
(l_curid,pos,l_varchar2,2000);
28 END CASE;
29 END LOOP;
30
31 -- Print column names of dynamic sql
32 FOR pos IN 1 .. l_col_cnt
33 LOOP
34 l_text := LTRIM (l_text || ',' || LOWER
(rec_tab (pos).col_name), ',');
35 END LOOP;
36
37 DBMS_OUTPUT.PUT_LINE (l_text);
38
39 -- Print data fetched by query
40 LOOP
41 l_flag := DBMS_SQL.FETCH_ROWS
(l_curid);
42 EXIT WHEN l_flag = 0;
43 l_text := NULL;
44
45 FOR pos IN 1 .. l_col_cnt
46 LOOP
47 CASE rec_tab(pos).col_type
48 WHEN 1 THEN
49 DBMS_SQL.COLUMN_VALUE
(l_curid, pos, l_varchar2);
50 l_text := LTRIM (l_text || ',"'
|| l_varchar2 || '"', ',');
51 WHEN 2 THEN
52 DBMS_SQL.COLUMN_VALUE
(l_curid, pos, l_number);
53 l_text := LTRIM (l_text || ','
|| l_number, ',');
54 WHEN 12 THEN
55 DBMS_SQL.COLUMN_VALUE
(l_curid, pos, l_date);
56 l_text := LTRIM (l_text||
','|| TO_CHAR (l_date, 'DD/MM/YYYY HH24:MI:SS'),',');
57 ELSE
58 l_text := LTRIM (l_text ||
',"' || l_varchar2 || '"', ',');
59 END CASE;
60 END LOOP;
61 DBMS_OUTPUT.PUT_LINE (l_text);
62 END LOOP;
63
64 DBMS_SQL.CLOSE_CURSOR (l_curid);
65 END;
66 /
Procedure created.
It compiled. Now we are good to test it. Let me try it first with a simple query.
SQL> set serveroutput on
SQL> declare
2 C SYS_REFCURSOR;
3 begin
4 MYPROC('SELECT * FROM DEPT', C);
5 PRINT_REFCURSOR(C);
6 END;
7 /
deptno,dname,loc
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"
PL/SQL procedure successfully completed.
Great !!! Now it is time test our procedure with for some complex query with joins and some analytic function.
SQL> declare
2 C SYS_REFCURSOR;
3 begin
4 MYPROC('SELECT EMPNO, ENAME, DNAME, SAL,
5 RANK() OVER (PARTITION BY DNAME ORDER BY
SAL DESC) SAL_RANK
6 FROM EMP E, DEPT D
7 WHERE E.DEPTNO = D.DEPTNO
8 ORDER BY DNAME, SAL_RANK', C);
9 PRINT_REFCURSOR(C);
10 END;
11 /
empno,ename,dname,sal,sal_rank
7839,"KING","ACCOUNTING",5000,1
7782,"CLARK","ACCOUNTING",2450,2
7934,"MILLER","ACCOUNTING",1300,3
7902,"FORD","RESEARCH",3000,1
7788,"SCOTT","RESEARCH",3000,1
7566,"JONES","RESEARCH",2975,3
7876,"ADAMS","RESEARCH",1100,4
7369,"SMITH","RESEARCH",800,5
7698,"BLAKE","SALES",2850,1
7499,"ALLEN","SALES",1600,2
7844,"TURNER","SALES",1500,3
7521,"WARD","SALES",1250,4
7654,"MARTIN","SALES",1250,4
7900,"JAMES","SALES",950,6
PL/SQL procedure successfully completed.
WOW !!! Our procedure PRINT_REFCURSOR is working as expected. DBMS_SQL.TO_CURSOR_NUMBER was the savior and power of
DBMS_SQL is awesome
No comments:
Post a Comment