SQLServer游标
SQLServer游标什么是游标结果集，结果集就是select查询之后返回的所有行数据的集合。 游标则是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。 一般复杂的存储过程，都会有游标的出现，他的用处主要有：
游标的分类根据游标检测结果集变化的能力和消耗资源的情况不同，SQLServer支持的API服务器游标分为一下4种：
静态游标在滚动时检测不到表数据变化，但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化，但消耗的资源却较多。键集驱动游标则处于他们中间，所以根据需求建立适合自己的游标，避免资源浪费。。 游标的生命周期游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。 ?1.声明游标，语法
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 ] ] ] ?
参数说明：
2.声明一个动态游标 declare orderNum_02_cursor cursor scroll for select OrderId from bigorder where orderNum='ZEORD003402' 3.打开游标 --打开游标语法 open [ Global ] cursor_name | cursor_variable_name cursor_name:游标名，cursor_variable_name：游标变量名称，该变量引用了一个游标。 --打开游标 open orderNum_02_cursor 4.提取数据
--提取游标语法 Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ] [Global] cursor_name [into @variable_name[,....]]
参数说明：
例子：
--提取数据 fetch first from orderNum_02_cursor fetch relative 3 from orderNum_02_cursor fetch next from orderNum_02_cursor fetch absolute 4 from orderNum_02_cursor fetch next from orderNum_02_cursor fetch last from orderNum_02_cursor fetch prior from orderNum_02_cursor select * from bigorder where orderNum='ZEORD003402'
结果（对比一下，就明白啦）： 例子： --提取数据赋值给变量 declare @OrderId int fetch absolute 3 from orderNum_02_cursor into @OrderId select @OrderId as id select * from bigorder where orderNum='ZEORD003402' 结果： 通过检测全局变量 @@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之 后，@@Fetch_Status可能出现3种值：0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。 这个状态值可以帮你判断提取数据的成功与否。
declare @OrderId int fetch absolute 3 from orderNum_02_cursor into @OrderId while @@fetch_status=0? --提取成功，进行下一条数据的提取操作 begin
?? select @OrderId as id
?? fetch? next from orderNum_02_cursor into @OrderId? --移动游标 end
5.利用游标更新删除数据? --游标修改当前数据语法 Update 基表名 Set 列名=值[,...] Where Current of游标名 --游标删除当前数据语法 Delete 基表名? Where Current of 游标名
---游标更新删除当前数据 ---1.声明游标 declare orderNum_03_cursor cursor scroll for select OrderId,userId from bigorder where orderNum='ZEORD003402' --2.打开游标 open orderNum_03_cursor --3.声明游标提取数据所要存放的变量 declare @OrderId int,@userId varchar(15) --4.定位游标到哪一行 fetch First from orderNum_03_cursor into @OrderId,@userId? --into的变量数量必须与游标查询结果集的列数相同 while @@fetch_status=0? ???????????--判断游标读取下一条数据是否成功。 ?begin
?? if @OrderId=122182
???? begin
???? Update bigorder Set UserId='123' Where Current of? orderNum_03_cursor? --修改当前行
???? end
?? if @OrderId=154074
????? begin
????? Delete bigorder Where Current of? orderNum_03_cursor? --删除当前行
????? end
?? fetch next from orderNum_03_cursor into @OrderId,@userId? --移动游标
end?
6.关闭游标 ?游标打开后，服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集，同时使用游标也会对某些数据进行封锁。所以游标一旦用过，应及时关闭，避免服务器资源浪费。 --关闭游标语法 close [ Global ] cursor_name | cursor_variable_name --关闭游标 close orderNum_03_cursor 7.删除游标 删除游标，释放资源 --释放游标语法 deallocate? [ Global ] cursor_name | cursor_variable_name --释放游标 deallocate orderNum_03_cursor ? ? 游标是邪恶的！ ?在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.对于类C的开发人员来着，这样的思考方式会更加舒服。 正常面向集合的思维方式是: ?????? ?????? 而对于游标来说: ?????? ????? 这也是为什么游标是邪恶的，它会使开发人员变懒，懒得去想用面向集合的查询方式实现某些功能. ????? 同样的，在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源，当然还有更多的代码量…… ????? 从游标对数据库的读取方式来说，不难看出游标为什么占用更多的资源，打个比方: ????????? ????? 当你从ATM取钱的时候，是一次取1000效率更高呢，还是取10次100？ ? ?
????? 我个人认为存在既是合理.归结来说，学习游标原因我归纳为以下2点 ??? 1.现存系统有一些游标，我们查询必须通过游标来实现 ??? 2.作为一个备用方式，当我们穷尽了while循环,子查询，临时表，表变量,自建函数或其他方式扔来无法实现某些查询的时候，使用游标实现. ?
??? 在T-SQL中，游标的生命周期由5部分组成 1.定义一个游标 ???? 在T-SQL中，定义一个游标可以是非常简单，也可以相对复杂，取决于游标的参数.而游标的参数设置取决于你对游标原理的了解程度. ???? 游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行，所以游标是定义在以Select开始的数据集上的: ? ???? ? ???? T-SQL中的游标定义在MSDN中如下: ? DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] ???? [ FORWARD_ONLY| SCROLL ] ???? [ STATIC |KEYSET | DYNAMIC | FAST_FORWARD ] ???? [ READ_ONLY |SCROLL_LOCKS | OPTIMISTIC ] ???? [ TYPE_WARNING] ???? FORselect_statement ???? [ FOR UPDATE [ OFcolumn_name [,...n ] ] ] [;] ???? ? ? ?? 看起来很让人头痛是吧.下面仔细讲一下如何定义游标: ?? 游标分为游标类型和游标变量，对于游标变量来说，遵循T-SQL变量的定义方法（啥，不知道T-SQL变量定义的规则？参考我前面的博文）.游标变量支持 两种方式赋值，定义时赋值和先定义后赋值，定义游标变量像定义其他局部变量一样，在游标前加”@”,注意，如果定义全局的游标，只支持定义时直接赋值，并 且不能在游标名称前面加“@”，两种定义方式如下: ??? ??? 下面我们来看游标定义的参数: ???? LOCAL和GLOBAL二选一 ???? LOCAL意味着游标的生存周期只在批处理或函数或存储过程中可见，而GLOBAL意味着游标对于特定连接作为上下文，全局内有效,例如: ???? ???? 如果不指定游标作用域，默认作用域为GLOBAL ? ???? FORWARD_ONLY 和 SCROLL 二选一 ???? FORWARD_ONLY意味着游标只能从数据集开始向数据集结束的方向读取，FETCH NEXT是唯一的选项，而SCROLL支持游标在定义的数据集中向任何方向，或任何位置移动，如下图: ???? ???? ??? STATIC? KEYSET?DYNAMIC? 和FAST_FORWARD 四选一 ??? 这四个关键字是游标所在数据集所反应的表内数据和游标读取出的数据的关系 ??? STATIC意味着，当游标被建立时，将会创建FOR后面的SELECT语句所包含数据集的副本存入tempdb数据库中，任何对于底层表内数据的更改不会影响到游标的内容. ??? DYNAMIC是和STATIC完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变 ??? KEYSET可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时，@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据 ??? FAST_FORWARD可以理解成FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划，而FAST_FORWARD是根据 情况进行选择采用动态计划还是静态计划，大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好. ? ??? READ_ONLY?SCROLL_LOCKS? OPTIMISTIC 三选一 ??? SCROLL_LOCKS是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功 ??? OPTIMISTIC是相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新 ?? ? ? 2.打开游标 ??? 当定义完游标后，游标需要打开后使用，只有简单一行代码: OPENtest_Cursor ??? 注意，当全局游标和局部游标变量重名时，默认会打开局部变量游标 3.使用游标 ? ?? 游标的使用分为两部分,一部分是操作游标在数据集内的指向，另一部分是将游标所指向的行的部分或全部内容进行操作 ?? 只有支持6种移动选项,分别为到第一行（FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),例如: ???? ??? 对于未指定SCROLL选项的游标来说，只支持NEXT取值. ??? 第一步操作完成后，就通过INTO关键字将这行的值传入局部变量: ??? 比如下面代码: ??? ??? ? ???? 游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的,例如： ??? ? 4.关闭游标 ??? 在游标使用完之后，一定要记得关闭,只需要一行代码:CLOSE+游标名称 CLOSEtest_Cursor ? ? ? ? ? 5.释放游标 ??? 当游标不再需要被使用后，释放游标，只需要一行代码:DEALLOCATE+游标名称 DEALLOCATE test_Cursor ? 对于游标一些优化建议
? 总结 ???? 本文从游标的基本概念，到生命周期来谈游标。游标是非常邪恶的一种存在，使用游标经常会比使用面向集合的方法慢2-3倍，当游标定义在大数据量时，这个比例还会增加。如果可能，尽量使用while,子查询，临时表，函数，表变量等来替代游标，记住，游标永远只是你最后无奈之下的选择，而不是首选。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |