Sqlserver 关于游标

对于sql来说查询的思维方式的面向集合
对于游标来说：思维方式是面向行的

性能上：游标会吃更多内存，减少可见的并发，锁定资源等

当穷尽了while循环，临时表，表变量，自建函数，或其他方式仍然无法实现某些查询的时候，可以考虑使用游标

游标的生命周期由5部分组成：

游标可以很简单，也可以很复杂，取决于游标的参数

游标可以理解为定义在数据集上的指针，可以控制这个指针遍历数据集，或者仅仅指向特定的行，所以游标是定义在以select开始的数据集上的

游标的定义：

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

游标分为：游标类型和游标变量，
游标变量遵循T-sql变量的定义方法，支持两种方式赋值，定义时赋值和先定义后赋值
如果定义局部游标：在游标前加 @
如果定义全局的游标，只支持在定义的时候直接赋值，并且不能在游标名称前面加 @
例如：

--定义的全局游标,游标变量没有@,全局游标定义需要直接进行赋值
DECLARE cur_test CURSOR FOR
SELECT * FROM AuthToken AS at


--定义的局部游标，需要使用@声明变量
DECLARE @cur_test2 CURSOR 
--先声明变量，然后再进行赋值
SET @cur_test2 = CURSOR FOR
SELECT * FROM AuthToken AS at

游标的参数：
Local和global二选一

Local：意味着游标的生存周期只在批处理或者函数或者存储过程中可见，
Global：意味着游标对于特定连接作为上下文，全局内有效

全局游标：在批处理后依然有效，
局部游标：在批处理结束后被隐式释放，无法在其他批处理中调用

--定义的全局游标,游标变量没有@,全局游标定义需要直接进行赋值
DECLARE cur_test CURSOR GLOBAL FOR
SELECT * FROM AuthToken AS at

DECLARE cur_test CURSOR LOCAL FOR
SELECT * FROM AuthToken AS at

如果不指定，默认为global

FORWARD_ONLY 和SCROLL二选一

forward_only:意味着游标只能从数据集开始向数据集结束的方向读取，Fetch next是唯一选项，
scroll支持游标在定义的数据集中向任何方向，或者任何位置移动

例如：

--不加参数,默认为 forward_only
DECLARE Test_Cursor CURSOR FOR
SELECT * FROM AuthToken AS at

-- 加scroll参数,支持游标指针向数据集的任意方向移动，
DECLARE Test2_Cursor CURSOR SCROLL FOR
SELECT * FROM AuthToken AS at

-- 加FORWARD_ONLY参数，支持游标指针 只能从 数据集开始方向向结束方向移动，
DECLARE Test3_Cursor CURSOR FORWARD_ONLY FOR
SELECT * FROM AuthToken AS at

OPEN Test_Cursor
OPEN Test2_Cursor
OPEN Test3_Cursor

--只支持从数据集开始方向向结束方向移动
FETCH NEXT FROM Test_Cursor
FETCH NEXT FROM Test3_Cursor
--SCROLL 支持向任意方向移动
FETCH NEXT FROM Test2_Cursor
FETCH  LAST FROM Test2_Cursor

static，keyset, dynamic 和 fast_forward 四选一

这四个参数是游标所在数据集所反应的表内数据和游标读取出的数据的关系

static：意味着当游标被建立时，将会创建 for后面的 select语句所包含数据集的副本存入 tempdb数据库中，任何对于底层表内数据的更改都不会影响到游标的内容

dynamic：和static相反，当底层数据库表内内容更改时，游标的内容也随之改变，下一次 fetch中，数据内容会随之改变

keyset：是上面两种的折中方案：将游标所在结果集的唯一能确定每一行的主键存入tempdb，当结果集中任何行改变或者删除时，@@Fetch_status 会为 -2，keyset无法探测新加入的数据

fast_forward:是forward_only的优化版本，forward_only执行的是静态计划，
而Fast_forward是根据情况进行选择采用动态计划还是静态计划，

Read_only ,Scroll_locks,Optimistic三选一
Read_only：意味着声明的游标只能读取数据，游标不能做任何更新操作
scroll_locks:将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功

Optimistic：不锁定任何数据，当需要在游标中更新数据时，如果底层表数据更新，则游标内数据更新不成功，如果底层表数据未更新，则游标内表数据可以更新

打开游标：

当游标定义完，需要打开后才能使用
Open test_cursor

注意：当全局游标和局部游标变量重名时，默认会打开局部变量游标

3 使用游标：

游标的使用分为两部分：
一部分是操作游标在数据集内的指向，
一部分是将游标所指向的行的部分或全部内容进行操作

支持6种移动选项
到第一行：first
最后一行：last
下一行：next
上一行：prior
直接跳到某行：absolute(n)
相对于目前跳几行(relative(n))

对于未指定scroll选项的游标来说，只支持next取值

例如：

DEALLOCATE test_cursor
--定义一个全局游标，并且支持向任意方向移动
DECLARE test_cursor CURSOR SCROLL FOR
SELECT c.Nickname FROM dbo.Customer AS c
--定义好之后需要首先打开游标
OPEN test_cursor

DECLARE @a NVARCHAR(50)

--使用游标：取第一行数据到@a
FETCH FIRST FROM test_cursor INTO @a
PRINT @a

--取当前位置的第n行数据(取绝对位置)
FETCH ABSOLUTE 3 FROM test_cursor INTO @a
PRINT @a

--取相对位置
FETCH RELATIVE 3 FROM test_cursor INTO @a
PRINT @a

--取当前位置的下一行数据
FETCH NEXT FROM test_cursor INTO @a
PRINT @a
--取最后一条数据
FETCH LAST FROM        test_cursor INTO @a
PRINT @a
--取游标的当前位置的上一行数据
FETCH PRIOR FROM test_cursor INTO @a
PRINT @a

--游标使用完之后需要关闭游标
CLOSE test_cursor

--如果不再需要游标，可以进行删除
DEALLOCATE test_cursor

游标经常会和全局变量 @@Fetch_status 与while循环来共同使用，以便达到遍历游标所在数据集的目的

例如：

DECLARE Test_Cursor CURSOR SCROLL FOR
SELECT c.Id,c.Nickname FROM Customer AS c

OPEN Test_Cursor
DECLARE @i INT
DECLARE @name NVARCHAR(20)


WHILE @@FETCH_status = 0
BEGIN
    PRINT @i
    PRINT @name
    FETCH NEXT FROM Test_Cursor INTO @i,@name
END
CLOSE Test_Cursor
DEALLOCATE Test_Cursor

使用游标注意点：

1 游标能不用就尽量不要用游标
2 用完之后一定要关闭和释放
3 尽量不要在大量数据上定义游标
4尽量不要使用游标上更新数据
5 尽量不要使用insensitive，static，keyset这些参数定义游标
6 如果可以，尽量使用fast_forward关键字定义游标
7如果只对数据进行读取，当读取只用到Fetch next选项，则最好使用 forward_only参数

posted @ 2016-07-28 14:22 那就让我这样吧阅读(487) 评论(0) 编辑收藏举报

刷新页面返回顶部

Sqlserver 关于游标

公告