拍拍尘土

一个效率很差的存储过程
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