概述
游标是邪恶的!
在关系数据库中,我们对于查询的思考是面向集合的。而游标打破了这一规则,游标使得我们思考方式变为逐行进行.对于类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.打开游标
当定义完游标后,游标需要打开后使用,只有简单一行代码:
注意,当全局游标和局部游标变量重名时,默认会打开局部变量游标
3.使用游标
游标的使用分为两部分,一部分是操作游标在数据集内的指向,另一部分是将游标所指向的行的部分或全部内容进行操作
只有支持6种移动选项,分别为到第一行(FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),例如:
对于未指定SCROLL选项的游标来说,只支持NEXT取值.
第一步操作完成后,就通过INTO关键字将这行的值传入局部变量:
比如下面代码:
游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的,例如:
4.关闭游标
在游标使用完之后,一定要记得关闭,只需要一行代码:CLOSE+游标名称
5.释放游标
当游标不再需要被使用后,释放游标,只需要一行代码:DEALLOCATE+游标名称
对于游标一些优化建议
- 如果能不用游标,尽量不要使用游标
- 用完用完之后一定要关闭和释放
- 尽量不要在大量数据上定义游标
- 尽量不要使用游标上更新数据
- 尽量不要使用insensitive, static和keyset这些参数定义游标
- 如果可以,尽量使用FAST_FORWARD关键字定义游标
- 如果只对数据进行读取,当读取时只用到FETCH NEXT选项,则最好使用FORWARD_ONLY参数
总结
本文从游标的基本概念,到生命周期来谈游标。游标是非常邪恶
的一种存在,使用游标经常会比使用面向集合的方法慢2-3倍,当游标定义在大数据量时,这个比例还会增加。如果可能,尽量使用while,子查询,临时表,函数,表变量等来替代游标,记住,游标永远只是你最后无奈之下的选择,而不是首选。
游标是邪恶的!
文章转载自 http://www.cnblogs.com/CareySon/archive/2011/11/01/2231381.html
相关推荐
Transact-SQL语言极其程序设计的方法; T-SQL游标的使用方法。
深入理解T-SQL体系结构,充分利用高级T-SQL查询技术。 本书深入介绍了T-SQL的内部体系结构,揭示了基于集合的查询的强大威力,并包含大量来自专家们的参考和建议。通过本书提供的最佳实践和示例代码,数据库开发...
本书及其续篇——《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题,并讨论了解决这些问题的方法。它们...
阅读本书,可以充分地理解T-SQL语言和获得良好的编程实践,学会如何编写更加有效而强大的查询。书中大部分章节后面都提供了练习题目,可以帮助读者更好地掌握所学的内容。 《Microsoft SQL Server 2008技术内幕:T...
T-Sql 编程 触发器 游标 储存过程 函数 T-Sql 编程 触发器 游标 储存过程 函数
阅读本书,可以充分地理解T-SQL语言和获得良好的编程实践,学会如何编写更加有效而强大的查询。书中大部分章节后面都提供了练习题目,可以帮助读者更好地掌握所学的内容。 《Microsoft SQL Server 2008技术内幕:...
深入讨论了SQL Server 2005中新增的T-SQL编程特性,包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解复杂的逻辑并掌握T-SQL。 本书适合于专业数据库开发者、BI开发者、DBA和以SQL Server...
介绍了T-SQL的使用,主要针对的是微软SQL SERVER 2005.Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题...
SQL学习课件及T-SQL视频教程,深入浅出,讲解的非常详细,很容易理解。 主要内容: SQL概述 SQL数据模型及数据库设计 游标 索引 视图 ... T-SQL语句简介,视频教程,非常实用
value VARCHAR(10), PRIMARY KEY(id) ); INSERT INTO test_main(id, value) VALUES (1, 'ONE'); INSERT INTO test_main(id, value) VALUES (2, 'TWO'); INSERT INTO test_main(id, value) VALUES (3, 'THREE')...
第1章 T-SQL查询和编程基础 1.1 理论背景 1.2 SQL SERVER体系结构 1.3 创建表和定义数据完整性 1.4 总结 第2章 单表查询 2.1 SELECT语句的元素 2.2 谓词和运算符 2.3 CASE表达式 2.4 NULL...
SQl SERVER 进阶 学习课件 学习E-R图的绘制,理解数据库范式,掌握如何规范地设计数据库。 数据的高级查询、子查询。 创建和使用索引、视图,实现高效的数据管理。 学习使用T-SQL进行数据库编程,实现多功能数据管理...
游标.sql
T-SQL强化RAISERROR详解,SQL 注入,使用 OUTPUT 游标参数,使用 WITH RECOMPILE 选项,使用sp_addmessage添加自定义消息 京华志&精华志出品 希望大家互相学习,互相进步 支持CSDN 支持微软 主要包括C# ASP.NET SQLDBA ...
T-SQL语法 一、数据库存储结构 二、数据库表存储结构 三、约 束 四、规 则 五、默认 六、索 引 七、视图 八、触发器 九、select,delete,update,insert语句使用方法 十、函数数据整理 十一、游标 十二、存储过程
SQL语句通常都可产生或处理一组记录,但是许多应用程序,尤其是T-SQL嵌入的主语言,通常不能把整个结果集作为一个单元来处理,这些应用程序就需要用一种机制来保证每次处理结果集中的一行或几行,游标(cursor)就提供...
实验九T-SQL编程、存储过程、游标.docx
痛苦的sql游标的止痛药 declare fetch 等还有其他的基本的sql操作,包含。
SQL相关资料
(1)对银行贷款数据库,用游标实现按如下报表形式显示结果的T-SQL语句。该报表统计每家银行的贷款情况,只考虑有贷款记录的银行,每家银行的贷款记录需要按先按法人名称的升序排列,再按贷款日期的升序排列。报表...