CurSor游标对于Sql的新手而言一直是个比较难的问题,其困难不在于理解,而在于应用。在这个问题上我也颇费了些周折,有段日子视Cursor如同一个神秘的姑娘,隔江相望。有了个机会叫我得到了佳人一笑,在此与大家分享一个。
范例:
当前,有一BOM表其结构为:
目前有A11这种物料10个,要求进行测算可以得到多少个其父项产品,其优先顺序为id顺序;
其过程结果如下:
注:qty1为分配扣的余额;
最终的应用结果如下:
角本过程:
if exists(select name from sysobjects where name='bom1')
begin
drop table bom1
end
go
create table bom1
(
id int primary key identity(1,1),
p varchar(5),
c varchar(5),
qty int,
qty1 int
)
go
DECLARE @d as int,@ld as int;
declare @p as varchar(5),@c as varchar(5),@qty as int;
select @d=10;
declare d1 SCROLL cursor FOR
select P,C,qty from bom where C='A11';
OPEN d1;
fetch next from d1 into @p,@c,@qty;
R:while @@FETCH_STATUS=0
begin
if (@d-@qty>0)
begin
print @@fetch_status;
insert into bom1 values(@p,@c,@qty,@d-@qty);
select @d=@d-@qty;
fetch next from d1 into @p,@c,@qty;
select @ld=@d-@qty
end;
else
begin
goto E
end;
end;
print '@d-@qty:' + cast(@d-@qty as varchar)+'@@FETCH_STATUS:'+cast(@@FETCH_STATUS as varchar)
if(@ld>=0 and @@FETCH_STATUS<>0)
begin
fetch first from d1 into @p,@c,@qty
print @@fetch_status
goto R;
end
E:CLOSE d1;
DEALLOCATE d1;
go
灵活使用Cursor以及正确理解Scroll Cursor与Cursor可以有效地解决相应多的实际问题。
最终的结果呈现:
select * from bom1 /*呈现过程*/
select p,count(1) as t from bom1 group by p /*呈现结果*/
祝大家快乐!