标题: MSSQL编程：前段工夫写的一个关于本钱盘算的存储过... [打印本页] 作者: 爱飞 时间: 2015-1-16 22:34 标题: MSSQL编程：前段工夫写的一个关于本钱盘算的存储过... 你看出了作者的深度？深处半米！当初是冲那么多的大牛给他写序才买的，后来才发现无啥内容，作者也只是才用几年的新手，百花了几十两银子，再次感叹当今社会的虚伪与浮躁存储历程
--|///
----//
--(@@)
--┏━━━━━━━━━oOOo-(_)-oOOo━┓
--┃界说于2004-07-16┃
--┃本钱试算存储历程界说，盘算本钱┃
--┃Oooo┃
--┗━━━━━━━━━oooO━-()━┛
--())/
--((_/
--\_)
CREATEPROCEDUREsp_costing_compute
(@out_matnrvarchar(18),
@out_werksvarchar(4),
@out_datetimedatetime)
AS
--拔出赠品
declare@matnrvarchar(18),@werksvarchar(4),@kriqidatetime,@meinsvarchar(10)
declare@bomatnrvarchar(18),@bomsehlvarchar(10),@mengefloat
declare@pricefloat,@danwvarchar(20)
declare@umrezfloat,@aapricefloat,@jjpricefloat,@aratefloat,@jratefloat,@asyfloat,@jsyfloat
declare@cpjgfloat,@umrenfloat
set@cpjg=0
--盘算产制品本钱代价
Declaremycursorcursorforselectmeins,aprice,jpricefromtb_matnrwhereconvert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)andmatnr=@out_matnrandplant=@out_werks
openmycursor
fetchmycursorinto@meins,@aaprice,@jjprice
closemycursor
deallocatemycursor
declarebccursorcursorforselectbomatnr,bomsehl,mengefromtb_bomwherecpmatnr=@out_matnrandwerks=@out_werksandconvert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
openbccursor
fetchbccursorinto@bomatnr,@bomsehl,@menge
while@@fetch_status=0
begin
declarejgcursorcursorforselectprice,meinsfromtb_bcjgwherematnr=@bomatnrandplant=@out_werksandconvert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
openjgcursor
fetchjgcursorinto@price,@danw
closejgcursor
deallocatejgcursor
if@price=0
begin
updatetb_matnrsetmstatus=置疑wherematnr=@out_matnrandplant=@out_werksandconvert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
break
end
--假如代价单元和bom单元分歧，必要举行转换
if@bomsehl@danw
begin
declarezhgxcursorcursorforselectumrezfromtb_switchwherematnr=@bomatnrandmsehl=@danw
openzhgxcursor
fetchzhgxcursorinto@umrez
closezhgxcursor
deallocatezhgxcursor
end
if@umrezisnull
set@umrez=1
set@cpjg=@cpjg+@menge*@price/@umrez
fetchnextfrombccursorinto@bomatnr,@bomsehl,@menge
end
closebccursor
deallocatebccursor
--检察bom制品单元与制品发卖单元，假如分歧增加转换干系
declarecpzhcursorcursorforselectumrenfromtb_switchwherematnr=@out_matnrandmsehl=@meins
opencpzhcursor
fetchcpzhcursorinto@umren
closecpzhcursor
deallocatecpzhcursor
if@umrenisnull
set@umren=1
if@cpjg0
set@cpjg=@cpjg/@umren
ifnot@aapriceisnull
begin
set@arate=@aaprice-@cpjg
if@aaprice0
set@asy=@arate/@aaprice
end
else
begin
set@arate=0
set@asy=0
end
ifnot@jjpriceisnull
begin
set@jrate=@jjprice-@cpjg
if@jjprice0
set@jsy=@jrate/@jjprice
end
else
begin
set@jrate=0
set@jsy=0
end
updatetb_matnrsetcbprice=@cpjg,apeyoff=@arate,jpayoff=@jrate,arate=@asy,jrate=@jsywherematnr=@out_matnrandplant=@out_werksandconvert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
GO