sqlserver游标的知识
⼀：认识游标
游标是SQL Server的⼀种数据访问机制，它允许⽤户访问单独的数据⾏。⽤户可以对每⼀⾏进⾏单独的处理，从⽽降低系统开销和潜在的阻隔情况，⽤户也可以使⽤这些数据⽣成的SQL代码并⽴即执⾏或输出。
1.游标的概念
游标是⼀种处理数据的⽅法，主要⽤于存储过程，触发器和 T_SQL脚本中，它们使结果集的内容可⽤于其它T_SQL 语句。在查看或处理结果集中向前或向后浏览数据的功能。类似与C语⾔中的指针，它可以指向结果集中的任意位置，当要对结果集进⾏逐条单独处理时，必须声明⼀个指向该结果集中的游标变量。
SQL Server 中的数据操作结果都是⾯向集合的，并没有⼀种描述表中单⼀记录的表达形式，除⾮使⽤WHERE⼦句限定查询结果，使⽤游标可以提供这种功能，并且游标的使⽤和操作过程更加灵活、⾼效。
2.游标的优点
SELECT 语句返回的是⼀个结果集，但有时候应⽤程序并不总是能对整个结果集进⾏有效地处理，游标便提供了这样⼀种机制，它能从包括多条记录的结果集中每次提取⼀条记录，游标总是与⼀跳SQL选择语句相关联，由结果集和指向特定记录的游标位置组成。使⽤游标具有⼀下优点：
(1).允许程序对由SELECT查询语句返回的⾏集中的每⼀次执⾏相同或不同的操作，⽽不是对整个集合执⾏同⼀个操作。
(2).提供对基于游标位置中的⾏进⾏删除和更新的能⼒。
(3).游标作为数据库管理系统和应⽤程序设计之间的桥梁，将两种处理⽅式连接起来。
3.游标的分类
SQL Server⽀持3中游标实现：
(1).Transact_SQL游标
基于DECLARE CURSOR 语法，主要⽤于T_SQL脚本，存储过程和触发器。T_SQL游标在服务器上实现，并由从客户端发送到服务器的T_SQL语句管理，它们还可能包含在批处理，存储过程或触发器中。
(2).应⽤程序编程接⼝(API)服务器游标
⽀持OLE DB和ODBC中的API游标函数，API服务器游标在服务器上实现。每次客户端应⽤程序调⽤API游标函数时，SQL Server Native Client OLE DB访问接⼝或ODBC驱动程序会把请求传输到服务器，以便对API服务器游标进⾏操作。
(3).客户端游标
由SQL Server Native Client ODBC驱动程序和实现ADO API的DLL在内部实现。客户端游标通过在客户端⾼速缓存所有结果集中的⾏来实现。每次客户端应⽤程序调⽤API游标函数时，SQL Server Native Client ODBC驱动程序或ADO DLL会对客户端上告诉缓存的结果集中的⾏执⾏游标操作。
由于T_SQL游标和服务器游标都在服务器上实现，所以它们统称为服务器游标。
ODBC和ADO定义了 Microsoft SQL Server ⽀持的4种游标类型，这样就可以为T_SQL游标指定4种游标类型。SQL Server⽀持的4种API服务器游标的类型是：
(i).只进游标
只进游标不⽀持滚动，它只⽀持游标从头到尾顺序提取。⾏只在从数据库中提取出来后才能检索。对
所有⼜当前⽤户发出或⼜其它⽤户提交、并影响结果集中的⾏的INSERT，UPDATE和DELETE语句，其效果在这些⾏从游标中提取是可见的。
由于游标⽆法向后滚动，则在提取⾏后对数据库中的⾏进⾏的⼤多数更改通过游标均不可见。当值⽤于确定所修改的结果集(例如更新聚集索引涵盖的列)中⾏的位置时，修改后的值通过游标可见。
(ii).静态游标
SQL Server静态游标始终是只读的。其完整结果集在打开游标时建⽴在tempdb中，静态游标总是按照打开游标时的原样显⽰结果集。
游标不反映在数据库中所做的任何影响结果集成员⾝份的更改，也不反映对组合成结果集的⾏的列值所做的更改，静态游标不会显⽰打开游标以后在数据库中新插⼊的⾏，即使这些⾏符合游标SELECT语句的搜索条件。如果组成结果集的⾏被其它⽤户更新，则新的数据值不会显⽰在静态游标中。静态游标会显⽰打开游标以后从数据中删除的⾏。静态游标中不反UPDATE、INSERT或者DELETE操作（除⾮关闭游标然后重新打开），甚⾄不反映使⽤打开游标的同⼀连接所做的修改。
(iii).由键驱动的游标
该游标中各⾏的成员⾝份和顺序是固定的。由键集驱动的游标由⼀组唯⼀标识符(键)控制，这组键成
为键集。键是根据以唯⼀⽅式标识结果集各⾏的⼀组列⽣成的，键集是打开游标时来⾃符合SELECT语句要求的所有⾏中的⼀组键值。由键集驱动的游标对应的键集是打开游标时在tempdb中⽣成的。
(IV).动态游标
动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。结果集中的⾏数据值、顺序和成员在每次提取时都会改变。所有⽤户做的全部UPDATE、INSERT和DELETE语句均通过游标可见。如果使⽤API函数（如SQLSePos）或T_SQL WHERE CURRENT OF ⼦句通过游标进⾏更新，它们将⽴即可见。在游标外部所做的更新直到提交时才可见，除⾮将游标的事物隔离级别设为未提交读。
⼆：游标的基本操作
1.声明游标
游标主要包括游标结果集和游标位置两部分，游标结果集是定义游标的SELECT语句返回的⾏集合，游标位置则是指向这个结果集中的某⼀⾏的指针。
使⽤游标之前，要声明游标，SQL Server中声明使⽤DECLARE CURSOR语句，声明游标包括定义游标的滚动⾏为和⽤户⽣成游标所操作的结果集的查询，其语法格式如下：
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] ] ]
cursor_name：是所定义的T_SQL 服务器游标的名称。
LOCAL：对于在其中创建批处理、存储过程或触发器来说，该游标的作⽤域是局部的。
GLOBAL：指定该游标的作⽤域是全局的
FORWARD_ONLY：指定游标只能从第⼀⾏滚动到最后⼀⾏。FETCH NEXT是唯⼀⽀持的提取选项，
如果在指定FORWARD_ONLY时不指定STATIC，KEYSET和DYNAMIC关键字，则游标作为DYNAMIC游标进⾏操作，如果FORWARD_ONLY和SCROLL均为指定，则除⾮指定STATIC，KEYSET和DYNAMIC关键字，否则默认为FORWARD_ONLY。STATIC，KEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不
同，STATIC，KEYSET和DYNAMIC T_SQL游标⽀持FORWARD_ONLY。
STATIC：定义⼀个游标，以创建将⼜该游标使⽤的数据临时复本，对游标的所有请求都从tempdb中的这以临时表中不得到应答；因此，在对该游标进⾏提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。
KEYSET：指定当游标打开时，游标重的⾏的成员⾝份和顺序已经固定。对⾏进⾏唯⼀标识的键值内置在tempdb内⼀个称为keyset的表中。
DYNAMIC：定义⼀个游标，以反映在滚动游标时对结果集内的各⾏所做的所有数据更改。⾏的数据值、顺序和成员⾝份在每次提取时都会更改，动态游标不⽀持ABSOLUTE提取选项。
FAST_FORWARD：指定启动了性能优化的FORWARD_ONLY、READ_ONLY游标。如果指定了SCROLL或
FOR_UPDATE，则不能指定FAST_FORWARD。
SCROLL_LOCKS：指定通过游标进⾏的定位更新或删除⼀定会成功。将⾏读⼊游标时SQL Server将锁定这些⾏，以确保随后可对它们进⾏修改，如果还指定了FAST_FORWARD或STATIC，则不能指定SCROLL_LOCKS。OPTIMISTIC：指定如果⾏⾃读⼊游标以来已得到更新，则通过游标进⾏的定位更新或定位删除不成功。当将⾏读⼊游标时，SQL Server不锁定⾏，它改⽤timestamp列值⽐较结果来确定⾏读⼊游标后是否发⽣了修改，如果表不包含timestamp列，它改⽤校验和值进⾏确定，如果以修改该⾏，则尝试进⾏的定位更新或删除将失败，如果还指定了FAST_FORWARD，则不能指定OPTIMISTIC。
TYPE_WARNING：指定游标从所请求的类型隐式转换为另⼀种类型时，向客户端发送警告消息。
select_statement：是定义游标结果集中的标准SELECT语句。
【例】声明名称为cursor_fruit的游标
USE sample_db;
GO
DECLARE cursor_fruit CURSOR FOR
SELECT f_name,f_price FROM fruits;
2.打开游标
在使⽤游标之前必须先打开游标，打开游标的语法如下：
OPEN [ GLOBAL ] cursor_name | cursor_variable_name;
GLOBAL：指定cursor_name是全局游标。
cursor_name：已声明的游标的名称。如果全局游标和局部游标都使⽤cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标，否则cursor_name指的是局部游标。
cursor_variable_name：游标变量的名称。
【例】打开上例声明的名称为cursor_fruit的游标
USE sample_db;
GO
OPEN cursor_fruit;
3.读取游标中的数据
打开游标之后就可以读取游标中的数据了，FETCH命令可以读取游标中的某⼀⾏数据。FETCH的语法如下：ETCH
sql触发器的使用
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [GLOBAL ] cursor_name } | @cursor_variable_name}
[ INTO @variable_name [ ,...n ] ]
NEXT：紧跟当前⾏返回结果⾏，并且当前⾏递增为返回⾏，如果FETCH NEXT为对游标的第⼀次提取操作，则返回结果集中的第⼀⾏。NEXT为默认的游标提取选项。
PRIOR：返回紧邻当前⾏前⾯的结果⾏，并且当前⾏递减为返回⾏，如果FETCH PRIOR为对游标的第⼀次提取操作，则没有⾏返回并且游标置于第⼀⾏之前。
FIRST：返回游标中的第⼀⾏并将其作为当前⾏。
LAST：返回游标中的最后⼀⾏并将其作为当前⾏。
ABSOLUTE { n | @nvar }：如果n或@nvar为正，则返回从游标头开始向后n⾏的第n⾏，并将返回⾏变成新的当前⾏。如果n或@nvar为负，则返回从游标末尾开始向前的n⾏的第n⾏，并将返回⾏变成新的当前⾏。如果n或@nvar为0，则不返回⾏。n必须是整数常量，并且@nvar的数据类型必须为int、tinyint或smallint.
RELATIVE { n | @nvar }：如果n或@nvar为正，则返回从当前⾏开始向后的第n⾏。如果n或@nvar为负，则返回从当
前⾏开始向前的第n⾏。如果n或@nvar为0，则返回当前⾏，对游标第⼀次提取时，如果在将n或@nvar设置为负数或0的情况下指定FETCH RELATIVE，则不返回⾏，n必须是整数常量，@nvar的数据类型必须是int、tinyint或smallint.
GLOBAL：指定cursor_name是全局游标。
cursor_name：已声明的游标的名称。如果全局游标和局部游标都使⽤cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标，否则cursor_name指的是局部游标。
@cursor_variable_name：游标变量名，引⽤要从中进⾏提取操作的打开的游标。
INTO @variable_name [ ,...n ]：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型相匹配，或是结果集列数据类型所⽀持的隐⼠转换。变量的数⽬必须与游标选择列表中的列数⼀致。
【例】使⽤名称为cursor_fruit的光标，检索fruits表中的记录，输⼊如下：
USE sample_db;
GO
FETCH NEXT FROM cursor_fruit
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM cursor_fruit
END;
4.关闭游标
SQL Server 在打开游标之后，服务器会专门为游标开辟⼀定的内存空间存放游标操作的数据结果集，同时游标的使⽤也会根据具体情况对某些数据进⾏封锁。所以在不使⽤游标的时候，可以将其关闭，以释放游标所占⽤的服务器资源，关闭游标使⽤CLOSE语句。语法格式如下：
CLOSE [ GLOBAL ] cursor_name | cursor_variable_name
【例】关闭名称为cursor_fruit的游标
1CLOSE cursor_fruit;
5.释放游标
游标操作的结果集空间虽然被释放了，但是游标本⾝也会占⽤⼀定的计算集资源，所以使⽤完游标之后，为了收回被游标占⽤的资源，应该将游标释放。释放游标使⽤DEALLOCATE语句，语法格式如下：
DEALLOCATE [GLOBAL] cursor_name | @ccursor_variable_name
@ccursor_variable_name：游标变量的名称，@ccursor_variable_name必须为cursor类型。
DEALLOCATE @ccursor_variable_name 语句只删除对游标变量名称的引⽤，直到批处理、存储过程或触发器结束时变量离开作⽤域，才释放变量。
【例】使⽤DEALLOCATE语句释放名称为cursor_fruit的变量，输⼊如下：
DEALLOCATE cursor_fruit;
三：游标的运⽤
1.使⽤游标变量
声明变量⽤DECLARE，为变量赋值可以⽤set或SELECT语句，对于游标变量的声明和赋值，其操作基本相同。在具体使⽤时，⾸先要创建⼀个游标，将其打开后，将游标的值赋给游标变量，并通过FETCH语句从游标变量中读取值，最后关闭释放游标。
【例】声明名称为@varCursor的游标变量，输⼊如下：
DECLARE @varCursor Cursor --声明游标变量
DECLARE cursor_fruit CURSOR FOR --创建游标
SELECT f_name,f_price FROM fruits;
OPEN cursor_fruit --打开游标
SET @varCursor=cursor_fruit --为游标变量赋值
FETCH NEXT FROM @varCursor --从游标变量中读取值
WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执⾏成功
BEGIN
FETCH NEXT FROM @varCursor --读取游标变量中的数据
END
CLOSE @varCursor --关闭游标
DEALLOCATE @varCursor; --释放游标
2.⽤游标为变量赋值
在游标的操作过程中，可以使⽤FETCH语句将数据值存⼊变量，这些保持表中列值的变量可以在后⾯的程序中使⽤。
【例】创建游标cursor_variable，将fruits表中的记录f_name,f_price值赋给变量@fruitName和@fruitPrice，并打印输出。
3.⽤ORDER BY ⼦句改变游标中的执⾏顺序
游标是⼀个查询结果集，那么能不能对结果进⾏排序呢？答案是否定的。与基本的SELECT语句中的排序⽅法相同，ORDER BY⼦句添加到查询中可以对游标查询的结果排序。
注意：只有出现在游标中的SELECT语句中的列才能作为ORDER BY ⼦句的排序列，⽽对与⾮游标的SELECT语句中，表中任何列都可以作为ORDER BY 的排序列，即使该列没有出现在SELECT语句的查询结果列中。
【例】声明名称为cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输⼊语句如下：
4.⽤游标修改数据
发表评论