游标相关操作

select * from zd_ksxx

--创建游标（scroll:滚动游标，没有scroll，只进）
declare mycur cursor scroll
for select ksmc from ZD_KSXX

--游标打开
open mycur

--提取某行数据
fetch first from mycur --第一行
fetch last from mycur --最后一行
fetch absolute 2 from mycur --提取第2行
fetch relative 2 from mycur --当前行，下移2行
fetch next from mycur --下移一行
fetch prior from mycur --上移一行

--提取游标数据存入变量，进行查询所有列信息
declare @acc varchar(20)
fetch absolute 2 from mycur into @acc --提取第2行
select * from ZD_KSXX where KSMC=@acc

--遍历游标
declare @acc2 varchar(20)
fetch absolute 1 from mycur into @acc2 --提取第1行
--@@fetch_status:0=>提取成功；-1=>失败；-2=>不存在
while @@FETCH_STATUS = 0
	begin
		print '提取成功：'+@acc2
		fetch next from mycur into @acc2
	end

--利用游标进行数据的需改和删除
select * from ZD_KSXX
fetch absolute 3 from mycur
update ZD_KSXX set KSLX=6 where current of mycur


fetch absolute 3 from mycur
delete from ZD_KSXX where current of mycur
--关闭游标
close mycur
--删除游标
deallocate mycur

------------------------------------------------------------------------
--创建执行某行多列的游标，循环显示多列数据
declare mycur cursor scroll
for select KSBM,KSMC from ZD_KSXX

--打开游标
open mycur

--遍历游标
declare @ksbm varchar(20)
declare @ksmc varchar(20)
fetch absolute 1 from mycur into @ksbm,@ksmc --提取第1行
--@@fetch_status:0=>提取成功；-1=>失败；-2=>不存在
while @@FETCH_STATUS = 0
	begin
		print '提取成功：'+@ksbm+'=>'+@ksmc
		fetch next from mycur into @ksbm,@ksmc
	end

--关闭游标
close mycur
--删除游标
deallocate mycur

-------------------------------------------------------------------------