Ref Cursor In SQL

Few days ago – one my client has requested to implement ref-cursor in a SQL. Initially, i was not so keen to implement that and finally prepared the solutions after spending some time with the functional logic and implement it. So, i thought – it might be useful for beginners to demonstrate the use of ref-cursor in SQL.

Query:

Refcursor in SQL

Solution:

satyaki>
satyaki>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>create table t_emp
 2  as
 3    select empno,
 4           ename,
 5           mgr,
 6           sal
 7    from emp
 8    where 1=2;

Table created.

Elapsed: 00:00:00.05
satyaki>
satyaki>set lin 80
satyaki>
satyaki>desc t_emp;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------
EMPNO                                              NUMBER(4)
ENAME                                              VARCHAR2(10)
MGR                                                NUMBER(4)
SAL                                                NUMBER(7,2)

satyaki>
satyaki>
satyaki>set lin 310
satyaki>
satyaki>select * from t_emp;

no rows selected

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>create or replace type etype as object
 2    (
 3       empno number,
 4       ename varchar2(10),
 5       mgr   number,
 6       sal   number
 7    );
 8  / 

Type created.

Elapsed: 00:00:01.03
satyaki>
satyaki>create or replace type t_etype as table of etype;
 2  / 

Type created.

Elapsed: 00:00:00.02
satyaki>
satyaki>create or replace function get_dept_emps(p_deptno in number)
 2  return sys_refcursor
 3  is
 4      v_rc sys_refcursor;
 5  begin
 6     open v_rc for 'select empno, ename, mgr, sal from emp where deptno = :deptno' using p_deptno;
 7     return v_rc;
 8  end;
 9  / 

Function created.

Elapsed: 00:00:00.05
satyaki>
satyaki>
satyaki>create or replace function fetch_emps(deptno in number := null)
 2  return t_etype
 3  is
 4      v_emptype t_etype := t_etype();  -- Declare a local table structure and initialize it
 5      v_cnt     number := 0;
 6      v_rc      sys_refcursor;
 7      v_empno   number;
 8      v_ename   varchar2(10);
 9      v_mgr     number;
10      v_sal     number;
11  begin
12      v_rc := get_dept_emps(deptno);
13      loop
14        fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
15        exit when v_rc%NOTFOUND;
16         v_emptype.extend;
17         v_cnt := v_cnt + 1;
18         v_emptype(v_cnt) := etype(v_empno, v_ename, v_mgr, v_sal);
19      end loop;
20      close v_rc;
21      return v_emptype;
22  end;
23  / 

Function created.

Elapsed: 00:00:00.06
satyaki>
satyaki>
satyaki>select * from t_emp;

no rows selected

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>
satyaki>select * from t_emp;

no rows selected

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>insert into t_emp
 2  select *
 3  from table(fetch_emps(30));

4 rows created.

Elapsed: 00:00:00.02
satyaki>
satyaki>select * from t_emp;

    EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
     7654 MARTIN           7698       1815
     7844 TURNER           7698       2178
     7900 JAMES            7698     1379.4
     7599 BILLY            7566       4500

Elapsed: 00:00:00.00
satyaki>
satyaki>commit;

Commit complete.

Elapsed: 00:00:00.00
satyaki>