SQL Server 游标的使用

一、SQL server 游标的简单使用

一个对表进行操作的T-SQL语句通常都可产生或处理一组记录，但是许多应用程序，尤其是T-SQL嵌入的主语言，通常不能把整个结果集作为一个单元来处理，这些应用程序就需要用一种机制来保证每次处理结果集中的一行或几行，游标(cursor)就提供了这种机制。

SQL Server对游标的使用要遵循：声明游标–打开游标–读取数据–关闭游标–删除游标。下面让我们来看看几种常用游标是怎么使用的！

1、只读游标的使用（只能使用next提取数据)

--声明一个只读游标
declare cur_stu cursor
for select sno as '学号',sname as '姓名' from stu where mno in(select mno from major where mname='计算机工程')
for read only
--打开游标
open cur_stu
--读取数据
--根据fetch的状态值读取数据，0表示执行成功
-- -1表示所要读取的数据不在结果集中
-- -2表示被提取的行已不存在
while @@FETCH_STATUS=0
begin
fetch next from cur_stu
end
--关闭游标
close cur_stu
--删除游标
deallocate cur_stu

2、动态游标的使用

--声明一个动态游标
declare cur_stu cursor dynamic
for select sno as '学号',sname as '姓名' from stu
--打开游标
open cur_stu
--从游标cur_stu中提取第一行数据
fetch first from cur_stu

--从游标cur_stu中提取下一行数据
fetch next from cur_stu

--从游标cur_stu中提取上一行数据
fetch prior from cur_stu

--从游标cur_stu中提取最后一行数据
fetch last from cur_stu

--从游标cur_stu中提取当前行的下两行记录
fetch relative 2 from cur_stu

--从游标cur_stu中提取当前行的上两行记录
fetch relative -2 from cur_stu
--关闭游标
close cur_stu 
--删除游标
deallocate cur_stu

3、使用游标更新表中某个字段

例：对SC表（学生选课表）添加一个等级列(rank)，若学生成绩80及以上等级A，70-79分 为B，其余为C

--定义一个动态游标
declare sc_cur cursor dynamic
for select sno,cno,grade from sc
declare @xh char(4)
declare @kch char(4)
declare @cj numeric(6,1)
declare @dj char(1)
--打开游标
open sc_cur
--根据fetch的状态值读取数据，0表示执行成功
-- -1表示所要读取的数据不在结果集中
-- -2表示被提取的行已不存在
while @@FETCH_STATUS=0
begin
fetch next from sc_cur into @xh,@kch,@cj
if @cj >=80
set @dj='A'
if @cj <80 and @cj>=70
set @dj='B'
if @cj<70 or @cj is null
set @dj='C'
update sc set rank = @dj where sno=@xh and cno=@kch
end
go
--关闭游标
close sc_cur
--删除游标
deallocate sc_cur

执行后的结果

4、游标变量与存储过程结合使用
例：创建一个存储过程，通过输入一个学生的学号，通过输出参数返 回该学生所有选修课程的成绩

create procedure pro_grade @xh char(4), 
        @cur_grade cursor varying output
as
--forward_only游标只能从第一行滚动到最后一行，只支持fetch的next提取选项
set @cur_grade = cursor forward_only  for
        select sno,cno,grade from sc where sno=@xh
--打开游标       
open @cur_grade
go

--声明一个游标变量
declare @my_cur cursor

--执行存储过程
exec pro_grade @xh='S001', @cur_grade=@my_cur output

--读取数据
while @@FETCH_STATUS=0
begin
fetch next from @my_cur
end

--关闭游标
close @my_cur
--删除游标
deallocate @my_cur

执行后的结果