1、创建一个带输出数据集的Oracle存储过程

create or replace procedure PRO_test(in_top in number,cur_out out sys_refcursor) is
--查询指定记录条数的数据，并返回总共记录数，返回多个数据集
begin
open cur_out for
SELECT * FROM dept_dict where rownum < in_top;
end PRO_test;

2、C#调用

Pu_Sys.GetConnObject con = new Pu_Sys.GetConnObject();
OracleConnection conn = new OracleConnection(con.Get_ConnStr());
OracleCommand Dcomm = new OracleCommand("PRO_TEST", conn);
Dcomm.CommandType = CommandType.StoredProcedure;

OracleParameter DpPass = new OracleParameter();
DpPass.Value = 5;
DpPass.Direction = System.Data.ParameterDirection.Input;
DpPass.ParameterName = "in_top";
Dcomm.Parameters.Add(DpPass);

OracleParameter DpOut = new OracleParameter("cur_out", OracleType.Cursor);
DpOut.Direction = System.Data.ParameterDirection.Output;
Dcomm.Parameters.Add(DpOut);
OracleDataAdapter Da = new OracleDataAdapter(Dcomm);
DataSet Ds = new DataSet();
try
{
Da.Fill(Ds);
}
catch (Exception)
{

}

conn.Close();
conn.Dispose();