游标操作--SQL

使用游标有四种基本的步骤:声明游标、打开游标、提取数据、关闭游标。
--声明游标
declare my_cursor cursor keyset for select * from info
--删除游标资源
deallocate my_cursor
--打开游标,在游标关闭或删除前都有效
open my_cursor
--关闭游标
close my_cursor
--声明局部变量
declare @id int,@name varchar(20),@address varchar(20)
--定位到指定位置的记录
fetch absolute 56488 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到当前记录相对位置记录
fetch relative -88 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到当前记录前一条
fetch prior from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到当前记录后一条
fetch next from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到首记录
fetch first from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到尾记录
fetch last from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address

例程：
Create proc Insert_Info
as

/*scroll表示可随意移动游标指针（否则只能向前），dynamic表示可以读写游标（否则游标只读）*/
declare my_cursor cursor scroll dynamic
for select * from Info
open my_cursor
declare @id bigint
declare @name sysname
declare @adress sysname
fetch next from my_cursor into @id,@name,@adress
while(@@fetch_status = 0)
 begin
  print 'UserName:' + @name
  fetch next from my_cursor into  @id,@name,@adress
 end
fetch first from my_cursor into @id,@name,@adress
print @adress

 

--update Info set name='PeterZhang' where current of my_cursor
--delete from Info where current of my_cursor

close my_cursor
deallocate my_cursor