`

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详解

    在Oracle数据库环境中，合并多个`sys_refcursor`（也称为游标）的需求常常出现在复杂的业务逻辑中，尤其是在需要重复调用相同逻辑的存储过程时。本文将详细介绍如何通过序列化和XML处理来实现这一功能。 首先，让...

    Oracle游标的使用

    - `sys_refCursor`则代表数据集的引用或指针，它允许数据集的引用在不同的PL/SQL块之间传递，甚至可以传递到其他应用程序（如Java或JDBC）。在示例中，`add_sal`过程返回一个`sys_refCursor`，调用者可以通过`FETCH`...

    plsql.zip_PLSQL Developer_cursor

    v_refcursor SYS_REFCURSOR; BEGIN v_query := 'SELECT * FROM employees WHERE department_id = ' || dept_id; OPEN v_refcursor FOR v_query; RETURN v_refcursor; END; ``` 在这个函数中，我们动态构造了SQL...

    Oracle存储过程返回结果集

    而`SYS_REFCURSOR`是Oracle提供的一种特殊类型，它允许存储过程动态地打开一个游标（即结果集）并将其作为`OUT`参数返回。 以下是一个简单的存储过程示例，它打开一个游标并返回包含`employees`表所有列的结果集： ...

    Oracle Procedurl返回数据集的方式总结

    它允许过程动态地构建SQL查询，并将结果集作为输出参数传递给调用者。这种方式灵活且高效，特别适合处理未知或动态的查询结果。 2. **DECLARE REF CURSOR** 在过程内部，我们可以声明一个REF CURSOR变量，如`REF ...

    ORACLE读取FTP上的EXCEL文件

    RETURN SYS_REFCURSOR IS L_SHEET VARCHAR2(30) := 'Sheet1'; L_QUERY VARCHAR2(200); L_CURSOR SYS_REFCURSOR; BEGIN L_QUERY := 'SELECT * FROM EXTERNAL ''(' || 'SELECT * FROM TABLE(READ_EXCEL_...

    ORACLE 游标

    4. **REF游标**：返回一个引用，可以跨过程或函数调用传递查询结果集。 #### 显式游标示例 显式游标是最常用的类型，下面的例子展示了如何在PL/SQL块中定义和使用显式游标： ```sql DECLARE CURSOR cur_employee...

    Oracle 存储过程分页

    ROWNUM是在结果集生成时自动分配的，表示每一行的顺序号。例如，以下存储过程展示了基于ROWNUM的分页查询： ```sql CREATE OR REPLACE PROCEDURE get_paged_data (start_row IN NUMBER, page_size IN NUMBER, ...

    Oracle数据库通用的分页存储过程

    p_data OUT SYS_REFCURSOR ) AS BEGIN OPEN p_data FOR SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT /*+ FIRST_ROWS(p_page_size) */ * FROM p_table_name WHERE p_where_clause ORDER BY p_column_...

    ORACLE数据库使用动态SQL的方法.pdf

    cursor_name SYS_REFCURSOR; BEGIN stmt := 'SELECT * FROM table WHERE column = :1'; PREPARE sql_statement FROM stmt; EXECUTE sql_statement USING bind_value; OPEN cursor_name FOR stmt USING bind_...

    oracle函数调用存储过程

    CREATE OR REPLACE PROCEDURE plan_station_contrast (p_start_date IN DATE, p_end_date IN DATE, o_cursor OUT SYS_REFCURSOR) AS BEGIN OPEN o_cursor FOR SELECT * FROM some_table WHERE date_column BETWEEN...

    oracle 游标

    cursor_name SYS_REFCURSOR; BEGIN stmt := 'SELECT * FROM table_name WHERE column = :value'; OPEN cursor_name FOR stmt USING 'some_value'; -- 遍历动态游标 FOR record IN cursor_name LOOP -- 处理...

    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; ...

    Oracle PLSQL应用指南

    Ref Cursor是一种特殊的游标类型，可以作为函数的结果返回，使得调用者可以获取多个结果集。 - **创建Ref Cursor**： ```sql CREATE OR REPLACE FUNCTION get_data (OUT ref_cur SYS_REFCURSOR) AS BEGIN OPEN ...

    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 存储过程分页示例

    ROWNUM是在查询结果集生成时就确定的，表示每一行的顺序号。但需要注意的是，ROWNUM的值是基于查询结果的顺序，而不是表中的物理顺序。如果在WHERE子句中使用ROWNUM，那么Oracle会在满足条件的行中从1开始计数。 ...

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

    系统游标，通常以`sys_refcursor`类型表示，是Oracle内置的一种方式，用于处理动态SQL的结果集。下面是如何声明和使用系统游标的步骤： - 创建存储过程： ```sql create or replace procedure p_temp_procedure ...

    数据库常用操作

    CREATE OR REPLACE PROCEDURE p_cx_user1 (v_usr_name IN VARCHAR2, aCursor OUT SYS_REFCURSOR) IS BEGIN OPEN aCursor FOR SELECT * FROM sys_user WHERE usr_name = v_usr_name; END; ``` #### 解析： - `...

    Hibernate使用存储过程

    st_cursor SYS_REFCURSOR; BEGIN OPEN st_cursor FOR Select EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE, REGIONCODE, EID, VALUE, CURRENCY FROM EMPLOYMENT; RETURN st_cursor; END; ``` 要在Hibernate中使用...

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