T-SQL之游标

最新推荐文章于 2023-05-11 07:30:00 发布

yibosun

最新推荐文章于 2023-05-11 07:30:00 发布

阅读量1.1k

点赞数

分类专栏： sqlserver

本文链接：https://blog.csdn.net/happyflyingave/article/details/24971131

版权

sqlserver 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

游标概念

在创建存储过程、存储函数和触发器时，我们必须掌握一种称为游标的功能。游标就是对SELECT语句取得的结果进行一件一件处理的功能。

SQL语言基本上可以说是“面向集合（SET）”的语句，也就是说SQL语言擅长将多个记录集中到一起进行处理，而不擅长那种对多个纪录进行一行行单独处理。但是很多时候需要这种对纪录进行一行行的，区别对待的单独处理。这样的处理方式正式SQL非常不擅长的地方，这种场合要发挥SQL的威力必须要使用游标了。

使用游标时，先将由SELECT语句检索出的记录集合临时保存到内存中，如下图所示，然后游标对这些保存在内存中的记录集合，按顺序依次取出进行处理。

另外，在使用游标前需要理解指针这个概念。指针是确定当前记录的信息，可以理解为内存中保存此记录的“地址”，在游标处理中，通过移动指正来进行逐行的数据处理。

因此，游标是邪恶的，它会使开发人员变懒，懒得去想用面向集合的查询方式实现某些功能。

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

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

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

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

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

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

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

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

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

1.定义一个游标

游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行，所以游标是定义在以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变量的定义方法.游标变量支持两种方式赋值，定义时赋值和先定义后赋值，定义游标变量像定义其他局部变量一样，在游标前加”@”,注意，如果定义全局的游标，只支持定义时直接赋值，并且不能在游标名称前面加“@”，两种定义方式如下:

--定以后赋值
DECLARE test_Cursor CURSOR FOR
SELECT * FROM dbo.table1
--先定义后赋值
DELCARE @test_Cursor2 CURSOR 
SET @test_Cursor2=CURSOR FOR
SELECT * FROM dbo.table2

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

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_Cursor

5.释放游标

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

DEALLOCATE test_Cursor

对于游标一些优化建议

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

示例：

set @num=0
--1、定义游标
DECLARE object_Cursor CURSOR FORWARD_ONLY FOR
SELECT objectId,fireObjectId,inspectPointId,fireSysId,fireSysName,fireObjectName,objectCode,objectInsideCode,
specification,workTypeId,checkPerson,resPerson FROM dbo.t_bss_institutionsobject
--2、打开游标
OPEN object_Cursor
--3、使用游标
FETCH NEXT FROM object_Cursor INTO @objectId,@fireObjectId,@inspectPointId,@fireSysId,@fireSysName,@fireObjectName,
@objectCode,@objectInsideCode,@specification,@workTypeId,@checkPerson,@resPerson
while @@fetch_status = 0
	BEGIN
		PRINT '~!!!!!!!!!!!!!!'	
		
		-- 自动生成每条记录对应的日工作记录
		SET @workRecordId= CONVERT(varchar(100), GETDATE(), 121)+'.'+convert(varchar(20),@num)
		PRINT @workRecordId
		-- 从t_bss_inspectpoint中查找inspectpoinId对应的inspectPointCode和inspectPointName
		SELECT @inspectPointCode=inspectPointCode,@inspectPointName=inspectPointName,@inspectPointAbbrName=inspectPointAbbrName FROM dbo.t_bss_inspectpoint where inspectPointId=@inspectPointId



		--将对应记录依次添加到todoworkinfo表中
		SET @todoWorkId= CONVERT(varchar(100), GETDATE(), 121)+'.'+convert(varchar(20),@num)
		PRINT '@todoWorkId'+@todoWorkId

		SET @generateTime=CONVERT(varchar(100), GETDATE(), 121)
		--执行存储过程
		EXEC [dbo].[InstitutionObjectGenerateToDoWork]
			@todoWorkId,
			@inspectPointId,
			@inspectPointCode,
			@inspectPointName,
			@inspectPointAbbrName,
			@fireSysId,
			@fireSysName,
			@fireObjectId,
			@fireObjectName,
			@objectCode,
			@objectId,
			@specification,
			@objectInsideCode,
			@workTypeId,
			@generateTime,
			'自动',
			@checkPerson,
			@resPerson

		
		set @num = @num+1
		FETCH NEXT FROM object_Cursor INTO @objectId,@fireObjectId,@inspectPointId,@fireSysId,@fireSysName,@fireObjectName,
		@objectCode,@objectInsideCode,@specification,@workTypeId,@checkPerson,@resPerson
	END
--4、关闭游标
CLOSE object_Cursor
--5、释放游标
DEALLOCATE object_Cursor