22,209
社区成员
发帖
与我相关
我的任务
分享
--生成本单位清单表存储过程
CREATE proc Proc_LocalPayList
as
--定义变量
declare @unitname varchar(50),@unitcode varchar (20),@showname varchar(20),
@m_cost decimal(9,2),@d_cost decimal(9,2),@w_cost decimal(9,2),@b_cost decimal(9,2),@tv_cost decimal(9,2),@o_cost decimal(9,2), @total decimal(9,2),
@m_price decimal(9,2),@d_price decimal(9,2),@w_price decimal(9,2),@b_price decimal(9,2), @tvprice decimal(9,2),
@m_used int,@d_used int,@w_used int, @tv_used int,@m_month varchar(15), @tv_month varchar(15),@d_up int,@w_up int,@iszhu varchar(6)
--定义游标
declare Local_cursor cursor for select unitname,unitcode,name,unitstate,menagementcost,Electrovalency,waterprice,blowdownprice,tvprice from LocalList_View
delete LocalCostList where exists (select unitcode from LocalCostList)
--打开游标
open Local_cursor
--操作游标
fetch next from Local_cursor into @unitname,@unitcode,@showname,@iszhu,@m_price,@d_price,@w_price,@b_price,@tvprice
while @@fetch_status=0
begin
--管理费
select @m_month=(min(CostMonth))+' - '+max(CostMonth),@m_used=count(id),@m_cost=sum(MenagementCost) from
LocalCostList_View where unitcode=@unitcode and MenagementCostIsJiao=0
--水电排污费
select @d_up=min(UpAmmeter),@d_used=sum(dianused),@d_cost=sum(ElectrovalencyCost),@w_up=min(UpWater),@w_used=sum(waterused),@w_cost=sum(WaterCost),@b_cost=sum(BlowdownCost)
from LocalCostList_View where unitcode=@unitcode and WaterDianCostIsJiao=0
--电视费
select @tv_used=count(id),@tv_cost=sum(TvCost), @tv_month=min(TvMonth)+' - '+max(TvMonth) from LocalCostList_View where unitcode=@unitcode and TvCostIsJiao=0
--滞纳金
select @o_cost=sum(OverdueFine) from LocalCostList_View where unitcode=@unitcode and OverdueFineIsJiao=0
--合计
select @total=sum(Payable) from LocalCostList_View where unitcode=@unitcode
--插入到表
insert into LocalCostList (unitname,unitcode,showname,ischuzhu,m_month,m_used,m_price,m_cost,d_up,d_used,d_price,d_cost,w_up,w_used,w_price,w_cost,b_price,b_cost,tv_cost,tv_price,tv_month,tv_used,o_cost,total)
values(@unitname,@unitcode,@showname,@iszhu,@m_month,@m_used,@m_price,@m_cost,@d_up,@d_used,@d_price,@d_cost,@w_up,@w_used,@w_price,@w_cost,@b_price,@b_cost,@tv_cost,@tvprice,@tv_month,@tv_used,@o_cost,@total)
fetch next from Local_cursor into @unitname,@unitcode,@showname,@iszhu,@m_price,@d_price,@w_price,@b_price,@tvprice
end
close Local_cursor
deallocate Local_cursor
GO
错了,是
每次循环里那么多select,当然慢
如果非要用游标的话那么记得建立合适的索引