游标

CREATE TABLE test_main (
id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);
INSERT INTO test_main(id, value) VALUES (1, 'ONE');
INSERT INTO test_main(id, value) VALUES (2, 'TWO');
INSERT INTO test_main(id, value) VALUES (3, 'THREE');

====================================

DECLARE
@id INT, @value VARCHAR(10);
BEGIN
-- 定义游标.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT id, value FROM test_main;
-- 打开游标.
OPEN c_test_main;
--填充数据.
FETCH NEXT FROM c_test_main INTO @id, @value;
--假如检索到了数据，才处理.
WHILE @@fetch_status = 0
BEGIN
PRINT @value;
--填充下一条数据.
FETCH NEXT FROM c_test_main INTO @id, @value;
END;
-- 关闭游标
CLOSE c_test_main;
--释放游标.
DEALLOCATE c_test_main;
END;

====================================

                        更新游标

==================================== 

declare @id int ,@value nvarchar(255);
begin
declare c3 cursor for
select * from CC ;
open C3
fetch next from C3 into @id ,@value ;
while @@FETCH_STATUS=0
begin
print @value ;
update CC set value=value+ '1' where current of C3 ;

fetch next from C3 into @id,@value ;
end ;
close C3 ;
deallocate C3 ;

End ;

================================= 

 滚动 游标

===================================

declare @id int ,@value nvarchar(255);
begin
declare c4 cursor scroll for
select id ,value from CC;
open c4 ;
fetch first from c4 into @id ,@value;
print @value ;
fetch last from c4 into @id ,@value ;
print @value ;
fetch absolute 3 from c4 into @id ,@value ;
print @value ;
fetch relative -2 from c4 into @id ,@value ;
print @value ;
fetch prior from c4 into @id,@value ;
print @value ;
fetch next from c4 into @id ,@value ;
close c4 ;
deallocate c4 ;

end ;
go