感谢各位大侠帮我解释一个存储过程。小弟感谢！！

yylzn 2006-01-18 05:12:54

alter procedure pr_sydsh @in_pzh T_PZHM,@rval integer OUTPUT
as
DECLARE @cur_year T_NIAN
DECLARE @cur_month T_YUE
DECLARE @bmh char(6)
DECLARE @spbh char(10)
DECLARE @rq T_RQ
DECLARE @pzh T_PZHM
DECLARE @sysl decimal(12,4)--T_SL
DECLARE @czy char(8)
DECLARE @zg char(8)
DECLARE @ckbgy char(8)
DECLARE @jz char(8)
DECLARE @shzt T_SHZT
DECLARE @lsh T_LSH

DECLARE @v_dqjje T_JE
DECLARE @v_dqsl decimal(12,4)--T_SL
DECLARE @v_pjdj T_JG
DECLARE @v_sysl decimal(12,4)--T_SL
DECLARE @v_sssl decimal(12,4)--T_SL
DECLARE @v_xssl decimal(12,4)--T_SL
DECLARE @v_xssr T_JE
DECLARE @v_xscb T_JE
DECLARE @v_xsml T_JE
DECLARE @v_jj T_JG /**/
DECLARE @v_lsj T_JG /**/

/*将损益单日期修改成审核日期，保证修改损益单审核日期的库存帐*/
begin transaction tran_sydsh
select @rval = -1
set @rq = cast(convert(char(10),getdate(),121) as datetime)
update syd
set rq = @rq
where pzh = @in_pzh

if @@error <> 0
begin
rollback transaction tran_sydsh
return
end

--exec pr_nextmonth @cur_year,@cur_month,0,@cur_year OUTPUT,@cur_month OUTPUT
DECLARE csr_syd CURSOR FOR select * from syd where pzh=@in_pzh
open csr_syd
fetch next from csr_syd into
@bmh,@spbh,@rq,@pzh,@sysl,@czy,@zg,@ckbgy,@jz,@shzt,@lsh
while (@@fetch_status<>-1)
begin
if (@@fetch_status<>-2)
begin
exec pr_rqgetny @rq,@cur_year OUTPUT,@cur_month OUTPUT

select @v_jj=jj,@v_lsj=lsj from spda where spbh=@spbh

if exists(select 1 from kcz where year=@cur_year and month=@cur_month and bmh=@bmh and spbh=@spbh)
begin
select @v_dqjje=dqjje,
@v_dqsl=dqsl
from kcz where year=@cur_year and month=@cur_month and bmh=@bmh and spbh=@spbh
IF @@ERROR<>0
begin
rollback transaction tran_sydsh
close csr_syd
deallocate csr_syd
return
end

end
else
begin
insert kcz(bmh,spbh,year,month,pjdj)values(@bmh,@spbh,@cur_year,@cur_month,0)
IF @@ERROR<>0
begin
rollback transaction tran_sydsh
close csr_syd
deallocate csr_syd
return
end

select @v_dqjje=0,@v_dqsl=0
end

exec pr_calPjdj @spbh, @v_dqjje, @v_dqsl, @v_pjdj OUTPUT
IF @@ERROR<>0
begin
rollback transaction tran_sydsh
close csr_syd
deallocate csr_syd
return
end

UPDATE kcz set
dqsl=dqsl+@sysl,
dqjje=dqjje+@sysl*@v_pjdj,
dqsje=dqsje+@sysl*@v_lsj
where year=@cur_year and month=@cur_month and bmh=@bmh and spbh=@spbh
IF @@ERROR<>0
begin
rollback transaction tran_sydsh
close csr_syd
deallocate csr_syd
return
end

if exists(select 1 from sylsz where rq=@rq and bmh=@bmh and spbh=@spbh and pzh=@pzh)
begin
UPDATE sylsz set sysl=sysl+@sysl,
Syjje=syjje+@sysl*@v_jj,
Sysje=sysje+@sysl*@v_lsj
where rq=@rq and bmh=@bmh and spbh=@spbh and pzh=@pzh
IF @@ERROR<>0
begin
rollback transaction tran_sydsh
close csr_syd
deallocate csr_syd
return
end

end
else
begin
insert sylsz(bmh,spbh,rq,pzh,sysl,syjje,sysje,czy,zg,ckbgy,jz)
values(@bmh,@spbh,@rq,@pzh,@sysl,@sysl*@v_jj,@sysl*@v_lsj,@czy,@zg,@ckbgy,@jz)
IF @@ERROR<>0
begin
rollback transaction tran_sydsh
close csr_syd
deallocate csr_syd
return
end

end

DELETE from syd where current of csr_syd
IF @@ERROR<>0
begin
rollback transaction tran_sydsh
close csr_syd
deallocate csr_syd
return
end
-- else
-- commit transaction tran_sydsh

end

fetch next from csr_syd into @bmh,@spbh,@rq,@pzh,@sysl,@czy,@zg,@ckbgy,@jz,@shzt,@lsh
end
close csr_syd
deallocate csr_syd
commit transaction tran_sydsh
select @rval = 0
return

...全文