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>
Thiѕ post ѡil help the internet users for creating neew webpage or even a weblog from start to end.