一个效率很差的存储过程例子

[ 2005-06-15 16:12:08 | 作者: admin ]

字号: 大 | 中 | 小

一个效率很差的存储过程
CREATE PROCEDURE sp_vbnet_compute AS
declare @matnr varchar(18),@werks varchar(4),@kriqi datetime,@meins varchar(10),@matnrtemp varchar(18)
declare @bomatnr varchar(18),@bomsehl varchar(10),@menge float
declare @price float,@danw varchar(20)
declare @umrez float,@aaprice float,@jjprice float,@arate float,@jrate float,@asy float,@jsy float
declare @cpjg float,@umren float
declare @mflag int
set @cpjg = 0
--插入赠品
insert into tb_bcjg(matnr,maktx,groes,mtart,matkl,meins,price,kriqi,plant,wgbez)
select matnr,maktx,groes,mtart,matkl,meins,aprice,getdate(),plant,wgbez from tb_matnr
where convert(varchar(10),kriqi,120)=convert(varchar(10),getdate()-1,120)
and (matnr = '1003964' or matnr = '1002730' or matnr='1002211' or matnr='1004242' or matnr='1001940')
--update tb_bcjg set price=1 where matnr='2100297' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
--update tb_bcjg set price=1 where matnr='2100781' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
--update tb_bcjg set price=1 where matnr='2100298' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
--update tb_bcjg set price=1 where matnr='2100293' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
--update tb_bcjg set price=1 where matnr='2100294' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
--update tb_bcjg set price=1 where matnr='2100295' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
--update tb_bcjg set price=1 where matnr='2100296' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
--计算产成品与半成品成本价格,首先计算半成品，供产成品计算使用
--搜索bom，查找最低一级bom,结果存储于tb_level临时表，此处认为bom最多三层，如果超过三层，将出现错误
set @mflag = 0
delete from tb_level
declare bomcursor cursor for select distinct bomatnr from tb_bom where cpmatnr like '21%' and bomatnr like '21%' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open bomcursor
fetch bomcursor into @matnr
while @@fetch_status = 0
begin
insert into tb_level(matnr,mlevel) values(@matnr,1)
fetch next from bomcursor into @matnr
end
close bomcursor
deallocate bomcursor

declare bomcursor cursor for select distinct bomatnr from tb_bom where cpmatnr in (select distinct matnr from tb_level where mlevel=1) and bomatnr like '21%' and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open bomcursor
fetch bomcursor into @matnr
while @@fetch_status = 0
begin
insert into tb_level(matnr,mlevel) values(@matnr,2)
fetch next from bomcursor into @matnr
end
close bomcursor
deallocate bomcursor

--bom展开结束，首先计算最底层bom
declare bomcursor cursor for select distinct matnr from tb_level where mlevel = 2
open bomcursor
fetch bomcursor into @matnrtemp
while @@fetch_status = 0
begin

Declare mycursor cursor for select matnr,plant,kriqi,meins from tb_matnr where convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120) and matnr=@matnrtemp
open mycursor
fetch mycursor into @matnr,@werks,@kriqi,@meins
while @@fetch_status = 0
begin
set @cpjg = 0
declare bccursor cursor for select bomatnr,bomsehl,menge from tb_bom where cpmatnr=@matnr and werks=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open bccursor
fetch bccursor into @bomatnr,@bomsehl,@menge
while @@fetch_status = 0
begin
declare jgcursor cursor for select price,meins from tb_bcjg where matnr=@bomatnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open jgcursor
fetch jgcursor into @price,@danw
close jgcursor
deallocate jgcursor
--如果价格单位和bom单位不同，需要进行转换
if @bomsehl <> @danw
begin
declare zhgxcursor cursor for select umrez from tb_switch where matnr = @bomatnr and msehl = @danw
open zhgxcursor
fetch zhgxcursor into @umrez
close zhgxcursor
deallocate zhgxcursor
end
if @umrez is null
set @umrez = 1
set @cpjg = @cpjg + @menge * @price / @umrez
fetch next from bccursor into @bomatnr,@bomsehl,@menge
end

close bccursor
deallocate bccursor
--查看bom成品单位与成品销售单位，如果不同添加转换关系
declare cpzhcursor cursor for select umren from tb_switch where matnr = @matnr and msehl = @meins
open cpzhcursor
fetch cpzhcursor into @umren
close cpzhcursor
deallocate cpzhcursor
if @umren is null
set @umren = 1
if @cpjg <> 0
set @cpjg = @cpjg / @umren
update tb_matnr set cbprice = @cpjg,apeyoff=0,jpayoff=0,arate=0,jrate=0,tflag=2 where matnr=@matnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
insert into tb_bcjg (matnr,maktx,groes,mtart,meins,price,kriqi,plant,wgbez) (select matnr,maktx,groes,mtart,meins,cbprice,kriqi,plant,wgbez from tb_matnr where matnr=@matnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120) and tflag=2)
fetch next from mycursor into @matnr,@werks,@kriqi,@meins
end
close mycursor
deallocate mycursor

end
close bomcursor
deallocate bomcursor

--计算第二层bom
declare bomcursor cursor for select distinct matnr from tb_level where mlevel = 1
open bomcursor
fetch bomcursor into @matnrtemp
while @@fetch_status = 0
begin

Declare mycursor cursor for select matnr,plant,kriqi,meins from tb_matnr where convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120) and matnr=@matnrtemp
open mycursor
fetch mycursor into @matnr,@werks,@kriqi,@meins
while @@fetch_status = 0
begin
set @cpjg = 0
declare bccursor cursor for select bomatnr,bomsehl,menge from tb_bom where cpmatnr=@matnr and werks=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open bccursor
fetch bccursor into @bomatnr,@bomsehl,@menge
while @@fetch_status = 0
begin
declare jgcursor cursor for select price,meins from tb_bcjg where matnr=@bomatnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open jgcursor
fetch jgcursor into @price,@danw
close jgcursor
deallocate jgcursor
--如果价格单位和bom单位不同，需要进行转换
if @bomsehl <> @danw
begin
declare zhgxcursor cursor for select umrez from tb_switch where matnr = @bomatnr and msehl = @danw
open zhgxcursor
fetch zhgxcursor into @umrez
close zhgxcursor
deallocate zhgxcursor
end
if @umrez is null
set @umrez = 1
set @cpjg = @cpjg + @menge * @price / @umrez
fetch next from bccursor into @bomatnr,@bomsehl,@menge
end
close bccursor
deallocate bccursor
--查看bom成品单位与成品销售单位，如果不同添加转换关系
declare cpzhcursor cursor for select umren from tb_switch where matnr = @matnr and msehl = @meins
open cpzhcursor
fetch cpzhcursor into @umren
close cpzhcursor
deallocate cpzhcursor
if @umren is null
set @umren = 1
if @cpjg <> 0
set @cpjg = @cpjg / @umren
update tb_matnr set cbprice = @cpjg,apeyoff=0,jpayoff=0,arate=0,jrate=0 where matnr=@matnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
insert into tb_bcjg (matnr,maktx,groes,mtart,meins,price,kriqi,plant,wgbez) (select matnr,maktx,groes,mtart,meins,cbprice,kriqi,plant,wgbez from tb_matnr where matnr=@matnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120) and tflag=1)
fetch next from mycursor into @matnr,@werks,@kriqi,@meins,@aaprice,@jjprice
end
close mycursor
deallocate mycursor

end
close bomcursor
deallocate bomcursor

Declare mycursor cursor for select matnr,plant,kriqi,meins,aprice,jprice from tb_matnr where convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120) and mtart = 'ZHAL' and tflag = 0
open mycursor
fetch mycursor into @matnr,@werks,@kriqi,@meins,@aaprice,@jjprice
while @@fetch_status = 0
begin
set @cpjg = 0
declare bccursor cursor for select bomatnr,bomsehl,menge from tb_bom where cpmatnr=@matnr and werks=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open bccursor
fetch bccursor into @bomatnr,@bomsehl,@menge
while @@fetch_status = 0
begin
declare jgcursor cursor for select price,meins from tb_bcjg where matnr=@bomatnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open jgcursor
fetch jgcursor into @price,@danw
close jgcursor
deallocate jgcursor
--如果价格单位和bom单位不同，需要进行转换
if @bomsehl <> @danw
begin
declare zhgxcursor cursor for select umrez from tb_switch where matnr = @bomatnr and msehl = @danw
open zhgxcursor
fetch zhgxcursor into @umrez
close zhgxcursor
deallocate zhgxcursor
end
if @umrez is null
set @umrez = 1
set @cpjg = @cpjg + @menge * @price / @umrez
fetch next from bccursor into @bomatnr,@bomsehl,@menge
end
close bccursor
deallocate bccursor
--查看bom成品单位与成品销售单位，如果不同添加转换关系
declare cpzhcursor cursor for select umren from tb_switch where matnr = @matnr and msehl = @meins
open cpzhcursor
fetch cpzhcursor into @umren
close cpzhcursor
deallocate cpzhcursor
if @umren is null
set @umren = 1
if @cpjg <> 0
set @cpjg = @cpjg / @umren

update tb_matnr set cbprice = @cpjg,apeyoff=0,jpayoff=0,arate=0,jrate=0,tflag=101 where matnr=@matnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
insert into tb_bcjg (matnr,maktx,groes,mtart,meins,price,kriqi,plant,wgbez) (select matnr,maktx,groes,mtart,meins,cbprice,kriqi,plant,wgbez from tb_matnr where matnr=@matnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120) and tflag=101)
fetch next from mycursor into @matnr,@werks,@kriqi,@meins,@aaprice,@jjprice
end
close mycursor
deallocate mycursor
--半成品计算结束

--计算产成品
Declare mycursor cursor for select matnr,plant,kriqi,meins,aprice,jprice from tb_matnr where convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120) and mtart='ZFER'
open mycursor
fetch mycursor into @matnr,@werks,@kriqi,@meins,@aaprice,@jjprice
while @@fetch_status = 0
begin
set @cpjg = 0
declare bccursor cursor for select bomatnr,bomsehl,menge from tb_bom where cpmatnr=@matnr and werks=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open bccursor
fetch bccursor into @bomatnr,@bomsehl,@menge
while @@fetch_status = 0
begin
declare jgcursor cursor for select price,meins from tb_bcjg where matnr=@bomatnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
open jgcursor
fetch jgcursor into @price,@danw
close jgcursor
deallocate jgcursor
--如果价格单位和bom单位不同，需要进行转换
if @bomsehl <> @danw
begin
declare zhgxcursor cursor for select umrez from tb_switch where matnr = @bomatnr and msehl = @danw
open zhgxcursor
fetch zhgxcursor into @umrez
close zhgxcursor
deallocate zhgxcursor
end
if @umrez is null
set @umrez = 1
set @cpjg = @cpjg + @menge * @price / @umrez
fetch next from bccursor into @bomatnr,@bomsehl,@menge
end
close bccursor
deallocate bccursor
--查看bom成品单位与成品销售单位，如果不同添加转换关系
declare cpzhcursor cursor for select umren from tb_switch where matnr = @matnr and msehl = @meins
open cpzhcursor
fetch cpzhcursor into @umren
close cpzhcursor
deallocate cpzhcursor
if @umren is null
set @umren = 1
if @cpjg <> 0
set @cpjg = @cpjg / @umren
if not @aaprice is null
begin
set @arate = @aaprice - @cpjg
if @aaprice <> 0
set @asy=@arate / @aaprice
end
else
begin
set @arate=0
set @asy=0
end
if not @jjprice is null
begin
set @jrate = @jjprice - @cpjg
if @jjprice <> 0
set @jsy = @jrate / @jjprice
end
else
begin
set @jrate = 0
set @jsy = 0
end

update tb_matnr set cbprice = @cpjg,apeyoff=@arate,jpayoff=@jrate,arate=@asy,jrate=@jsy where matnr=@matnr and plant=@werks and convert(varchar(10),kriqi,120)=convert(varchar(10),getdate(),120)
fetch next from mycursor into @matnr,@werks,@kriqi,@meins,@aaprice,@jjprice
end
close mycursor
deallocate mycursor
GO