T-sql 游标的使用

--游标就是从数据库一行一行的查询数据，用户可以一行一行的处理数据，不像临时表一样，一下把所有的都查询出来
--定义游标
declare Test_Cursor cursor for
--查询数据
select top 10 ID from PersonInfo
--打开游标
open Test_Cursor
declare @ID int
--给每一行数据赋值
fetch next from Test_Cursor into @ID
print @ID
--关闭游标
close Test_Cursor
--释放
deallocate Test_Cursor

--例子

declare test_Cursor cursor scroll for
select Name from PersonInfo
open test_Cursor
declare @Name varchar(50)
--取下一行
fetch next from test_Cursor into @Name
print @Name
--取得最后一行
fetch last from test_Cursor into @Name
print @Name
--取第一行
fetch prior from test_Cursor into @Name
print @Name
--取得第三行
fetch absolute 3 from test_Cursor into @Name
print @Name
--取得相对目前来说上一行
fetch relative -1 from test_Cursor into @Name
print @Name

close test_Cursor
deallocate test_Cursor

--实例

declare test_Cursor cursor scroll for
select ID from PersonInfo
open test_Cursor
declare @ID varchar(50),@GSCount int
--取下一行
fetch next from test_Cursor into @ID
WHILE @@FETCH_STATUS = 0
BEGIN
select * from PersonInfo where ID = @ID
fetch next from test_Cursor into @ID
end
close test_Cursor
deallocate test_Cursor