oracle存储过程写法

Question

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数据库

Answer 1

表名改过来，试下？create procedurce pro_insertasdeclare @allAmount double,@per double,@safeQty double,@typeid intset xact_abort on begin  transelect  amount,avg_consume,sum(amount) into @mAmount,@avg_consume,@allAmount from tableNameset @per=@mAmount / @allAmountif @per<=0.7 then     @safteQty=@avg_consume * 7     @typeid=1end ifif @per>0.9  then     @safeQty=@avg_consume  * 30     @typeid=3end if      @safeQty=@avg_consume * 10      @typeid=2insert into tableName2(对应字段列) values((select loginHospital,prod,price,consume from tableName),@mAmount,@per,@typeid,@safeQty)commit tran

Answer 2

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

Answer 3

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