游标学习笔记

数据库的游标是类似于C语言指针一样的语言结构。通常情况下，数据库执行的大多数SQL命令都是同时处理集合内部的所有数据。但是，有时侯用户也需要对这些数据集合中的每一行进行操作。在没有游标的情况下，这种工作不得不放到数据库前端，用高级语言来实现。这将导致不必要的数据传输，从而延长执行的时间。通过使用游标，可以在服务器端有效地解决这个问题。游标提供了一种在服务器内部处理结果集的方法，它可以识别一个数据集合内部指定的工作行，从而可以有选择地按行采取操作。

游标的功能比较复杂，要灵活使用游标需要花费较长的时间练习和积累经验。本节只介绍使用游标最基本和最常用的方法。如果想进一步地学习，可以参考数据库的相关     书籍。

4.6.1  声明游标

在使用游标之前首先要声明游标，Transact-SQL在ANSI 92 SQL 的基础上对游标的功能作了一定的扩充，因此产生了与ANSI 92 SQL稍有不同的游标声明方法。

声明游标的语法如下：

 

DECLARE 游标名 [INSENSITIVE] [SCROLL] CURSOR

FOR SELECT 语句

[FOR READ ONLY | UPDATE [OF 列名1，列名2，列名3 …]

 

游标名为声明的游标所取的名字，声明游标必须遵守Transact-SQL对标识符的命名规则。

使用INSENSITIVE定义的游标，把提取出来的数据放入一个在tempdb数据库创建的临时表里。任何通过这个游标进行的操作，都在这个临时表里进行。所以所有对基本表的改动都不会在用游标进行的操作中体现出来。如果忽略了INSENSITIVE关键字，那么用户对基本表所做的任何操作，都将在游标中得到体现。

使用SCROLL关键字定义的游标，具有包括如下所示的所有取数功能：

l          FIRST  取第一行数据。

l          LAST  取最后一行数据。

l          PRIOR  取前一行数据。

l          NEXT  取后一行数据。

l          RELATIVE  按相对位置取数据。

l          ABSOLUTE  按绝对位置取数据。

如果没有在声明时使用SCROLL关键字，那么所声明的游标只具有默认的NEXT    功能。

READ ONLY声明只读光标，不允许通过只读光标进行数据的更新。

UPDATE [OF 列名1，列名2，列名3 …]定义在这个游标里可以更新的列。如果定义了[OF 列名1，列名2，列名3 …]，那么只有列在表中的列可以被更新；如果没有定义[OF 列名1，列名2，列名3 …]，那么游标里的所有列都可以被更新。

下面是两个声明游标的例子：

 

--这个游标可以在整个authors表中所有的数据行上进行操作

DECLARE authors_cursor CURSOR

    FOR  SELECT  *  

    FROM authors

 

--这个例子声明了一个只读游标，并对游标可以处理的结果集进行了筛选和排序

DECLARE authors_cursor CURSOR FOR

SELECT au_id, au_fname, au_lname

FROM authors

WHERE state = "UT"

ORDER BY au_id

FOR READONLY

 

4.6.2  打开游标

在可以使用游标之前，必须首先打开游标。打开游标的语法如下：

 

OPEN  cursor_name

 

当执行打开游标的语句时，服务器执行声明游标时使用的SELECT语句，如果使用了INSENSITIVE关键字，则服务器会在 tempdb中建立一张临时表，以存放游标将要操作的数据集的副本。

在打开游标后，可以使用@@CURSOR_ROWS全局变量来返回当前声明的游标可以操作数据行的数量。

4.6.3  关闭游标

在打开游标以后，SQL Server服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以，在不使用游标的时候，一定要关闭游标，以通知服务器释放游标所占用的资源。

关闭游标的语法如下：

 

CLOSE cursor_name

 

关闭游标以后，可以再次打开游标，在一个批处理中，也可以多次打开和关闭游标。

4.6.4  释放游标

游标结构本身也会占用一定的计算机资源，所以在使用完游标后，为了回收被游标占用的资源，应该将游标释放。

释放游标的语法是：

 

DEALLOCATE  cursor_name

 

当释放完游标以后，如果要重新使用这个游标必须重新执行声明游标的语句。

4.6.5  使用游标取数

在打开游标以后，就可以打开游标提取数据了。使用游标提取某一行的数据应该使用下面的语法：

 

FETCH

     [  [    NEXT | PRIOR | FIRST | LAST

             | ABSOLUTE {n | @nvar}

             | RELATIVE {n | @nvar}

          ]

          FROM

     ]

     cursor_name

    [INTO @variable[,...n]]

 

 

在这个语法里，n和@nvar表示游标相对与作为基准的数据行所偏离的位置。

在使用INTO子句对变量赋值时，变量的数量和相应的数据类型必须和声明游标时使用的SELECT语句中引用到的数据列的数目、排列顺序和数据类型完全保持一致，否则服务器会提示错误。

事实上，使用游标取数的操作通常与WHILE循环紧密结合在一起。下面的代码演示了游标的使用方法：

 

USE pubs

GO

 

/*声明游标*/

DECLARE authors_cur CURSOR FOR

SELECT au_lname,au_fname FROM authors

WHERE state = "CA"

ORDER BY au_lname

 

/*打开一个游标*/

OPEN authors_cur

 

/*执行第一次取数操作*/

FETCH NEXT FROM authors_cur

 

/*检查@@FETCH_STATUS以确定是否还可以继续取数*/

WHILE @@FETCH_STATUS = 0

BEGIN

    FETCH NEXT FROM authors_cur

END

 

/*关闭游标*/

CLOSE authors_cur

 

当游标移动到最后一行数据的时候，继续执行取下一行数据的操作，将返回错误信息，但这个信息只在@@FETCH_STATUS中体现，同时返回空白的数据，根据判断条件，程序现在就终止循环。

下面的例子定义了一个滚动游标，从而可以实现更灵活的数据提取。

 

USE pubs

GO

 

/*首先执行一遍查询语句以提供滚动游标操作成功与否的对比*/

SELECT au_lname, au_fname

FROM authors

WHERE state = "CA"

ORDER BY au_lname, au_fname

 

-- 定义滚动游标

DECLARE authors_cur SCROLL CURSOR FOR

SELECT au_lname, au_fname FROM authors

WHERE state = "CA"

ORDER BY au_lname, au_fname

 

/*打开游标*/

OPEN authors_cur

 

/*提取数据集中的最后一行*/

FETCH LAST FROM authors_cur

 

/*提取当前游标所在行的上一行*/

FETCH PRIOR FROM authors_cur

 

/* 提取数据集中的第4行*/

FETCH ABSOLUTE 4 FROM authors_cur

 

/*提取当前行的前两行*/

FETCH RELATIVE -2 FROM authors_cur

 

/*关闭游标*/

CLOSE authors_cur

 

/*释放游标*/

DEALLOCATE authors_cur

GO

 

程序执行结果如下：

 

au_lname                                 au_fname             

---------------------------------------- --------------------

Bennet                                   Abraham

Carson                                   Cheryl

Dull                                     Ann

…

Yokomoto                                 Akiko

 

(所影响的行数为16行)

 

au_lname                                 au_fname            

---------------------------------------- --------------------

Yokomoto                                 Akiko

 

(所影响的行数为1行)

 

…

 

(所影响的行数为1行)

 

下面的程序将所有取到的数据存放在变量里，再打印出来：

 

USE pubs

GO

/*定义变量*/

DECLARE @au_lname varchar(40), @au_fname varchar(20)

 

/*声明游标*/

DECLARE authors_cur CURSOR FOR

SELECT au_lname, au_fname FROM authors

WHERE state = "CA"

ORDER BY au_lname, au_fname

 

/*打开游标*/

OPEN authors_cur

 

/*执行第一次提取数据操作*/

FETCH NEXT FROM authors_cur

INTO @au_lname, @au_fname

 

/*检查上一次操作的执行状态*/

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT "作者： " + @au_fname + " " +  @au_lname

    FETCH NEXT FROM authors_cur

    INTO @au_lname, @au_fname

END

 

/*关闭并释放游标*/

CLOSE authors_cur

DEALLOCATE authors_cur

GO

 

4.6.6  利用游标修改数据

要使用游标进行数据的修改，其前提条件是该游标必须被声明为可更新的游标。在进行游标声明时，没有带READONLY关键字的游标都是可更新的游标。

在游标声明过程中可以使用SELECT语句对多个表中的数据进行访问，因此如果声明的是可更新游标，那么可以使用该游标对多表中的数据进行修改，但是这不是一个更改数据的好办法，因为这种不规范更新数据的途径很容易造成数据的不一致。在计算机编程过程中，经常会遇到这样的情况，具有充分灵活性的语法总是难于操纵，易于出错。

使用游标更新数据的常用语法如下：

 

UPDATE table_name

{SET  column_name = expression}

[,...n]

WHERE   CURRENT OF  cursor_name

 

其中，CURRENT OF cursor_name表示当前游标的当前数据行。CURRENT OF子句只能使用在进行UPDATE和DELETE操作的语句中。

下面的例子声明了一个可更新的游标，并限定了可以更新的列，然后针对该列进行了更新运算。

 

USE pubs

GO

 

/*定义一个对价格可以进行更改的滚动游标*/

DECLARE a_ta_t CURSOR SCROLL FOR

SELECT au_lname , title ,price

FROM   authors a, titleauthor ta, titles t

WHERE  a.au_id     = ta.au_id

AND    ta.title_id = t.title_id

AND    a.state     = "CA"

FOR   UPDATE OF price

 

DECLARE  @fetch_status INT

/*打开一个游标*/

OPEN a_ta_t

 

/*取第1行数据*/

FETCH a_ta_t

SELECT @fetch_status = @@FETCH_STATUS

 

WHILE  @fetch_status = 0

BEGIN

UPDATE titles

SET    price = price / 1.5

     WHERE CURRENT OF a_ta_t

 

/*继续取下一数*/

FETCH a_ta_t

SELECT @fetch_status = @@FETCH_STATUS

END  

 

/*关闭并释放游标*/

CLOSE      a_ta_t

DEALLOCATE a_ta_t

GO

 

/*再次取数进行验证*/

SELECT au_lname , title ,price

FROM   authors a, titleauthor ta, titles t

WHERE  a.au_id     = ta.au_id

AND    ta.title_id = t.title_id

AND    a.state     = "CA"

GO

 

这个例子查找来自加利福尼亚州的作者，并把他的书的价格减了价。由于这次声明的游标是用了UPDATE OF子句，所以只有包含在这个子句列表中的数据行可以被更新。

使用游标还可以进行数据的删除，语法是：

 

DELETE

FROM   table_name

WHERE  CURRENT OF  cursor_name

 

在使用游标进行数据的更新或删除之前，必须事先获得相应数据库对象的更新或删除的特权，这是进行这类操作的前提。