游标

基本操作

1. 申明游标
2. 打开游标
3. 读取游标中的数据
4. 关闭游标
5. 释放游标

申明游标

【例1】声明名称为cursor_author的游标

DECLARE cursor_author CURSOR FOR
SELECT auth_name,auth_phone FROM authors

打开游标

【例2】打开上例中声明的名称为cursor_author的游标

OPEN cursor_author

读取游标中的数据

【例3】使用名称为cursor_author的光标，检索authors表中的记录

FETCH NEXT FROM cursor_author
WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM cursor_author
END

关闭游标

【例4】关闭名称为cursor_author的游标

CLOSE cursor_author

释放游标

【例5】使用DEALLOCATE语句释放名称为cursor_fruit的变量

DEALLOCATE cursor_author

游标的运用

使用游标变量

【例6】声明名称为@VarCursor的游标变量

DECLARE @VarCursor CURSOR                 --声明游标变量
DECLARE cursor_author CURSOR FOR          --创建游标
SELECT auth_name,auth_phone FROM authors
OPEN cursor_author                        --打开游标
SET @VarCursor = cursor_author            --为游标变量赋值 
FETCH NEXT FROM @VarCursor                --从游标变量中读取值
WHILE @@FETCH_STATUS = 0                  --判断FETCH语句是否执行成功
BEGIN 
  FETCH NEXT FROM @VarCursor              --读取游标变量中的数据
END
CLOSE @VarCursor                          --关闭游标
DEALLOCATE @VarCursor                     --释放游标

用游标为变量赋值

在游标的操作过程中，可以使用FETCH语句将数据值存入变量，这些保持表中列值的变量可以在后面的程序中使用。

【例17】创建游标cursor_variable，将fruits表中记录的f_name，f_price值赋给变量@fruitName和@fruitPrice，并打印输出

DECLARE @AuthorName VARCHAR(20),@AuthorPhone VARCHAR(20)
DECLARE cursor_variable CURSOR FOR
SELECT auth_name,auth_phone FROM authors
WHERE auth_id = 1;
OPEN cursor_variable
FETCH NEXT FROM cursor_variable
INTO @AuthorName,@AuthorPhone
PRINT '姓名：'+'电话：'
WHILE @@FETCH_STATUS = 0 
BEGIN 
  PRINT @AuthorName + ''+@AuthorPhone
  FETCH NEXT FROM cursor_variable
  INTO @AuthorName,@AuthorPhone
END
CLOSE cursor_variable
DEALLOCATE cursor_variable

用ORDER BY子句改变游标中行的顺序

游标是一个查询结果集，将ORDER BY子句添加到查询中可以对游标查询结果排序。

【例8】声明名称为cursor_author的游标，对authors表中的记录按照价格字段降序排列

DECLARE cursor_author CURSOR FOR
SELECT auth_id,auth_name,auth_age FROM authors
ORDER BY auth_age DESC
OPEN cursor_author
FETCH NEXT FROM cursor_author
WHILE @@FETCH_STATUS = 0 
FETCH NEXT FROM cursor_author
CLOSE cursor_author
DEALLOCATE cursor_author

用游标修改数据

【例9】声明整型变量@ID=7，然后声明一个对authors表进行操作的游标，打开该游标，使用FETCH NEXT方法来获取游标中的每一行的数据，如果获取到的记录的id字段值与@ID值相同，将记录中的auth_phone字段修改，最后关闭并释放游标

DECLARE @sID INT
DECLARE @ID INT = 7
DECLARE cursor_author CURSOR FOR
SELECT auth_id FROM authors
OPEN cursor_author
FETCH NEXT FROM cursor_author
INTO @sID
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @sID = @ID
    BEGIN
      UPDATE authors SET auth_phone = 100 WHERE auth_id = @ID
    END
  FETCH NEXT FROM cursor_author
  INTO @sID
END
CLOSE cursor_author
DEALLOCATE cursor_author
 
SELECT * FROM authors WHERE auth_id = 7

用游标删除数据

【例10】使用游标删除表中id=7的记录

DECLARE @sID INT
DECLARE @ID INT = 7
DECLARE cursor_delete CURSOR FOR
SELECT auth_id FROM authors
OPEN cursor_delete
FETCH NEXT FROM cursor_delete
INTO @sID
WHILE @@FETCH_STATUS = 0
BEGIN 
  IF @sID = @ID
    BEGIN
      DELETE FROM authors WHERE auth_id = @ID
    END
  FETCH NEXT FROM cursor_delete
  INTO @sID
END
CLOSE cursor_delete
DEALLOCATE cursor_delete

使用系统存储过程管理游标

使用系统存储过程sp_sursor_list、sp_describe_cursor、sp_describe_cursor_columns、sp_describe_cursor_table可以分别查看当前为连接打开的服务器游标属性、服务器游标属性、游标结果集中列的属性、被引用对象或基本表的属性。

sp_sursor_list存储过程

报告当前为连接打开的服务器游标的属性。

【例11】打开一个全局游标，并使用sp_cursor_list报告该游标的属性

-- 申明游标
DECLARE cursor_author CURSOR FOR
SELECT auth_name FROM authors
WHERE auth_name LIKE '%飞'
-- 打开游标
OPEN cursor_author
 
-- 申明游标变量
DECLARE @Report CURSOR
 
-- 执行sp_cursor_list存储过程，将结果保存到@Report游标变量中
EXEC sp_cursor_list @cursor_return = @Report OUTPUT,@cursor_scope = 2
 
-- 输出游标变量中的每一行
FETCH NEXT FROM @Report
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  FETCH NEXT FROM @Report
END
 
--关闭并释放游标变量
CLOSE @Report
DEALLOCATE @Report
 
--关闭并释放原始游标
CLOSE cursor_author
DEALLOCATE cursor_author

问题

1. 游标变量可以为游标变量赋值吗？

当然可以，游标可以赋值为游标变量，也可以将一个游标变量赋值给另一个游标变量，例如SET @cursorVal1 = @cursorVal12。

2. 游标用完后如何处理？

当然是要关闭和删除，关闭游标的作用是释放游标和数据库的连接，删除游标是将其从内存中删除，释放系统资源。