SQL SERVER CURSOR游标的使用(转载)

一：认识游标

游标(Cursor)它使用户可逐行访问由SQL Server返回的结果集。

使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。

用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。

游标机制允许用户在SQL server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

二：游标的基本形式

声明游标：形式1
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
形式2
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。
如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。
SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。
如果忽略该选项，则游标只能向前滚动（next）。
Select_statement指明SQL语句建立的结果集。Transact SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。
READ ONLY指明在游标结果集中不允许进行数据修改。
UPDATE关键字指明游标的结果集可以修改。
OF column_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。
LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。
GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。
FORWARD_ONLY指明游标只能向前滚动。
STATIC的游标与INSENSITIVE的游标是相同的。
KEYSET指明选取的行的顺序。SQL Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。
因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。
DYNAMIC指明游标将反映所有对结果集的修改。
SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。
OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。
注意：
· 如果在SELECT语句中使用了DISTINCT、UNION、GROUP BY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。
· 如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。
· 如果SELECT语句包含了ORDER BY，而被ORDER BY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。
如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用SQL ANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。
打开游标
打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
GLOBAL指明一个全局游标。
Cursor_name是被打开的游标的名称。
Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。
在游标被打开之后，系统变量@@cursor_rows可以用来检测结果集的行数。
@@cursor_rows为负数时，表示游标正在被异步迁移，其绝对值（如果@@cursor_rows为－５，则绝对值为５）为当前结果集的行数。
异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。
从游标中取值
在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。
如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。
对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO @variable_name ][,……n]]
NEXT指明从当前行的下一行取值。
PRIOR指明从当前行的前一行取值。
FIRST是结果集的第一行。
LAST是结果集的最后一行。
ABSOLUTE n表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。
RELATIVE n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。
INTO @cursor_variable_name表示游标列值存储的地方的变量列表。
该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。
变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。
每一次FETCH的执行都存储在系统变量@@fetch_status中。
如果FETCH成功，则@@fetch_status被设置成0。@@fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。
@@fetch_status可以用来构造游标处理的循环。
关闭游标
CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：
CLOSE [GLOBAL] cursor_name | cursor_variable_name
释放游标
游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name

三：游标的基本使用模板

declare ：

declare  游标名[scroll]  cursor  for select语句[for update [of列表名]]

定义一个游标，使之对应一个select语句

for update任选项，表示该游标可用于对当前行的修改与删除

open

打开一个游标，执行游标对应的查询，结果集合为该游标的活动集

open  游标名

fetch

在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中

fetch [next | prior | first | last | current | relative n | absolute m] 游标名into  [变量表]

close

关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句

close  游标名

deallocate

删除游标，以后不能再对该游标执行open语句

deallocate 游标名

@@FETCH_STATUS

返回被FETCH 语句执行的最后游标的状态.

0 fetch语句成功

-1 fetch语句失败

-2 被提取的行不存在

例：DECLARE Employee_Cursor CURSOR FORSELECT EmployeeID, Title

FROM AdventureWorks.HumanResources.Employee;

OPEN Employee_Cursor;FETCH NEXT FROM Employee_Cursor;

WHILE @@FETCH_STATUS = 0   

BEGIN      

--//TO DO...

FETCH NEXT FROM Employee_Cursor;   

END;

CLOSE Employee_Cursor;DEALLOCATE Employee_Cursor;

GO

四：游标性能问题

最好的改进游标性能的技术就是：能避免时就避免使用游标，尽可能用对应的语句完成相同的功能（一般情况下，考虑得当效率能大大提升）。

SQL Server是关系数据库，其处理数据集比处理单行好得多，单独行的访问根本不适合关系DBMS。
若有时无法避免使用游标，则可以用如下技巧来优化游标的性能。
(1). 除非必要否则不要使用static/insensitive游标。打开static游标会造成所有的行都被拷贝到临时表。
这正是为什么它对变化不敏感的原因——它实际上是指向临时数据库表中的一个备份。
很自然，结果集越大，声明其上的static游标就会引起越多的临时数据库的资源争夺问题。
(2). 除非必要否则不要使用keyset游标。和static游标一样，打开keyset游标会创建临时表。
虽然这个表只包括基本表的一个关键字列(除非不存在唯一关键字)，但是当处理大结果集时还是会相当大的。
(3). 当处理单向的只读结果集时，使用fast_forward代替forward_only。使
用fast_forward定义一个forward_only，则read_only游标具有一定的内部性能优化。
(4). 使用read_only关键字定义只读游标。这样可以防止意外的修改，并且让服务器了解游标移动时不会修改行。
(5). 小心事务处理中通过游标进行的大量行修改。根据事务隔离级别，这些行在事务完成或回滚前会保持锁定，这可能造成服务器上的资源争夺。
(6). 小心动态光标的修改，尤其是建在非唯一聚集索引键的表上的游标，因为他们会造成“Halloween”问题——对同一行或同一行的重复的错误的修改。
因为SQL Server在内部会把某行的关键字修改成一个已经存在的值，并强迫服务器追加下标，使它以后可以再结果集中移动。
当从结果集的剩余项中存取时，又会遇到那一行，然后程序会重复，结果造成死循环.
(7). 对于大结果集要考虑使用异步游标，尽可能地把控制权交给调用者。当返回相当大的结果集到可移动的表格时，异步游标特别有用.