一、游标：游动的指针

1、游标使用过程（生命周期）
A、声明游标
声明游标的语法： declare 游标名 cursor
for
select 语句 [翻滚][只读]
实例：定义游标存放员工表的所有记录
declare mycursor cursor for select * from yuangong
说明：游标会占用内存
cursor -- 游标类型
B、打开游标
打开游标的语法：　open 游标名
open mycursor
C、数据处理
操作游标中数据的语法：fetch next from 游标名
从游标中取出数据
fetch next from mycursor
D、关闭游标
关闭游标语法：close 游标名
close mycursor
E、释放游标
释放游标的语法：deallocate 游标名
deallocate mycursor
说明：A、游标名
B、结果集：数据来自何处
C、游标的属性（只读、可写（可限制）、标准（默认））
D、游标的类型（只进（默认）、翻滚（scroll））
综合实例：
方法一：@@fetch_status 用于存放fetch的结果
0 fetch语句成功
-1 fetch语句失败或行不在结果集中
-2 提取的行不存在
declare ygcursor cursor for select * from yuangong
open ygcursor
declare @s int,@i int
set @s=(select count(*) from yuangong)
set @i=1
fetch next from ygcursor
while @@fetch_status=0 and @i<@s
begin
fetch next from ygcursor
set @i=@i+1
end
close ygcursor
deallocate ygcursor
2、实例：显示1、3、5、最后一行
方法一：ADO技术（ASP、PHP、JSP...）
方法二：游标
实现：对结果集中的记录逐条处理
declare ygcursor cursor scroll for select * from yuangong --scroll为翻滚游标
open ygcursor
fetch first from ygcursor
fetch absolute 3 from ygcursor
fetch absolute 5 from ygcursor
fetch last from ygcursor
close ygcursor
deallocate ygcursor
说明：fetch next prior first last absolute N from 游标名
next -- 下一条 prior -- 上一条　first -- 第一条 last -- 最后一条 absolute N -- 第N条
实例：
declare kscursor cursor for select username,score from ks --score为分数
open kscursor
declare @i int,@uname varchar(10)
fetch next from kscursor into @uname,@i --into为放入的意思
while @@fetch_status=0
begin
if @i<60
begin
print @uname+'：'+cast(@i as char(3))+'不及格'
end
else
begin
if @i>=60 and @i<=79
begin
print @uname+'：'+cast(@i as char(3))+'及格'
end
else
begin
print @uname+'：'+cast(@i as char(3))+'优秀'
end
end
fetch next from kscursor into @uname,@i
end
deallocate kscursor
实例：通过游标修改倪国庆的分数：修改为72
declare kscursor cursor for select username,score from ks --注意字段的顺序
open kscursor
declare @i int,@uname varchar(10)
fetch next from kscursor into @uname,@i --注意与上面字段的顺序一致
while @@fetch_status=0
begin
update ks set score=72 where current of kscursor
end
fetch next from kscursor into @uname,@i
end
deallocate kscursor
说明：删除数据：delete from ks ...
过多的使用游标，会造成系统资源的浪费
实例：定义只读游标(for read only)
declare kscursor cursor for select username,score from ks for read only
open kscursor
fetch next from kscursor
while @@fetch_status=0
begin
fetch next from kscursor
end
deallocate kscursor
实例：通过游标只允许修改分数
declare kscursor cursor for select username,score from ks for update of score --或可写(for update of score,age)
open kscursor
declare @i int,@uname varchar(10)
fetch next from kscursor into @uname,@i
while @@fetch_status=0
begin
if @uname='孙萍萍'
begin
update ks set age=29 where current of kscursor
end
fetch next from kscursor into @uname,@i
end
deallocate kscursor