游标的概念.doc

1、游标的概念游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的 SQL 语句，或是批处 理、存储过程、触发器中的数据处理请求。游标的优点在于它可以定位到结果集中的某一行，并可以对该行数据执行特定操作，为用户在处理数据的过程中提供了很 大方便。一个完整的游标由 5 部分组成，并且这 5 个部分应符合下面的顺序。（1）声明游标。（2）打开游标。（3）从一个游标中查找信息。（4）关闭游标。（5）释放游标。声明游标首先来学习如何声明一个游标，声明游标使用 DECLARE CURSOR 语句。此语句有两种语法声明格式，分别为 SQL 92 标准格式和 SQ

2、L Server 扩展（扩展了声明游标的参数）格式，下面将分别介绍声明游标的两种语法格式。1SQL-92 语法格式语法：DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n 参数说明：l DECLARE cursor_name：指定一个游标名称，其游标名称必须符合标识符规则。l INSENSITIVE：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的临时表中得到应答；因此，在对该游 标进行

3、提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用 SQL-92 语法时，如果省略 INSENSITIVE，（任何用户）对 基表提交的删除和更新都反映在后面的提取中。l SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。? FIRST：取第一行数据。? LAST：取最后一行数据。? PRIOR：取前一行数据。? NEXT：取后一行数据。? RELATIVE：按相对位置取数据。? ABSOLUTE：按绝对位置取数据。如果未指定 SCROLL，则 NEXT 是惟一支持的提取选项。l select_stat

4、ement：定义游标结果集的标准 SELECT 语句。在游标声明的select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和INTO。l READ ONLY：表明不允许游标内的数据被更新，尽管在默认状态下游标是允许更新的。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不允许引用游标。l UPDATE OF column_name ,.n ：定义游标内可更新的列。如果指定 OF column_name ,.n参数，则只允许修改所列出的列。如果在 UPDATE中未指定列的列表，则可以更新所有列。2SQL

5、 Server 扩展格式语法：DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWARD_ONLY | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDATE OF column_name ,.n 参数说明：l DECLARE cursor_name：指定一个游标名称，其游标名称必须符合标识符规则。l LOCAL：定义游标的作用域仅限在其所在的批处理、

6、存储过程或触发器中。当建立游标在存储过程执行结束后，游标会被自动释放。l GLOBAL：指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在脱接时隐性释放。l FORWARD_ONLY：指定游标只能从第一行滚动到最后一行。FETCH NEXT 是惟一受支持的提取选项非指定 STATIC、KEYSET 或 DYNAMIC 关键字，否则默认为FORWARD_ONLY。STATIC、 KEYSET 和 DYNAMIC 游标默认为 SCROLL。与 ODBC 和ADO 这类数据库 API 不同，STATIC、KEYSET 和 DYNAMICTrans

7、act-SQL 游标支持FORWARD_ONLY。FAST_FORWARD 和 FORWARD_ONLY 是互斥的；如果指定一 个，则不能指定另一个。l STATIC：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。l KEYSET：指定当游标打开时，游标中行的成员资格和顺序已经固定。对行进行惟一标识的键集内置在 tempdb 内一个称为 keyset 的表中。对基表中 的非键值所做的更改（由游标所有者更改或由其他用户提交）在用户滚动游标时是可

8、视的。其他用户进行的插入是不可视的（不能通过 Transact-SQL 服 务器游标进行插入）。如果某行已删除，则对该行的提取操作将返回FETCH_STATUS值-2。从游标外更新键值类似于删除旧行后接着插入新行的操 作。含有新值的行不可视，对含有旧值的行的提取操作将返回FETCH_STATUS 值-2。如果通过指定 WHERE CURRENT OF 子句用游标完成更新，则新值可视。l DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的行所做的所有数据的更改。行的数据值、顺序和成员在每次提取时都会更改。动态游标不支持 ABSOLUTE 提取选项。l FAST_FORWARD：指明一个

9、 FORWARD_ONLY、READ_ONLY 型游标。l SCROLL_LOCKS：指定确保通过游标完成的定位更新或定位删除可以成功。将行读入游标以确保它们可用于以后的修改时，SQL Server 会锁定这些行。如果还指定了 FAST_FORWARD，则不能指定 SCROLL_LOCKS。l OPTIMISTIC：指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。l TYPE_WARNING：指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。使用 DECLARE CURSOR 语句创建以下几种形式的游标。（1）示例：创建一

10、个名为“MyCursor”的标准游标。USE 销售管理系统DECLARE MyCursor CURSOR FORSELECT * FROM 操作员信息表GO（2）示例：创建一个名为“MyCursor_01”的只读游标。USE 销售管理系统DECLARE MyCursor_01 CURSOR FORSELECT * FROM 操作员信息表FOR READ ONLY -只读游标GO（3）示例：创建一个名为“MyCursor_02”的更新游标。USE 销售管理系统DECLARE MyCursor_02 CURSOR FORSELECT 操作员编号,操作员姓名,操作员年龄 FROM 操作员信息表FOR

11、 UPDATE -更新游标GO打开游标打开一个声明的游标使用 OPEN 命令。语法：OPEN GLOBAL cursor_name | cursor_variable_name 参数说明：l GLOBAL：指定 cursor_name 为全局游标。l cursor_name：已声明的游标名称，如果全局游标和局部游标都使用cursor_name 作为其名称，那么如果指定了 GLOBAL，cursor_name 指的是全局游标，否则，cursor_name 指的是局部游标。l cursor_variable_name：游标变量的名称，该名称引用一个游标。说 明：如果使用 INSENSITIV 或

12、STATIC 选项声明了游标，那么 OPEN 将创建一个临时表以保留结果集。如果结果集中任意行的大小超过 SQL Server 表的最大行大小，OPEN 将失败。如果使用 KEYSET 选项声明了游标，那么 OPEN 将创建一个临时表以保留键集。临时表存储在 tempdb 中。首先声明一个名为 MyCursor_001 的游标，然后使用 OPEN 命令打开该游标。操作步骤如下：（1）在操作系统中选择“开始”“所有程序”“Microsoft SQL Server”“查询分析器”命令，打开查询分析器。（2）在查询分析器的工具栏中选择要连接的数据库，这里选择“销售管理系统”。（3）在代码编辑区中编写

13、如下代码。SQL 语句如下：USE 销售管理系统DECLARE MyCursor_001 CURSOR FOR -声明游标SELECT 操作员编号,操作员姓名,操作员年龄 FROM 操作员信息表WHERE 操作员编号 = CY20040604006OPEN MyCursor_001 -打开游标GO从游标中读取数据当打开一个游标之后，就可以读取游标中的数据了。可以使用 FETCH 命令读取游标中的某一行数据。语法：FETCH NEXT | PRIOR | FIRST | LAST| ABSOLUTE n | nvar | RELATIVE n | nvar FROM GLOBAL cursor_

14、name | cursor_variable_name INTO variable_name ,.n 参数说明：l NEXT：返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。l PRIOR：返回紧临当前行前面的结果行，并且当前行递减为结果行。如果FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。l FIRST：返回游标中的第一行并将其作为当前行。l LAST：返回游标中的最后一行并将其作为当前行。l ABSOLUTE n | nvar：如果 n 或

15、nvar 为正数，返回从游标头开始的第n 行，并将返回的行变成新的当前行。如果 n 或nvar 为负数，返回游标尾之前的第 n 行，并将返回的行变成新的当前行。如果 n 或nvar 为 0，则没有行返回。l RELATIVE n | nvar：如果 n 或nvar 为正数，返回当前行之后的第 n行，并将返回的行变成新的当前行。如果 n 或nvar 为负数，返回当前行之前的第 n 行，并 将返回的行变成新的当前行。如果 n 或nvar 为 0，返回当前行。如果对游标的第一次提取操作时将 FETCHRELATIVE 的 n 或nvar 指定为负数 或0，则没有行返回。n 必须为整型常量且nvar

16、必须为 smallint、tinyint 或int。说明：在前两个参数中，包含了 n 和nvar 其表示游标相对与作为基准的数据行所偏离的位置。l GLOBAL：指定 cursor_name 为全局游标。l cursor_name：要从中进行提取的开放游标的名称。如果同时有以cursor_name 作为名称的全局和局部游标存在，若指定为 GLOBAL，则cursor_name 对应于全局游标，未指定 GLOBAL，则对应于局部游标。l cursor_variable_name：游标变量名，引用要进行提取操作的打开的游标。l INTO variable_name,.n：允许将提取操作的列数据放到

17、局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的 数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。l FETCH_STATUS：返回上次执行 FETCH 命令的状态。在每次用 FETCH 从游标中读取数据时，都应检查该变量，以确定上次 FETCH 操作是否成功，决定如何进行下一步处理。FETCH_STATUS 变量有 3 个不同的返回值，说明如下：? 返回值为 0：FETCH 语句成功。? 返回值为-1：FETCH 语句失败或此行不在结果集中。? 返回值为-2：被提取的行不存在。说 明：当使用

18、SQL-92 语法来声明一个游标时，没有选择 SCROLL 选项，则只能使用 FETCH NEXT 命令来从游标中读取数据，即只能从结果集第一行按顺序地每次读取一行。由于不能使用 FIRST、LAST、PRIOR，所以无法回滚读取以前的数 据。如果选择了 SCROLL 选项，则可以使用所有的 FETCH 操作。通常游标取数的操作与 WHILE 循环紧密结合，下面将使用FETCH_STATUS 控制在一个 WHILE 循环中的游标活动。程序运行结果如图 1 所示。图 1 从游标中读取数据SQL 语句如下：USE 销售管理系统 -引入数据库DECLARE ReadCursor CURSOR FOR

19、 -声明一个游标SELECT 操作员编号,操作员姓名,操作员性别,操作员住址FROM 操作员信息表OPEN ReadCursor -打开游标FETCH NEXT FROM ReadCursor -执行取数操作WHILE FETCH_STATUS=0 -检查FETCH_STATUS，以确定是否还可以继续取数BEGINFETCH NEXT FROM ReadCursorEND关闭游标当游标使用完毕之后，使用 CLOSE 语句可以关闭游标，但不释放游标占用的系统资源。语法：CLOSE GLOBAL cursor_name | cursor_variable_name 参数说明：l GLOBAL：指定

20、 cursor_name 为全局游标。l cursor_name：开放游标的名称。如果全局游标和局部游标都使用cursor_name 作为它们的名称，那么当指定 GLOBAL 时，cursor_name 引用全局游标；否则，cursor_name 引用局部游标。l cursor_variable_name：与开放游标关联的游标变量名称。示例：声明一个名为“CloseCursor”的游标，并使用 Close 语句关闭游标。SQL 语句如下：USE 销售管理系统 -引入数据库DECLARE CloseCursor Cursor FOR -声明游标SELECT * FROM 销售表FOR READ

21、ONLYOPEN CloseCursor -打开游标CLOSE CloseCursor -关闭游标释放游标当游标关闭之后，并没有在内存中释放所占用的系统资源，所以可以使用DEALLOCATE 命令删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由 SQL Server 释放。语法：DEALLOCATE GLOBAL cursor_name | cursor_variable_name 参数说明：l cursor_name：已声明游标的名称。当全局和局部游标都以 cursor_name作为它们的名称存在时，如果指定 GLOBAL，则 cursor_name 引用全局游标，如果未指定 G

22、LOBAL，则 cursor_name 引用局部游标。l cursor_variable_name：cursor 变量的名称。cursor_variable_name必须为 cursor 类型。当使用 DEALLOCATE cursor_variable_name 来删除游标时，游标变量并不会被释放，除非超过使用该游标的存储过程和触发器的范围。示例：使用 DEALLOCATE 命令释放名为“FreeCursor”的游标。SQL 语句如下：USE 销售管理系统DECLARE FreeCursor Cursor FOR -声明游标SELECT * FROM 销售表OPEN FreeCursor -

23、打开游标Close FreeCursor -关闭游标DEALLOCATE FreeCursor -释放游标创建游标变量在批处理或过程的正文中用 DECLARE 语句声明变量，并用 SET 或 SELECT 语句给其指派值。游标变量可通过该语句声明，并且可用在其他与游标相关的语句中。所有变量在声明后均初始化为 NULL。首先创建一个游标并打开该游标，之后创建一个游标变量并将游标的值（SELECT * FROM Jobs）赋给游标变量，并通过 FETCH 语句读取游标变量中的值，最后关闭并释放游标。程序运行结果如图 1 所示。图 1 创建游标变量SQL 语句如下：USE pubsDECLARE M

24、yCursor_001 Cursor FOR -创建游标SELECT * FROM JobsOPEN MyCursor_001 -打开游标 DECLARE CursorVar Cursor -创建游标变量SET CursorVar = MyCursor_001 -为游标变量赋值FETCH NEXT FROM CursorVar -读取游标变量中的值 CLOSE MyCursor_001 -关闭游标DEALLOCATE MyCursor_001 -释放游标静态游标静 态游标的完整结果集在游标打开时建立在 tempdb 中。静态游标总是按照游标打开时的原样显示结果集。静态游标在滚动期间很少或根本检

25、测不到变化，虽然它 在 tempdb 中存储了整个游标，但消耗的资源很少。尽管动态游标使用tempdb 的程度最低，在滚动期间它能够检测到所有变化，但消耗的资源也更多。键 集驱动游标介于二者之间，它能检测到大部分的变化，但比动态游标消耗更少的资源。动态游标动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部 UPDATE、INSERT 和 DELETE 语句均通过游标可见。只进游标只进游标不支持滚动，它只支持游标从头到尾顺序提取。只在从数据库中提取出来后才能行检索。对所有由当前用户发出或由其他用户提交、并影响结果集中的行的 INSERT、UPDATE 和 DELETE 语句，其效果在这些行从游标中提取时是可见的。键集驱动游标