`

ref cursor sys_refcursor传递结果集

 
阅读更多
  1. SQL>set linesize 200 pages 100
  2. SQL> variable v refcursor;
  3. SQL>exec open :v for'select * from emp';
  5. PL/SQL procedure successfully completed.
  7. SQL>print:v;
  9. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
  10. --------------------------------------------------------------------------------------------------------------------------------
  11. 7369 SMITH CLERK 790217-DEC-8080020 RESEARCH
  12. 7499 ALLEN SALESMAN 769820-FEB-81160030030 noname
  13. 7521 WARD SALESMAN 769822-FEB-81125050030 noname
  14. 7566 JONES MANAGER 783902-APR-81297520 RESEARCH
  15. 7654 MARTIN SALESMAN 769828-SEP-811250140030 noname
  16. 7698 BLAKE MANAGER 783901-MAY-81285030 noname
  17. 7782 CLARK MANAGER 783909-JUN-81245010 ACCOUNTING
  18. 7788 SCOTT ANALYST 756619-APR-87300020 RESEARCH
  19. 7839 KING PRESIDENT 17-NOV-81500010 ACCOUNTING
  20. 7844 TURNER SALESMAN 769808-SEP-811500030 noname
  21. 7876 ADAMS CLERK 778823-MAY-87110020 RESEARCH
  22. 7900 JAMES CLERK 769803-DEC-8195030 noname
  23. 7902 FORD ANALYST 756603-DEC-81300020 RESEARCH
  24. 7934 MILLER CLERK 778223-JAN-82130010 ACCOUNTING
  25. 1111 YODA JEDI 17-NOV-815000 noname
  27. 15 rows selected.
  30. sys_refcursor 做为参数传递结果集:
  31. create or replace procedure pr_sys_refcursor(v_sys out sys_refcursor)
  32. as
  33. BEGIN
  34. open v_sys for'select * from emp';
  35. end;
  36. /
  38. declare
  39. type emp_type is table of emp%rowtype;
  40. emp_tab emp_type;
  41. v sys_refcursor;
  42. BEGIN
  43. pr_sys_refcursor(v);
  44. fetch v bulk collect into emp_tab;
  45. for i in1..emp_tab.count LOOP
  46. dbms_output.put_line(emp_tab(i).ename||','||emp_tab(i).empno);
  47. end loop;
  48. end;
  52. sys_refcursor 函数返回表
  53. create or replace function f_get_emp return sys_refcursor
  54. is
  55. v_emp sys_refcursor;
  56. BEGIN
  57. open v_emp for'select * from emp';
  58. return v_emp;
  59. end;
  60. /
  62. SQL>select f_get_emp from dual;
  64. F_GET_EMP
  65. --------------------
  66. CURSOR STATEMENT :1
  68. CURSOR STATEMENT :1
  70. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
  71. --------------------------------------------------------------------------------------------------------------------------------
  72. 7369 SMITH CLERK 790217-DEC-8080020 RESEARCH
  73. 7499 ALLEN SALESMAN 769820-FEB-81160030030 noname
  74. 7521 WARD SALESMAN 769822-FEB-81125050030 noname
  75. 7566 JONES MANAGER 783902-APR-81297520 RESEARCH
  76. 7654 MARTIN SALESMAN 769828-SEP-811250140030 noname
  77. 7698 BLAKE MANAGER 783901-MAY-81285030 noname
  78. 7782 CLARK MANAGER 783909-JUN-81245010 ACCOUNTING
  79. 7788 SCOTT ANALYST 756619-APR-87300020 RESEARCH
  80. 7839 KING PRESIDENT 17-NOV-81500010 ACCOUNTING
  81. 7844 TURNER SALESMAN 769808-SEP-811500030 noname
  82. 7876 ADAMS CLERK 778823-MAY-87110020 RESEARCH
  83. 7900 JAMES CLERK 769803-DEC-8195030 noname
  84. 7902 FORD ANALYST 756603-DEC-81300020 RESEARCH
  85. 7934 MILLER CLERK 778223-JAN-82130010 ACCOUNTING
  86. 1111 YODA JEDI 17-NOV-815000 noname
  88. 15 rows selected.
  92. ref cursor 做为参数传递结果集
  93. create or replace package pkg_ref_cursor
  94. as
  95. type ref_type isref cursor;
  96. function f_ref return ref_type;
  97. end;
  98. /
  100. create or replace package body pkg_ref_cursor
  101. is
  102. function f_ref return ref_type
  103. is
  104. cursor_ref ref_type;
  105. BEGIN
  106. open cursor_ref for'select * from emp';
  107. return cursor_ref;
  108. end;
  109. end;
  110. /
  114. SQL>select pkg_ref_cursor.f_ref from dual;
  116. F_REF
  117. --------------------
  118. CURSOR STATEMENT :1
  120. CURSOR STATEMENT :1
  122. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
  123. --------------------------------------------------------------------------------------------------------------------------------
  124. 7369 SMITH CLERK 790217-DEC-8080020 RESEARCH
  125. 7499 ALLEN SALESMAN 769820-FEB-81160030030 noname
  126. 7521 WARD SALESMAN 769822-FEB-81125050030 noname
  127. 7566 JONES MANAGER 783902-APR-81297520 RESEARCH
  128. 7654 MARTIN SALESMAN 769828-SEP-811250140030 noname
  129. 7698 BLAKE MANAGER 783901-MAY-81285030 noname
  130. 7782 CLARK MANAGER 783909-JUN-81245010 ACCOUNTING
  131. 7788 SCOTT ANALYST 756619-APR-87300020 RESEARCH
  132. 7839 KING PRESIDENT 17-NOV-81500010 ACCOUNTING
  133. 7844 TURNER SALESMAN 769808-SEP-811500030 noname
  134. 7876 ADAMS CLERK 778823-MAY-87110020 RESEARCH
  135. 7900 JAMES CLERK 769803-DEC-8195030 noname
  136. 7902 FORD ANALYST 756603-DEC-81300020 RESEARCH
  137. 7934 MILLER CLERK 778223-JAN-82130010 ACCOUNTING
  138. 1111 YODA JEDI 17-NOV-815000 noname
  140. 15 rows selected.
  152. REF 参照类型(ref cursor 程序间传递结果集)
  153. create or replace package ref_package as
  154. TYPE emp_record_type IS RECORD(
  155. ename VARCHAR2(25),
  156. job VARCHAR2(10),
  157. sal NUMBER(7,2));
  158. TYPE weak_ref_cursor IS REF CURSOR;--弱类型，不规定返回值
  159. TYPE strong_ref_cursor IS REF CURSOR return emp%rowtype;--强类型，规定返回值
  160. TYPE strong_ref2_cursor IS REF CURSOR return emp_record_type;--强类型，规定返回值
  161. end ref_package;
  162. /
  164. 弱类型ref测试:
  165. create or replace procedure test_ref_weak(p_deptno emp.deptno%type,
  166. p_cursor out ref_package.weak_ref_cursor)is
  167. begin
  168. case p_deptno
  169. when10then
  170. open p_cursor for
  171. select empno, ename, sal, deptno from emp where deptno = p_deptno;
  172. when20then
  173. open p_cursor for
  174. select*from emp where deptno = p_deptno;
  175. endcase;
  176. end;
  177. /
  179. var c refcursor
  180. exec test_ref_weak(10,:c);--传入不同形式参数，走不同分支，返回不同结果集！
  181. print c
  182. exec test_ref_weak(20,:c);
  183. print c
  185. oracle 9i中定义了系统弱游标类型 sys_refcursor
  186. create or replace procedure test_p(p_deptno number,
  187. p_cursor out sys_refcursor)is
  188. begin
  189. open p_cursor for
  190. select*from emp where deptno = p_deptno;
  191. end test_p;
  192. /
  194. create or replace function getemp return sys_refcursor as
  195. emp_cursor sys_refcursor;
  196. begin
  197. open emp_cursor for
  198. select*from emp;
  199. return emp_cursor;
  200. end;
  201. /
  202. select getemp from dual;
  205. 强类型ref测试:查询结构必须符合游标返回值结构，否则报错：
  206. PLS-00382: expression is of wrong type
  207. create or replace procedure test_ref_strong(p_deptno emp.deptno%type,
  208. p_cursor out ref_package.strong_ref_cursor)is
  209. begin
  210. open p_cursor for
  211. select*from emp where deptno = p_deptno;
  212. end test_ref_strong;
  213. /
  215. var c refcursor
  216. exec test_ref_strong(10,:c);
  217. print c;
  219. create or replace procedure test_call is
  220. c_cursor ref_package.strong_ref_cursor;
  221. r_emp emp%rowtype;
  222. begin
  223. test_ref_strong(10, c_cursor);
  224. loop
  225. fetch c_cursor
  226. into r_emp;
  227. exitwhen c_cursor%notfound;
  228. dbms_output.put_line(r_emp.ename);
  229. end loop;
  230. close c_cursor;
  231. end test_call;
  232. /
  234. exec test_call;
  236. 强类型ref测试:
  237. create or replace procedure test_ref2_strong(p_deptno emp.deptno%type,
  238. p_cursor out ref_package.strong_ref2_cursor)is
  239. begin
  240. open p_cursor for
  241. select ename, job, sal from emp where deptno = p_deptno;
  242. end test_ref2_strong;
  243. /
  245. var c refcursor
  246. exec test_ref2_strong(10,:c);
  1. 11g dbms_sql可以解析出来或者传给外部程序解析
  3. 和table结合要写管道函数的
  7. DECLARE
  8. TYPE index_by_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  9. index_by_table index_by_type;
  10. v_num number :=1;
  11. BEGIN
  12. --Populate index by table
  13. FOR i IN 7..10 LOOP
  14. index_by_table(v_num):= i;
  15. v_num := v_num +1;
  16. END LOOP;
  18. v_num := index_by_table.first();
  19. dbms_output.put_line(v_num);
  20. v_num := index_by_table.first;
  21. dbms_output.put_line(v_num);
  22. v_num := index_by_table(3);
  23. dbms_output.put_line(v_num);
  24. end;
  27. create or replace package PCKG_CSM_DATA_STL_SET is
  29. TYPE RET_PATH_SET IS TABLE OF NUMBER;
  31. TYPE CV_TYPE IS REF CURSOR;
  33. FUNCTION F_RET_PATH_SET(I_PRTN_ID NUMBER) RETURN RET_PATH_SET PIPELINED;
  34. end;
  35. /
  37. create or replace package body PCKG_CSM_DATA_STL_SET is
  38. FUNCTION F_RET_PATH_SET(I_PRTN_ID NUMBER) RETURN RET_PATH_SET PIPELINED is
  39. CV SYS_REFCURSOR;
  40. V_PRTN_ID PCKG_CSM_DATA_STL_SET.RET_PATH_SET;
  41. RS NUMBER;
  42. BEGIN
  43. OPEN CV FOR select empno from emp where deptno=I_PRTN_ID;
  44. FETCH CV BULK COLLECT
  45. INTO V_PRTN_ID;
  46. RS := V_PRTN_ID.FIRST();
  47. WHILE RS IS NOT NULL LOOP
  48. PIPE ROW(V_PRTN_ID(RS));
  49. RS := V_PRTN_ID.NEXT(RS);
  50. END LOOP;
  51. RETURN;
  52. END F_RET_PATH_SET;
  53. end;
  54. /

 

参考至：http://blog.csdn.net/ashic/article/details/52140232

如有错误，欢迎指正

邮箱：czmcj@163.com

 

 

分享到：
| Duplicate a Database Using RMAN in Oracl ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle如何合并多个sys_refcursor详解

    在数据开发中，有时你需要合并两个动态游标sys_refcursor。 开发一个存储过程PROC_A,这个过程业务逻辑相当复杂，代码篇幅较长。一段时间后要开发一个PROC_B,要用PROC_A同样的逻辑,而且在这个过程中，还要循环调用...

    Oracle存储过程返回游标实例详解

    有俩种方法: 一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为 in out 或out (1)声明个人系统游标.(推荐) 代码如下: create or replace p_temp_procedure ( cur_arg out sys_refcursor;...

    asp.net中oracle 存储过程（图文）

    CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR,MYCS2 OUT SYS_REFCURSOR,a out varchar)as BEGIN a:='test'; OPEN MYCS1 FOR SELECT 1 from dual; OPEN MYCS2 FOR SELECT 2 from dual; END; ...

    PL_SQL模块学习之十、游标

    文章目录游标1.1 显式游标1.1.1 创建游标1.1.2 打开游标1.1.3 获取数据1.1.4 关闭游标1.1.5 使用实例1.2 隐式游标1.2.2 游标属性1.3 ...详情参考oracle中cursor与refcursor及sys_refcursor的区别 1.1 显式游标 1.1.1 创

    oracle 在一个存储过程中调用另一个返回游标的存储过程

    第一种情况是返回的游标是某个具体的表或视图的数据，如： SQL-Code: 代码如下: CREATE OR REPLACE PROCEDURE P_TESTA ( PRESULT OUT SYS_REFCURSOR ) AS BEGIN OPEN PRESULT FOR SELECT * FROM USERS; END P_TESTA;...

    oracle常用sql查询语句部分集合(图文)

    主要介绍了oracle常用sql查询语句部分，并用图文并茂的方式为大家进程实例说明，需要的朋友可以参考下

    Oracle生成单据编号存储过程的实例代码

    procedure Pro_GetBillNO(TypeTable in varchar2,cur_mycursor out sys_refcursor) as DReceiptCode varchar2(40); DReceiptName varchar2(50); DPrefix1 varchar2(50); DISO varchar2(50); DIsAutoCreate varchar2...

    asp.net中调用oracle存储过程的方法

    存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，...CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR

    建立新年分区脚本.txt

    my_cur sys_refcursor; val varchar2(1000); v_year varchar2(50); v_max_part_name varchar2(256); v_maxdate_part_name varchar2(256); v_part_tablespace varchar2(256); v_part_sums int; begin for v_...

    Oracle P/L SQL实现发送Email、浏览网页等网络操作功能

    Type DynamicCursor is ref Cursor; --动态游标 --Purpose : 获得汉字拼音编码 Type ut_PYIndex_191_List is Varray( 191 ) OF Number; Type ut_PYIndex_List is Varray( 10 ) OF ut_PYIndex_191_...

    Turbo C++ 3.0[DISK]

    #pragma extref _floatconvert This will force inclusion of floating point formats, which may not be linked to reduce executable size. COMPILER - The default extension for source files to the ...

    Turbo C++ 3.00[DISK]

    #pragma extref _floatconvert This will force inclusion of floating point formats, which may not be linked to reduce executable size. COMPILER - The default extension for source files to the ...

Magicbox
Global site tag (gtag.js) - Google Analytics