SQL Server 2005 数据库原理与应用案例教程
游标的主要用途就是在T-SQL脚本程序、存储过程、触发器中对SELECT语句返回的结果集进行逐行逐字段处理，把一个完整的数据表按行分开，一行一行的逐一提取记录，并从这一记录行中逐一提取各项数据。
游标与变量类似，必须先定义后使用。
游标的使用过程：定义声明游标→打开游标→从游标中提取记录并分离数据→关闭游标→释放游标。 6.3.2 游标使用
1．定义游标 命令格式：
DECLARE 游标名 CURSOR
[FORWARD_ONLY|SCROLL]
[STATIC|KEYSET|DYNAMIC|FAST_FORWARD] [READ_ONLY|OPTIMISTIC] [TYPE_WARNING] FOR SELECT语句
[ FOR UPDATE [ OF 字段名 [ , ? n ] ] ]
说明：
? FORWARD_ONLY指定该游标为顺序结果集，只能用NEXT向后方式顺序提取
记录。
? SCROLL指定该游标为滚动结果集，可以使用向前、向后、定位方式提取记录。 ? STATIC与INSENSITIVE含义相同，在系统tempdb数据库中创建临时表存储游
标使用的数据，即游标不会随基本表内容而变化，同时也无法通过游标来更新基本表。
? KEYSET指定游标中列的顺序是固定的，并且在tempdb内建立一个KEYSET表，
基本表数据修改时能反映到游标中。如果基本表添加符合游标的新记录时该游标无法读取(但其他语句使用WHERE CURRENT OF子句可对游标中新添加的记录数据进行修改)。如果游标中的一行被删除掉，则用游标提取时@@FETCH_STATUS的返回值为-2。
? DYNAMIC 指定游标中的数据将随基本表而变化，但需要大量的游标资源。 ? FAST_FORWARD指定FORWARD_ONLY而且READ_ONLY类型游标。使用
FAST_FORWARD参数则不能同时使用FORWARD_ONLY、SCROLL、OPTIMISTIC或FOR UPDATE参数。
? OPTIMISTIC指明若游标中的数据已发生变化，则对游标数据进行更新或删除时
可能会导致失败。
? TYPE_WARNING 指定若游标中的数据类型被修改成其他类型时，给客户端发送
警告。 提示：若省略FORWARD_ONLY|SCROLL则不使用STATIC、KEYSET和DYNAMIC时默
认为FORWARD_ONLY游标，使用STATIC、KEYSET或DYNAMIC之一则默认为SCROLL游标。
134
第6章 T-SQL编程
提示：若省略READ_ONLY|OPTIMISTIC参数，则默认选项为：如果未使用UPDATE参
数不支持更新，则游标为READ_ONLY；STATIC和FAST_FORWARD类型游标默认为READ_ONLY；DYNAMIC和KEYSET类型游标默认为OPTIMISTIC。 【例6
-10】 标准游标
Use Student_Course_Teacher Go
DECLARE YB1 CURSOR
FOR SELECT * FROM T
【例6
-11】 只读游标
Use Student_Course_Teacher Go
DECLARE YB2 CURSOR FOR SELECT * FROM T FOR READ ONLY
2．打开游标
命令格式：
OPEN [GLOBAL] 游标名
说明：打开指定的游标，如果全局游标与局部游标同名时，GLOBAL表示打开全局游标，省略为打开局部游标。
用DECLARE定义的游标，必须打开以后才能对游标中的结果集进行处理。就是说DECLARE只声明了游标的结构格式，打开游标才执行SELECT语句得到游标中的结果集。 提示：打开游标后，可以使用全局变量@@ERROR判断该游标是否打开成功。@@ERROR
为0则打开成功，否则打开失败。
3．从游标中提取数据 命令格式：
FETCH
[NEXT|PRIORr|FIRST|LAST| ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]
FROM [GLOBAL] 游标名 [ INTO @变量名 [ , ?n ] ]
说明：
(1) 在游标内有一个游标指针CURSOR指向游标结果集的某个记录行—称为当前行，游标刚打开时CURSOR指向游标结果集第一行之前。
(2) FETCH之后的参数为提取记录的方式，可以是以下方式之一：
? NEXT顺序向下提取当前记录行的下一行，并将其作为当前行。第一次对游标操
作时取第一行为当前行，处理完最后一行，再用FETCH NEXT则CURSOR指向结果集最后一行之后，@@FETCH_STATUS的值为-1。
? PRIOR顺序向前提取当前记录的前一行，并将其作为当前行。第一次用FETCH
PRIOR对游标操作时，没有记录返回，游标指针CURSOR仍指向第一行之前。
135
SQL Server 2005 数据库原理与应用案例教程
? FIRST提取游标结果集的第一条记录，并将其作为当前行。 ? LAST提取游标结果集的最后一条记录，并将其作为当前行。
? ABSOLUTE{n|@nvar}按绝对位置提取游标结果集的第n或第@nvar条记录，并将
其作为当前行。若n或@nvar为负值则提取结尾之前的倒数第n或第@nvar条记录。n为整数，@nvar为整数类型变量。
? RELATIVE{n|@nvar}按相对位置提取当前记录之后(正值)或之前(负值)的第n或
第@nvar条记录，并将其作为当前行。
(3) FROM指定提取记录的游标，GLOBAL用于指定全局游标，省略为局部游标。 (4) INTO指定将提取记录中的字段数据存入对应的局部变量中。变量名列表的个数、类型必须与结果集中记录的字段的个数、类型相匹配。
(5) 打开游标用FETCH提取记录后，可用@@FETCH_STATUS检测游标的当前状态。 @@FETCH_STATUS的返回值为： 0：FETCH语句提取记录成功
-1：FETCH语句执行失败或提取的记录不在结果集内 -2：被提取的记录已被删除或根本不存在 提示：@@FETCH_STATUS只能检测游标提取记录后的状态，若用作循环条件输出多条记
录时，必须在循环之前先用FETCH提取一条记录，再用@@FETCH_STATUS判断提取记录是否成功，以确定是否进行循环。
4．关闭游标 命令格式：
说明：释放游标中的结果集，解除游标记录行上的游标指针。当游标提取记录完毕后，应及时关闭该游标释放结果集的内存空间。游标关闭后，其定义结构仍然存储在系统中，但不能提取记录和定位更新，需要时可用OPEN语句再次打开。 提示：关闭只有定义而没有打开的游标会产生语法错误。
5．释放游标 命令格式：
说明：删除指定的游标，释放该游标所占用的所有系统资源。
提示：关闭游标并不改变其定义，可用open再次打开。若想放弃游标，必须使用deallocate
释放它。游标释放后，不再允许另一进程在其上执行Open操作。 【例6-12】 一个完整的游标声明、定位、更新、关闭、释放的例子
Use Student_Course_Teacher Go
DEALLOCATE [GLOBAL] 游标名 CLOSE [GLOBAL] 游标名
136
第6章 T-SQL编程
DECLARE @tno VARCHAR (10), @tn VARCHAR (10) DECLARE t_cur CURSOR
FOR SELECT tno, tn FROM T FOR UPDATE OF tno, tn OPEN t_cur
FETCH next FROM t_cur INTO @tno, @tn WHILE @@fetch_status = 0 BEGIN
IF @tno='t1' UPDATE T
SET tn ='吴胜'
WHERE CURRENT OF t_cur
FETCH next FROM t_cur INTO @tno, @tn END
CLOSE t_cur
DEALLOCATE t_cur
运行结果如图6.5所示。
图6.5 例6-12执行结果
小 结
本章首先介绍了T-SQL程序设计中GO语句的使用，全局变量及局部变量的定义，输入输出格式，注释的使用。接下来介绍了T-SQL程序设计中流程控制语句的使用，包括：选择语句、循环语句、 转移语句、等待语句、返回语句。最后介绍了T-SQL程序设计中游标的定义、打开、从游标中提取数据、关闭、释放。
本章的重点是变量的使用，流程控制语句的使用，游标的使用。难点是从游标中提取数据。
137
SQL Server 2005 数据库原理与应用案例教程
阅 读 材 料
在SQL Server 2005中，某些 T-SQL 系统函数(以下简称为函数)的名称以两个 at 符号 (@@) 打头。在 Microsoft SQL Server 的早期版本中，@@functions 被称为全局变量，但它们不是变量，也不具备变量的行为。@@functions 是系统函数，它们的语法遵循函数的规则。接下来我们来了解一下这些函数及使用方法。
这些函数由SQL Server 2005系统提供，可以在任何程序中随时调用。通过这些函数可以访问SQL Server 2005的一些配置设定值和统计数据。在使用时应注意以下几点：
(1) 用户只能使用这些预先定义的函数。
(2) 引用函数时，必须以标记符“@@”开头。
(3) 可以通过函数获取系统的配置设定值或统计数据，但不能通过函数修改系统的配置设定值或统计数据。
(4) 用户定义的变量名称不能与函数的名称相同。 常用函数如表6-1：
表6-1 常用函数
变 量 @@SERVICENAME @@REMSERVER @@VERSION @@MAX_CONNECTIONS @@PACK_RECEIVED @@LOCK_TIMEOUT @@SERVERNAME @@PACK_SENT @@ERROR @@TRANCOUNT 作 用 返回SQL Server正运行于哪种服务状态之下 返回登录记录中记载的远程SQL Server服务器的名称。 返回SQL Server当前安装的日期、版本和处理器类型。 返回允许连接到SQL Server的最大连接数目。 返回SQL Server通过网络读取的输入包的数目。 返回当前会话等待锁的时间长短其单位为毫秒。 返回运行SQL Server本地服务器的名称。 返回SQL Server写给网络的输出包的数目。 返回最后执行的Transact-SQL语句的错误代码。 返回当前连接中处于激活状态的事务数目。 【例6-13】 利用函数查看SQL Server的版本、当前使用的SQL Server服务器的名称以及所使用的服务器的服务名称等信息。
代码如下：
PRINT ‘目前所用SQL Server的版本信息’
PRINT @@version --版本
PRINT ‘目前所用SQL Server服务器的名称:’+@@servername --服务器的名称 PRINT ‘目前所用服务器的服务名称:’+@@servicename --服务名称 GO
执行的结果如图6.6所示。
138