.NET Framework Oracle REF CURSOR数据类型

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.ConnectionStrings["scott"].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)).Direction = ParameterDirection.Output;
            conn.Open();
            OracleDataReader rdr = comm.ExecuteReader();
                GridView1.DataSource = rdr;
                GridView1.DataBind();          
            conn.Close();
        }
    }
}

示例:使用 OracleDataReader 从多个 REF CURSOR 检索数据

OracleConnection conn;           
            using (conn = new OracleConnection(OracleConnectionString))
            {
                conn.Open();
                OracleCommand comm = new OracleCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = "curspkg.open_two_cursors";
                comm.Parameters.Add(new OracleParameter("empcursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
                comm.Parameters.Add(new OracleParameter("deptcursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
                
                OracleDataReader rdr = comm.ExecuteReader();
                GridView2.DataSource = rdr;
                GridView2.DataBind();
                rdr.NextResult();
                GridView3.DataSource = rdr;
                GridView3.DataBind();
                rdr.Close();
            }