请教:
代码用了两个游标,一个是当前记录的,一个是当前记录的上一条;
当前记录下移时,其上一条也跟着下移;
可现在执行了,却实现不了.
代码如下;(加权平均)
create procedure Proc_Kcjymxb
asdeclare Kcjymxb_Cursor CURSOR for select * from KCJYMXB
select * from KCJYMXB order by spbh
declare @kc_id int,@kc_djlx varchar(50),@kc_spbh varchar(50),@kc_rq datetime,@kc_spsl int,@kc_spdj money,@kc_spje money,
@kc_jslb varchar(50),@kc_jcsl int,@kc_jcdj money,@kc_jcje money
open Kcjymxb_Cursor
fetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcje
while @@fetch_status=0
begin declare Pr_Kc_Cursor CURSOR for select * from KCJYMXB
select * from KCJYMXB order by rq
declare @pr_kc_id int,@pr_kc_djlx varchar(50),@pr_kc_spbh varchar(50),@pr_kc_rq datetime,@pr_kc_spsl int,@pr_kc_spdj money,
@pr_kc_spje money,@pr_kc_jslb varchar(50),@pr_kc_jcsl int,@pr_kc_jcdj money,@pr_kc_jcje money
open Pr_Kc_Cursor
fetch next from Pr_Kc_Cursor into @pr_kc_id,@pr_kc_djlx,@pr_kc_spbh,@pr_kc_rq,@pr_kc_spsl,@pr_kc_spdj,@pr_kc_spje,@pr_kc_jslb,@pr_kc_jcsl,@pr_kc_jcdj,@pr_kc_jcje
while @@fetch_status=0
begin
fetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcje
if(@kc_spbh=@pr_kc_spbh)/* 当同编号,同单据类型时计算*/
begin
if (@kc_jslb='+')
begin
set @kc_spdj=@pr_kc_jcdj
set @kc_spje=@kc_spdj*@kc_spsl
set @kc_jcje=@pr_kc_jcje+@kc_spje
set @kc_jcsl=@pr_kc_jcsl+@kc_spsl
set @kc_jcdj=@kc_jcje/@kc_jcsl
end
else
if (@kc_jslb='-')
begin
set @kc_spdj=@pr_kc_jcdj
set @kc_spje=@kc_spdj*@kc_spsl
set @kc_jcje=@pr_kc_jcje-@kc_spje
set @kc_jcsl=@pr_kc_jcsl-@kc_spsl
set @kc_jcdj=@pr_kc_jcdj
end
end
else /*不同编号时将第一次获得的记录作为新值,其它全部重新算过*/
begin
if (@kc_jslb='+')
begin
set @kc_jcje=@kc_jcje
set @kc_jcsl=@kc_jcsl
set @kc_jcdj=@kc_jcje/@kc_jcsl
end
else
if (@kc_jslb='-')
begin
set @kc_jcje=@kc_jcje/*考虑当金额和数量小于零时*/
set @kc_jcsl=-@kc_jcsl
if(@kc_jcsl<0) print'库存已不够输出!'
set @kc_jcdj=@pr_kc_jcdj
end
end
fetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcje
fetch next from Pr_Kc_Cursor into @pr_kc_id,@pr_kc_djlx,@pr_kc_spbh,@pr_kc_rq,@pr_kc_spsl,@pr_kc_spdj,@pr_kc_spje,@pr_kc_jslb,@pr_kc_jcsl,@pr_kc_jcdj,@pr_kc_jcje
endend
close Kcjymxb_Cursor
close Pr_Kc_Cursor
deallocate Kcjymxb_Cursor
deallocate Pr_Kc_Cursor
代码用了两个游标,一个是当前记录的,一个是当前记录的上一条;
当前记录下移时,其上一条也跟着下移;
可现在执行了,却实现不了.
代码如下;(加权平均)
create procedure Proc_Kcjymxb
asdeclare Kcjymxb_Cursor CURSOR for select * from KCJYMXB
select * from KCJYMXB order by spbh
declare @kc_id int,@kc_djlx varchar(50),@kc_spbh varchar(50),@kc_rq datetime,@kc_spsl int,@kc_spdj money,@kc_spje money,
@kc_jslb varchar(50),@kc_jcsl int,@kc_jcdj money,@kc_jcje money
open Kcjymxb_Cursor
fetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcje
while @@fetch_status=0
begin declare Pr_Kc_Cursor CURSOR for select * from KCJYMXB
select * from KCJYMXB order by rq
declare @pr_kc_id int,@pr_kc_djlx varchar(50),@pr_kc_spbh varchar(50),@pr_kc_rq datetime,@pr_kc_spsl int,@pr_kc_spdj money,
@pr_kc_spje money,@pr_kc_jslb varchar(50),@pr_kc_jcsl int,@pr_kc_jcdj money,@pr_kc_jcje money
open Pr_Kc_Cursor
fetch next from Pr_Kc_Cursor into @pr_kc_id,@pr_kc_djlx,@pr_kc_spbh,@pr_kc_rq,@pr_kc_spsl,@pr_kc_spdj,@pr_kc_spje,@pr_kc_jslb,@pr_kc_jcsl,@pr_kc_jcdj,@pr_kc_jcje
while @@fetch_status=0
begin
fetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcje
if(@kc_spbh=@pr_kc_spbh)/* 当同编号,同单据类型时计算*/
begin
if (@kc_jslb='+')
begin
set @kc_spdj=@pr_kc_jcdj
set @kc_spje=@kc_spdj*@kc_spsl
set @kc_jcje=@pr_kc_jcje+@kc_spje
set @kc_jcsl=@pr_kc_jcsl+@kc_spsl
set @kc_jcdj=@kc_jcje/@kc_jcsl
end
else
if (@kc_jslb='-')
begin
set @kc_spdj=@pr_kc_jcdj
set @kc_spje=@kc_spdj*@kc_spsl
set @kc_jcje=@pr_kc_jcje-@kc_spje
set @kc_jcsl=@pr_kc_jcsl-@kc_spsl
set @kc_jcdj=@pr_kc_jcdj
end
end
else /*不同编号时将第一次获得的记录作为新值,其它全部重新算过*/
begin
if (@kc_jslb='+')
begin
set @kc_jcje=@kc_jcje
set @kc_jcsl=@kc_jcsl
set @kc_jcdj=@kc_jcje/@kc_jcsl
end
else
if (@kc_jslb='-')
begin
set @kc_jcje=@kc_jcje/*考虑当金额和数量小于零时*/
set @kc_jcsl=-@kc_jcsl
if(@kc_jcsl<0) print'库存已不够输出!'
set @kc_jcdj=@pr_kc_jcdj
end
end
fetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcje
fetch next from Pr_Kc_Cursor into @pr_kc_id,@pr_kc_djlx,@pr_kc_spbh,@pr_kc_rq,@pr_kc_spsl,@pr_kc_spdj,@pr_kc_spje,@pr_kc_jslb,@pr_kc_jcsl,@pr_kc_jcdj,@pr_kc_jcje
endend
close Kcjymxb_Cursor
close Pr_Kc_Cursor
deallocate Kcjymxb_Cursor
deallocate Pr_Kc_Cursor
open Pr_Kc_Cursor
fetch next from Pr_Kc_Cursor into @pr_kc_id,@pr_kc_djlx,@pr_kc_spbh,@pr_kc_rq,@pr_kc_spsl,@pr_kc_spdj,@pr_kc_spje,@pr_kc_jslb,@pr_kc_jcsl,@pr_kc_jcdj,@pr_kc_jcje
open Kcjymxb_Cursorfetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcjefetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcje --第二条开始set @temp_fetch_status =@@fetch_status --定义个变量while @temp_fetch_status=0
begin --处理
fetch next from Pr_Kc_Cursor into @pr_kc_id,@pr_kc_djlx,@pr_kc_spbh,@pr_kc_rq,@pr_kc_spsl,@pr_kc_spdj,@pr_kc_spje,@pr_kc_jslb,@pr_kc_jcsl,@pr_kc_jcdj,@pr_kc_jcje set @temp_fetch_status=@@fetch_status
if @temp_fetch_status<>0
处理出错fetch next from Kcjymxb_Cursor into @kc_id,@kc_djlx,@kc_spbh,@kc_rq,@kc_spsl,@kc_spdj,@kc_spje,@kc_jslb,@kc_jcsl,@kc_jcdj,@kc_jcje set @temp_fetch_status=@@fetch_status
end
是应该将我的算法部分插入“处理”处,对吗?