用存储过程执行数据查询

1.存储过程

create or replace package PP_CastIronOut is

  -- Public type declarations
  type V_CUR is ref cursor; --定义游标

。。。。

procedure ElementsQuery(c_in_date1 varchar2, --记账日期
                        p_cusor    out V_CUR --游标
                        ) as
begin
  open p_cusor for
    select a.c_batchnumber,
           c.c_itemvalue,
           d.c_itemvalue,
           e.c_itemvalue,
           f.c_itemvalue,
              a.c_accdate
      from (select distinct c_batchnumber, c_accdate
              from tp_pp_puddingoutput) a
      left join tq_proquote b
        on a.c_batchnumber = b.c_steelno
      left join tq_proquoteitem c
        on b.c_proquoteid = c.c_proquoteid
       and c.c_item = 'FE0002'
      left join tq_proquoteitem d
        on b.c_proquoteid = d.c_proquoteid
       and d.c_item = 'FE0007'
      left join tq_proquoteitem e
        on b.c_proquoteid = e.c_proquoteid
       and e.c_item = 'FE0003'
      left join tq_proquoteitem f
        on b.c_proquoteid = f.c_proquoteid
       and f.c_item = 'FE0005'
           where a.c_accdate like c_in_date1 || '%'
     order by c_batchnumber;

end ElementsQuery;

 

2.C#调用

IDbDataParameter[] parameters = { new OracleParameter("c_in_date1", OracleType.VarChar, 10),
                                               new OracleParameter("p_cusor", OracleType.Cursor)};
           //parameters[0].Direction = ParameterDirection.Input;  // 设置为输入参数
           parameters[0].Value = date1;
           parameters[1].Direction = ParameterDirection.Output;
           DataTable dt = RV.DataAccess.DBHelperNH.Instance().ExecuteProcReader("PP_CastIronOut.ElementsQuery", parameters);
           gridControl1.DataSource = dt;
           if (dt.Columns.Count!=0)
           {
               gridColumn1.FieldName = dt.Columns[0].ColumnName;
               gridColumn2.FieldName = dt.Columns[1].ColumnName;
               gridColumn3.FieldName = dt.Columns[2].ColumnName;
              。。。           }
           gridView1.BestFitColumns();

 

3.参数简写

        /// <summary>
        /// 获取综合查询信息
        /// </summary>
        /// <param name="yearMonth">制定年月</param>
        /// <returns>DataTable</returns>

 public DataTable GetSmInfo(string yearMonth)
        {
            OracleParameter[] param = { new OracleParameter("P_Date",yearMonth),
                                        new OracleParameter("P_CUR", OracleType.Cursor)
                                         };
            //输出类型指定
            param[1].Direction = ParameterDirection.Output;
            DataTable dt = RV.DataAccess.DBHelperNH.Instance().ExecuteProcReader("PC002.GetZHSmInfo", param);
            return dt;
        }