SqlServer2008实例50游标

最新推荐文章于 2024-04-18 16:47:11 发布

ngbshzhn

最新推荐文章于 2024-04-18 16:47:11 发布

阅读量297

点赞数

分类专栏： # SqlServer2008实例

本文链接：https://blog.youkuaiyun.com/ngbshzhn/article/details/107905076

版权

SqlServer2008实例专栏收录该内容

142 篇文章

订阅专栏

有编程背景的查询编写者通常更习惯于使用游标，而不是基于集合的方案来获取或更新行。例如，程序员可能会决定一次遍历一行，一条一条地更新行，而不是用一个操作来更新整个行集。但是，游标会耗尽SQL Server实例的内存、减少并发性、减少网络带宽、锁定资源，并且经常会需要比基于集合的方案更多的代码。T-SQL是基于集合的语言，也就是说它擅长操作和获取行集，而不是一行一行地处理。
然而，应用程序或业务需求可能需要T-SQL游标提供的一行一行的处理方式。通常，应该在其他诸如WHILE循环、子查询、临时表或表变量等其他行级别的处理方法都不管用的时候才应该考虑使用游标。
T-SQL游标的大致生存周期如下所示。

游标通过返回有效结果集的SQL语句来定义。
然后游标被填充（打开）。
一旦打开，行就能从游标中获取，一次一个或一组。行还能向前或向后移动，这取决于原始游标的定义。
根据游标类型，数据能随着行的滚动进行修改，或读取并用在其他操作中。
最后，游标使用后应该显式关闭并从内存中释放。

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 ] ] ]

有一些选项会影响游标数据是否能更新以及你是否可以向后向前移动游标中填充的行。

游标选项

参数	描述
LOCAL或GLOBAL	如果选择了LOCAL，游标只在SQL批处理、触发器或存储过程的范围中可用。如果选择了GLOBAL，游标对连接自身可用（例如，一个执行创建游标的存储过程的连接能使用通过执行存储过程创建的游标）
FORWARD ONLY或SCROLL	FORWARO ONLY选项只允许我们从游标第一行开始向前移动。而SCROLL允许我们使用所有的FETCH选项（FIRST、LAST、NEXT、PRIOR、ABSOLUTE和RELATIVE）在结果集中前后移动。如果考虑性能，最好使用FORWARD_ ONLY——因为这种游标类型的负载比SCROLL低
STATIC或KEYSET或DYNAMLC或 FASTFORWARD	如果指定了STATIC．则游标数据的快照会保存在DYNAMIC或FAST FORWARD tempdb数据库中，任何对原始数据源的修改不会影响游标数据。KEYSET允许我们看到游标外部对行的修改，但是看不到符合游标SELECT查询的插入或游标打开之后的删除．DYNAMIC允许我们在游标打开对看到基础数据源的更新、插入和删除。FAST_FORWARD定义两种行为：设置游标为只读和只向前状态（通常这是性能最好的游标选项．但是也最不灵活）。如果面临性能问题．并且你的功能不复杂．则使用这个选项
READ_ONLY或 SCROLL LOCKS或 OPTIMISTIC	READ ONLY选项意味着不能通过游标进行更新。如粜考虑性能和并发，则使用这个选项．SCROLL_LOCKs在行上加锁，这样就能保证在游标结束后进行更新和删除。OPTIMISTIC选项在已更新或已删除的行上不加锁，只有当上次数据读取之后游标外部没有进行数据更新时才会使修改有效
TYPE WARNINGS	如果指定了TYPE WARNINGS，且游标显式地从一种类型转换成了另一种类型，那么会有警告被发送到客户端

select_statement参数是用来在游标中定义数据的查询。避免使用查询来返回很多用不到的列和行．因为游标打开的时候会放在内存中。UPDATE [ OF column_name [ ,...n ] ]用来指定那些允许游标更新的列。

一旦游标使用DECLARE CURSOR来声明，下一步就足使用OPEN命令打开它并填充数据。语法如下：

OPEN { [ GLOBAL ] cursor_name }

游标能在局部（默认）或全局打开。一旦打开了，你就可以使用FETCH命令在游标中切换行。FETCH NEXT的语法如下：

FETCH   
          [ [ NEXT | PRIOR | FIRST | LAST   
                    | ABSOLUTE { n | @nvar }   
                    | RELATIVE { n | @nvar }   
               ]   
               FROM   
          ]   
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }   
[ INTO @variable_name [ ,...n ] ]

FETCH提供了几个选项用来在游标中切换行，通过把每个游标定义的列的结果填充到局部变最来实现。
@@FETCH_STATUS函数用在FETCH操作后，用来检测FETCH状态，返回0表示成功，-1表示不成功，-2表示丢失。
一旦用完了打开的游标，就执行CLOSE命令从内存中释放结果集。语法如下：

CLOSE { [GLOBAL ] cursor_name }

现在，仍然可以重新打开游标。然而，如果已经用完了，应该使用DEALLOCATE命令移除内部系统对游标的引用。这样会释放游标使用的所有资源。例如，如果游标的卷锁被表引用，这些锁在OEALLOCATE后释放。语法如下：

DEALLOCATE { [GLOBAL ] cursor_name }

示例：创建和使用T-SQL游标

USE AdventureWorks
GO

--不在结果集中显示行数
SET NOCOUNT ON
DECLARE @session_id smallint

--声明游标
DECLARE session_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT session_id
    FROM sys.dm_exec_requests
    WHERE status IN ('runnable','sleeping','running')
    
--打开游标
OPEN session_cursor

--从游标中一次检索一行
FETCH NEXT
FROM session_cursor
INTO @session_id

--当存在游标就持续检索行
WHILE @@FETCH_STATUS=0
BEGIN
	PRINT 'Spid #: ' +STR(@session_id)
	EXEC ('DBCC OUTPUTBUFFER (' + @session_id + ')')
	
	--提取下一行
	FETCH NEXT
	FROM session_cursor
	INTO @session_id
END

--关闭游标
CLOSE session_cursor

--释放游标
DEALLOCATE session_cursor