任务2 创建基本游标
(一)游标的使用
游标的操作由五个部分组成,这五个关键的部分符合下面的顺序:
(1)声明游标DECLARE。
(2)打开游标OPEN。
(3)从游标中提取数据FETCH。
(4)关闭游标CLOSE。
(5)当不再需要游标时释放游标DEALLOCATE。
例: 一个完整的示例:
USE 教务数据库
GO
--声明定义 名为男生_cur 的基本游标
DECLARE 男生_cur SCROLL CURSOR
FOR SELECT 学号,姓名,性别,专业班级
FROM 学生表
WHERE 性别='男'
FOR READ ONLY
--打开男生_cur 游标
OPEN 男生_cur
--利用游标读取数据
FETCH NEXT FROM 男生_cur
WHILE @@fetch_status=0
BEGIN
FETCH NEXT FROM 男生_cur
END
--关闭游标
CLOSE 男生_cur
--释放游标
DEALLOCATE 男生_cur
(二) 定义游标
SQL Server系统的游标必须按照以下五个步骤使用。
(1)游标定义,声明其特性,如游标中的数据行是否可以更新。
(2)执行T-SQL语句生成游标,如用OPEN打开游标。
(3)从游标中获取信息。
(4)关闭游标。
(5)释放游标。
•
(1)基本游标定义
基本游标定义的语法格式如下。
DECLARE 游标名 [INSENSITIVE] [SCROLL] CURSOR
FOR <SELECT查询>
[FOR{ READ ONLY|UPDATE [OF 字段名> [,…n]]}]
•
格式中各关键字、参数说明如下。
1)INSENSITIVE选项表示游标是静态游标。
2)SCROLL选项表示游标存取数据的方式。
3)“SELECT查询”决定游标结果集。
4)“READ ONLY”表示定义的游标为只读游标。
5)“OF 字段名[,…n]表示游标可以修改的列。
•
【例1】对教务数据库,定义一个名为“女生_cur”的基本游标,读取学生表的数据。
USE 教务数据库
GO
DECLARE 女生_cur SCROLL CURSOR
FOR SELECT 学号,姓名,性别,专业班级
FROM 学生表
WHERE 性别=‘女’
FOR READ ONLY
一个完整的示例:
USE 教务数据库
GO
--定义名为男生_cur 的基本游标
DECLARE 男生_cur SCROLL CURSOR
FOR SELECT 学号,姓名,性别,专业班级
FROM 学生表
WHERE 性别='男'
FOR READ ONLY
--打开男生_cur 游标
OPEN 男生_cur
--利用游标读取数据
FETCH NEXT FROM 男生_cur
WHILE @@fetch_status=0
BEGIN
FETCH NEXT FROM 男生_cur
END
--关闭游标
CLOSE 男生_cur
--释放游标
DEALLOCATE 男生_cur
•
(2)扩展游标定义
扩展游标定义的语法格式如下。
DECLARE 游标名 CURSOR
[ LOCAL|GLOBAL ] [ FORWARD_ONLY |SCROLL ] [ STATIC|KEYSET|DYNAMIC |FAST_FORWARD ] [ READ_ONLY| SCROLL_LOCKS|OPTIMISTIC ] [ TYPE_WARNING ]
FOR <SELECT查询>
[FOR UPDATE[OF 字段名>[,…n]]]
•
格式中各关键字、参数说明如下:
1)LOCAL选项表示游标的作用域为声明该游标的批处理、存储过程或触发器中,当建立游标的批处理、存储过程、触发器执行结束后,游标会自动释放。
2)GLOBAL选项表示游标的作用域为当前连接。
3)FORWARD_ONLY选项表示游标为单进游标,只能从第一行滚动到最后一行。
4)STATIC选项将游标定义为静态游标,使用数据的临时备份,禁止应用程序通过它修改基表数据。
•
5)KEYSET选项将游标定义为键集驱动游标,即在游标打开时,游标中列的顺序是固定的。对于键集游标有以下性质。
①应用程序不能通过键集游标向其基表插入数据。
②应用程序可以修改基表中的非键集列值。
③读取键集游标中被删除的数据行时,@@FETCH_ STATUS参数将返回-2。
④修改游标中的键集列相当于删除旧行,插入新行。
⑤可以看到通过WHERE CURRENT OF子句所修改的数据。
•
6)DYNAMIC选项将游标定义动态游标,使基表的变化能反映到游标中。
7) FAST_FORWARD选项表示启用优化的FORTWARD_ONLY和READ_ONLY游标。
8)SCROLL_LOCKS选项表示SQL Server系统在将数据读入游标的同时锁定基表中的数据行。
9)OPTIMISTIC选项表示数据读入游标时不锁定基表中被读入的数据行。
10)TYPE_WARNING选项表示无法建立用户指定类型的游标向客户端发出警告消息。
•
【例2】定义一个名为“学生_cura”的扩展游标,读取学生表的数据。
USE 教务数据库
GO
DECLARE 学生_cura CURSOR
LOCAL SCROLL DYNAMIC
TYPE_WARNING
FOR SELECT 学号,姓名,性别, 专业班级 ,出生地区
FROM 学生表
--完整应用示例
USE 教务数据库
GO
DECLARE 学生_cura CURSOR
LOCAL SCROLL DYNAMIC
TYPE_WARNING
FOR SELECT 学号,姓名,性别, 专业班级,出生地区
FROM 学生表
OPEN 学生_cura
FETCH NEXT FROM 学生_cura
WHILE @@fetch_status = 0
BEGIN
FETCH NEXT FROM 学生_cura
END
CLOSE 学生_cura
DEALLOCATE 学生_cura
2.查看游标信息和状态
(1)sp_cursor_list:检索当前连接的所有可见游标。
(2)sp_describe_cursor:检索游标属性信息,如作用域、名称、类型、状态和行数。
(3)sp_describe_cursor_columns:检索游标结果集合中的列属性。
(4)sp_describe_cursor_tables:检索游标锁引用的基表信息
(5)@@CURSOR_STATUS:读取游标状态或检查游标变量是否与游标相关联。
(6)@@FETCH_STATUS:读取最后一次游标数据提取操作结果状态。
(7)@@CURSOR_ROWS:显示游标集合中的行数。