SQL游标详解.docx

SQL游标详解.docx

《SQL游标详解.docx》由会员分享，可在线阅读，更多相关《SQL游标详解.docx（23页珍藏版）》请在冰豆网上搜索。

SQL游标详解

本章前半部分提供了在MSSQLSERVER中应用游标所应具有的有关游标的必要知识和各种语法。

从中读者可以了解游标的优点、种类、作用、学会如何定义、打开、存取、关闭、释放游标以及游标的应用。

除此之外，在本章的后半部分我们介绍了视图和用户自定义函数，使读者了解视图的众多优点，比如简化操作、提高数据安全性；了解如何创建、管理视图和用户自定义函数；了解如何在存储过程和批处理中调用用户自定义函数等诸多问题。

　　在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECTINSERT语句。

但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。

那么如何解决这种问题呢？

游标为我们提供了一种极为优秀的解决方案。

　　13.1.1游标和游标的优点

　　在数据库中，游标是一个十分重要的概念。

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标总是与一条T_SQL选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。

当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

如果曾经用C语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。

对于游标而言，其道理是相同的。

可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

　　我们知道关系数据库管理系统实质是面向集合的，在MSSQLSERVER中并没有一种描述表中单一记录的表达形式，除非使用where子句来限制只有一条记录被选中。

因此我们必须借助于游标来进行面向单条记录的数据处理。

　　由此可见，游标允许应用程序对查询语句select返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。

　　13.1.2游标种类

　　MSSQLSERVER支持三种类型的游标：

Transact_SQL游标，API服务器游标和客户游标。

（1）Transact_SQL游标

　　Transact_SQL游标是由DECLARECURSOR语法定义、主要用在Transact_SQL脚本、存储过程和触发器中。

Transact_SQL游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL语句或是批处理、存储过程、触发器中的Transact_SQL进行管理。

Transact_SQL游标不支持提取数据块或多行数据。

（2）API游标

　　API游标支持在OLEDB，ODBC以及DB_library中使用游标函数，主要用在服务器上。

每一次客户端应用程序调用API游标函数，MSSQLSEVER的OLEDB提供者、ODBC驱动器或DB_library的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。

　　（3）客户游标

　　客户游标主要是当在客户机上缓存结果集时才使用。

在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。

客户游标仅支持静态游标而非动态游标。

由于服务器游标并不支持所有的Transact-SQL语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。

因为在一般情况下，服务器游标能支持绝大多数的游标操作。

　　由于API游标和Transact-SQL游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。

在本章中我们主要讲述服务器（后台）游标。

每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序；

　　1.DECLARE游标

　　2.OPEN游标

　　3.从一个游标中FETCH信息

　　4.CLOSE或DEALLOCATE游标

　　通常我们使用DECLARE来声明一个游标声明一个游标主要包括以下主要内容：

游标名字数据来源（表和列）选取条件属性（仅读或可修改）

　　其语法格式如下：

　　DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR

　　FORselect_statement

　　[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]

　　其中：

cursor_name

　　指游标的名字。

INSENSITIVE

　　表明MSSQLSERVER会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb数据库下）。

对该游标的读取操作皆由临时表来应答。

因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过

　　游标来更新基本表。

如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。

　　另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE选项。

　　在SELECT语句中使用DISTINCT、GROUPBY、HAVINGUNION语句；

　　使用OUTERJOIN；

　　所选取的任意表没有索引；

　　将实数值当作选取的列。

SCROLL

　　表明所有的提取操作（如FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）都可用。

如果不使用该保留字，那么只能进行NEXT提取操作。

由此可见，SCROLL极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再

　　重开游标。

select_statement

　　是定义结果集的SELECT语句。

应该注意的是，在游标中不能使用COMPUTE、COMPU-TEBY、FORBROWSE、INTO语句。

READONLY

　　表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。

而且在UPDATE或DELETE语句的WHERECURRENTOF子句中，不允许对该游标进行引用。

UPDATE[OFcolumn_name[,…n]]

　　定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。

当游标被成功创建后，游标名成为该游标的惟一标识，如果在以后的存储过程、触发器或Transact_SQL脚本中使用游标，必须指定该游标的名字。

　　上面介绍的是SQL_92的游标语法规则。

下面介绍MSSQLSERVER提供的扩展了的游标声明语法，通过增加另外的保留字，使游标的功能进一步得到了增强其语法规则为；

LOCAL

　　定义游标的作用域仅限在其所在的存储过程、触发器或批处理中。

当建立游标的存储过程执行结束后，游标会被自动释放。

因此，我们常在存储过程中使用OUTPUT保留字，将游标传递给该存储过程的调用者，这样在存储过程执行结束后，可以引用该游标变量，在该种情况下，直到引用该游标的最后一个就是被释放时，游标才会自动释放。

GLOBAL

　　定义游标的作用域是整个会话层会话层指用户的连接时间它包括从用户登录到SQLSERVER到脱离数据库的整段时间。

选择GLOBAL表明在整个会话层的任何存储过程、触发器或批处理中都可以使用该游标，只有当用户脱离数据库、时该游标才会被自动释放。

　　注意：

如果既未使用GLOBAL也未使用LOCAL，那么SQLSERVER

将使用defaultlocalcursor数据库选项，为了与以彰的版本歉容，该选项常设置为FALSE。

FORWARD_ONLY

　　选项指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用FETCHNEXT操作。

除非使用STATIC，KEYSET和DYNAMIC关键字，否则如果未指明是使用FORWARD_ONLY还是使用SCROLL，那么FORWARD_ONLY将成为缺省选项，因为若使用STATICKEYSET和DYNAMIC关键字，则变成了SCROLL游标。

另外如果使用了FORWARD_ONLY，便不能使用FAST_FORWARD。

STATIC

　　选项的含义与INSENSITIVE选项一样，MSSQLSERVER会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb数据库下）。

对该游标的读取操作皆由临时表来应答。

因此对基本表的修改并不影响游标中的数据，即游标不会随着基本表内容的

　　改变而改变，同时也无法通过游标来更新基本表。

KEYSET

　　指出当游标被打开时，游标中列的顺序是固定的，并且MSSQLSERVER会在tempdb内建立一个表，该表即为KEYSETKEYSET的键值可惟一识别游标中的某行数据。

当游标拥有者或其它用户对基本表中的非键值数据进行修改时，这种变化能够反映到游标中，所以游标用户或所有者可以通过滚动游标提限这些数据。

　　当其它用户增加一条新的符合所定义的游标范围的数据时，无法由此游标读到该数据。

因为Transact-SQL服务器游标不支持INSERT语句。

　　如果在游标中的某一行被删除掉，那么当通过游标来提取该删除行时，@@FETCH_STATUS的返回值为-2。

@@FETCH_STATUS是用来判断读取游标是否成功的系统全局变量。

　　由于更新操作包括两部分：

删除原数据插入新数据，所以如果读取原数据，@@FETCH_STATUS的返回值为-2；而且无法通过游标来读取新插入的数据。

但是如果使用了WHERECURRENTOF子句时，该新插入行数据便是可见的。

　　注意：

如果基础表未包含惟一的索引或主键，则一个KEYSET游标将回复成STATIC游标。

DYNAMIC

　　指明基础表的变化将反映到游标中，使用这个选项会最大程度上保证数据的一致性。

然而，与KEYSET和STATIC类型游标相比较，此类型游标需要大量的游标资源。

FAST_FORWARD

　　指明一个FORWARD_ONLY,READ_ONLY型游标。

此选项已为执行进行了优化。

如果SCROLL或FOR_UPDATE选项被定义，则FAST_FORWARD选项不能被定义。

SCROLL_LOCKS

　　指明锁被放置在游标结果集所使用的数据上当。

数据被读入游标中时，就会出现锁。

这个选项确保对一个游标进行的更新和删除操作总能被成功执行。

如果FAST_FORWARD选项被定义，则不能选择该选项。

另外，由于数据被游标锁定，所以当考虑到数据并发处理时，应避免使用该选项。

OPTIMISTIC

　　指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。

如果使用了FAST_FORWARD选项，则不能使用该选项。

TYPE_WARNING

　　指明若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端。

　　注意：

不可以将SQL_92的游标语法规则与MSSQLSERVER的游标扩展用法混合在一起使用。

　　下面我们将总结一下声明游标时应注意的一些问题。

　　如果在CURSOR前使用了SCROLL或INSENSITIVE保留字，则不能在CURSOR和FORselect_statement之间使用任何的保留字。

反之同理。

　　如果用DECLARECURSOR声明游标时，没有选择READ_ONLY、OPTIMISTIC或SCROLL_LOCKS选项时，游标的缺省情况为：

　　如果SELECT语句不支持更新，则游标为READ_ONLY；

　　STATIC和FAST_FORWARD类型的游标缺省为READ_ONLY；

DYNAMIC和KEYSET游标缺省为OPTIMISTIC。

　　我们仅能在Transact-SQL语句中引用游标，而不能在数据库API函数中引用。

　　游标被声明以后，可以通过系统过程对其特性进行设置。

　　对那些有权限对视图、表或某些列执行SELECT语句的用户而言，它也具有使用游标的缺省权限。

打开游标游标在声明以后，如果要从游标中读取数据必须打开游标。

打开一个Transact-SQL服务器游标使用OPEN命令，其语法规则为：

　　OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}

　　各参数说明如下：

　　•GLOBAL

　　定义游标为一全局游标。

　　•cursor_name

　　为声明的游标名字。

如果一个全局游标和一个局部游标都使用同一个游标名，则如果使用GLOBAL便表明其为全局游标，否则表明其为局部游标。

　　•cursor_variable_name

　　为游标变量。

当打开一个游标后时，MSSQLSERVER首先检查声明游标的语法是否正确，如果游标声明中有变量，则将变量值带入。

　　在打开游标时，如果游标声明语句中使用了INSENSITIVE或STATIC保留字，则OPEN产生一个临时表来存放结果集；如果在结果集中任何一行数据的大小超过MSSQLSERVER定义的最大行尺寸时，OPEN命令将失败；如果声明游标时作用了KEYSET选项，则OPEN产生一个临时表来存放键值。

所有的临时表都存在tempdb数据库中。

　　在游标被成功打开之后，@@CURSOR_ROWS全局变量将用来记录游标内数据行数。

为了提高性能，MSSQLSERVER允许以异步方式从基础表向KEYSET或静态游标读入数据，即如果MSSQLSERVER的查询优化器估计从基础表中返回给游标的数据行已经超过sp_configurecursorthreshold参数值，则MSSQLSERVER将启动另外一个独立的线程来继续从基础表中读入符合游标定义的数据行，此时可以从游标。

中读取数据进行处理而不必等到所有的符合游标定义的数据行都从基础表中读入游标@@CURSOR_ROWS变量存储的正是在调用@@CURSOR_ROWS时，游标已从基础表读入的数据行。

@@CURSOR_ROWS的返回值有以下四个，如表13-1所示。

　　如果所打开的游标在声明时带有SCROLL或INSENSITIVE保留字，那么@@CURSOR_ROWS的值为正数且为该游标的所有数据行。

如果未加上这两个保留字中的一个，则@@CURSOR_ROWS的值为-1，说明该游标内只有一条数据记录。

当游标被成功打开以后，就可以从游标中逐行地读取数据，以进行相关处理。

从游标中读取数据主要使用FETCH命令。

其语法规则为：

　　各参数含义说明如下：

　　•NEXT

　　返回结果集中当前行的下一行，并增加当前行数为返回行行数。

如果FETCHNEXT是第一次读取游标中数据，则返回结果集中的是第一行而不是第二行。

　　•PRIOR

　　返回结果集中当前行的前一行，并减少当前行数为返回行行数。

如果FETCHPRIOR是第一次读取游标中数据，则无数据记录返回，并把游标位置设为第一行。

　　•FIRST

　　返回游标中第一行。

　　•LAST

　　返回游标中的最后一行。

　　•ABSOLUTE{n|@nvar}

　　如果n或@nvar为正数，则表示从游标中返回的数据行数。

如果n或@nvar为负数，则返回游标内从最后一行数据算起的第n或@nvar行数据。

若n或@nvar超过游标的数据子集范畴，则@@FETCH_STARS返回-1，在该情况下，如果n或@nvar为负数，则执行FETCHNEXT命令会得到第一行数据，如果n或@nvar为正值，执行FETCHPRIOR命令则会得到最后一行数据。

n或@nvar可以是一固定值也可以是一smallint,tinyint或int类型的变量。

　　•RELATIVE{n|@nvar}

　　若n或@nvar为正数，则读取游标当前位置起向后的第n或@nvar行数据；如果n或@nvar为负数，则读取游标当前位置起向前的第n或@nvar行数据。

若n或@nvar超过游标的数据子集范畴，则@@FETCH_STARS返回-1，在该情况下，如果n或@nvar为负数，则执行FETCHNEXT命令则会得到第一行数据；如果n或@nvar为正值，执行FETCHPRIOR命令则会得到最后一行数据。

n或@nvar可以是一固定值也可以是一smallint,tinyint或int类型的变量。

　　•INTO@variable_name[,...n]

　　允许将使用FETCH命令读取的数据存放在多个变量中。

在变量行中的每个变量必须与游标结果集中相应的列相对应，每一变量的数据类型也要与游标中数据列的数据类型相匹配。

　　@@FETCH_STATUS全局变量返回上次执行FETCH命令的状态。

在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH操作是否成功，来决定如何进行下一步处理。

@@FETCH_STATUS变量有三个不同的返回值,如表13-2。

　　在使用FETCH命令从游标中读取数据时，应该注意以下的情况：

　　当使用SQL-92语法来声明一个游标时，没有选择SCROLL选项时，只能使用FETCHNEXT命令来从游标中读取数据，即只能从结果集第一行按顺序地每次读取一行，由于不能使用FIRST、LAST、PRIOR，所以无法回滚读取以前的数据。

如果选择了SCROLL选项，则可能使用所有的FETCH操作。

　　当使用MSSQLSERVER的扩展语法时，必须注意以下约定：

　　•如果定义了FORWARD-ONLY或FAST_FORWARD选项，则只能使用FETCHNEXT命令；

　　•如果没有定义DYNAMIC,FORWARD_ONLY或FAST_FORWARD选项，而定义了KEYSET,STATIC或SCROLL中的任何一个，则可使用所有的FETCH操作；

　　•DYNAMICSCROLL游标支持所有的FETCH，选项但禁用ABSOLUTE选项。

13.5.1关闭游标

　　1、使用CLOSE命令关闭游标

　　在处理完游标中数据之后必须关闭游标来释放数据结果集和定位于数据记录上的锁。

CLOSE语句关闭游标，但不释放游标占用的数据结构。

如果准备在随后的使用中再次打开游标，则应使用CLOSE命令。

其关闭游标的语法规则为：

　　CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}

　　2、自动关闭游标

　　我们已经了解到游标可应用在存储过程、触发器和Transact_SQL脚本中。

如果在声明游标与释放游标之间使用了事务结构，则在结束事务时游标会自动关闭。

其具体的情况如下所示：

（1）、声明一个游标

（2）、打开游标

　　（3）、读取游标

　　（4）、BEGINTRANSATION

　　（5）、数据处理

　　（6）、COMMITTRANSATION

　　（7）、回到步骤3

　　在这样的应用环境中。

当从游标中读取一条数据记录进行以BEGINTRANSATION为开头，COMMITTRANSATION或ROLLBACK为结束的事务处理时，在程序开始运行后，第一行数据能够被正确返回，经由步骤7，程序回到步骤3，读取游标的下一行，此时常会发现游标未打开的错误信息。

其原因就在于当一个事务结束时，不管其是以COMMITTRANSATION还是以ROLLBACKTRANSATION结束，MSSQLSERVER都会自动关闭游标，所以当继续从游标中读取数据时就会造成错误。

　　解决这种错误的方法就是使用SET命令将CURSOR_CLOSE_ON_COMMIT这一参数设置为OFF状态。

其目的就是让游标在事务结束时仍继续保持打开状态，而不会被关闭。

使用SET命令的格式为：

　　SETCURSOR_CLOSE_ON_COMMITOFF

　　13.5.2释放游标

　　在使用游标时，各种针对游标的操作或者引用游标名，或者引用指向游标的游标变量。

当CLOSE命令关闭游标时，并没有释放游标占用的数据结构。

因此常使用DEALLOCATE命令。

通过该命令可以删除掉游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。

其语法规则为：

　　DEALLOCATE{{[GLOBAL]cursor_name}|@cursor_variable_name}

　　各参数的含义参看13.3打开游标一节。

　　当使用DEALLOCATE@cursor_variable_name来删除游标时，游标变量并不会被释放，除非超过使用该游标的存储过程、触发器的范围（即游标的作用域）。

　　13.5.3游标变量

　　游标变量是从MSSQLSERVER7版本才开始使用的一种新增数据类型。

定义一个游标变量主要有两种方法。

　　首先我们先声明一个游标。

　　使用SET语句将一游标赋值给游标变量：

　　将声明游标语句放在游标赋值语句中，如下所示：

　　例13-5：

下面给出一个具体完整的例子，在该例子中我们对DEALLOCATE命令将有更加清晰的了解。

通常情况下我们用游标来从基础表中检索数据，以实现对数据的行处理。

但在某些情况下，我们也常要修改游标中的数据，即进行定位更新或删除游标所包含的数据。

所以必须执行另外的更新或删除命令，并在WHERE子句中重新给定条件才能修改到该行数据。

但是如果在声明游标时使用了FORUPDATE语句，那么就可以在UPDATE或DELETE命令中以WHERECURRENTOF关键字直接修改或删除当前游标中所存储的数据，而不必使用WHERE子句重新给出指定条件。

当改变游标中数据时，这种变化会自动地影响到游标的基础表。

但是如果在声明游标时选择了INSENSITIVE选项时，该游标中的数据不能被修改，具体含义请参看声明游标一节中对INSENSITIVE选项的详细解释。

　　进行定位修改或删除游标中数据的语法规则为：

　　其中：

　　•table_name：

UPDATE或DELETE的表名；

　　•column_name：

UPDATE的列名；

　　•cursor_name：

游标名。

　　下面我们将给出两个例子来说明如何对游标进行定位更新或删除，首先声明一个游标。

　　例13-6：

更新authors表中的au_lname和au_fname列

　　例13-7：

删除authors表中的一行数据

　　提示：

以上更新或删除操作总是基于游标的当前位置。

　　例13-8：

下面是一个定位更新的完整例子，首先查看authors表中每一行，将au_id等于’172-32-1176’的记录的au_lname和au_fname分别更改为’Smith’和’Jake’。

在前面几节，我们详细介绍了如何声明游标，从游标中读取数据以及关闭、释放游标的方法。

下面我们将给出几个应用实例使读者对游标有更为全面