Oracle开发之REF_CURSOR.doc

前言关于 REF_CURSOR 我们已经不再陌生，在前边的博客我们已经接触过了 REF_CURSOR：在博客怎样让 Oracle 的存储过程返回结果集中我们在一个返回结果集的 Hello World 级别的存储过程实例中用到了 REF_CURSOR，在博客烟草局绩效考核系统打分模块开发笔记中我们在一个真实的项目中体会到 REF_CURSOR 给我们带来的神奇效果。今天，我们将通过学习 Oracle 官方的这篇Oracle 开发之 REF_CURSOR进一步解开 REF_CURSOR 的神秘面纱，通过本文的学习，我们对 REF_CURSOR 的理解将会更加深入，而在以后的项目中对它的使用也必将更加规范。正文Oracle REF_CURSOR 简介使用 REF_CURSOR 我们可以从存储过程中得到一个结果集对象。REF_CURSOR 分为两种基本类型：强类型 REF_CURSOR 和弱类型 REF_CURSOR，强类型 REF_CURSOR 返回的数据类型和长度在编译期就应该指明，而弱类型 REF_CURSOR 不需要。强类型 REF_CURSOR 和 Oracle 9i 之前的弱类型 REF_CURSOR 在包中应该这样定义：view plainprint?1. create or replace package REFCURSOR_PKG as 2. TYPE WEAK8i_REF_CURSOR IS REF CURSOR; 3. TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE; 4. end REFCURSOR_PKG; view plaincopy to clipboardprint?1. create or replace package REFCURSOR_PKG as 2. TYPE WEAK8i_REF_CURSOR IS REF CURSOR; 3. TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE; 4. end REFCURSOR_PKG; 返回 REF_CURSOR 的 PL/SQL 存储过程的编写示例：view plainprint?1. /* until Oracle 9 */ 2. create or replace procedure test( p_deptno IN number 3. , p_cursor OUT 4. REFCURSOR_PKG.WEAK8i_REF_CURSOR) 5. is 6. begin 7. open p_cursor FOR 8. select * 9. from emp 10. where deptno = p_deptno; 11. end test; view plaincopy to clipboardprint?1. /* until Oracle 9 */ 2. create or replace procedure test( p_deptno IN number 3. , p_cursor OUT 4. REFCURSOR_PKG.WEAK8i_REF_CURSOR) 5. is 6. begin 7. open p_cursor FOR 8. select * 9. from emp 10. where deptno = p_deptno; 11. end test; Oracle 9i 之后，我们可以使用 SYS_REFCURSOR 作为 REF_CURSOR 的返回类型，例如：view plainprint?1. /* From Oracle 9 */ 2. create or replace procedure test( p_deptno IN number 3. , p_cursor OUT SYS_REFCURSOR) 4. is 5. begin 6. open p_cursor FOR 7. select * 8. from emp 9. where deptno = p_deptno; 10. end test; 11.12.13. /* Strong type */ 14.15. create or replace procedure test( p_deptno IN number 16. , p_cursor OUT REFCURSOR_PKG.STRONG 17. REF_CURSOR) 18. is 19. begin 20. open p_cursor FOR 21. select * 22. from emp 23. where deptno = p_deptno; 24. end test; view plaincopy to clipboardprint?1. /* From Oracle 9 */ 2. create or replace procedure test( p_deptno IN number 3. , p_cursor OUT SYS_REFCURSOR) 4. is 5. begin 6. open p_cursor FOR 7. select * 8. from emp 9. where deptno = p_deptno; 10. end test; 11.12.13. /* Strong type */ 14.15. create or replace procedure test( p_deptno IN number 16. , p_cursor OUT REFCURSOR_PKG.STRONG 17. REF_CURSOR) 18. is 19. begin 20. open p_cursor FOR 21. select * 22. from emp 23. where deptno = p_deptno; 24. end test; JDBC 对 REF_CURSOR 的调用我们可以在 JDBC 中使用以下 Java 代码来得到 REF_CURSOR：view plainprint?1. public void method() throws SQLException 2. Connection conn = getConnection(); 3. CallableStatement cstmt = null; 4. ResultSet rs = null; 5. int deptno = 10; 6. Object temp; 7. try 8. cstmt = conn.prepareCall(begin test(?,?); end;); 9. cstmt.setInt(1, deptno); 10. cstmt.registerOutParameter(2, OracleTypes.CURSOR); 11. cstmt.execute(); 12. rs = (ResultSet) cstmt.getObject(2); 13. ResultSetMetaData rsm = rs.getMetaData(); 14. int columnCount = rsm.getColumnCount(); 15. while (rs.next() 16. for (int j=0;j columnCount;j+) 17. temp = rs.getObject(j+1); 18. 19. 20. finally 21. if (!rs=null) 22. rs.close(); 23. 24. if (!stmt=null) 25. stmt.close(); 26. 27. if (!conn=null) 28. conn.close(); 29. 30. 31. view plaincopy to clipboardprint?1. public void method() throws SQLException 2. Connection conn = getConnection(); 3. CallableStatement cstmt = null; 4. ResultSet rs = null; 5. int deptno = 10; 6. Object temp; 7. try 8. cstmt = conn.prepareCall(begin test(?,?); end;); 9. cstmt.setInt(1, deptno); 10. cstmt.registerOutParameter(2, OracleTypes.CURSOR); 11. cstmt.execute(); 12. rs = (ResultSet) cstmt.getObject(2); 13. ResultSetMetaData rsm = rs.getMetaData(); 14. int columnCount = rsm.getColumnCount(); 15. while (rs.next() 16. for (int j=0;j 2000;LoopFatch vRefCurA InTo vTempA;Exit When vRefCurA%NotFound;DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount| | vTempA.eno| |vTempA.ename | |vTempA.sal)End Loop;Close vRefCurA;DBMS_OUTPUT.PUT_LINE(-);Open vRefCurB For Select ename from emp Where SAL 2000;LoopFatch vRefCurB InTo vTempB;Exit When vRefCurB%NotFound;DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount| |vTempB)End Loop;Close vRefCurB;DBMS_OUTPUT.PUT_LINE(-); Open vRefCurA For Select * from emp Where JOB = CLERK;LoopFatch vRefCurA InTo vTempA;Exit When vRefCurA%NotFound;DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount| | vTempA.eno| |vTempA.ename | |vTempA.sal)End Loop;Close vRefCurA;End;例子：弱类型REF游标DeclareType MyRefCur IS Ref Cursor;vRefCur MyRefCur;vtemp vRefCur%RowType;BeginCase(&n)When 1 Then Open vRefCur For Select * from emp;When 2 Then Open vRefCur For Select * from dept;ElseOpen vRefCur For Select eno, ename from emp Where JOB = CLERK;End Case;Close vRefCur;End;6，怎样让REF游标作为参数传递？这个是经过修改的，可以运行的程序：DeclareType MyRefCurA IS REF CURSOR ;vRefCurA MyRefCurA;vRefCurB MyRefCurA;vTempA emp%RowType;vTempB emp.ename%Type;BeginOpen vRefCurA For Select * from emp Where SAL 2000;LoopFetch vRefCurA InTo vTempA;Exit When vRefCurA%NotFound;DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount| | vTempA.empno| |vTempA.ename | |vTempA.sal);End Loop;Close vRefCurA;DBMS_OUTPUT.PUT_LINE(-);Open vRefCurB For Select ename from emp Where SAL 2000;LoopFetch vRefCurB InTo vTempB;Exit When vRefCurB%NotFound;DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount| |vTempB);End Loop;Close vRefCurB;DBMS_OUTPUT.PUT_LINE(-); Open vRefCurA For Select * from emp Where JOB = CLERK;LoopFetch vRefCurA InTo vTempA;Exit When vRefCurA%NotFound;DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount| | vTempA.empno| |vTempA.ename | |vTempA.sal);End Loop;Close vRefCurA;End;2.1.要执行返回 REF CURSOR 的存储过程，必须在 OracleParameterCollection 中定义参数，包括 Cursor 的 OracleType 以及 Output 的 Direction。 数据提供程序只支持作为输出参数绑定 REF CURSOR。示例:REF CURSOR 示例(使用 Oracle Scott/Tiger 架构中定义的表)创建 Oracle 包和包正文CREATE OR REPLACE PACKAGE CURSPKG AS TYPE T_CURSOR IS REF CURSOR; PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, IO_CURSOR IN OUT T_CURSOR); PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOR OUT T_CURSOR);END CURSPKG;/ CREATE OR REPLACE PACKAGE BODY CURSPKG AS PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, IO_CURSOR IN OUT T_CURSOR) IS V_CURSOR T_CURSOR; BEGIN IF N_EMPNO 0 THEN OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO = N_EMPNO; ELSE OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; END IF; IO_CURSOR := V_CURSOR; END OPEN_ONE_CURSOR; PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOR OUT T_CURSOR) IS V_CURSOR1 T_CURSOR; V_CURSOR2 T_CURSOR; BEGIN OPEN V_CURSOR1 FOR SELECT * FROM EMP; OPEN V_CURSOR2 FOR SELECT * FROM DEPT; EMPCURSOR := V_CURSOR1; DEPTCURSOR := V_CURSOR2; END OPEN_TWO_CURSORS; END CURSPKG;/示例:OracleDataReader 中的 REF CURSOR 参数 using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Configuration;using System.Data;using System.Data.OracleClient;namespace pro public partial class WebForm4 : System.Web.UI.Page string OracleConnectionString = ConfigurationManager.ConnectionStringsscott.ConnectionString; protected void Page_Load(object sender, EventArgs e) OracleConnection conn = new OracleConnection(OracleConnectionString); OracleCommand comm = new OracleCommand(); comm.Connection = conn; comm.CommandType = CommandType.StoredProcedure; comm.CommandText = curspkg.open_one_cursor; comm.Parameters.Add(new OracleParameter(n_empno, OracleType.Number).Value = 0; comm.Parameters.Add(new OracleParameter(io_cursor, OracleType.Cursor).Directi