.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;

　　

.net 使用oracle 的預存程序有傳回值也有資料集