oracle存储过程写法

0

```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(" 失败，请联系系统管理员。");
}```

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

Mark1997 | 初学一级 |园豆：147 | 2012-11-06 18:17

0

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

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

您需要登录以后才能回答，未注册用户请先注册