SQL Server游标的使用
原文地址:http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html
游标是邪恶的!
在关系数据库中,我们对于查询的思考是面向集合的。而游标打破了这一规则,游标使得我们思考方式变为逐行进行.对于类C的开发人员来着,这样的思考方式会更加舒服。
正常面向集合的思维方式是:
而对于游标来说:
这也是为什么游标是邪恶的,它会使开发人员变懒,懒得去想用面向集合的查询方式实现某些功能.
同样的,在性能上,游标会吃更多的内存,减少可用的并发,占用宽带,锁定资源,当然还有更多的代码量……
从游标对数据库的读取方式来说,不难看出游标为什么占用更多的资源,打个比方:
当你从ATM取钱的时候,是一次取1000效率更高呢,还是取10次100?
既然游标这么“邪恶”,为什么还要学习游标我个人认为存在既是合理.归结来说,学习游标原因我归纳为以下2点
1.现存系统有一些游标,我们查询必须通过游标来实现
2.作为一个备用方式,当我们穷尽了while循环,子查询,临时表,表变量,自建函数或其他方式扔来无法实现某些查询的时候,使用游标实现.
T-SQL中游标的生命周期以及实现在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 ] FOR select_statement [ FOR UPDATE [ OF column_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 三选一
READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作
SCROLL_LOCKS是另一种极端,将读入游标的所有数据进行锁定,防止其他程序进行更改,以确保更新的绝对成功
OPTIMISTIC是相对比较好的一个选择,OPTIMISTIC不锁定任何数据,当需要在游标中更新数据时,如果底层表数据更新,则游标内数据更新不成功,如果,底层表数据未更新,则游标内表数据可以更新
2.打开游标
当定义完游标后,游标需要打开后使用,只有简单一行代码:
OPEN test_Cursor
注意,当全局游标和局部游标变量重名时,默认会打开局部变量游标
3.使用游标游标的使用分为两部分,一部分是操作游标在数据集内的指向,另一部分是将游标所指向的行的部分或全部内容进行操作
只有支持6种移动选项,分别为到第一行(FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),例如:
对于未指定SCROLL选项的游标来说,只支持NEXT取值.
第一步操作完成后,就通过INTO关键字将这行的值传入局部变量:
比如下面代码:
游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的,例如:
4.关闭游标
在游标使用完之后,一定要记得关闭,只需要一行代码:CLOSE+游标名称
CLOSE test_Cursor5.释放游标
当游标不再需要被使用后,释放游标,只需要一行代码: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 Server存储过程详细介绍(一) 存储过程其实就是已预编译为可执行过程的一个或多个SQL语句。 通过调用和传递参数即可完成该存储过程的功能。 前面有介绍过存储过程的一些语法,但是没有详细示例,今天我们来一起研究一下存储过程。
SQL Server存储过程详细介绍(二) 存储过程其实就是已预编译为可执行过程的一个或多个SQL语句。 通过调用和传递参数即可完成该存储过程的功能。 前面有介绍过存储过程的一些语法,但是没有详细示例,今天我们来一起研究一下存储过程。
SQL Server存储过程总结 存储过程简介: 存储过程(Stored Procedure)是在大型数据库中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升 ------来源于百度百科 存储过程的种类: 1系统存储过程 以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。 2本地存储过程 用户创建的存储过程是由用户创建并完成某一特定功能的存储过程,事实上一般所说的存储过程就是指本地存储过程。
初识 Sql Server存储过程 之前的公司并未使用存储过程来做项目,所以小生对存储过程的调用、使用也是一知半解,刚好这家公司就大量用到了存储过程 这次做的功能,为了保持风格一致,也是需要使用存储过程来实现动态sql和数据分页 下面一起来看看如何实现的吧(小白一枚,不喜勿喷,请轻拍)~ 调用存储过程(其中condit...
相关文章
- sql server 备份还原_什么是SQL
- ASP.NET Core技术--Identity Server 4 基础
- 完全卸载SQL SERVER 2008
- T-sql语句修改SQL Server数据库逻辑名、数据库名、物理名的方法
- SQL Server查看未释放游标的方法
- 修改SQL-SERVER数据库表结构的SQL命令附sql命令行修改数据库
- SQL Server 压缩日志与减少SQL Server 文件大小的方法
- SQL Server 主机命名规范(sqlserver主机名)
- 查询SQL Server 交集查询实战演练(sqlserver交集)
- 开发简易SQL Server接口开发指南(sqlserver接口)
- 使用MySQL执行SQL指令:简单易学的基础教程(mysql运行sql命令)
- 罗会涛:深耕SQL Server数据库服务器的技术突破者(罗会涛sqlserver)
- 王进深入到SQL Server 的极致之旅(王进sqlserver)
- 探索SQL Server:惊喜不断(深入sqlserver)
- serverOracle 与 SQL Server之间的友谊连接(oracle连接sql)
- 使用SQL Server 走上成功之路(sqlserver 路径)
- SQL Server表锁——实现并发控制的有效方法(sqlserver表锁定)
- 集令人惊叹!SQL Server群集的强大优势(sqlserver群)
- SQL Server快速恢复还原数据(sqlserver的还原)
- 使用SQL Server中游标加速油品管理(sqlserver油游标)
- 用SQL Server:禁用无法回避(sqlserver已禁)
- SQL Server已经激活:让工作效率大大提升!(sqlserver已激活)
- sql两大主流数据库之比较—SQL Server和MySQL(sqlserver和my)
- 从SQL Server中删除符号——解决方案(sqlserver去符号)
- SQL Server指定端口号连接配置指南(mssql连接指定端口号)
- serverMySQL 与 SQL Server 的比较与选择(mysql 和 sql)
- 阿里云搭建SQL Server数据库实现在线访问(阿里sqlserver)
- SQLServer中用T—SQL命令查询一个数据库中有哪些表的sql语句