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_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

Answer 2

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

Answer 3

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