任务4 使用循环提取游标数据
（四）游标应用
1．打开游标
游标定义之后，必须打开才能使用。打开游标的语法格式如下。
OPEN {{[GLOBAL] 游标名}|游标变量名}
(1)GLOBAL选项表示游标为全局游标。
(2)如果全局游标和局部游标都使用同一个游标名，如果使用GLOBAL，便表明其为全局游标，否则表明其为局部游标。
(3)游标变量名表示引用一个游标。
•
【例6】打开前面定义的“学生_cura”游标。
①OPEN 学生_cura
GO
②OPEN ＠学生游标
GO
2．读取游标数据•
从游标中读取数据主要使用FETCH命令，其语法格式如下。
FETCH
[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|＠nvar}| RELATIVE{n|＠nvar}] FROM]
{{[GLOBAL] 游标名}|＠游标变量名}
[INTO ＠变量名[,…n]]
(1)NEXT选项表示如果是在OPEN之后第一次执行FETCH命令，返回结果集的第一行，否则移动游标(指针)指向结果集的下一行。
(2)PRIOR、FIRST、LAST、ABSOLUTE{n |＠nvar}、RELATIVE{n|＠nvar}等选项，只有在定义游标时使用了SCROLL选项才能使用。
•
(3)INTO ＠变量名[,…n] 选项表示将用FETCH命令提取的数据存放在（多个）变量中。
(4)用＠＠FETCH_STATUS全局变量可以返回上次执行FETCH命令的状态。
使用FETCH语句从游标中读取数据时，应注意以下情况。
(l)在定义基本游标时，没有选择SCROLL选项，只能使用FETCH NEXT命令从游标中读取数据。
(2) 在定义扩展游标时，应注意以下情况。
①如果使用了FORWARD_ONLY或FAST_ FORWARD选项，则只能使用FETCH NEXT命令。
②使用DYNAMIC、SCROLL选项，游标支持所有的FETCH选项，但是禁用ABSOLUTE选项。
【例7】打开已定义的“学生_cura”游标，读取游标中的数据。
USE 教务数据库
GO
DECLARE 女生_cura CURSOR
LOCAL SCROLL DYNAMIC
TYPE_WARNING
FOR SELECT 学号,姓名,性别,专业班级,出生地区
FROM 学生表
WHERE 性别='女'
OPEN 女生_cura
FETCH NEXT FROM 女生_cura
WHILE @@fetch_status = 0
BEGIN
FETCH NEXT FROM 女生_cura
END
CLOSE 女生_cur
DEALLOCATE 女生_cur
3．移动游标
如果在游标定义时使用了关键字SCROLL，则可以用FETCH语句在游标的结果集内向前或向后移动，也可以直接跳到集合的某一条记录。
【例8】定义游标并移动游标的指针。
USE 教务数据库
GO
DECLARE 学生_curc CURSOR
SCROLL
READ_ONLY
FOR SELECT * FROM 学生表
OPEN 学生_curc
FETCH NEXT FROM 学生_curc
FETCH PRIOR FROM 学生_curc
FETCH FIRST FROM 学生_curc
FETCH LAST FROM 学生_curc
FETCH ABSOLUTE 5 FROM 学生_curc
FETCH RELATIVE-2 FROM 学生_curc
4．用游标进行修改和删除操作
•
如果在声明游标时使用了FOR UPDATE子语句，那么就可以在UPDATE或DELETE语句中，用WHERE CURRENT OF子句直接修改或删除游标中当前数据行的数据。当改变游标中数据时，会自动地影响到游标的基表；如果在定义游标时选择了INSENSITIVE选项，该游标中的数据不能修改。
•
(1)用游标修改基表数据
用游标修改基表数据的语法格式如下。
UPDATE 表名
SET {字段名 = {表达式|DEFAULT | NULL}[,…n]
WHERE CURRENT OF {{[GLOBAL] 游标名}|游标变量}
•
(2)用游标删除基表数据
用游标删除基表数据的语法格式如下。
DELETE FROM 表名 WHERE
CURRENT OF {{[GLOBAL] 游标名}|游标变量}
【例9】用游标进行定位修改学生名册表中数据。要求先查看学生名册表中的每一数据行，然后将学号为“201207309”学生的联系电话改为“63888320”，并将其家庭住址改为“重庆”
5．关闭游标
(1)使用CLOSE命令关闭游标
使用CLOSE命令关闭游标的语法格式如下。
CLOSE{{[GLOBAL]游标名}|游标变量}
(2)自动关闭游标
游标在存储过程、触发器和T-SQL语句中，如果使用了事务结构，在结束事务时游标会自动关闭。
•
【例10】关闭前面定义的“学生_cura”游标。
①CLOSE 学生_cura
GO
②CLOSE ＠学生游标
GO
6．释放游标
用CLOSE命令关闭游标时，并没有释放游标占用的数据空间。
释放游标命令的语法格式如下。
DEALLOCATE{{[GLOBAL] 游标名} | 游标变量}
【例11】通过游标变量“＠学生游标a”来操作“学生_cura”游标，操作完成后删除游标变量。
USE 学生管理
GO
DECLARE 学生_cura CURSOR
GLOBAL SCROLL DYNAMIC
FOR
SELECT 学号,姓名,入学成绩,联系电话
FROM 学生名册
DECLARE ＠学生游标a CURSOR
SET ＠学生游标a = 学生_cura
OPEN ＠学生游标a
FETCH NEXT FROM ＠学生游标a
CLOSE ＠学生游标a
DEALLOCATE ＠学生游标a
要点小结:
游标的作用及使用步骤
创建基本游标
使用变量保存游标数据
使用循环环提取游标数据
•
一、简答题
1、简述游标的作用及使用步骤
2、游标可分成哪几种类型？
二、上机实践题
上机调试示例并适当改进优化。
备用资料：
1.使用sp_cursor_list 查看
2.通过sp_describe_cur r查看
sp_cursor_list报告当前为连接打开的服务器游标的属性。
语法如下：
sp_cursor_list [ @cursor_return = ] cursor_variable_name OUTPUT
, [ @cursor_scope = ] cursor_scope
参数说明：
[ @cursor_return = ] cursor_variable_name OUTPUT：已声明的游标变量的名称。cursor_variable_name 的数据类型为 cursor，无默认值。游标是只读的可滚动动态游标。
[ @cursor_scope = ] cursor_scope：指定要报告的游标级别。cursor_scope 的数据类型为 int，无默认值，可取值如表10.3所示。
表10.3 cursor_scope可取的值
| 值 | 说 明 |
| 1 | 报告所有本地游标 |
| 2 | 报告所有全局游标 |
| 3 | 报告本地游标和全局游标 |
sp_describe_cursor用于报告服务器游标的属性。
语法如下：
sp_describe_cursor [ @cursor_return = ] output_cursor_variable OUTPUT
{ [ , [ @cursor_source = ] N'local'
, [ @cursor_identity = ] N'local_cursor_name' ]
| [ , [ @cursor_source = ] N'global'
, [ @cursor_identity = ] N'global_cursor_name' ]
| [ , [ @cursor_source = ] N'variable'
, [ @cursor_identity = ] N'input_cursor_variable' ]
}
sp_describe_cursor语句的参数及说明如表10.4所示。
表10.4 sp_describe_cursor语句的参数及说明
--【 例】创建一个游标，依次从教务数据库的[学生表]中提取第一、三、四、六和最后一行的学生信息。
--T-SQL语句如下：
DECLARE stu_cursor SCROLL CURSOR FOR
SELECT *
FROM 教务数据库.dbo.学生表
FOR READ ONLY
GO
OPEN stu_cursor
GO
FETCH FIRST FROM stu_cursor--提取第1行数据
FETCH RELATIVE 2 FROM stu_cursor--使用相对位置取第3行数据
FETCH NEXT FROM stu_cursor--提取第4行数据
FETCH ABSOLUTE 6 FROM stu_cursor--使用绝对位置取第6行数据
FETCH LAST FROM stu_cursor--提取最后1行数据
CLOSE stu_cursor
DEALLOCATE stu_cursor
| 参 数 | 描 述 |
| [ @cursor_return = ] output_cursor_variable OUTPUT | 用于接收游标输出的声明游标变量的名称。output_cursor_variable的数据类型为cursor，无默认值。调用sp_describe_cursor时，该参数不得与任何游标关联。返回的游标是可滚动的动态只读游标 |
| [ @cursor_source = ] { N'local'| N'global' | N'variable' } | 指定是使用局部游标的名称、全局游标的名称还是游标变量的名称来指定要报告的游标。该参数的类型为nvarchar(30) |
| [ @cursor_identity = ] N'local_cursor_name' ] | 由具有LOCAL关键字或默认设置为LOCAL的DECLARE CURSOR语句创建的游标名称。local_cursor_name的数据类型为nvarchar(128) |
| [ @cursor_identity = ] N'global_cursor_name' ] | 由具有GLOBAL关键字或默认设置为GLOBAL的DECLARE CURSOR语句创建的游标名称。global_cursor_name的数据类型为nvarchar(128) |
| [ @cursor_identity = ] N'input_cursor_variable' ] | 与所打开游标相关联的游标变量的名称。input_cursor_variable的数据类型为nvarchar(128) |
本章常见错误
1.打开游标使用之后忘记关闭和释放游标
初学者经常在使用游标之后忘记关闭游标，这会造成结构的占用和资源浪费。使用游标之后要记得用CLOSE关闭，并使用DEALLOCATE释放游标占用的数据结构，删除游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。
2.为什么要避免使用游标
因为游标的效率较差，如果游标操作的数据量非常大，那么就应该改写。如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。游标会破坏系统整体性能，而且游标还占用内存。
