本人写了一个存储过程，之后，数据库老是死锁，请高人指点

xiaocaoya 2016-05-19 08:54:59

CREATE procedure proc_outstockt_pici
@sbillno char(13),
@sdeptno char(4),
@suser char(12)
as
begin
declare @sbalance char(4)
declare @i integer
declare @iino integer
declare @idw4row integer
declare @idw4rows integer
declare @curmoney decimal
declare @spartno char(4)
declare @damount decimal(10,4)
declare @damount1 decimal(10,4)
declare @idsamount decimal(10,4)
declare @idsamount1 decimal(10,4)
declare @dcurdmoney decimal(10,4)
declare @ddmoney decimal(10,4)
declare @spass char(1)
declare @svalue char(16)
declare @idifference decimal(10,4)
declare @idifference1 decimal(10,4)
declare @dinstockdcuryue decimal(10,4)
declare @dinstockdcuryue1 decimal(10,4)
declare @idifferenceamount decimal(10,4)
declare @idifferenceamount1 decimal(10,4)
declare @spici char(16)
declare @dprice decimal(10,4)
declare @dprice1 decimal(10,4)
declare @dpicirows integer
set @i= 1
set @ddmoney= 0
set @dcurdmoney= 0
/*取ino自增列的最大号*/
select @idw4row= max(ino) from ps_kcoutstockdpici where billno=@sbillno;
if @idw4row is null
set @idw4row = 0
else

/*取发分店号*/
select @sbalance= balance from ps_kcoutstockt where billno= @sbillno;
/*取dw2中出库记录*/
declare curstockd cursor for select ino,partno,amount,amount1,pass from ps_kcoutstockd where billno= @sbillno
open curstockd;
fetch from curstockd into @iino,@spartno,@damount,@damount1,@spass
while @@FETCH_STATUS = 0
begin
--print @iino
--print @spartno
if @spass<> '2'
begin

/*取可出库批次记录总数*/
SELECT @dpicirows= count(*) FROM ps_kcinstockd
WHERE ( pass = '2' ) AND( curyue > 0 ) AND ( deptno = @sdeptno ) and (partno = @spartno ) ;

declare curstockdpici cursor for
SELECT curyue, curyue1,price,price1,pici FROM ps_kcinstockd
WHERE ( pass = '2' ) AND( curyue > 0 ) AND ( deptno = @sdeptno ) and (partno = @spartno ) order by pici asc ;
open curstockdpici;
fetch from curstockdpici into @dinstockdcuryue ,@dinstockdcuryue1,@dprice,@dprice1,@spici ;

set @i= 1
while @i<= @dpicirows
begin

set @idw4row = @idw4row + 1
select @idifferenceamount= @dinstockdcuryue - @damount ,@idifferenceamount1= @dinstockdcuryue1 - @damount1

if @idifferenceamount>= 0
begin
set @dcurdmoney = @damount *@dprice
set @ddmoney = @ddmoney + @dcurdmoney
update ps_kcinstockd set curyue= @idifferenceamount,curyue1= @idifferenceamount1 where pici= @spici;
insert into ps_kcoutstockdpici(ino,billno,partno,amount,price,amount1,price1,dmoney,pici,deptno,balance,icolumn,pass)
values(@idw4row,@sbillno,@spartno,@damount,@dprice,@damount1 ,@dprice1,@dcurdmoney,@spici,@sdeptno,@sbalance,@iino,'1');
break;
end
else
begin

set @dcurdmoney = @dinstockdcuryue *@dprice
set @ddmoney = @ddmoney + @dcurdmoney
update ps_kcinstockd set curyue= 0,curyue1= 0 where pici= @spici;
insert into ps_kcoutstockdpici(ino,billno,partno,amount,price,amount1,price1,dmoney,pici,deptno,balance,icolumn,pass)
values(@idw4row,@sbillno,@spartno,@dinstockdcuryue,@dprice,@dinstockdcuryue1 ,@dprice1,@dcurdmoney,@spici,@sdeptno,@sbalance,@iino,'2');
select @damount = @damount - @dinstockdcuryue
select @damount1= @damount1 - @dinstockdcuryue1

set @i= @i + 1
fetch next from curstockdpici into @dinstockdcuryue ,@dinstockdcuryue1,@dprice,@dprice1,@spici;
end
--set @idw4row = @idw4row + 1
--set @i= @i + 1
--fetch next from curstockdpici into @dinstockdcuryue ,@dinstockdcuryue1,@dprice,@dprice1,@spici ,@dpicirows;

end
--print @spartno
--print @ddmoney
update ps_kcoutstockd set dmoney= @ddmoney,price= @ddmoney / amount , price1= @ddmoney/ amount1 ,pass= '2' where billno= @sbillno and ino= @iino;
close curstockdpici
DEALLOCATE curstockdpici
set @ddmoney= 0
end
fetch from curstockd into @iino,@spartno,@damount,@damount1,@spass ;
end
update ps_kcoutstockt set state= '2',shdate= getdate(),assessor= @suser where billno= @sbillno;
end
close curstockd
DEALLOCATE curstockd

GO

...全文