首页 新闻 搜索 专区 学院

oracle存储过程写法

0
悬赏园豆:5 [已解决问题] 解决于 2012-12-13 11:07
DataSet amount = aBC_MaintanceDal.CalculateAmount(this.LoginHospital, datefrom, dateto);
            double allAmount = 0;
            double mAmount = 0;
            try
            {
                //先算出全部所用金额
                for (int i = 0; i < amount.Tables[0].Rows.Count; i++)
                {
                    allAmount += double.Parse(amount.Tables[0].Rows[i]["amount"].ToString());
                }
                //分别算出金额,然后判断属性,计算安全存量
                for (int i = 0; i < amount.Tables[0].Rows.Count; i++)
                {
                    mAmount += double.Parse(amount.Tables[0].Rows[i]["amount"].ToString());
                    double per = mAmount / allAmount;
                    string prod = amount.Tables[0].Rows[i]["tprod"].ToString();
                    double price = double.Parse(amount.Tables[0].Rows[i]["price"].ToString());
                    double consume = double.Parse(amount.Tables[0].Rows[i]["consume"].ToString());
                    double avgconsume = double.Parse(amount.Tables[0].Rows[i]["avg_consume"].ToString());
                    double safeqty = 0;
                    int typeid = 0;
                    if (per <= 0.7)//累计使用金额百分比<=0.7,属性为A
                    {
                        safeqty = avgconsume * 7;   
                        typeid = 1;
                    }
                    else if (per > 0.9)//累计使用金额百分>0.9,属性为C
                    {
                        safeqty = avgconsume * 30;
                        typeid = 3;
                    }
                    else//累计使用金额百分>0.7,<=0.9,属性为B
                    {
                        safeqty = avgconsume * 10;
                        typeid = 2;
                    }
                    //往drugabc_detail内插入数据
                    aBC_MaintanceDal.InsertDrugabcDetail(this.LoginHospital, prod, price, consume, mAmount, per, typeid, safeqty);
                    this.BindGrid(null, null);
                }
                ShowMessage("生成成功。");
                
                BindDropDownList();
                Pag.Visible = true;
            }
            catch
            {
                Response.Redirect("../Error.aspx");
                Response.Write(" 失败,请联系系统管理员。");
            }

因为需要一次次访问数据库,所以很慢,求问怎样把这一段代码全写在存储过程中,然后直接用sql进行批量更新,oracle数据库

暗夜的萤火虫的主页 暗夜的萤火虫 | 初学一级 | 园豆:8
提问于:2012-10-30 08:40
< >
分享
最佳答案
0

表名改过来,试下?
create procedurce pro_insert
as
declare @allAmount double,@per double,@safeQty double,@typeid int
set xact_abort on

begin  tran
select  amount,avg_consume,sum(amount) into @mAmount,@avg_consume,@allAmount from tableName
set @per=@mAmount / @allAmount
if @per<=0.7 then     @safteQty=@avg_consume * 7
     @typeid=1
end if
if @per>0.9  then     @safeQty=@avg_consume  * 30
     @typeid=3
end if
      @safeQty=@avg_consume * 10
      @typeid=2
insert into tableName2(对应字段列) values((select loginHospital,prod,price,consume from tableName),@mAmount,@per,@typeid,@safeQty)
commit tran

收获园豆:5
Mark1997 | 初学一级 |园豆:147 | 2012-11-06 18:17
其他回答(1)
0

话说楼主也太懒了点吧,这个难度没多大,还是尝试着自己写下吧

oppoic | 园豆:560 (小虾三级) | 2012-10-30 08:43

lz确实懒了点,但这个是真心不会啊

支持(0) 反对(0) 暗夜的萤火虫 | 园豆:8 (初学一级) | 2012-10-30 11:19
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册