《SQL Server 2005数据库实用教程 高职应用技术专业 屈武江第9章 游标》由会员分享，可在线阅读，更多相关《SQL Server 2005数据库实用教程 高职应用技术专业 屈武江第9章 游标（51页珍藏版）》请在金锄头文库上搜索。
1、第9章 游标,1、掌握游标的概念 2、学习游标的定义 3、掌握功能和游标的类型 4、掌握游标的基本操作,本章重点,9.1 游标的概述 9.2 游标的基本操作 9.3 使用系统过程查看游标信息,第9章 游标,SQL Server 2005通过游标提供了对一个结果集进行逐行处理的功能。游标可以被看作是一个指针，它与一个结果集相关联。在游标打开时，游标指向结果集第一条记录的顶部，游标的移动范围为整个结果集，游标指向的记录为当前记录，游标可以指向结果集的任何位置，以便对指定位置的记录进行处理。,9.1 游标的概述,9.1.1 游标的功能,应用程序通过游标对查询语句返回的结果集中的每一行进行相同或不同处
2、理，而不是一次对整个结果集进行统一操作，游标提供了对当前记录的更新和删除功能，具体的处理功能有以下几个方面： 1允许定位在结果集的特定行。 2可从结果集的当前位置检索一行或部分行。 3支持对结果集中当前位置的行进行数据修改。 4为由其他用户对显示在结果集中的记录所做的更改，提供不同级别的可见性支持。 5提供用于访问结果集中记录的 T-SQL 语句。,9.1.2 游标的类型,在SQL Server 2005中，根据游标的使用方式、用途等不同，可以将游标分为多种类型。 1根据运行的位置和用途分类 （1）T-SQL游标 T-SQL游标是使用DECLARE CURSOR等T-SQL语句进行定义，主要用
3、于T-SQL脚本、存储过程和触发器中。T-SQL游标主要用在服务器上，处理由客户端发送给服务器的T-SQL语句请求。 T-SQL游标名称和在游标中定义的变量只能在T-SQL语句中引用，而不能由OLE DB、ODBC、ADO和DB-library的API中函数引用。 （2）API服务器游标（应用程序编程接口服务器游标） API服务器游标支持OLE DB、ODBC 、ADO和DB-library中使用的游标函数。OLE DB、ODBC 和 ADO API 支持将游标映射到已执行SQL语句的结果集中。每次客户端应用程序调用API游标函数时，Microsoft SQL Native Client OL
4、E DB 访问接口和 SQL Native Client ODBC 驱动程序将客户请求传输到服务器，以便对API服务器游标进行操作。,（3）客户游标 客户游标，即在客户端实现的游标。ODBC支持客户端游标，客户游标主要在客户机上缓存结果集时使用，由SQL Native Client ODBC驱动程序和实现ADO API的DLL在内部实现。客户端游标通过在客户端高速缓存所有结果集来实现。每次客户端应用程序调用API游标函数时，SQL Native Client ODBC驱动程序或ADO DLL就对客户端高速缓存的结果集执行游标操作。 T-SQL游标和API服务器游标都在服务器上实现，所以将它们统
5、称为服务器游标。本书只介绍T-SQL服务器游标。,2根据处理特性分类 根据T-SQL服务器游标的处理特性，可将游标分为4种类型：静态游标、动态游标、只进游标和键集驱动游标。 （1）静态游标（STATIC Cursor） 静态游标的完整结果集在游标打开时建立在Tempdb中。静态游标总是按照游标打开时的原样显示结果集。静态游标不反映在数据库中所做的任何影响结果集的更改，也不反映对组成结果集中行或列值所做的更新。静态游标不会显示游标打开以后在数据库中新插入的行，即使这些行符合游标SELECT语句的搜索条件。如果组成结果集的行被其他用户更新，则新的数据值不会显示在静态游标中。静态游标显示打开游标以后
6、从数据库中删除的行。静态游标中不会反映UPDATE、INSERT或DELETE操作。静态游标始终是只读的。由于静态游标的结果集存储在tempdb数据库的工作表中，因此结果集中行的大小不能超过SQL Server表的最大行大小。,（2）动态游标（DYNAMIC Cursor） 动态游标与静态游标相反。当游标移动时，它时时的反映结果集中所做的任何更改。结果集中行数据的值、顺序在每次提取时都会发生改变。所有用户所做的UPDATE、INSERT、DELETE操作通过游标都可以反映出来。,（3）只进游标（FAST_FORWARD Cursor） 只进游标不支持滚动，它只支持游标从头到尾的顺利提取。行只有
7、从数据库中提取出来后才能检索。对所有由当前用户发出或由其他用户提交，并影响结果集中行的INSERT、UPDATE、DELETE语句，其效果在这些行从游标中提取时是可见的。 SQL Server 2005将只进和滚动都作为能应用于静态游标、键集驱动游标和动态游标的选项。T-SQL游标支持只进静态游标、键集驱动游标和动态游标。 （4）键集驱动游标（KEYSET Cursor） 键集驱动游标打开时，该游标中各行的成员身份和顺序是固定的。键集驱动游标由一组唯一标识符控制，这组键称为键集。键是根据以唯一方式标识结果集中各行的一组列生成的。键集是打开游标时来自符合SELECT语句要求的所有行中的一组键值。
8、由键集驱动的游标，对应的键集是打开该游标时在tempdb数据库中生成的。 当用户滚动游标时，对非键集列中的数据值所做的更改是可见的。在游标外对数据库所做的插入，在游标内是不可见的，除非关闭并重新打开游标。,3根据移动方式分类 根据T-SQL服务器游标在结果集中的移动方式，可将游标分为滚动游标和向前游标。 （1）滚动游标(SCROLL Cursor) 在游标结果集中，滚动游标可以前后移动，包括移向下一行、上一行、第一行、最后一行或根据当前位置移动到某一行，或直接移动到指定行。 （2）向前游标(FORWARD_ONLY Cursor) 在游标结果集中，向前游标只能向前移动，即只有向前一种移动方式。
9、,4根据是否允许更新分类 根据T-SQL服务器游标结果集是否允许修改，可将游标分为两大类：只读游标和可更新游标 （1）只读游标（READ_ONLY Cursor） 只读游标禁止修改游标结果集中的数据。 （2）可更新游标（FOR UPDATE Cursor） 可以修改游标结果集中的数据，它又分为部分可修改和全部可修改。部分可修改表示只能修改数据行指定的列，而全部可修改游标可以修改数据行的所有列。,1按照不同的变量选择游标类型 （1）结果集大小。 （2）很可能需要的数据的百分比。 （3）打开的游标的性能。 （4）需要的游标操作，如滚动或定位更新。 （5）由其他用户对数据所做修改的可见性级别。 如果
10、无更新操作，则默认设置对于小结果集是适合的，但对于大结果集，使用动态游标更为适合，因为用户可能在检索许多行之前就可以找到答案。 如果要检索的数据将一次全部使用，且无需定位更新或滚动操作，则建议使用默认结果集。Microsoft SQL Server 2005 实现了多个活动的结果集 (MARS)，消除了防止有多个等待的默认结果集的限制。,9.1.3 游标类型的选择,2选择游标类型的规则 选择游标类型时遵循的一些简单规则包括： （1）尽可能使用默认结果集。如果需要滚动操作，将小结果集缓存在客户端，并在缓存中滚动而不是要求服务器实现游标，其效率可能更高。 （2）将整个结果集提取到客户端（如产生报表
11、）时，使用默认设置。默认结果集是将数据传送到客户端的最快方式。 （3）如果应用程序正在使用定位更新，则不能使用默认结果集。 （4）默认结果集必须用于将生成多个结果集的 Transact-SQL 语句或 Transact-SQL 语句批。,（5）动态游标的打开速度比静态游标或由键集驱动的游标的打开速度快。当打开静态游标和由键集驱动的游标时，必须生成内部临时工作表，而动态游标则不需要。 （6）在联接中，由键集驱动的游标和静态游标的速度可能比动态游标的速度快。 （7）如果要进行绝对提取，必须使用由键集驱动的游标或静态游标。 （8）静态游标和由键集驱动的游标增加了 tempdb 的使用率。静态服务器游
12、标在 tempdb 中创建整个游标，由键集驱动的游标则在 tempdb 中创建键集。 （9）如果游标在整个回滚操作期间必须保持打开状态，请使用同步静态游标，并将 CURSOR_CLOSE_ON_COMMIT 设为 OFF。 使用服务器游标时，对 API 提取函数或方法的每次调用都会产生一个到服务器的往返过程。应用程序应使用块状游标尽量减少这些往返过程，同时每次提取时返回适当数目的行。,使用游标对结果集进行操作，应遵循“声明游标”“打开游标”“读取数据”“关闭游标”“释放游标”的处理顺序。,9.2 游标的基本操作,9.2.1 声明游标,声明游标是指利用SELECT查询语句创建游标的结构，指明游标
13、的结果集中包含哪些数据。声明游标有两种方式：SQL-92标准方式和T-SQL扩展方式。,1SQL-92标准方式游标声明 DECLARE CURSOR_name /*指定游标名称*/ INSENSITIVE SCROLL CURSOR /*指定游标类型*/ FOR SELECT_statement /*指定查询语句*/ FOR READ ONLY | UPDATE OF column_name ,.n /*指定游标读写方式*/ ;,参数说明： （1）CURSOR_name：游标名称 （2）INSENSITIVE：表示声明一个静态游标 （3）SCROLL：表示声明一个滚动游标 （4）SELECT_
14、statement：表示SELECT查询语句 （5）READ ONLY：表示声明一个只读游标 （6）UPDATE：表示声明一个可修改游标，有OF column_name，则指定可被修改的列名。,2T-SQL扩展方式声明 DECLARE CURSOR_name CURSOR /*指定游标名称*/ LOCAL | GLOBAL /*指定游标的作用域*/ FORWARD_ONLY | SCROLL /*指定游标的移动方向*/ STATIC | KEYSET | DYNAMIC | FAST_FORWARD /*指定游标类型*/ READ_ONLY | SCROLL_LOCKS | OPTIMISTI
15、C /*指定游标属性*/ TYPE_WARNING /*指定游标转换警告*/ FOR SELECT_statement /*指定查询语句*/ FOR UPDATE OF column_name ,.n /*指定游标可修改的列*/ ;,参数说明： （1）CURSOR_name：所定义的 Transact-SQL 服务器游标的名称。CURSOR_name 必须符合标识符规则。 （2）LOCAL：表示定义游标的作用域仅限在其所在的存储过程、触发器、或批处理中。当建立游标的存储过程执行结束后，游标会被自动释放。 （3）GLOBAL：指定该游标的作用域，说明所声明的是全局游标。在由连接执行的任何存储过程
16、或批处理中，都可以引用该游标名称。该游标仅在断开连接时被自动释放。 注意：如果没有指定GLOBAL 和 LOCAL 参数，SQL Server 2005将取默认值LOCAL。,（4）FORWARD_ONLY：表明在从游标中提取数据记录时，只能从第一行滚动到最后一行。FETCH NEXT 是唯一受支持的提取选项。 （5）SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未在 SQL-92 DECLARE CURSOR 中指定 SCROLL，则 NEXT 是唯一支持的提取选项。如果也指定了 FAST_FORWARD，则不能指定 SCROLL。 （6）STATIC：该选项的含义与INS
