6.关闭游标: close 游标名
暂时关闭游标,还可再使用OPEN打开.
7.释放游标: deallocate 游标名
从内存中清除游标.如果还想使用,必须再次声明.
对当前游标状态进行判断:
8. @@fetch_status 如果返回是0,说明当前操作是成功的.否则是失败的.
0 FETCH 语句成功。
-1 FETCH 语句失败或此行不在结果集中。
-2 被提取的行不存在。
举例1：
利用游标从学生表中逐条读取所有数据：
declare @i INT
DECLARE @TN CHAR(8),@FU CHAR(20)
declare mycursor cursor for select sno,sname from student
open mycursor
select @i=count(*) from student
while @@fetch_status=0 and @i>1
BEGIN
fetch next from mycursor INTO @TN,@FU
set @i=@i-1
PRINT @TN + ' ' + @FU
END
close mycursor
deallocate mycursor
DECLARE @TN CHAR(8),@FU CHAR(20)
declare mycursor cursor for select sno,sname from student
open mycursor
select @i=count(*) from student
while @@fetch_status=0 and @i>1
BEGIN
fetch next from mycursor INTO @TN,@FU
set @i=@i-1
PRINT @TN + ' ' + @FU
END
close mycursor
deallocate mycursor
结果：
s1001 Jack Dong
s1002 Lucy Dong
s1003 Brezse Dong
s1004 Andy Liu
s1005 Jack Chen
