Declare @dav as varchar(50) ---定义变量,用来保存从游标中取得的值Declare cursor5 cursor for select fl from table ---定义游标
open cursor5 ---打开游标fetch cursor5 into @pdav while @@fetch_status=0 ---循环读取值
begin
update table11 set .... where f1=@dav ---执行一定的操作
fetch cursor5 into @dav
endclose cursor5 ---关闭游标Deallocate Cursor5 ---删掉游标
open cursor5 ---打开游标fetch cursor5 into @pdav while @@fetch_status=0 ---循环读取值
begin
update table11 set .... where f1=@dav ---执行一定的操作
fetch cursor5 into @dav
endclose cursor5 ---关闭游标Deallocate Cursor5 ---删掉游标
日期 datetime,
材料代号 char,
数量 int,
单位成本 money)insert 收料单
select '2003-9-2','A',80,5
union all select '2003-9-5','B',40,3
union all select '2003-9-6','A',50,6
union all select '2003-9-12','B',60,4
union all select '2003-9-18','A',50,7
union all select '2003-9-28','A',50,6
create table 发料单(
日期 datetime,
材料代号 char,
数量 int)insert 发料单
select '2003-9-8','B',20
union all select '2003-9-15','B',50
union all select '2003-9-12','A',120
union all select '2003-9-20','A',80select isnull(A.日期,isnull(B.日期,C.日期)) 日期,
A.数量 收料数量, A.单位成本 收料单位成本, A.单位成本*A.数量 收料总成本,
B.数量 发料数量, null 发料单位成本, null 发料总成本,
C.数量 结存数量, C.单位成本 结存单位成本, C.单位成本*C.数量 结存总成本
into #t
from 收料单 A full join 发料单 B on A.日期 = B.日期 and A.材料代号 = B.材料代号
full join (select cast('2003-9-1' as datetime) 日期, 'A' 材料代号,100 数量, cast(4 as numeric(10,4)) 单位成本 ) C on A.日期 = C.日期 and A.材料代号 = C.材料代号
where isnull(A.材料代号,isnull(B.材料代号,C.材料代号)) = 'A'
order by 日期declare @ina int, @inc money, @outa int, @sta int, @stc money
declare @sta1 int, @stc1 money
declare c1 cursor SCROLL_LOCKS for select 收料数量, 收料单位成本, 发料数量, 结存数量, 结存单位成本 from #t for update
open c1
fetch c1 into @ina, @inc, @outa, @sta, @stc
if @@fetch_status = 0
begin
set @sta1 = @sta
set @stc1 = @stc
fetch c1 into @ina, @inc, @outa, @sta, @stc
end
while @@fetch_status = 0
begin
select @ina, @inc, @outa, @sta1, @stc1
update #t set
结存数量 = @sta1 + isnull(@ina, 0) - isnull(@outa, 0),
结存单位成本 = (@stc1 * @sta1 + isnull(@inc, 0) * isnull(@ina, 0) - @stc1 * isnull(@outa, 0)) / (@sta1 + isnull(@ina, 0) - isnull(@outa, 0)),
结存总成本 = @stc1 * @sta1 + isnull(@inc, 0) * isnull(@ina, 0) - @stc1 * isnull(@outa, 0)
where CURRENT OF c1
if @outa > 0
update #t set
发料单位成本 = @stc1,
发料总成本 = @stc1 * @outa
where CURRENT OF c1
set @stc1 = (@stc1 * @sta1 + isnull(@inc, 0) * isnull(@ina, 0) - @stc1 * isnull(@outa, 0)) / (@sta1 + isnull(@ina, 0) - isnull(@outa, 0))
set @sta1 = @sta1 + isnull(@ina, 0) - isnull(@outa, 0)
fetch c1 into @ina, @inc, @outa, @sta, @stc
end
close c1
deallocate c1
select * from #t order by 日期
create proc p1 (@材料代号 char)
as
select isnull(A.日期,isnull(B.日期,C.日期)) 日期,
A.数量 收料数量, A.单位成本 收料单位成本, A.单位成本*A.数量 收料总成本,
B.数量 发料数量, null 发料单位成本, null 发料总成本,
C.数量 结存数量, C.单位成本 结存单位成本, C.单位成本*C.数量 结存总成本
into #t
from 收料单 A full join 发料单 B on A.日期 = B.日期 and A.材料代号 = B.材料代号
full join (select cast('2003-9-1' as datetime) 日期, @材料代号 材料代号,100 数量, cast(4 as numeric(10,4)) 单位成本 ) C on A.日期 = C.日期 and A.材料代号 = C.材料代号
where isnull(A.材料代号,isnull(B.材料代号,C.材料代号)) = @材料代号
order by 日期declare @ina int, @inc money, @outa int, @sta int, @stc money
declare @sta1 int, @stc1 money
declare c1 cursor SCROLL_LOCKS for select 收料数量, 收料单位成本, 发料数量, 结存数量, 结存单位成本 from #t for update
open c1
fetch c1 into @ina, @inc, @outa, @sta, @stc
if @@fetch_status = 0
begin
set @sta1 = @sta
set @stc1 = @stc
fetch c1 into @ina, @inc, @outa, @sta, @stc
end
while @@fetch_status = 0
begin
select @ina, @inc, @outa, @sta1, @stc1
update #t set
结存数量 = @sta1 + isnull(@ina, 0) - isnull(@outa, 0),
结存单位成本 = (@stc1 * @sta1 + isnull(@inc, 0) * isnull(@ina, 0) - @stc1 * isnull(@outa, 0)) / (@sta1 + isnull(@ina, 0) - isnull(@outa, 0)),
结存总成本 = @stc1 * @sta1 + isnull(@inc, 0) * isnull(@ina, 0) - @stc1 * isnull(@outa, 0)
where CURRENT OF c1
if @outa > 0
update #t set
发料单位成本 = @stc1,
发料总成本 = @stc1 * @outa
where CURRENT OF c1
set @stc1 = (@stc1 * @sta1 + isnull(@inc, 0) * isnull(@ina, 0) - @stc1 * isnull(@outa, 0)) / (@sta1 + isnull(@ina, 0) - isnull(@outa, 0))
set @sta1 = @sta1 + isnull(@ina, 0) - isnull(@outa, 0)
fetch c1 into @ina, @inc, @outa, @sta, @stc
end
close c1
deallocate c1
select * from #t order by 日期
goexec p1 'A'