asp.net oracle 存储过程

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;

C#代码

  /// <summary>
        /// 执行oracle存储过程返回多个结果集
        /// </summary>
        /// <param name="strProcName">存储过程名称</param>
        /// <param name="ResultCount">返回个数</param>
        /// <param name="paras">参数</param>
        /// <returns>任意对象数组</returns>
        public  object[] ExcuteProc_N_Result(string strProcName, int ResultCount, params OracleParameter[] paras)
        {
            using (OracleConnection conn = new OracleConnection("User ID=用户名;Password=密码;Data Source=数据库;"))
            {
                OracleCommand cmd = new OracleCommand(strProcName, conn);
                if (paras != null && paras.Length > 0)
                {
                    for (int j = 0; j < paras.Length; j++)
                    {
                        if (paras[j].Value == null)
                        {
                            paras[j].Value = DBNull.Value;
                        }
                    }
                }
                cmd.Parameters.AddRange(paras);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                cmd.ExecuteNonQuery();
                int i = 0;
                //int nOutputParametersCount = 0;
                object[] objResult = new object[ResultCount];
                foreach (OracleParameter p in cmd.Parameters)
                {
                    if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.InputOutput)
                    {

                        if (p.Value is OracleDataReader)
                        {
                            OracleDataReader reader = p.Value as OracleDataReader;
                            objResult[i++] = ConvertDataReaderToDataTable(reader);
                        }
                        else
                        {
                            objResult[i++] = p.Value;
                        }
                    }

                }
                return objResult;
            }
        }

        /// <summary> 
        /// 将DataReader 转为 DataTable 
        /// </summary> 
        /// <param name="DataReader">OleDbDataReader</param> 
        protected  DataTable ConvertDataReaderToDataTable(OracleDataReader reader)
        {
            DataTable objDataTable = new DataTable("TmpDataTable");
            try
            {
                int intFieldCount = reader.FieldCount;//获取当前行中的列数；
                for (int intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
                {
                    objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
                }
                //populate   datatable   
                objDataTable.BeginLoadData();
                //object[]   objValues   =   new   object[intFieldCount   -1];   
                object[] objValues = new object[intFieldCount];
                while (reader.Read())
                {
                    reader.GetValues(objValues);
                    objDataTable.LoadDataRow(objValues, true);
                }
                reader.Close();
                objDataTable.EndLoadData();
                return objDataTable;
            }
            catch (Exception ex)
            {
                throw new Exception("转换出错出错!", ex);
            }
        }

调用方法

OracleParameter[] oracleParameter = new OracleParameter[]{
new OracleParameter("MYCS1",OracleType.Cursor),
new OracleParameter("MYCS2",OracleType.Cursor),
new OracleParameter("a",OracleType.VarChar,200),
};

oracleParameter[0].Direction = ParameterDirection.Output;
oracleParameter[1].Direction = ParameterDirection.Output;
oracleParameter[2].Direction = ParameterDirection.Output;

object[] xxx = ExcuteProc_N_Result("gd_CURSOR", 3, oracleParameter);