.net调用带游标输出的oracle存储过程

参数配置

        OracleParameter[] paras=
        {
            new OracleParameter("p_stdate",OracleType.DateTime),
            new OracleParameter("p_eddate",OracleType.DateTime),
            new OracleParameter("p_pinx",OracleType.VarChar),
            new OracleParameter("p_cursor",OracleType.Cursor)
        };
        paras[0].Value=DateTime.Parse(stdate).AddYears(-1);
        paras[1].Value = DateTime.Parse(eddate).AddYears(-1);
        paras[2].Value=pxnum;
        paras[0].Direction = ParameterDirection.Input;
        paras[1].Direction = ParameterDirection.Input;
        paras[2].Direction = ParameterDirection.Input;
        paras[3].Direction = ParameterDirection.Output;
        //paras[3].IsNullable = true;
        DataTable dt = ohr.QueryBySqlProc("GetNdxse",paras);

OracleHelper

    /// <summary>
    /// 通过SQL存储过程进行查询
    /// </summary>
    /// <param name="sqlProc">要执行的存储过程名</param>
    /// <param name="prams">该查询语句所需要的参数</param>
    /// <returns>返回查询的数据集</returns>
    public DataTable QueryBySqlProc(string sqlProc, OracleParameter[] prams)
    {
        using (OracleConnection conn = GetOraConn())
        {
            using (OracleCommand cmd = new OracleCommand())
            {
                PrepareCommand(conn, cmd, null, sqlProc, prams);
                cmd.CommandType = CommandType.StoredProcedure;
                OracleDataReader odr = null;
                try
                {
                    conn.Open();
                    odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    //conn.Close();
                }
                catch (OracleException e)
                {
                    throw new Exception(e.Message);
                }

                DataTable dt = ConvertOraDataReader2DataTable(odr);
                return dt;
            }
        }
    }

存储过程

create or replace procedure GetNdxse(p_stdate in date,p_eddate in date,p_pinx in varchar2,p_cursor out sys_refcursor) is
begin
  open p_cursor for
  SELECT FY.FCY_0,FCYNAM_0,PXNUM_0,AMT1_0 from FACILITY FY 
            LEFT OUTER JOIN ( 
            select zd.fcy_0,zd.pxnum_0,round(sum(zd.amt1_0)/10000,2) as amt1_0 from zinvcrd zd 
            where zd.invdat_0>=p_stdate and zd.invdat_0<p_eddate
            and zd.cce6_0='001' and zd.acccod_0='1405' and zd.cpy_0='S00' and pxnum_0= p_pinx
            group by zd.fcy_0,zd.pxnum_0) XL 
            ON FY.FCY_0=XL.FCY_0 
            WHERE FY.YSFYSBM_0='2' ORDER BY FCY_0 DESC ;
end GetNdxse;

需要注意的几点:

　　参数名,参数类型,参数个数要对,特别是参数名要和存储过程里面的参数名一模一样,游标的输出类型要用output

　　参数值为null的话有可能报参数值或参数个数不对的错误,参数在.net里面无需加:(冒号)

　　(转载)经过试验，也可以用如下方法用自己的参数名，而不用默认的参数名。
　　也可以，在一个PROCEDURE中返回多个 CURSOR
　　我的存储过程：

转载的存储过程

　Procedure STATIC_USER_SelectAll
　　( cur_OUT_f OUT T_OUT, cur_OUT_g OUT T_OUT)
　　AS
　　Begin
　　OPEN cur_OUT_f FOR Select * from STATIC_USER;
　　OPEN cur_OUT_g FOR Select * from STATIC_ROLE;
　　End;

转载的代码

　　Database db = DatabaseFactory.CreateDatabase("oraserver");
　　string sqlCommand = "Static_UserPackage.STATIC_USER_SelectAll";
　　Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleCommandWrapper dbCommandWrapper =(Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleCommandWrapper)db.GetStoredProcCommandWrapper(sqlCommand);
　　dbCommandWrapper.AddParameter("cur_OUT_f", OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
　　dbCommandWrapper.AddParameter("cur_OUT_g", OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
　　DataSet dsCustomers = db.ExecuteDataSet(dbCommandWrapper);
　　DataGrid1.DataSource=dsCustomers.Tables[0];
　　DataGrid1.DataBind();
　　DataGrid2.DataSource=dsCustomers.Tables[1];
　　DataGrid2.DataBind();

 

转载于:https://www.cnblogs.com/shadowtale/archive/2013/01/24/2875404.html