游标
--为什么要使用游标?
select * from Student
为了逐一地读取结果集中的每条记录;
--什么是游标?
总是与一个select语句相关联;
由结果集和指向结果中记录的位置指针来组成;
--如何使用游标?
(1)声明游标
(2)打开游标
(3)从游标中读取数据
fetch first | last | next | prior | absolute n| relative n
from 游标名
into @变量1, @变量2,...
(4)关闭游标
(5)释放游标资源
--演示1
-- (1)声明游标
declare cur_Student cursor for select Sno, Sname, Sage from Student
-- (2)打开游标
open cur_Student
-- (3)从游标中读取数据
declare @Sno char(5), @Sname char(6), @Sage int
fetch next from cur_Student into @Sno, @Sname, @Sage
while @@fetch_status = 0
begin
print '学号:' + @Sno + '姓名:' + @Sname + '年龄:' + Cast(@Sage as char(2))
if @Sage <20
print '该生还未成年!'
fetch next from cur_Student into @Sno, @Sname, @Sage
end
-- (4)关闭游标
close cur_Student
-- (5)释放游标资源
deallocate cur_Student
--演示2:游标嵌套
declare cur_Student cursor for select Sno, Sname, Sage from Student
-- (2)打开游标
open cur_Student
-- (3)从游标中读取数据
declare @Sno char(5), @Sname char(6), @Sage int, @Cname varchar(20)
fetch next from cur_Student into @Sno, @Sname, @Sage
while @@fetch_status = 0
begin
print '学号为:' + @Sno + '的学生' + @Sname + '选课了如下课程:'
declare cur_SC cursor for select Cname from Course, SC where Course.Cno = SC.Cno and Sno = @Sno
open cur_SC
fetch next from cur_SC into @Cname
while @@fetch_status = 0
begin
print @Cname
fetch next from cur_SC into @Cname
end
close cur_SC
deallocate cur_SC
fetch next from cur_Student into @Sno, @Sname, @Sage
end
close cur_Student
deallocate cur_Student