第10章游标和触发器

更新数据语法形式如下：
UPDATE {table_name|view_name} SET [[{table_name.|view_name.}] column_name = { new_value}[ ...n]
WHERE CURRENT OF cursor_name
其中： – 紧跟UPDATE之后的table_name| view_name：要更新的表 名或视图名，可以加或不加限定。但它必须是声明该游标1的5 SELECT语句中的表名或视图名。
cursor_name：是已声明为FOR UPDATE方式并已打开的 游标名。
16
注意： – ① 使用UPDATE...CURRENT OF语句一次只能更新当前 游标位置确定的那一行，OPEN语句将游标位置定位在结 果集第一行前，可以使用FETCH语句把游标位置定位在 要被更新的数据行处。 – ② 用UPDATE...WHERE CURRENT OF语句更新表中的 行时，不会移动游标位置，被更新的行可以再次被修改， 直到下一个FETCH语句的执行。 – ③ UPDATE...WHERE CURRENT OF语句可以更新多表 视图或被连接的多表，但只能更新其中一个表的行，即所 有被更新的列都来自同一个表。
10
4. 关闭游标 关闭(Close)游标是停止处理定义游标的那个查询。关闭游标并
不改变它的定义，可以再次用open语句打开它，SQL Server会用该游标的定义重新创建这个游标的一个结果集。 关闭游标的语法如下： CLOSE cursor_name 其中： – cursor_name：是已被打开并将要被关闭的游标名字。 – 在如下情况下，SQL Server会自动地关闭已打开的游标
2
10.2.2 游标的定义及使用过程
1. 声明游标
声明游标是指用DECLARE语句声明或创建一个游标。
声明游标的语法如下：
DECLARE cursor_name [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY|UPDATE[OF column_name_list]}]
--释放游标
DEALLOCATE emp_coursor
本章首页
14
10.3 使用游标修改数据
用户可以在UPDATE或DELETE语句中使用游标来更新、删除表或 视图中的行，但不能用来插入新行。
10.3.1 更新数据
通过在UPDATE语句中使用游标可以更新表或视图中的行。被更新 的行依赖于游标位置的当前值。
USE northwind GO DECLARE @emp_name VARCHAR(16),@emp_title
VARCHAR(16) --声明游标 DECLARE emp_coursor SCROLL CURSOR FOR SELECT lastname,title FROM employees FOR READ ONLY --打开游标 OPEN emp_coursor --读取数据 FETCH FROM emp_coursor INTO @emp_name,@emp_title
9
有两个全局变量提供关于游标活动的信息： ① @@FETCH_STATUS 保存着最后FETCH语句执行后的状态信
息，其值和含义如下： – 0 ：表示成功完成FETCH 语句。 – -1：表示FETCH语句执行有错误，或者当前游标位置已在结
果集中的最后一行，结果集中不再有数据。
– -2：提取的行不存在。 ② @@rowcount保存着自游标打开后的第一个FETCH语句，直到
– select_statement：是定义游标结果集的标准 SELECT语句
，它可以是一个完整语法和语义的Transact-SQL的
SELECT语句。
5
– 但是这个SELECT语句必须有FROM子句，不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。
– FOR READ ONLY：指出该游标结果集只能读，不能修改。 – FOR UPDATE：指出该游标结果集可以被修改。 – OF column_name_list：列出可以被修改的列的名单。
最近一次的FETCH语句为止，已从游标结果集中提取的行数。 也就是说它保存着任何时间点客户机程序看到的已提取的总行数 。一旦结果集中所有行都被提取，那么@@rowcount的值就是 该结果集的总行数。每个打开的游标都与一特定的 @@rowcount有关，关闭游标时，该@@rowcount变量也被删 除。在FETCH语句执行后查看这个变量，可得知从游标结果集 中已提取的行数。
– column_name：是要更新的列的列名，可以加或不加限 定。但它们必须是声明游标的SELECT语句中UPDATE OF column_name_list的子集
– new_value：为被更新列的新值，它可以是一个表达式 、空值或子查询。
– WHERE CURRENT OF：使SQL Server只更新由指定 游标的游标位置当前值确定的行。
： • 当你退出这个SQL Server会话时 • 从声明游标的存储过程中返回时
11
5. 释放游标 释放( Deallocate)游标是指释放所有分配给此游标的资源，包括该
游标的名字。 释放游标的语法是：
DEALLOCATE CURSOR cursor_name 其中：
– cursor_name：将要被DEALLOCATE释放的游标名字。如 果释放一个已打开但未被关闭的游标，SQL Server会自动先 关闭这个游标，然后再释放它。
第10章游标和触发器
10.1 游标的定义及其优点
数据检索可以得到数据库中有关表的数据，但这些数据是作 为一个结果集得到的，用户可以把这个结果集保存到一个文件里 ，或生成一个新表以便于以后使用。这种查询是非常重要的。但 这种查询形式有一个很大的缺点，它不能对结果集中每一行的数 据进行处理。使用游标可以实现对查询结果集中的数据逐行处理 。
其中： – table_name|view_name：为要从其中删除行的表名或视图 名，可以加或不加限定。但它必须是定义该游标的SELECT 语句中的表名或视图名。 – cursor_name：为已声明并已打开的游标名。 – WHERE CURRENT OF：它使SQL Server只删除由指定游 标的游标位置当前值确定的行。
标移动方向，缺省情况下是NEXT，即向下移动。 – NEXT：取下一行数据。 – PRIOR：取前一行ห้องสมุดไป่ตู้据。 – FIRST：取第一行数据。
8
LAST：取最后一行数据。 ABSOLUTE：按绝对位置取数据。 RELATIVE：按相对位置取数据。 游标位置确定了结果集中哪一行可以被提取，如果游标方式
title --打开游标 OPEN emp_coursor --取第3行数据 FETCH ABSOLUTE 3 FROM emp_coursor
18
--更新当前游标中的值 UPDATE employees SET title = 'Vice President,Sales' WHERE CURRENT OF emp_coursor --取第三行数据 FETCH ABSOLUTE 3 FROM emp_coursor --关闭并释放游标 CLOSE emp_coursor DEALLOCATE emp_coursor
其中：
– cursor_name:是游标的名字，为一个合法的SQL Server标 识符，游标的名字必须遵循SQL Server命名规范。
– SCROLL：表示取游标时可以使用关键字NEXT、PRIOR、 FIRST、LAST、ABSOLUTE、RELATIVE。每个关键字的 含义将在介绍FETCH子句时讲解。
为FOR UPDATE的话，也就确定该位置一行数据可以被更 新或删除。 • INTO fetch_target_list：指定存放被提取的列数据的目的 变量清单。这个清单中变量的个数、数据类型、顺序必须 与定义该游标的select_statement的SELECT_list中列出 的列清单相匹配。为了更灵活地操纵数据，可以把从已声 明并已打开的游标结果集中提取的列数据，分别存放在目 的变量中。INTO fetch_target_list是T-SQL对ANSI-92 SQL标准的扩充。
注意一下关闭游标与释放游标的区别：关闭游标并不改变游标的定 义，一个游标关闭后，不需要再次声明，就可以重新打开并使用 它。但一个游标释放后，就释放了与该游标有关的一切资源，也 包括游标的声明，游标释放后就不能再使用该游标了，如需再次 使用游标，就必须重新定义。
12
【例10-1】定义一个游标，将职员表中所有的职员姓名、头衔显 示出来。
应该注意: – ① 游标有且只有两种方式：FOR READ ONLY或FOR UPDATE。 – ② 当游标方式指定为FOR READ ONLY时，游标涉及的表不 能被修改。 – ③ 当游标方式指定为FOR UPDATE时，可以删除或更新游标 涉及的表中的行。通常，这也是缺省方式，即不指定游标方 式时为FOR UPDATE方式。
20
注意： – ① 使用游标的DELETE语句，一次只能删除当前游标位置 确定的那一行。OPEN语句将游标位置定位在结果集第一 行之前，可以用FETCH语句把游标位置定位在要被删除的 行处。 – ② 在DELETE语句中使用的游标必须声明为FOR UPDATE方式。而且声明游标的SELECT语句中不能含有 连接操作或涉及多表视图，否则即使声明中指明了FOR UPDATE方式，也不能删除其中的行。 – ③ 对使用游标删除行的表，要求有一个唯一索引。 – ④ 使用游标的DELETE语句，删除一行后将游标位置向前 移动一行。
19
10.3.2 删除数据
通过在DELETE语句中使用游标来删除表或视图中的行。被删除 的行依赖于游标位置的当前值。
删除数据语法形式如下：
DELETE [FROM]
[[database.]owner.]{table_name|view_name}
WHERE CURRENT OF cursor_name
13
WHILE @@FETCH_STATUS=0
BEGIN
PRINT N'职员姓名:'+@emp_name+' '+N'职 称:' +@emp_title
-- 取下一条数据
FETCH next FROM emp_coursor INTO @emp_name,@emp_title
END
--关闭游标
CLOSE emp_coursor
– ④ 声明游标的DECLARE CURSOR 语句必须是在该游标的 任何OPEN语句之前。
6
2. 打开游标 打开游标是指打开已被声明但尚未被打开的游标，打开游
标使用OPEN语句。 打开游标的语法如下：
OPEN cursor_name 其中： • cursor_name是一个已声明的尚未打开的游标名。 注意：
17
【例10-2】通过游标将职员表中记录号为3的的职称由“ 头衔”改为“'Vice President,Sales”。
USE northwind GO --声明游标,指定更新列 DECLARE emp_coursor SCROLL CURSOR FOR SELECT lastname,title FROM employees FOR UPDATE OF
– ① 当游标打开成功时，游标位置指向结果集的第一行 之前。
– ② 只能打开已经声明但尚未打开的游标。
7
3. 从打开的游标中提取行 游标被打开后，游标位置位于结果集的第一行前，此时可以从结
果集中提取（FETCH）行。SQL Server将沿着游标结果集一 行或多行向下移动游标位置，不断提取结果集中的数据，并 修改和保存游标当前的位置，直到结果集中的行全部被提取 。 从打开的游标中提取行的语法如下： FETCH [[NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE] FROM] cursor_name [INTO fetch_target_list] 其中： – cursor_name：为一已声明并已打开的游标名字。 – NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游
10.1.1 游标的概念
游标(Cursor)是一种处理数据的方法，为了查看或者处理结 果集中的数据，游标提供了在结果集中向前或者向后浏览数据的 能力。可以把游标看成一种指针，它既可以指向当前位置，也可 以指向结果集中的任意位置，它允许用户对指定位置的数据进行 处理，可以把结果集中的数据放在数组、应用程序中或其它地方 。Transact-SQL游标遵循ANSI-92标准。