游标

我虽然不善言辞，但关于爱你，我无法掩饰。 --zhu
游标
定义：定位到结果集中某一行。
游标分类：
（1）静态游标（Static）:在操作游标的时候，数据发生变化，游标中数据不变
（2）动态游标（Dynamic）:在操作游标的时候，数据发生变化，游标中数据改变，默认值
（3）键集驱动游标（KeySet）:在操作游标的时候，被标识的列发生变化，游标中数据改变，其他列改变，游标中数据不变。
假设有如下表结构和数据：

create table Member
(
  MemberId int primary key indentity(1,1),
  MemberAccount nvarchar(20)  unique check(len(MemberAccount) between 6 and 12),
  MemberPwd nvarchar(20),
  MemberNickname nvarchar(20),
  MemberPhhone nvarchar(20)
)

insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('liubei','123456','刘备','4659874564')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('guanyu','123456','关羽','4659874565')
insert into Member(MemberAccount,MemberPwd,MemberNickname,MemberPhone)
values('zhangfei','123456','张飞','4659874566')

创建游标
(scroll:滚动游标，没有scroll则只能向下移动)

declare mycur cursor scroll
for select MemberAccount from Member

--打开游标

open mycur

--关闭游标

close mycur

--删除游标

deallocate mycur

--提取某行数据

fetch first from mycur --第一行
fetch last from mycur  --最后一行
fetch absolute 2 from mycur  --提取第二行
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
select *  from Member where  MemberAccount = @acc

--遍历游标

fetch absolute 1 from mycur into @acc
--@@fetch_status:0 提取成功，-1 失败，-2 不存在
while @@fetchh_status = 0
  begin
    print '提取成功:' + @acc
    fetch next from mycur into @acc 
  end

--利用游标进行数据的修改和删除

select * from Member
fetch absolute 2 from  mycur
update Member set MemberPwd ='123456' where current of mycur

fetch absolute 2  from mycur 
delete from  Member where current of mycur

--创建指向某行多列的游标，循环显示多列数据

declare mycur cursor scroll
for select MemberAccount,MemberPwd,MemberNickname from Member

open mycur

declare @acc varchar(20)
declare @pwd varchar(20)
declare @nickname varchar(20)
fetch absolute 1 from mycur into @acc,@pwd,@nickname
while @@fetchh_status = 0
  begin
    print '用户名:' + @acc + ',密码：' +@pwd + ',昵称：' +@nickname
    fetch next from mycur into @acc ,@pwd, @nickname
  end

close mycur