第11章游标 11-1游标概泷 11-2声明游标 11-3打开游标 11-4提取游标数据 11-5关闭释放游标 11-6游标的应用 在 SQL Server数据库中,游标是÷个比较重要的 概念,能提供这种机制对结果集的部分行记录进行处理。 本章主要介绍了游标的概念、分类、定义、嵌套以游标 的打开、存取、定位、修改、删除、关闭、释放等操作
11-1 游标概述 11-2 声明游标 11-3 打开游标 11-4 提取游标数据 11-5 关闭释放游标 11-6 游标的应用 在SQL Server数据库中，游标是一个比较重要的 概念，能提供这种机制对结果集的部分行记录进行处理。 本章主要介绍了游标的概念、分类、定义、嵌套以游标 的打开、存取、定位、修改、删除、关闭、释放等操作。 第11章 游标
11-1游标概述 在 SQL Server数据库中,游标是一个比较重要的概念,能提供这种机 制对结果集的部分行记录进行处理。本章主要介绍了游标的概念、分类 定义、嵌套以游标的打开、存取、定位、修改、删除、关闭、释放等操作。 在 SQL Server2000统数据库开发过程中,执行 SELECT语句可进 行查询并返回满足 WHERE等子句中条件的所有数据的记录,这一完整 的记录集称为行结果集。由于应用程序并不能总将整个结果集作为一个单 元来有效地处理,因而往往需要某种机制,以便每次处理时可从某一结果 集中逐一地读取一条或一部分行记录。游标( Cursor)能提供这种机制 对结果集的部分行记录进行处理,不但允许定位在结果集的特定行记录上 而且还可从结果集的当前位置检索若干条行记录,并可实施对相应的数 据修改。 1.游标 在 SQL Server数据库中,游标是一个比较重要的概念,游标总是与一条 Transact-SQL选择语句相关联。 定义:游标是一种处理数据的方法,它可对结果集进行逐行处理,可将游 标视作一种指针,用于指向并处理结果集任意位置的数据。就本质而言游 标提供了一种对从表中检索出的数据进行操作的灵活手段,由于游标由结 果集和结果集中指向特定记录的游标位置组成,当块定对结果集进行处理 时,必须声明定义一个指向该结果集的游标
在SQL Server数据库中，游标是一个比较重要的概念，能提供这种机 制对结果集的部分行记录进行处理。本章主要介绍了游标的概念、分类、 定义、嵌套以游标的打开、存取、定位、修改、删除、关闭、释放等操作。 在SQL Server 2000系统数据库开发过程中，执行SELECT 语句可进 行查询并返回满足WHERE 等子句中条件的所有数据的记录，这一完整 的记录集称为行结果集。由于应用程序并不能总将整个结果集作为一个单 元来有效地处理，因而往往需要某种机制，以便每次处理时可从某一结果 集中逐一地读取一条或一部分行记录。游标（Cursor）能提供这种机制 对结果集的部分行记录进行处理，不但允许定位在结果集的特定行记录上， 而且还可从结果集的 当前位置检索若干条行记录，并可实施对相应的数 据修改。 1．游标 在SQL Server数据库中，游标是一个比较重要的概念，游标总是与一条 Transact-SQL选择语句相关联。 定义：游标是一种处理数据的方法，它可对结果集进行逐行处理，可将游 标视作一种指针，用于指向并处理结果集任意位置的数据。就本质而言游 标提供了一种对从表中检索出的数据进行操作的灵活手段，由于游标由结 果集和结果集中指向特定记录的游标位置组成，当决定对结果集进行处理 时，必须声明定义一个指向该结果集的游标。 11-1 游标概述
游标具有如下特点: 1允许程序对由查询语句 select返回的记录行集合中的每一行执行相同或不同 的操作,而不是对整个行集合执行同一个操作。 1提供对基于游标位置的表中记录行进行删除和更新的能力。 1游标实际上作为面向集合的数据库管理系统(DBMS)和面向行的程序设 计之间的桥梁,使这两种处理方式通过游标沟通起来。 SQL Server是一个关系数据库管理系统( RDBMS),游标的特点表明系统 并没有一种描述表中单一记录的表达形式,除非使用 where子句来强行选择 被选中的一条记录,因此我们必须借助于游标来进行面向单条记录的数据处 理。游标允许应用程序对 select查询语句返回的行结果集中每一行记录进行 相同或不同的操作,而不是一次对整个结果集进行同一种操作。它还提供基 于游标位置而对表中数据进行删除、更新的能力,可为由其他用户对显示在 结果集中的数据库数据所做的更改提供不同级别的可见性支持。 SQL Server2000下的游标可以分为三类: Transact SQL游标、API服务器 游标和客户机游标。 (1) Transact SQL游标。 Transact SQL游标是由 SQL Server服务器实现的 游标,它的具体控制和管理通过脚本程序、存储过程和触发器将 Transact SQL语句传给服务器来完成。 (2)API游标。声明( Declare):为执行某操作声明一个游标 打开(Open).打开这个一个游标 空否?:直到全部行处理完为止
1 允许程序对由查询语句select返回的记录行集合中的每一行执行相同或不同 的操作，而不是对整个行集合执行同一个操作。 1 提供对基于游标位置的表中记录行进行删除和更新的能力。 1 游标实际上作为面向集合的数据库管理系统（DBMS）和面向行的程序设 计之间的桥梁，使这两种处理方式通过游标沟通起来。 SQL Server是一个关系数据库管理系统（RDBMS），游标的特点表明系统 并没有一种描述表中单一记录的表达形式，除非使用where 子句来强行选择 被选中的一条记录，因此我们必须借助于游标来进行面向单条记录的数据处 理。游标允许应用程序对select查询语句返回的行结果集中每一行记录进行 相同或不同的操作，而不是一次对整个结果集进行同一种操作。它还提供基 于游标位置而对表中数据进行删除、更新的能力，可为由其他用户对显示在 结果集中的数据库数据所做的更改提供不同级别的可见性支持。 SQL Server 2000下的游标可以分为三类：Transact_SQL 游标、API 服务器 游标和客户机游标。 （1）Transact_SQL 游标。Transact_SQL 游标是由SQL Server服务器实现的 游标，它的具体控制和管理通过脚本程序、存储过程和触发器将 Transact_SQL 语句传给服务器来完成。 （2）API 游标。声明(Declare):为执行某操作声明一个游标 打开(Open): 打开这个一个游标 空否?: 直到全部行处理完为止 游标具有如下特点：
2.游标分类 API(数据库应用程序接口)游标支持在ADO、ODBC、 OLE DB以及 DB_library中使用游标函数,主要用在服务器上,每·次客户端应用程序调用 API游标函数, SQL Server的ADO、 OLE DB提供者、ODBC驱动器或 DB_library的动态链接库DLL都会将这些客户请求传给服务器以对API游标 进行处理。 (3)客户机游标。客户机游标是当在客户机上缓存结果集时扌使用静态游标, 是一种临时性的游标应用方法。在客户机游标建立在客户机的数据集上,有 个缺省的结果集被用来在客户机上缓存整个结果集,客户游标常常仅被用作 Transact_ SQL游标与API游标的辅助。 由于 Transact-SQL游标和API游标使用于服务器端,所以被称为服务器游标, 也被称为后台游标,而客户端游标被称为前台游标。本章中主要讲泷服务器游 3.游标使用步骤 应用程序对每一个游标的操作过程可分为五个步骤(如图11-1所示) (1)用 DECLARE语句声明、定义游标的类型和属性。 (2)用OPEN语句打开和填充游标。 (3)执行 FETCH语句,从一个游标中获取信息(即从结果集中提取若干行数 据库)。可按需使用 UPDATE、 DELETE语句在游标当前位置上进行操作。 (4)用 CLOSE语句关闭游标。 (5)用 DEALLOCATED语句释放游标
API（数据库应用程序接口）游标支持在ADO、ODBC、OLE DB以及 DB_library 中使用游标函数，主要用在服务器上，每一次客户端应用程序调用 API游标函数，SQL Server 的ADO、OLE DB 提供者、ODBC驱动器或 DB_library 的动态链接库DLL 都会将这些客户请求传送给服务器以对API游标 进行处理。 （3）客户机游标。客户机游标是当在客户机上缓存结果集时才使用静态游标， 是一种临时性的游标应用方法。在客户机游标建立在客户机的数据集上，有一 个缺省的结果集被用来在客户机上缓存整个结果集，客户游标常常仅被用作 Transact_SQL 游标与API 游标的辅助。 由于Transact-SQL 游标和API 游标使用于服务器端，所以被称为服务器游标， 也被称为后台游标，而客户端游标被称为前台游标。本章中主要讲述服务器游 标。 3．游标使用步骤 应用程序对每一个游标的操作过程可分为五个步骤（如图11-1所示）： （1）用DECLARE语句声明、定义游标的类型和属性。 （2）用OPEN 语句打开和填充游标。 （3）执行FETCH 语句，从一个游标中获取信息（即从结果集中提取若干行数 据库）。可按需使用UPDATE、DELETE语句在游标当前位置上进行操作。 （4）用CLOSE语句关闭游标。 （5）用DEALLOCATED语句释放游标。 2．游标分类
声明( Declare):为执行某操作声明一个游 打开(Open):打开这个一个游标 提取( Fetch):逐行处理游标中的行 空否?直到全鄙行处理完为止 关闭( Close):关闭这个游标 游标的操作过程示意图 释放( Deallocate):释放该游标
声明(Declare):为执行某操作声明一个游 打开(Open): 打开这个一个游标 提取(Fetch): 逐行处理游标中的行 空否?: 直到全部行处理完为止 关闭(Close): 关闭这个游标 释放(Deallocate): 释放该游标 游 标 的 操 作 过 程 示 意 图
11-2声明游标 通常我们使用 DECLARE来声明一个游标,主要内容为游标名字、数据来源 表和列、选取条件与属性。游标的声明有两种格式:SQL-92标准定义和 Transact SQL扩展定义(但仍支持SQL-92标准定义),前者只能说明游标 的属性,而不能定义游标的类型。 1.8QL-92标准定义 SQL-92标准声明游标的语法格式如下: DECLARE游标名[ INSENSITIVE][ SCROLLI CURSOR FOR select语句 [FOR{ READ ONLY| UPDATE[OF列名1[,n] 语法格式中参数说明如表11-1。 例[11-1】建立一游标,用于访问pubs数据库中 authors表。 use pubs DECLARE authors cursor CURSOR 声明游标 FOR SELECT FROM authors OPEN authors cursor 一从游标中提取一记录行,由于为指定 SCROLL选项, 那么 FETCH NEXT是唯一的提取选项
通常我们使用DECLARE 来声明一个游标，主要内容为游标名字、数据来源 表和列、选取条件与属性。游标的声明有两种格式：SQL-92标准定义和 Transact_SQL扩展定义（但仍支持SQL-92标准定义），前者只能说明游标 的属性，而不能定义游标的类型。 1．SQL-92标准定义 SQL-92标准声明游标的语法格式如下： DECLARE 游标名 [INSENSITIVE] [SCROLL] CURSOR FOR select_语句 [FOR {READ ONLY | UPDATE [OF 列名1[,…n]]}] 语法格式中参数说明如表11-1。 例[11-1】建立一游标，用于访问pubs数据库中authors表。 use pubs DECLARE authors_cursor CURSOR --声明游标 FOR SELECT * FROM authors OPEN authors_cursor --从游标中提取一记录行，由于为指定SCROLL选项， 那么FETCH NEXT是唯一的提取选项。 11-2 声明游标
FETCH NEXT FROM authors cursor Close authors cursor 关闭游标 运行结果如 238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley CA 94705 1 表11-1SQL-92标准语法参数说明 例[11-2】建立一个只读游标。 declare cur authors cursor for select au Iname, au fname, phone, address, city, state from authors for read only 2. Transact SQL扩展定义 Transact SQL扩展定义游标语法格式为 DECLARE游标名 CURSOR [LOCAL GLOBAL [FORWARD ONLY SCROLL] [STATIC KEYSET DYNAMIC FAST FORWARD TREAD ONLY SCROLL LOCKS OPTIMISTIC ITYPE WARNING FOR Select语句 LFOR UPDATE [ OF column name L, 语法格式中参数简要说明如表11-2
FETCH NEXT FROM authors_cursor Close authors_cursor --关闭游标 运行结果如下： -------------------------------------------------------------------------------------------------------- --------- 238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley CA 94705 1 表11-1 SQL-92标准语法参数说明 例[11-2】建立一个只读游标。 declare cur_authors cursor for select au_lname, au_fname, phone, address, city, state from authors for read only Transact_SQL扩展定义游标语法格式为： DECLARE游标名CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_语句 [FOR UPDATE [OF column_name [,...n]]] 语法格式中参数简要说明如表11-2。 2．Transact_SQL扩展定义
参数 参数说明 游标名 给出所定乂的游标名称,必须遑从标识符规则 使用 INSENSITIVE选项,表明 SQL Server会将游标定义所选取出 来的数据记录存放在 tempdb数据库下的临时表内,对该游标的读取 INSENSITIVE操作皆在这个临时表里进行,因此游标不会随着基本表内容的改变 而改变,同时也无法通过游标来更新基本表。如果不使用该保留字 那么对基本表的更新删除都会体现到游标中 指定所选的提取操作( FIRST、LAST、 PRIOR、NEXT RELATIVE、 ABSOLUTE)均可用,若不选用 SCROLL选项,那么 SCROLL FETCH NEXT是唯一的提取选项。 SCROLL增加了提取数据的灵 活性。 用于定义游标所要进行处理的结果集。在标准的 select语句中游标中 SELECT语句不能使用 COMPUTE、 COMPUTE BY、 FOR BROWSE、INTO语 句 不允许游标内数据被更新,是一种只读状态。 UPDATE、 DELETE等 READ ONLY 语句不能使用游标 UPDATE 用于定义游标内可更新字段列。若指定of字段列[n]参数,则所 列出的字段列可被更新修改,否则所有的列都将被更新修改
参 数 参 数 说 明 游标名 给出所定义的游标名称，必须遵从标识符规则。 INSENSITIVE 使用INSENSITIVE 选项，表明SQL Server 会将游标定义所选取出 来的数据记录存放在tempdb 数据库下的临时表内，对该游标的读取 操作皆在这个临时表里进行，因此游标不会随着基本表内容的改变 而改变，同时也无法通过游标来更新基本表。如果不使用该保留字 那么对基本表的更新删除都会体现到游标中。 SCROLL 指定所选的提取操作（FIRST、LAST、PRIOR、NEXT、 RELATIVE、ABSOLUTE）均可用，若不选用SCROLL选项，那么 FETCH NEXT是唯一的提取选项。SCROLL 增加了提取数据的灵 活性。 SELECT_语句 用于定义游标所要进行处理的结果集。在标准的select语句中游标中 不能使用COMPUTE、COMPUTE BY、FOR BROWSE、INTO 语 句。 READ ONLY 不允许游标内数据被更新，是一种只读状态。UPDATE、DELETE等 语句不能使用游标 UPDATE 用于定义游标内可更新字段列。若指定of 字段列 [,…n]]参数，则所 列出的字段列可被更新修改，否则所有的列都将被更新修改
使用 Transact SQL扩展定义游标时需注意 (1)若在指定 FORWARD_ONLY时不指定 STATIO、 KEYSET和 DYNAMIC关键字 则游标作为 DYNAMIC游标进行操作。若 FORWARD ONLY和 SCROLL均未指定 除井指定 STATIO、 KEYSET或 DYNAMIC关键字,否则默认为 FORWARD ONLY。 STATIO、 KEYSET和 DYNAMIC游标默认为 SCROLL。 FAST FORWARD和 FORWARD_ONLY是互斥的;如果指定一个,则不能指定另一个。 (2)若指定 FAST FORWARD,则不能也指定 SCROLL、 FOR UPDATE SCROLL LOCKS和 FORWARD ONLY。 FAST FORWARD和 FORWARD_ONLY是 互斥的。 3.游标变量 游标变量是一种新增数据类型,用于定义一个游标变量。可先声明一个游标,如 declare yu_ cur scroll cursor for select s from titleauthor 再使用SET语句将一游标赋值给游标变量。 declare (@pan cursor set @pan=yu_cur 当然,可将声明游标语句放在游标赋值语句中。 declare @pan cursor declare yu_ cur scroll cursor for select s from titleauthor set pan= yu_cur
使用Transact_SQL扩展定义游标时需注意： （1）若在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET 和 DYNAMIC 关键字， 则游标作为 DYNAMIC 游标进行操作。若 FORWARD_ONLY 和 SCROLL 均未指定， 除非指定 STATIC、KEYSET 或 DYNAMIC 关键字，否则默认为 FORWARD_ONLY。 STATIC、KEYSET 和 DYNAMIC 游标默认为 SCROLL。FAST_FORWARD 和 FORWARD_ONLY 是互斥的；如果指定一个，则不能指定另一个。 （2）若指定FAST_FORWARD，则不能也指定 SCROLL、FOR_UPDATE、 SCROLL_LOCKS和FORWARD_ONLY。FAST_FORWARD 和 FORWARD_ONLY 是 互斥的。 3．游标变量 游标变量是一种新增数据类型，用于定义一个游标变量。可先声明一个游标，如： declare yu_cur scroll cursor for select * from titleauthor 再使用SET 语句将一游标赋值给游标变量。 declare @pan cursor set @pan = yu_cur 当然，可将声明游标语句放在游标赋值语句中。 declare @pan cursor declare yu_cur scroll cursor for select * from titleauthor set @pan = yu_cur
参数参数说明 指定该游标为局部游标,即其作用域仅限在所在的存储过程、触发器或批处理中。 LOCAL 当建立游标的存储过程或触发器等结束后,游标会被自动释放;但可在存储过程中 使用 OUTPUT保留字,将游标传递给该存储过程的调用者,在存储过程结束后, 还可引用该游标变量 GLOBAL指定该游标为全局游标,即作用域是整个当前连接。选项表明在整个连接的任何存 储过程、触发器或批处理中都可以使用该游标,该游标在连接断开时会自动隐性释 放 FORWAR游标提取数据时只能从第一行向前滚动到最后一行, FETCH NEXT.是唯一支持的 D_ONLY提取选项 指定所选的提取操作(如 FIRST、LAST、 PRIOR、NEXT、 RELATIVE、 SCROLL ABSOLUTE)均可用, SCROLL增加了提取数据的灵活性,可随意读取结果集 中的行数据而不必重新打开游标。 STATIC STATIC:定义游标为静态游标,与Ⅰ NSENSITIVE选项作用相同 指定游标为键集驱动游标,即当游标打开时,游标中记录行的顺序已经固定。对记 录行进行唯一标识的键集内置在 tempdb内一个称为 keyset的表中。对基表中的 KEYSET非键值所做的更改在用户滚动游标时是可视的。其他用户进行的插入是不可视的。 如果某行已删除,则对该行的提取操作将返回@@ FETCH_ STATUS值-2。 DYNAMI/定义游标为动态游标。即基础表的变化将反映到游标中,行的数据值、顺序和成员 在每次提取时都会更改。使用该选项可保证数据的一致性,不支持 ABSOLUTE选 项
参 数 参 数 说 明 LOCAL 指定该游标为局部游标，即其作用域仅限在所在的存储过程、触发器或批处理中。 当建立游标的存储过程或触发器等结束后，游标会被自动释放；但可在存储过程中 使用OUTPUT 保留字，将游标传递给该存储过程的调用者，在存储过程结束后， 还可引用该游标变量。 GLOBAL 指定该游标为全局游标，即作用域是整个当前连接。选项表明在整个连接的任何存 储过程、触发器或批处理中都可以使用该游标，该游标在连接断开时会自动隐性释 放。 FORWAR D_ONLY 游标提取数据时只能从第一行向前滚动到最后一行，FETCH NEXT是唯一支持的 提取选项。 SCROLL 指定所选的提取操作（如FIRST、LAST、PRIOR、NEXT、RELATIVE、 ABSOLUTE）均可用， SCROLL 增加了提取数据的灵活性，可随意读取结果集 中的行数据而不必重新打开游标。 STATIC STATIC：定义游标为静态游标，与INSENSITIVE 选项作用相同。 KEYSET 指定游标为键集驱动游标，即当游标打开时，游标中记录行的顺序已经固定。对记 录行进行唯一标识的键集内置在 tempdb 内一个称为 keyset 的表中。对基表中的 非键值所做的更改在用户滚动游标时是可视的。其他用户进行的插入是不可视的。 如果某行已删除，则对该行的提取操作将返回 @@FETCH_STATUS 值 -2。 DYNAMI C 定义游标为动态游标。即基础表的变化将反映到游标中，行的数据值、顺序和成员 在每次提取时都会更改。使用该选项可保证数据的一致性，不支持ABSOLUTE选 项