This is the continuation of the previous post ‘Ref Cursor Concept
Part1’. In the previous post we have discussed type of ref cursors,
Similarities of ref cursors with the static cursors, Rowtype_mismatch
exceptions and assigning of one ref cursor variable into another ref cursor
variable. In this edition we are going to discuss about how to pass ref cursor
variable from one pl/sql unit to another,How we can use ref cursors in SQL
statement and how to print data in sqlplus when the data is referenced by a ref
cursor variable.
Passing Cursor
Variables as arguments:-
As i told earlier that ref cursor variable can be passed
from one programming unit to another as a argument of function and procedures.
In the next example we are going to see that. But before that we must know that
we are going to use EMP and DEPT table for the purpose.
SQL> SELECT
dname,loc FROM dept WHERE deptno
= 30;
DNAME
LOC
-------------- -------------
SALES
CHICAGO
SQL> SELECT
ename,deptno FROM emp WHERE empno='7499';
ENAME DEPTNO
---------- --------------------
ALLEN 30
We will use this
particular set of data for our example.
SET SERVEROUTPUT ON SIZE 20000;
DECLARE
v_ref_emp SYS_REFCURSOR;
v_ref_dept
SYS_REFCURSOR;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE;
v_dname dept.dname%TYPE;
v_loc dept.loc%TYPE;
PROCEDURE Show_Emp_Loc_Dname(v_emp IN
SYS_REFCURSOR,
v_dept IN OUT SYS_REFCURSOR,
p_ename OUT emp.ename%TYPE)
IS
BEGIN
FETCH v_emp
INTO p_ename,v_deptno;
OPEN
v_ref_dept FOR SELECT dname,
loc
FROM dept
WHERE deptno = v_deptno;
v_emp.CLOSE;
END Show_Emp_Loc_Dname;
BEGIN
OPEN
v_ref_emp FOR SELECT ename,
deptno
FROM emp
WHERE empno='7499';
--
Show_emp_loc_dname(v_ref_emp,v_ref_dept,v_ename);
--
FETCH
v_ref_dept INTO v_dname,v_loc;
DBMS_OUTPUT.PUT_LINE(v_ename||' works in '||v_dname||' and his office is
in '||v_loc);
v_ref_dept.CLOSE;
END;
/
Output:-
anonymous block completed
ALLEN works in SALES and his office is in CHICAGO
In the above example we have taken two ref cursor
variables(v_rfe_emp,v_ref_dept) to establish the fact.
1>
Firstly we have used v_ref_emp ref cursor
variable to point the result set of the following query. (SELECT ename, deptno FROM emp WHERE empno='7499';).
2>
After that we have passed that ref cursor
variable to a procedure Show_emp_loc_dname as an argument.
3>
Inside that procedure system fetch data which
was pointed by v_emp_ref ref cursor (FETCH v_emp INTO p_ename,v_deptno;).So v_deptno
holds the deptno ( here it is 30) in which
Employee number 7499 ( Here it is Allen) works.
4>
After that we have used v_ref_dept ref cursor variable
to point out the department name and its location from dept table by using
v_deptno variable.
5>
Now if you look into the signature of Show_Emp_Loc_Dname you
will see that the v_ref_dept is declare as IN OUT variable. So after returning
from Show_Emp_Loc_Dname
in the body of main procedure system fetches the data referenced by
v_emp_ref ref cursor (FETCH v_ref_dept INTO v_dname,v_loc).
6>
Basically we have sent one ref cursor into a
procedure and from that procedure we have received another ref cursor.
7>
In next step we have print the name of the
employee have empno=7499 and his respective department and location.
Using Ref Cursor
In SQL Statements:-
CREATE OR REPLACE PACKAGE EXP_PIPE_ROW_EXAMPLE AS
TYPE
emp_data IS RECORD
(
ename emp.ename%TYPE,
empno emp.empno%TYPE,
job emp.job%TYPE,
deptno
emp.deptno%TYPE
);
TYPE
emp_data_set IS TABLE OF emp_data;
FUNCTION
Show_Emp_Record(p_deptno IN emp.deptno%TYPE) RETURN emp_data_set PIPELINED;
END EXP_PIPE_ROW_EXAMPLE;
--
CREATE OR REPLACE PACKAGE BODY EXP_PIPE_ROW_EXAMPLE
AS
FUNCTION
Show_Emp_Record(p_deptno IN emp.deptno%TYPE) RETURN emp_data_set PIPELINED
IS
v_emp_ref SYS_REFCURSOR;
v_dyn_qry VARCHAR2(2000) :=
NULL;
v_count NUMBER := 0;
v_emp_rec
emp_data;
BEGIN
v_dyn_qry
:= 'SELECT ename,empno,job,deptno from emp where deptno ='||p_deptno;
OPEN
v_emp_ref FOR v_dyn_qry;
v_count
:= 0;
LOOP
FETCH
v_emp_ref INTO v_emp_rec;
EXIT
WHEN v_emp_ref%NOTFOUND;
PIPE
ROW(v_emp_rec);
END LOOP;
CLOSE
v_emp_ref;
RETURN;
END
Show_Emp_Record;
END EXP_PIPE_ROW_EXAMPLE;
SQL>
select * from table(EXP_PIPE_ROW_EXAMPLE.Show_Emp_Record(10));
ENAME EMPNO JOB DEPTNO
---------- ---------- --------- ----------
CLARK 7782 MANAGER 10
KING 7839 PRESIDENT 10
MILLER 7934 CLERK 10
Print the Data in
SQL plus which is pointed by ref cursor variable.
In the following way we can see a contents pointed by ref
cursor variable.
SQL> CREATE OR REPLACE FUNCTION PRINT_REF_DATA
RETURN SYS_REFCURSOR IS
2 v_emp_ref SYS_REFCURSOR;
3 BEGIN
4 OPEN v_emp_ref FOR SELECT
ename,empno,job,deptno from emp;
5 RETURN v_emp_ref;
6 END ;
7 /
Function created.
SQL> VARIABLE v_emp REFCURSOR;
SQL> EXEC :v_emp := PRINT_REF_DATA;
PL/SQL procedure successfully completed.
SQL> PRINT v_emp_ref;
SP2-0552: Bind variable "V_EMP_REF" not
declared.
SQL> PRINT v_emp;
ENAME EMPNO JOB DEPTNO
---------- -------------------- ---------
--------------------
SMITH 7400 CLERK 20
SMITH 7369 CLERK 20
ALLEN 7499 SALESMAN 30
JONES 7566 MANAGER 20
MARTIN 7654 SALESMAN 30
BLAKE
7698 MANAGER 30
CLARK 7782 MANAGER 10
SCOTT 7788 ANALYST 20
KING 7839 PRESIDENT 10
TURNER
7844 SALESMAN 30
ADAMS 7876 CLERK 20
ENAME EMPNO JOB DEPTNO
---------- -------------------- ---------
--------------------
JAMES 7900 CLERK 30
FORD 7902 ANALYST 20
MILLER 7934 CLERK 10
14 rows selected.
