.net 使用oracle 的存储过程有返回值也有数据集（游标）

        public void GetData(string username, string userip, string userkey, string userareaid, string ypid, string in_provinces, string yearid, out  DataTable data, out string out_success, out string out_message)
        {
            try
            {
                string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();
                using (OracleConnection con = new OracleConnection(constr))
                {
                    con.Open();
                    OracleCommand cmd = new OracleCommand("proc_pricefind", con);

                    cmd.CommandType = CommandType.StoredProcedure;

                    OracleParameter[] paras = new OracleParameter[] 
                { 
                    new OracleParameter("username", username),
                    new OracleParameter("userip", userip),
                    new OracleParameter("userkey", userkey),
                    new OracleParameter("userareaid", userareaid),
                    new OracleParameter("ypids", ypid),
                    new OracleParameter("in_provinces", in_provinces),
                    new OracleParameter("yearid", yearid),
                new OracleParameter("data",OracleType.Cursor),
                new OracleParameter("out_success",OracleType.VarChar,4000),
                new OracleParameter("out_message",OracleType.VarChar,4000)
                };
                    paras[paras.Length - 1].Direction = ParameterDirection.Output;
                    paras[paras.Length - 2].Direction = ParameterDirection.Output;
                    paras[paras.Length - 3].Direction = ParameterDirection.Output;
                    cmd.Parameters.AddRange(paras.ToArray());

                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    out_success = paras[paras.Length - 2].Value.ToString();
                    out_message = paras[paras.Length - 1].Value.ToString();
                    data = ds.Tables[0];
                    //如果没有返回数据集的话，可以直接使用ExecuteNonQuery（）。然后取out的值，这样就不需要把OracleDataReader装换为datatable
                    //cmd.ExecuteNonQuery();
                    //  out_message = paras[paras.Length - 3].Value;

                    //OracleDataReader   odr = paras[paras.Length - 3].Value as OracleDataReader;
                    //  data=ConvertDataReaderToDataTable(odr);
                }
            }
            catch (Exception ex)
            {
                out_success = "0";
                out_message = ex.Message;
                data = null;
            }


        }

　　

create or replace procedure proc_pricefind(username     varchar,
                                           userip       varchar,
                                           userkey      varchar,
                                           userareaid   varchar,
                                           ypids         varchar,
                                           in_provinces varchar,
                                           yearid       varchar,
                                           data         out  sys_refcursor,
                                           out_success  out varchar,
                                           out_message  out varchar) is
  findcount       number;
  configcount     number;
  findareaidcount number;
  configareacount number;
  guid            varchar(36);

begin
  out_success := '0';
  out_message := '';
  select sys_guid() into guid from dual;

  insert into findprice_userrecord
    (recordid,
     usernmae,
     userip,
     userkey,
     userareaid,
     ypid,
     findareaid,
     yearid,
     addtime)
  values
    (guid,
     username,
     userip,
     userkey,
     userareaid,
     ypids,
     in_provinces,
     yearid,
     sysdate);
  commit;
  insert into findprice_log_userrecord
    select guid,
           sys_guid(),
           b.产品名称,
           b.剂型分类名称,
           b.规格,
           b.转换系数,
           b.材质,
           b.企业名,
           t.purprice
      from mid_monthspurprice t
     inner join view_drug a
        on a.sdid = t.sdid
     inner join base_stddrug b
        on a.sdcode = b.原流水码
     where substr(t.dr, 0, 4) = yearid
       and b.ypid = ypids
       and t.provinceareaid = in_provinces;
  commit;
  --判断计算条数
  select count(distinct t.ypid ||','|| t.findareaid||','|| t.yearid) kk
    into findcount
    from findprice_userrecord t
   where t.userareaid = userareaid;

  select t.findnumber
    into configcount
    from findprice_config t
   where t.areaid = userareaid;

  if findcount > configcount then
    delete from findprice_userrecord t where t.recordid = guid;
    delete from findprice_log_userrecord t where t.recordid = guid;
    commit;
    --删除插入进去的数据
    raise_application_error(-20000, '查询的总条数超过了限制');
  end if;
  --判断计算的省份
  select count(1) tol
    into out_success
    from mon_joinpoint
   where instr((select min(t.findareaid)
                 from findprice_config t
                where t.areaid = userareaid),
               provinceidnew) > 0
     and provinceareaid in (in_provinces);

  select count(distinct t.findareaid) tol
    into findareaidcount
    from findprice_userrecord t
   where t.userareaid = userareaid;
  if out_success = 0 and findareaidcount > 10 then
    delete from findprice_userrecord t where t.recordid = guid;
    delete from findprice_log_userrecord t where t.recordid = guid;
    commit;
    raise_application_error(-20000, '查询省份超过了限制' || configcount);
  end if;
  open data for
    select ypids,
           b.产品名称,
           b.剂型分类名称,
           b.规格,
           b.转换系数,
           b.材质,
           b.企业名,
           t.purprice
      from mid_monthspurprice t
     inner join view_drug a
        on a.sdid = t.sdid
     inner join base_stddrug b
        on a.sdcode = b.原流水码
     where substr(t.dr, 0, 4) = yearid
       and b.ypid = ypids
       and t.provinceareaid = in_provinces;
 out_success:='1';
end proc_pricefind;

　　如果给存储过程加了获取异常的脚本

EXCEPTION
WHEN OTHERS THEN

 

那么需要在

data         out  sys_refcursor, 返回的类型顺便赋一些值，不然在.net中执行存储过程会报错
如下：

open data for

select '0' from dual;

 

最简单的

create or replace procedure proc_relpage(pageindex number,
                                         data      out sys_refcursor) is

begin

  open data for
    select *
      from rel_stdzdrugtemp20160204 a
     where a.rn between (pageindex - 1) * 20 + 1 and (pageindex * 20);
end proc_relpage;

 

转载于:https://www.cnblogs.com/wlzhang/p/4793591.html