sql游标总结

sql游标总结

2010-05-04 13:36:55| 分类： oracle 开发 | 标签： |字号大中小 订阅

游标的概念
游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的优点在于它可以定位到结果集中的某一行，并可以对该行数据执行特定操作，为用户在处理数据的过程中提供了很大方便。一个完整的游标由5部分组成，并且这5个部分应符合下面的顺序。

（1）声明游标。

（2）打开游标。

（3）从一个游标中查找信息。

（4）关闭游标。

（5）释放游标。
声明游标
首先来学习如何声明一个游标，声明游标使用DECLARE CURSOR语句。此语句有两种语法声明格式，分别为SQL 92标准格式和SQL Server扩展（扩展了声明游标的参数）格式，下面将分别介绍声明游标的两种语法格式。

1．SQL-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中的临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用SQL-92语法时，如果省略INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。

l SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。

2 FIRST：取第一行数据。

2 LAST：取最后一行数据。

2 PRIOR：取前一行数据。

2 NEXT：取后一行数据。

2 RELATIVE：按相对位置取数据。

2 ABSOLUTE：按绝对位置取数据。

如果未指定SCROLL，则NEXT是惟一支持的提取选项。

l select_statement：定义游标结果集的标准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中未指定列的列表，则可以更新所有列。

2．SQL 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：定义游标的作用域仅限在其所在的批处理、存储过程或触发器中。当建立游标在存储过程执行结束后，游标会被自动释放。

l GLOBAL：指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在脱接时隐性释放。

l FORWARD_ONLY：指定游标只能从第一行滚动到最后一行。FETCH NEXT是惟一受支持的提取选项非指定STATIC、KEYSET或DYNAMIC关键字，否则默认为FORWARD_ONLY。STATIC、KEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不同，STATIC、KEYSET和DYNAMICTransact-SQL游标支持FORWARD_ONLY。FAST_FORWARD和FORWARD_ONLY是互斥的；如果指定一个，则不能指定另一个。

l STATIC：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。

l KEYSET：指定当游标打开时，游标中行的成员资格和顺序已经固定。对行进行惟一标识的键集内置在tempdb内一个称为keyset的表中。对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交）在用户滚动游标时是可视的。其他用户进行的插入是不可视的（不能通过Transact-SQL服务器游标进行插入）。如果某行已删除，则对该行的提取操作将返回@@FETCH_STATUS值-2。从游标外更新键值类似于删除旧行后接着插入新行的操作。含有新值的行不可视，对含有旧值的行的提取操作将返回@@FETCH_STATUS值-2。如果通过指定WHERE CURRENT OF子句用游标完成更新，则新值可视。

l DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的行所做的所有数据的更改。行的数据值、顺序和成员在每次提取时都会更改。动态游标不支持ABSOLUTE提取选项。

l FAST_FORWARD：指明一个FORWARD_ONLY、READ_ONLY型游标。

l SCROLL_LOCKS：指定确保通过游标完成的定位更新或定位删除可以成功。将行读入游标以确保它们可用于以后的修改时，SQL Server会锁定这些行。如果还指定了FAST_FORWARD，则不能指定SCROLL_LOCKS。

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

l TYPE_WARNING：指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。

使用DECLARE CURSOR语句创建以下几种形式的游标。

（1）示例：创建一个名为“MyCursor”的标准游标。

USE 销售管理系统

DECLARE MyCursor CURSOR FOR

SELECT * FROM 操作员信息表

GO

（2）示例：创建一个名为“MyCursor_01”的只读游标。

USE 销售管理系统

DECLARE MyCursor_01 CURSOR FOR

SELECT * FROM 操作员信息表

FOR READ ONLY --只读游标

GO

（3）示例：创建一个名为“MyCursor_02”的更新游标。

USE 销售管理系统

DECLARE MyCursor_02 CURSOR FOR

SELECT 操作员编号,操作员姓名,操作员年龄 FROM 操作员信息表

FOR 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或STATIC选项声明了游标，那么OPEN将创建一个临时表以保留结果集。如果结果集中任意行的大小超过SQL Server表的最大行大小，OPEN将失败。如果使用KEYSET选项声明了游标，那么OPEN将创建一个临时表以保留键集。临时表存储在tempdb中。

首先声明一个名为MyCursor_001的游标，然后使用OPEN命令打开该游标。

操作步骤如下：

（1）在操作系统中选择“开始”→“所有程序”→“Microsoft SQL Server”→“查询分析器”命令，打开查询分析器。

（2）在查询分析器的工具栏中选择要连接的数据库，这里选择“销售管理系统”。

（3）在代码编辑区中编写如下代码。

SQL语句如下：

USE 销售管理系统

DECLARE MyCursor_001 CURSOR FOR --声明游标

SELECT 操作员编号,操作员姓名,操作员年龄 FROM 操作员信息表

WHERE 操作员编号 = 'CY20040604006'

OPEN MyCursor_001 --打开游标

GO
从游标中读取数据
当打开一个游标之后，就可以读取游标中的数据了。可以使用FETCH命令读取游标中的某一行数据。

语法：

FETCH

[ [ NEXT | PRIOR | FIRST | LAST

| ABSOLUTE { n | @nvar }

| RELATIVE { n | @nvar }

]

FROM

]

{ { [ GLOBAL ] cursor_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或@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必须为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]：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。

l @@FETCH_STATUS：返回上次执行FETCH命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH操作是否成功，决定如何进行下一步处理。@@FETCH_STATUS变量有3个不同的返回值，说明如下：

2 返回值为0：FETCH 语句成功。

2 返回值为-1：FETCH 语句失败或此行不在结果集中。

2 返回值为-2：被提取的行不存在。

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

通常游标取数的操作与WHILE循环紧密结合，下面将使用@@FETCH_STATUS控制在一个WHILE循环中的游标活动。

程序运行结果如图1所示。

图1 从游标中读取数据

SQL语句如下：

USE 销售管理系统 --引入数据库

DECLARE ReadCursor CURSOR FOR --声明一个游标

SELECT 操作员编号,操作员姓名,操作员性别,操作员住址

FROM 操作员信息表

OPEN ReadCursor --打开游标

FETCH NEXT FROM ReadCursor --执行取数操作

WHILE @@FETCH_STATUS=0 --检查@@FETCH_STATUS，以确定是否还可以继续取数

BEGIN

FETCH NEXT FROM ReadCursor

END
关闭游标
当游标使用完毕之后，使用CLOSE语句可以关闭游标，但不释放游标占用的系统资源。

语法：

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

参数说明：

l GLOBAL：指定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 ONLY

OPEN CloseCursor --打开游标

CLOSE CloseCursor --关闭游标

释放游标
当游标关闭之后，并没有在内存中释放所占用的系统资源，所以可以使用DEALLOCATE命令删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由SQL Server释放。

语法：

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

参数说明：

l cursor_name：已声明游标的名称。当全局和局部游标都以cursor_name作为它们的名称存在时，如果指定GLOBAL，则cursor_name引用全局游标，如果未指定GLOBAL，则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 --打开游标

Close FreeCursor --关闭游标

DEALLOCATE FreeCursor --释放游标

创建游标变量
在批处理或过程的正文中用DECLARE语句声明变量，并用SET或SELECT语句给其指派值。游标变量可通过该语句声明，并且可用在其他与游标相关的语句中。所有变量在声明后均初始化为NULL。

首先创建一个游标并打开该游标，之后创建一个游标变量并将游标的值（SELECT * FROM Jobs）赋给游标变量，并通过FETCH语句读取游标变量中的值，最后关闭并释放游标。

程序运行结果如图1所示。

图1 创建游标变量

SQL语句如下：

USE pubs

DECLARE MyCursor_001 Cursor FOR --创建游标

SELECT * FROM Jobs

OPEN MyCursor_001 --打开游标DECLARE @CursorVar Cursor --创建游标变量

SET @CursorVar = MyCursor_001 --为游标变量赋值

FETCH NEXT FROM @CursorVar --读取游标变量中的值

CLOSE MyCursor_001 --关闭游标

DEALLOCATE MyCursor_001 --释放游标

静态游标
静态游标的完整结果集在游标打开时建立在tempdb中。静态游标总是按照游标打开时的原样显示结果集。静态游标在滚动期间很少或根本检测不到变化，虽然它在tempdb中存储了整个游标，但消耗的资源很少。尽管动态游标使用tempdb的程度最低，在滚动期间它能够检测到所有变化，但消耗的资源也更多。键集驱动游标介于二者之间，它能检测到大部分的变化，但比动态游标消耗更少的资源。
动态游标
动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标可见。
只进游标
只进游标不支持滚动，它只支持游标从头到尾顺序提取。只在从数据库中提取出来后才能行检索。对所有由当前用户发出或由其他用户提交、并影响结果集中的行的INSERT、UPDATE和DELETE语句，其效果在这些行从游标中提取时是可见的。
键集驱动游标
打开游标时，键集驱动游标中的成员和行顺序是固定的。键集驱动游标由一套被称为键集的惟一标识符（键）控制。键由以惟一方式在结果集中标识行的列构成。键集是游标打开时来自所有适合SELECT语句的行中的一系列键值。键集驱动游标的键集在游标打开时建立在tempdb中。

对非键集列中的数据值所做的更改（由游标所有者更改或其他用户提交）在用户滚动游标时是可见的。在游标外对数据库所做的插入在游标内是不可见的，除非关闭并重新打开游标。
使用游标修改数据
本节主要介绍如何使用游标修改数据

在查询分析器中声明变量，并设置@id变量的值，然后声明一个游标并打开该游标，使用FETCH NEXT方法来获取游标的下一行数据，并将此数据赋值给变量，如果FETCH语句执行成功则判断当前游标所指定的操作员编号是否与变量@id相等，如果相等则更新数据，最后关闭游标并释放游标所占用的资源。

程序运行结果如图1所示。

将编号为“CY20061010001”的操作员年龄修改为“30”岁

图1 使用游标修改数据

SQL语句如下：

Declare @id char(20) /*声明变量*/

declare @ids char(20)

declare @names char(20)

set @id='CY20061010001' --为变量赋值

DECLARE authors_cursor CURSOR --声明游标

FOR SELECT 操作员编号,操作员姓名 FROM 操作员信息表

OPEN authors_cursor --打开游标

FETCH NEXT FROM authors_cursor --获取游标的下一行数据

into @ids,@names --使变量获得当前游标指定行的操作员编号和操作员姓名

WHILE @@FETCH_STATUS = 0 --FETCH语句执行成功

BEGIN

if @id=@ids --判断变量的值是否与游标指定的操作员编号相等

begin

--更新指定条件的操作员年龄

update 操作员信息表 set 操作员年龄=30 where 操作员编号 = @ids

end

FETCH NEXT FROM authors_cursor --获取游标的下一行

into @ids,@names --使变量获得当前游标指定行的操作员编号和操作员姓名

End

CLOSE authors_cursor --关闭游标

DEALLOCATE authors_cursor --释放游标

SELECT * FROM 操作员信息表 --重新选择操作员信息表

使用游标删除数据
Transact-SQL脚本、存储过程和触发器可以使用DELETE语句中的WHERE CURRENT OF子句删除它们当前所处的游标行。

语法：

Delete table_name

SET column_name1-{expression1|NULL(select_statement)}

[,column_name2={expression2|NULL(select_statement)}

WHERE CURRENT OF cursor_name

参数说明：

l table_name：用来指定UPDATE或DELETE的表名。

l column_name：用来指定UPDATE的列名。

l cursor_name：用来指定游标的名称。

下面介绍如何使用WHERE CURRENT OF方法删除数据

声明一个游标并打开，然后使用FETCH NEXT方法将游标指针下移一行，最后使用WHERE CURRENT OF方法删除指定条件的数据，然后关闭并释放游标所占用的系统资源。

SQL语句如下：

USE 销售管理系统 --引入数据库

DECLARE deletecursor CURSOR --声明游标

FOR SELECT * FROM 操作员信息表

WHERE 操作员编号 = 'CY20061010211'

OPEN deletecursor --打开游标

GO

FETCH NEXT FROM deletecursor --游标指针下移一行

DELETE 操作员信息表

WHERE CURRENT OF deletecursor --删除指定条件的数据

FETCH NEXT FROM deletecursor

GO

CLOSE deletecursor --关闭游标

DEALLOCATE deletecursor --释放游标

GO

下面介绍如何使用游标删除数据

在查询分析器中声明变量，并设置@age变量的值，然后声明一个游标并打开该游标，使用FETCH NEXT方法来获取游标的下一行数据，并将此数据赋值给变量，如果FETCH语句执行成功，则判断当前游标所指定的操作员年龄是否与变量@age相等，如果相等，则删除此数据，最后关闭游标并释放游标所占用的系统资源。

SQL语句如下：

USE 销售管理系统 --引入数据库

DECLARE @id char(20) /*声明变量*/

DECLARE @names char(20)

DECLARE @age int

SET @age = 30 --为变量赋值

DECLARE @ages int

DECLARE deletecursor Cursor For --声明游标

SELECT 操作员编号,操作员姓名,操作员年龄 FROM 操作员信息表

OPEN deletecursor --打开游标

FETCH NEXT FROM deletecursor --获取游标的下一行

--使变量获得当前游标指定行的操作员编号,操作员姓名，操作员年龄

into @id,@names,@ages

WHILE @@FETCH_STATUS = 0 --FETCH语句执行成功

BEGIN

if @age = @ages --判断变量的值是否与游标指定的操作员年龄相等

BEGIN

--删除指定条件的数据

DELETE 操作员信息表 WHERE 操作员年龄 = @ages

END

FETCH NEXT FROM deletecursor --获取游标的下一行

--使变量获得当前游标指定行的操作员编号,操作员姓名，操作员年龄

into @id,@names,@ages

END

CLOSE deletecursor --关闭游标

DEALLOCATE deletecursor --释放游标

将游标中的数据进行排序显示
在DECLARE CURSOR语句中，将ORDER BY子句添加到查询中使游标数据排序。

ORDERY BY子句语法：

ORDER BY <column name> [ ASC | DESC ]

[ ,...<last column name> [ ASC | DESC ]]

注意：与非游标的SELECT语句中的ORDER BY子句不同，只有在查询的SELECT子句中列出的供显示的列才能作为ORDER BY子句中的列出现（在非游标的SELECT语句中，表中任何在查询的FROM子句中列出的列都可能出现在ORDER BY子句中，即使列没有在SELECT子句中）。

下面将游标中的数据进行排序显示

在声明的mycursor游标中选择指定的数据列，并将库存数量进行降序显示。

程序运行结果如图1所示。

SQL语句如下：

USE 销售管理系统

DECLARE mycursor CURSOR

FOR SELECT 商品编号,商品名称,库存数量,库存金额

FROM 库存表

ORDER BY 库存数量 DESC

OPEN mycursor

FETCH NEXT FROM mycursor

WHILE @@FETCH_STATUS = 0

FETCH NEXT FROM mycursor

CLOSE mycursor

DEALLOCATE mycursor

使用FETCH语句将数据值存入变量
本例将“操作员信息表”中“操作员性别”为“男”的数据存储在变量中，然后利用PRINT语句将变量中的数据一一输出。

程序运行结果如图1所示。

图1 使用FETCH语句将数据值存入变量

SQL语句如下：

USE 销售管理系统

GO

DECLARE @names char(20),@age char(20),@sex char(20) --声明变量

DECLARE mycursor Cursor FOR --声明游标

SELECT 操作员姓名,操作员年龄,操作员性别 FROM 操作员信息表

WHERE 操作员性别 = '男'

ORDER BY 操作员编号 --按操作员编号进行排序

OPEN mycursor --打开游标

PRINT '操作员姓名 '+'操作员年龄 '+'操作员性别 ' --使用PRINT语句输出字符串

FETCH NEXT FROM mycursor --游标指针下移一行

INTO @names,@age,@sex

WHILE @@FETCH_STATUS = 0 --FETCH语句执行成功

BEGIN

PRINT + @names + @age + @sex --使用PRINT语句将数据输出

FETCH NEXT FROM mycursor

INTO @names,@age,@sex

END

CLOSE mycursor --关闭游标

DEALLOCATE mycursor --释放游标

GO

在游标中包含计算列
本实例介绍如何在游标中包含计算列。

声明一个游标，在SELECT语句中选择数据表中的某些数据字段，并将数量字段与金额字段中的数据进行相乘计算，然后通过FETCH NEXT语句每次向下移动游标指针，将当前指定的数据进行计算，最后关闭并释放游标。

程序运行结果如图1所示。

图1 计算商品销售总金额

SQL语句如下：

DECLARE cur CURSOR --声明游标

FOR SELECT 商品编号,商品名称,数量,金额,

数量*金额 as 销售总额

FROM 销售表

OPEN cur库 --打开游标

FETCH NEXT FROM cur

WHILE @@FETCH_STATUS = 0 --FETCH语句执行成功

BEGIN

FETCH NEXT FROM cur

END

CLOSE cur --关闭游标

DEALLOCATE cur --释放游标

SELECT * FROM 销售表 --选择销售表