游标之SQLServer

游标：定位到结果集中某一行

游标分类：

（1）静态游标（static）：在操作游标的时候，数据发生变化，游标中数据不变

（2）动态游标（Dynamic）：在操作游标的时候，数据发生变化，游标中数据改变，默认值

（3）键集驱动游标（KeySet）：在操作游标的时候，被标识的列发生变化，游标中数据改变，其他列改变，游标中数据不变。

• 假如有如下表结构和数据库

create table Member
(
MemberId int primary key identity(1,1),
MemberAccount nvarchar(20) unique check(len(MemberAccount) between 6 and 12),
MemberPwd nvarchar(20),
MemberNickName nvarchar(20),
MemberPhone nvarchar(20)
);
insert into Member(MemberAccount,MemberPwd,MemberNickName,MemberPhone)
values
('liubei','123456','刘备','123456789'),
('guanyu','123456','关羽','123456789'),
('zhangfei','123456','张飞','123456789'),
('zhaoyun','123456','赵云','123456789'),
('caocao','123456','曹操','123456789');

 游标简单介绍使用

--创建游标（scroll:滚动游标，没有scroll，只进）
declare mycur cursor scroll for select MemberAccount from Member;
--打开游标
open 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 --上移一行
--关闭游标
close mycur;
--删除游标
deallocate mycur;
go

• 提取游标数据存入变量，进行查询所有列信息

declare @acc varchar(20);
--定义游标
declare mycur cursor scroll for select MemberAccount from Member;
open mycur;
fetch absolute 2 from mycur into @acc;
--@@fetch_status: 0:提取成功，-1:失败 -2：不存在
while(@@fetch_status=0)
	begin
		print '提取成功:'+@acc;
		fetch next from mycur into @acc;
	end
--关闭游标
close mycur;
--删除游标
deallocate mycur;

• 利用游标进行删除数据

--定义游标
declare mycur cursor scroll for select * from Member;
open mycur;
fetch absolute 2 from mycur;
--用游标进行删除数据
delete from Member where current of mycur;
close mycur;
deallocate mycur;
go

• 利用游标进行修改数据

declare mycur cursor scroll for select *from Member;
open mycur;
fetch absolute 2 from mycur;
--用游标进行修改数据
update Member set MemberPwd='654321' where current of mycur;
close mycur;
deallocate mycur;
go

• 利用游标进行展示一行数据 

declare mycur cursor scroll for select MemberAccount,MemberNickName,MemberPhone from Member;
open mycur;
declare @MemberAccount varchar(20);
declare @MemberPwd varchar(20);
declare @MemberNickName varchar(20);
declare @MemberPhone varchar(20);
fetch first from mycur into @MemberAccount,@MemberNickName,@MemberPhone;
while @@FETCH_STATUS=0
begin
print '用户名:'+@MemberAccount+' 昵称:'+@MemberNickName+' 手机:'+@MemberPhone;
fetch next from mycur into @MemberAccount,@MemberNickName,@MemberPhone;
end
close mycur;
deallocate mycur;