SQL Server游标的使用

时间：2022-05-26 13:27:46
  游标是邪恶的！

       在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.对于类C的开发人员来着，这样的思考方式会更加舒服。

       正常面向集合的思维方式是:

       SQL Server游标的使用

       而对于游标来说:

       SQL Server游标的使用

      这也是为什么游标是邪恶的，它会使开发人员变懒，懒得去想用面向集合的查询方式实现某些功能.

      同样的，在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源，当然还有更多的代码量……

      从游标对数据库的读取方式来说，不难看出游标为什么占用更多的资源，打个比方:

          SQL Server游标的使用

      当你从ATM取钱的时候，是一次取1000效率更高呢，还是取10次100？

 

 

既然游标这么“邪恶”，为什么还要学习游标

      我个人认为存在既是合理.归结来说，学习游标原因我归纳为以下2点

    1.现存系统有一些游标，我们查询必须通过游标来实现

    2.作为一个备用方式，当我们穷尽了while循环,子查询，临时表，表变量,自建函数或其他方式扔来无法实现某些查询的时候，使用游标实现.

 

T-SQL中游标的生命周期以及实现

    在T-SQL中，游标的生命周期由5部分组成

1.定义一个游标

     在T-SQL中，定义一个游标可以是非常简单，也可以相对复杂，取决于游标的参数.而游标的参数设置取决于你对游标原理的了解程度.

     游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行，所以游标是定义在以Select开始的数据集上的:

 

     SQL Server游标的使用

 

     T-SQL中的游标定义在MSDN中如下:

 

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变量的定义方法（啥，不知道T-SQL变量定义的规则？参考我前面的博文）.游标变量支持两种方式赋值，定义时赋值和先定义后赋值，定义游标变量像定义其他局部变量一样，在游标前加”@”,注意，如果定义全局的游标，只支持定义时直接赋值，并且不能在游标名称前面加“@”，两种定义方式如下:

    SQL Server游标的使用

    下面我们来看游标定义的参数:

     LOCAL和GLOBAL二选一

     LOCAL意味着游标的生存周期只在批处理或函数或存储过程中可见，而GLOBAL意味着游标对于特定连接作为上下文，全局内有效,例如:

     SQL Server游标的使用

     如果不指定游标作用域，默认作用域为GLOBAL

 

     FORWARD_ONLY 和 SCROLL 二选一

     FORWARD_ONLY意味着游标只能从数据集开始向数据集结束的方向读取，FETCH NEXT是唯一的选项，而SCROLL支持游标在定义的数据集中向任何方向，或任何位置移动，如下图:

     SQL Server游标的使用

    

    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 三选一 
    READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作

    SCROLL_LOCKS是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功

    OPTIMISTIC是相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新

  

 

 

2.打开游标

    当定义完游标后，游标需要打开后使用，只有简单一行代码:

OPEN test_Cursor

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

3.使用游标

 

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

   只有支持6种移动选项,分别为到第一行（FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),例如:

     SQL Server游标的使用

    对于未指定SCROLL选项的游标来说，只支持NEXT取值.

    第一步操作完成后，就通过INTO关键字将这行的值传入局部变量:

    比如下面代码:

    SQL Server游标的使用

    SQL Server游标的使用

 

     游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的,例如：

    SQL Server游标的使用

 

4.关闭游标

    在游标使用完之后，一定要记得关闭,只需要一行代码:CLOSE+游标名称

CLOSE test_Cursor

 

 

 

 

 

5.释放游标

    当游标不再需要被使用后，释放游标，只需要一行代码:DEALLOCATE+游标名称

DEALLOCATE test_Cursor

 

对于游标一些优化建议

  •      如果能不用游标，尽量不要使用游标
  •      用完用完之后一定要关闭和释放
  •      尽量不要在大量数据上定义游标
  •      尽量不要使用游标上更新数据
  •      尽量不要使用insensitive, static和keyset这些参数定义游标
  •      如果可以，尽量使用FAST_FORWARD关键字定义游标
  •      如果只对数据进行读取，当读取时只用到FETCH NEXT选项，则最好使用FORWARD_ONLY参数

 

总结

     本文从游标的基本概念，到生命周期来谈游标。游标是非常邪恶的一种存在，使用游标经常会比使用面向集合的方法慢2-3倍，当游标定义在大数据量时，这个比例还会增加。如果可能，尽量使用while,子查询，临时表，函数，表变量等来替代游标，记住，游标永远只是你最后无奈之下的选择，而不是首选。

     游标是邪恶的！




[sql] view plain copy print?SQL Server游标的使用SQL Server游标的使用
  1. /***  
  2. 游标的使用  
  3.  讲了这个多游标的优点，现在我们就亲自来揭开游标的神秘的面纱。  
  4.  使用游标的顺序： 声名游标、打开游标、读取数据、关闭游标、删除游标。  
  5. 1.3.1声明游标  
  6. 最简单游标声明：DECLARE <游标名>CURSOR FOR<SELECT语句>；  
  7. 其中select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询  
  8. 例子：[已表2 AddSalary为例子]  
  9. Declare mycursor cursor for select * from AddSalary  
  10. 这样我就对表AddSalary申明了一个游标mycursor   
  11. 【高级备注】  
  12. DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT语句>  
  13. 这里我说一下游标中级应用中的[INSENSITIVE]和[SCROLL]  
  14. INSENSITIVE  
  15. 表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。  
  16. 另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。  
  17. a.在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；  
  18. b.使用OUTER JOIN；  
  19. c.所选取的任意表没有索引；  
  20. d.将实数值当作选取的列。  
  21. SCROLL  
  22. 表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再  
  23. 重开游标。   
  24. 1.3.2 打开游标  
  25. 非常简单，我们就打开刚才我们声明的游标mycursor  
  26. OPEN mycursor   
  27. 1.3.3读取数据  
  28. FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名  | @游标变量名 } [ INTO @变量名 [，…] ]  
  29. 参数说明：  
  30. NEXT   取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。  
  31. INTO @变量名[,…]  把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。  
  32. 现在我们就取出mycursor游标的数据吧！   
  33. 当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。就本例而言，可以使用下列操作读取第1行数据：  
  34. Eg: Fetch next from mycursor 或则 Fetch first from mycursor  
  35. 这样我就取出了游标里的数据，但是光光这样可不够，我们还需要将取出的数据赋给变量  
  36. --声明2个变量  
  37. declare @O_ID NVARCHAR(20)  
  38. declare @A_Salary float  
  39. --将取出的值传入刚才声明的2个变量  
  40. Fetch next from mycursor into @ O_ID,@ A_Salary  
  41.    
  42. 1.3.4关闭游标  
  43. CLOSE mycursor     
  44.            
  45. 1.3.5删除游标  
  46. DEALLOCATE mycursor           
  47.    
  48. 1.3.6 实例训练  
  49. **/  
  50. CREATE PROCEDURE PK_Test  
  51. AS  
  52.     --声明2个变量  
  53.     declare @O_ID nvarchar(20)    
  54.     declare @A_Salary float    
  55.     --声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同  
  56.     declare mycursor cursor for select O_ID,A_Salary from AddSalary    
  57.     --打开游标  
  58.     open mycursor      
  59.     --从游标里取出数据赋值到我们刚才声明的2个变量中  
  60.     fetch next from mycursor into @O_ID,@A_Salary      
  61.     --判断游标的状态  
  62.     -- 0 fetch语句成功      
  63.     ---1 fetch语句失败或此行不在结果集中      
  64.     ---2 被提取的行不存在  
  65.     while (@@fetch_status=0)  
  66.     begin      
  67.     --显示出我们每次用游标取出的值  
  68.        print '游标成功取出一条数据'  
  69.        print @O_ID  
  70.        print @A_Salary     
  71.     --用游标去取下一条记录  
  72.        fetch next from mycursor into @O_ID,@A_Salary  
  73.     end  
  74.     --关闭游标  
  75.     close mycursor  
  76.     --撤销游标  
  77.     DEALLOCATE mycursor   
  78. GO  
SQL Server游标的使用




http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html

http://blog.csdn.net/smartsmile2012/article/details/41043613















标签：

相关文章