如何从oracle存储过程中读取不同类型的输出参数

procedure SearchData
(
    p_search in number
    ,p_pages out number
    ,p_pageNumber out number
    ,p_resultSet out sys_refcursor
)

首先你应该安装oracle客户端为c#，例如:我总是使用

using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

您需要从oracle网站下载它以在c#中使用

，那么你可以像下面

那样调用过程

   OracleCommand cmd = con.CreateCommand(); //con is the oracle connection
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Connection = con;
    cmd.CommandText = "SearchData";
  OracleParameter p_search = new OracleParameter();
  p_search.OracleDbType = OracleDbType.Int64;
  p_search.Direction = ParameterDirection.Input;
  p_search.Value = .....;
  OracleParameter p_pages = new OracleParameter();
  p_pages.OracleDbType = OracleDbType.Int64;
  p_pages.Direction = ParameterDirection.Output;
  p_pages.Size = 1000;
 OracleParameter p_pageNumber = new OracleParameter();
 p_pageNumber.OracleDbType = OracleDbType.Int64;
 p_pageNumber.Direction = ParameterDirection.Output;
 p_pageNumber.Size = 1000;
 OracleParameter p_resultSet = new OracleParameter();
 p_resultSet.OracleDbType = OracleDbType.RefCursor;
 p_resultSet.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_search);
cmd.Parameters.Add(p_pages);
cmd.Parameters.Add(p_page_number);
cmd.Parameters.Add(p_resultSet);
con.Open();
cmd.ExecuteNonQuery();

执行后，你可以调用out形参来获取值，比如

string pages = p_pages.Value.ToString()

对于引用游标，我们需要使用oracle reader

OracleDataReader rd = ((OracleRefCursor)cmd.Parameters[3].Value).GetDataReader(); 
//3 is for 4rth parameter bcos parameter index start from 0

这就是我如何解决我的需求

   //sample result entity 
   public class SearchResult
    {
        public int NumberOfPagesAvailable { get; set; }
        public int CurrentPageNumber { get; set; }
        public IEnumerable<ResultItem> ResultItems { get; set; }
    }

        /// <summary>
        /// Method to Create an output parameter
        /// </summary>
        /// <param name="paramName">The Parameter name</param>
        /// <param name="paramType">Type of the parameter</param>
        /// <param name="value">The value to set</param>
        /// <returns></returns>
        public static OracleParameter CreateOutputParameter(string paramName, OracleDbType paramType, object value)
        {
            var outParam = new OracleParameter(paramName, paramType, ParameterDirection.Output)
            {
                Value = value ?? DBNull.Value
            };
            return outParam;
        }

        //1     create command
        //2     created the params as below     
            OracleParameter pages = OracleHelper.CreateOutputParameter("p_pages", OracleDbType.Int64, null);
            OracleParameter pageNumber = OracleHelper.CreateOutputParameter("p_pageNumber", OracleDbType.Int64, null);
            OracleParameter resultSet = OracleHelper.CreateOutputParameter("p_resultSet");
        //3     execute using oralce command
        //4     read the param values as below after executing the command
           if (pages.Value != null)
            {
                result.NumberOfPagesAvailable = Convert.ToInt32(pages.Value.ToString());
            }
            if (pageNumber.Value != null)
            {
                result.CurrentPageNumber = Convert.ToInt32(pageNumber.Value.ToString());
            }
                      if (resultSet.Value != null && result.NumberOfPagesAvailable>0 && result.CurrentPageNumber>0)
            {
                OracleRefCursor refCursor;
                    using (refCursor = (OracleRefCursor)resultSet.Value)
                    {
                        using (OracleDataReader rdr = refCursor.GetDataReader())
                        {
                           //iterate through the loop and read the values from the reader
//set ResultItems 
                        }
                    }
            }