详解游标-阿里云开发者社区

开发者社区> 吞吞吐吐的> 正文

详解游标

简介:
+关注继续查看

概念

简单点说游标的作用就是存储一个结果集,并根据语法将这个结果集的数据逐条处理。


观点

正因为游标可以将结果集一条条取出处理,所以会增加服务器的负担。再者使用游标的效率远远没有使用默认的结果集效率高,在默认结果集中,从客户端发送到服务器的唯一一个数据包是包含需执行语句的数据包。而在使用服务器游标时,每一个FETCH语句都必须从客户端发送到服务器,然后在服务器中将它解析并编译为执行计划。除非要再sqlserver上进行很复杂的数据操作。


基本知识

一. SQL Server 2005 支持两种请求游标的方法

  1.Transact-SQL (支持 SQL-92);

  2.数据库应用程序编程接口(API)游标函数(ADO、OLE DB、ODBC)应用程序不能混合使用这两种请求游标的方法。ODBC 还支持客户端游标,即在客户  端实现的游标。

二. 游标根据范围可以分成全局游标和局部游标。全局游标可以在整个会话过程中使用,局部游标只能在一个T-SQL批、存储过程或触发器中执行,当执行完毕后局部游标会自动删除。

三. 游标的基本操作,定义游标、打开游标、循环读取游标、关闭游标和删除游标。


语法基础(SQL-92)

以SQL-92方式为例

初始数据PPS_App_Infomation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @Parm01 varchar(100)
DECLARE @Parm02 varchar(100)
DECLARE @Parm03 varchar(100)
DECLARE cur_Pay INSENSITIVE CURSOR FOR
    SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
OPEN cur_Pay
FETCH cur_Pay INTO @Parm01 , @Parm02 , @Parm03
WHILE @@FETCH_STATUS = 0
BEGIN
    WAITFOR DELAY '00:00:01'
    PRINT @Parm01+'__'+@Parm02+'__'+@Parm03
    FETCH cur_Pay INTO @Parm01 , @Parm02 , @Parm03
END
DEALLOCATE cur_Pay

其中 cur_Pay为游标名称 INSENSITIVE 用于设置游标是否使用副本 OPEN 打开游标 fetch循环读入游标 DEALLOCATE 删除游标

INSENSITIVE

举个简单的例子 在游标读取过程中 我们将熬夜虫子改成早起虫子看看 是否添加INSENSITIVE 会是什么样的区别

 

前一个有INSENSITIVE 关键字使用数据副本,后一个无INSENSITIVE 是即时数据

游标的敏感性行为定义了对基行(用于建立游标)所做的更新是否对游标可见。敏感性也定义了能否通过游标进行更新。

scroll

我们再来看看scroll关键字

1
2
3
4
5
6
7
8
9
10
11
DECLARE @Parm01 varchar(100)
DECLARE @Parm02 varchar(100)
DECLARE @Parm03 varchar(100)
DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
    SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
OPEN cur_Pay
BEGIN     
    FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03
    PRINT @Parm01+'__'+@Parm02+'__'+@Parm03
END
DEALLOCATE cur_Pay

上面的程序是ok的 成功输出 熬夜虫子__Maoya__06 20 2009  1:32PM

如果去掉SCROLL关键字会提示

消息 16911,级别 16,状态 1,第 8 行 fetch: 提取类型 last 不能与只进游标一起使用。

SCROLL通过Transact-SQL服务器游标检索特定行。如果SCROLL 选项未在SQL-92样式的DECLARE CURSOR语句中指定,则NEXT是唯一受支持的FETCH 选项。如果在SQL-92样式的DECLARE CURSOR语句中指定了SCROLL 选项,则支持所有FETCH 选项。

FETCH 语法

除了last参数(返回游标中的最后一行并将其作为当前行)再介绍下其他的

NEXT 紧跟当前行返回结果行,并且当前行递增为返回行。如果FETCH NEXT为对游标的第一次提取操作,则返回结果集中的第一行NEXT为默认的游标提取选项。

PRIOR 返回紧邻当前行前面的结果行,并且当前行递减为返回行。如果 FETCH PRIOR 为对游标的第一次提取操作,则没有行返回并且游标置于第一行之前。

FIRST 返回游标中的第一行并将其作为当前行。

ABSOLUTE { n | @nvar} 如果 n 或 @nvar 为正数,则返回从游标头开始的第 n 行,并将返回行变成新的当前行。如果 n 或 @nvar 为负数,则返回从游标末尾开始的第 n 行,并将返回行变成新的当前行。如果 n 或 @nvar 为 0,则不返回行。n 必须是整数常量,并且 @nvar 的数据类型必须为 smallint、tinyint 或 int。

RELATIVE { n | @nvar} 如果 n 或 @nvar 为正数,则返回从当前行开始的第 n 行,并将返回行变成新的当前行。如果 n 或 @nvar 为负数,则返回当前行之前第 n 行,并将返回行变成新的当前行。如果 n 或 @nvar 为 0,则返回当前行。在对游标完成第一次提取时,如果在将 n 或 @nvar 设置为负数或 0 的情况下指定 FETCH RELATIVE,则不返回行。n 必须是整数常量,@nvar 的数据类型必须为 smallint、tinyint 或 int。

定义全局游标

FETCH NEXT FROM GLOBAL cur_Pay INTO @Parm01 , @Parm02 , @Parm03

如果未指定 GLOBAL,则指局部游标。

READ ONLY  与 UPDATE

(下文中只摘要不重复或者必要的代码)

1
2
3
4
5
6
7
DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
    SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
    FOR READ ONLY
    OPEN cur_Pay
BEGIN 
       
    FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03
1
2
3
         UPDATE PPS_App_Infomation SET GameName ='熬夜虫子forupdate' WHERE CURRENT OF cur_Pay
    PRINT @Parm01+'__'+@Parm02+'__'+@Parm03
END

消息 16929,级别 16,状态 1,第 9 行 游标是只读的。 语句已终止。熬夜虫子__Maoya__06 20 2009  1:32PM

1
2
3
DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
    SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
    FOR UPDATE

消息 1048,级别 15,状态 1,第 7 行 游标选项 FOR UPDATE 和 INSENSITIVE 冲突。

1
2
3
4
5
6
7
8
9
10
DECLARE cur_Pay  SCROLL CURSOR FOR
    SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
    FOR Update
    OPEN cur_Pay
BEGIN 
    FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03
    UPDATE PPS_App_Infomation SET GameName ='熬夜虫子forupdate' WHERE CURRENT OF cur_Pay   
    FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03
    PRINT @Parm01+'__'+@Parm02+'__'+@Parm03
END

(1 行受影响) 熬夜虫子forupdate__Maoya__06 20 2009  1:32PM

READ ONLY不允许通过游标进行定位更新,并且不持有针对组成结果集的行的锁。UPDATE与READ ONLY相对,并且UPDATE可以定义到可更新的列。


语法基础(SQL SERVER扩展格式)

基础数据同上

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE @Parm01 varchar(100)
DECLARE @Parm02 varchar(100)
DECLARE @Parm03 varchar(100)
DECLARE cur_Pay  CURSOR
GLOBAL --和SQL-92格式同理 可选LOCAL本地游标
SCROLL --可选FORWARD_ONLY 指定游标只能从第一行滚动到最后一行
DYNAMIC --和上一行参数关联 static表示临时副本 DYNAMIC直接反映在滚动游标时对结果集内行所做的修改
        --keyset表示除了唯一键其他都获取最新值 FAST_FORWARD性能优化的FOR_WARD READONLY游标
OPTIMISTIC --可选READ_ONLY同上 SCROLL_LOCKS定位更新并对当前数据加锁 OPTIMISTIC想当与乐观锁可以进行更新动作但是如果读取的数据被更新会导致操作失败
TYPE_WARNING --如果游标从所请求的类型隐形转换到另一种类型,则给客户端发送警告
FOR
    SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
    FOR UPDATE
OPEN cur_Pay
BEGIN 
    FETCH LAST FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03
    PRINT @Parm01+'__'+@Parm02+'__'+@Parm03
END
DEALLOCATE cur_Pay

相关语法都直接注释在code里了 和SQL-92相似的部分就不赘述了


游标应用

定义游标变量cursor_variable_name

declare @tcur cursor
set @tcur = cursor for SELECT * FROM PPS_App_Infomation

打开游标 OPEN {{[GLOBAL]cursor_name}|cursor_variable_name}

关闭游标 CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}

释放游标 DEALLOCATE{{[GLOBAL]cursor_name}|cursor_variable_name}

获取游标行数 @@CURSOR_ROWS

1
2
3
4
5
6
DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
    SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
OPEN cur_Pay
BEGIN     
    PRINT @@CURSOR_ROWS
END

输出 4

检测fetch操作的状态@@FETCH_STATUS 返回值0表示fetch语句执行成功 -1表示fetch语句执行失败或此行不再结果集中 -2表示所要读取的数据信息不存在

1
2
3
4
5
6
7
8
9
DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
    SELECT GameName,CreateUser,CreateDate FROM [PicPromotion].[dbo].[PPS_App_Infomation]
FETCH next FROM cur_Pay    
OPEN cur_Pay
BEGIN     
    if(@@FETCH_STATUS = 0) print('FETCH 语句成功')
    if(@@FETCH_STATUS = -1) print('FETCH 语句失败或行不在结果集中')
    if(@@FETCH_STATUS = -2) print('提取的行不存在')
END

消息 16917,级别 16,状态 2,第 6 行 游标未打开。 FETCH 语句失败或行不在结果集中

游标嵌套

在游标中使用另一个游标。一般来说使用游标已经很占用系统资源了,再嵌套游标会大影响效率,本文只作参考用。

添加一张数据表PPS_AppConfig_Infomation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DECLARE @Parm01 int
DECLARE @Parm02 varchar(100)
DECLARE @Parm03 varchar(100)
DECLARE @Parm04 varchar(100)
DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
    SELECT AppId,GameName,CreateUser,CreateDate FROM PPS_App_Infomation
OPEN cur_Pay
BEGIN 
   FETCH next FROM cur_Pay  INTO @Parm01 , @Parm02 , @Parm03 ,@Parm04
   WHILE(@@FETCH_STATUS =0)
   BEGIN
        PRINT('当前游戏编号'+cast(@Parm01 as varchar(4)) +' 游戏名称为'+@Parm02)
        DECLARE sub_cur CURSOR FOR
        SELECT WM_Type,WM_Text FROM PPS_AppConfig_Infomation WHERE AppId = @Parm01
        DECLARE @Parm05 varchar(100)
        DECLARE @Parm06 varchar(100)
        OPEN sub_cur
        FETCH next FROM sub_cur INTO @Parm05 , @Parm06
        WHILE(@@FETCH_STATUS =0)
        BEGIN
            PRINT('当前游戏类型'+@Parm05+'默认软文为'+@Parm06)
            FETCH next FROM sub_cur INTO @Parm05 , @Parm06
        END
        CLOSE sub_cur
        DEALLOCATE sub_cur
        FETCH next FROM cur_Pay INTO @Parm01 , @Parm02 , @Parm03 ,@Parm04
   END
   
END
 
DEALLOCATE cur_Pay

输出结果为

游标关联的系统存储过程

sp_cursor_list 报告当前为连接打开的服务器游标的属性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
    SELECT AppId,GameName,CreateUser,CreateDate FROM PPS_App_Infomation
OPEN cur_Pay
   DECLARE @REPORT CURSOR
BEGIN 
   FETCH next FROM cur_Pay 
   WHILE(@@FETCH_STATUS =0)
   BEGIN          
            FETCH next FROM cur_Pay
   END
   exec sp_cursor_list @cursor_return = @REPORT output,@cursor_scope =3  --设置1时报告所有本地游标 设置2时报告所有全局游标 设置3时报告所有本地游标和全局游标
END
CLOSE cur_Pay
DEALLOCATE cur_Pay

 

sp_describe_cursor查看游标的全局特性 作用和sp_cursor_list差不多。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE cur_Pay INSENSITIVE SCROLL CURSOR FOR
    SELECT GameName,CreateUser,CreateDate FROM PPS_App_Infomation
OPEN cur_Pay
   DECLARE @REPORT CURSOR
BEGIN 
   FETCH next FROM cur_Pay 
   WHILE(@@FETCH_STATUS =0)
   BEGIN          
            FETCH next FROM cur_Pay
   END
   exec sp_describe_cursor @cursor_return = @REPORT output,@cursor_source =N'globle',@cur_identity=N'cur_Pay'
         --@cursor_source 可选N'local',N'globle',N'variable' 选择本地、全局还是变量
         --@cur_identity 查看的游标名称
END
CLOSE cur_Pay
DEALLOCATE cur_Pay

 

关系数据库中的操作会对整个行集起作用。由 SELECT 语句返回的行集包括满足该语句的 WHERE 子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序,特别是交互式联机应用程序,并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展。

http://www.cnblogs.com/dubing/archive/2011/11/07/2238868.html

本文转自左正博客园博客,原文链接:http://www.cnblogs.com/soundcode/archive/2012/09/14/2684845.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
怒肝俩月,新鲜出炉史上最有趣的Java小白手册,第一版,每个 Java 初学者都应该收藏(2)
怒肝俩月,新鲜出炉史上最有趣的Java小白手册,第一版,每个 Java 初学者都应该收藏
3 0
Kafka的安装与入门基础(上)
Kafka的安装与入门基础(上)
4 0
怒肝俩月,新鲜出炉史上最有趣的Java小白手册,第一版,每个 Java 初学者都应该收藏(1)
怒肝俩月,新鲜出炉史上最有趣的Java小白手册,第一版,每个 Java 初学者都应该收藏
3 0
shell编程05【自定义函数和高级命令】
shell编程05【自定义函数和高级命令】
3 0
面试官:兄弟,说说Java的static关键字吧(1)
面试官:兄弟,说说Java的static关键字吧
3 0
基于协同过滤算法的电影推荐系统设计(二) - 推荐系统介绍(下)
基于协同过滤算法的电影推荐系统设计(二) - 推荐系统介绍(下)
3 0
NeurIPS提前看 | 四篇论文,一窥元学习的最新研究进展
2019 年,NeurIPS 接受与元学习相关的研究论文约有 20 余篇。元学习(Meta-Learning)是近几年的研究热点,其目的是基于少量无标签数据实现快速有效的学习。本文对本次接收的元学习论文进行了梳理和解读。
6 0
看完保送阿里的Elasticsearch知识点(入门篇2)
看完保送阿里的Elasticsearch知识点(入门篇)
5 0
我以为我对MongoDB十分了解,直到我遇到了字节面试官(1)
我以为我对MongoDB十分了解,直到我遇到了字节面试官
3 0
看完保送阿里的Elasticsearch知识点(入门篇1)
看完保送阿里的Elasticsearch知识点(入门篇)
3 0
4252
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载