SQL Server游标的使用

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介: 原文地址:http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html 游标是邪恶的!        在关系数据库中,我们对于查询的思考是面向集合的。而游标打破了这一规则,游标使得我们思考方式变为逐行进行.对于类C的开发人员来着,这样的思考方式会更加舒服。        正常面向集合的思维方式是:      

原文地址:http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html

游标是邪恶的!

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

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

       2

       而对于游标来说:

       3

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

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

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

          1

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

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

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

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

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

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

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

1.定义一个游标

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

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

     4

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

    5

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

     LOCAL和GLOBAL二选一

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

     6

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

     FORWARD_ONLY 和 SCROLL 二选一

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

     7

   

    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)),例如:

     8

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

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

    比如下面代码:

    10

    9

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

    11

4.关闭游标

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

CLOSE test_Cursor

5.释放游标

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

DEALLOCATE test_Cursor

对于游标一些优化建议

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

总结

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

     游标是邪恶的!

小注:

        @@FETCH_STATUS 函数报告上一个 FETCH 语句的状态
        1 > . 0 :表示成功取出了一行。@@FETCH_STATUS=0
        2 > . - 1表示未取到数据。游标位置超出结果集。@@FETCH_STATUS=-1
        3 > . - 2表示返回的行已经不再是结果集的一个成员,这种情况只有在游标不是insensitive的情况下出现,即其它进程已删除了行或改变了游标打开的关键值。@@FETCH_STATUS=-2

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
253 1
|
2天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
18 3
|
8天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
14天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
24 2
|
19天前
|
SQL 存储 测试技术
|
18天前
|
SQL 机器学习/深度学习 搜索推荐
SQL SERVER 转换失败
【6月更文挑战第25天】
|
23天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
1月前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
58 3
|
1月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程

热门文章

最新文章