T-SQL——游标

2020年5月27日 21:10:00

1.游标简介

1.0 理解定义

SQL游标(cursor)是一个数据库对象，用于从结果集中检索某一行的数据。

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

在编程中，我们使用诸如for或while之类的循环一次遍历一项，游标遵循相同的方法。当在SQL中，应用程序逻辑需要一次只处理一行，而不是一次处理整个结果集。可以使用游标完成此操作。

怎么理解“为了处理查询的结果集中特定行的数据，我们使用游标处理”? 其实，游标的英文单词是cursor,也可以翻译为光标，其实类比我们编辑文档，当想要编辑具体的某一行的时候，我们需要使用光标移到该行进行编辑，在SQL中游标的作用是一样的。

当然，本质上就是个定义在结果集上的指针，我们可以控制该指针遍历结果集。

这里补充一下：理论上SQL编写是按照面向集合的思维模式，而我们使用游标则又回到了面向过程的思维模式。此中思想非三言二语可说明白的，相关知识可以参考《SQL进阶教程》2.6章节！

1.1 游标的主要作用

  1. 定位到结果集中的某一行。
  2. 对当前位置的数据进行读写。
  3. 可以对结果集中的数据单独操作，而不是整行执行相同的操作。
  4. 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

1.2 游标的优缺点

  1. 优点：参考上文中游标的作用
  2. 缺点：滥用游标会影响系统性能。
    一般来说，有一个共识：能不用游标就不要用游标。
    事实上，编写SQL语句的时候大多数的情形下是没有必要使用游标的。

1.3 游标生命周期

游标的生命周期：

  1. 声明游标(Declare Cursor)
  2. 打开游标(Open Cursor)
  3. 提取游标(Fetch Cursor)
  4. 关闭游标(Close Cursor)
  5. 释放游标(Deallocate Cursor)

使用游标的过程如下：
游标生命周期

注：图片来源 https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/

1.4 基本语法

①完整的声明游标

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 ] ] ]

【说明】方括号中的关键之是可选的，具体作用如下：

  1. 作用域

    • Local：游标作用域为局部，只在定义它的批处理、函数和存储过程中有效。
    • Global：游标作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
    • 默认值是Local

  2. 游标方向

    • Forward_Only:指定游标智能从第一行滚到最后一行，种游标称为：只进游标。
    • Scroll：指定游标在定义的数据集中向任何方向，或任何位置移动。
    • 默认是Forward_Only

  3. 游标读取的数据和基表数据关系

    • Static表明：游标一旦指定了select查询出的结果集，之后任何对于基表（即：select语句所查询的表）内数据的更改不会影响到游标的内容。该种游标称为静态游标

    • Dynamic和Static完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变。该种游标称为动态游标。

    • KeySet:指明当再游标被打开时游标中的列的顺序时固定的，游标只维持其所依赖的基表的键

    • Fast_Forward:指明一个Forward_Only且Read_Only型游标。注意：一旦声明了Fast_Forward,则之前就不可以选择Scroll类型的游标。同样，在之后也就不能使用Scroll_Locks和Optimistic选项。

    • 默认值是Dynamic

  4. 游标是否锁定数据

    • Read_Only意味着声明的游标只能读取数据,游标不能做任何更新操作

    • Scroll_Locks是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功

    • Optimistic是相对比较好的一个选择，不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新或删除会不成功，如果，底层表数据未更新，则游标内表数据可以更新或删除

  5. Type_Warning:指明若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端。

  6. Update[Of colunm_name[,...n]]:定义利用游标可更新的列。若果列出了Of colunm_name[,...n]，则只允许修改列出的列

  7. 其实，从上面可以看出游标的声明是有许多的可选项。
    但是一般来说，只要记住游标声明的默认值。一般实际开发中，如无必要则使用默认值即可。

②打开游标

OPEN cursor_name

③提取行数据到指定的变量列表中

--提取下一行数据
FETCH NEXT FROM cursor_name INTO variateList;
--提取上一行数据
FETCH PRIOR FROM cursor_name INTO variateList;
--提取第一行数据
FETCH FIRST FROM cursor_name INTO variateList;
--提取最后一行数据
FETCH LAST FROM cursor_name INTO variateList;
--提取第3行数据（提取指定的行）
FETCH ABSOLUTE 3 FROM cursor_name INTO variateList;
--提取当前行的上一行(复数为向后，正数为向前)
FETCH RELATIVE -1 FROM cursor_name INTO variateList;

【注意】：

  • 游标只有上述的6种移动方式，但是要注意的是：一旦在声明游标的时候，定义为Forward_Only(默认值),则提取行数据中时候，只能是Fetch next

  • INTO列表中声明的变量数目必须与所选列的数目相同。即：select的结果集中有几列，则INTO后的变量就该有几个。

④关闭游标

CLOSE cursor_name

⑤释放游标

DEALLOCATE cursor_name

2.游标示例

2.0 准备测试数据

USE [db_Tome1]
GO

CREATE TABLE [dbo].[szmUser]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nchar](10) NULL
)

Insert into szmUser (UserName) values (N'张三'),(N'李四'),(N'王五'),(N'赵六'), (N'Tom'),(N'Jerry'),(N'Bob');

GO

2.1 示例1-FORWARD_ONLY类型游标

使用FORWARD_ONLY声明只进游标，实现从头到尾提取行数据

DECLARE test_cur CURSOR FORWARD_ONLY --声明游标，定义为FORWARD_ONLY类型
FOR  SELECT * FROM szmUser--游标作用的结果集

OPEN test_cur --打开游标

DECLARE @userId INT ,@userName NCHAR(10)--声明标量用于存储行数据


WHILE ( @@fetch_status = 0 )          
    BEGIN
	FETCH NEXT FROM test_cur INTO @userId ,@userName--提取下一行数据并存入定义的变量中
	PRINT @userName--打印数据
    END

CLOSE test_cur--关闭游标

DEALLOCATE test_cur--释放游标

消息框打印信息如下：

张三        
李四        
王五        
赵六        
Tom       
Jerry     
Bob       
Bob

【注意】：

  • 全局变量@@Fetch_Status的值表示游标提取状态信息，该状态用于判断Fetch语句返回数据的有效性。
    当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：

    状态码 含义
    0 Fetch语句成功
    -1 Fetch语句失败或行不在结果集中
    -2 提取的行不存在

  • 这里声明的游标定义为FORWARD_ONLY类型，所以只能使用FETCH NEXT提取数据，若是使用其他的提取数据的方式则会报错，比如使用FETCH LAST，则报错：
    fetch: 提取类型 last 不能与只进游标一起使用。

2.2 示例2-SCROLL类型游标

使用SCROLL声明游标，实现读取特定行数据

DECLARE test_cur CURSOR scroll --声明游标，定义为FORWARD_ONLY类型
FOR  SELECT * FROM szmUser--游标作用的结果集

OPEN test_cur --打开游标

DECLARE @userId INT ,@userName NCHAR(10)--声明标量用于存储行数据

FETCH FIRST FROM test_cur INTO　@userId, @userName--提取当前结果集的第一行
PRINT CAST(@userId as varchar)+':'+@userName

FETCH LAST FROM test_cur INTO @userId ,@userName--提取当前结果集的最后一行
PRINT CAST(@userId as varchar)+':'+@userName

FETCH prior From test_cur INTO @userId ,@userName--提取当前游标指向的上一行数据
PRINT CAST(@userId as varchar)+':'+@userName

FETCH ABSOLUTE 2 FROM test_cur INTO @userId ,@userName--提取当前结果集中的第二行数据
PRINT CAST(@userId as varchar)+':'+@userName

FETCH RELATIVE 1 FROM test_cur INTO @userId ,@userName--提取当前游标指向的下一行数据
PRINT CAST(@userId as varchar)+':'+@userName

FETCH RELATIVE -1 FROM test_cur INTO @userId ,@userName--提取当前游标指向的上一行数据
PRINT CAST(@userId as varchar)+':'+@userName

CLOSE test_cur--关闭游标

DEALLOCATE test_cur--释放游标

消息框打印信息如下：

1:张三        
7:Bob       
6:Jerry     
2:李四        
3:王五        
2:李四

2.3 示例3-使用游标进行更新和删除数据

使用游标对结果集中数据进行更改和删除

示例：删除SELECT * FROM szmUser结果集中的名叫张三的的人，同时将该结果集中名叫李四的名字改为李四四

DECLARE @userId int ,@userName nchar(10)
DECLARE	test_cur CURSOR SCROLL 
FOR  SELECT * FROM szmUser
OPEN test_cur
FETCH First FROM test_cur INTO @userId,@userName--定位游标到第一行（注意这里，一定要将游标首先定位到某一行）
WHILE (@@FETCH_STATUS=0)
BEGIN 
	IF @userName='李四'
		BEGIN 
		Update szmUser Set UserName='李四四' WHERE CURRENT OF  test_cur  --修改当前行
		END
	IF @userName='张三'
		BEGIN 
		DELETE szmUser  WHERE CURRENT OF  test_cur  --删除当前行
		END
     FETCH NEXT FROM test_cur INTO @userId ,@userName  --移动游标
 END
 CLOSE test_cur
 DEALLOCATE test_cur

【注意】：

  • 在这里使用while循环一定要首先将定位游标的起始位置，类比其它类型的编程语言中循环语句，循环就要有起始位置，步长，结束位置

  • 注意：一开始，使用的测试表虽然定义了标识规范及标识增量，但是没有定义主键，测试的时候报错：游标是只读的。 语句已终止。,其实只是因为表没有主键或唯一性约束，所以CURRENT OF test_cur会报错

    当然，也是可以在更新或删除语句中使用where指定具体的记录。

2.4 示例4-静态游标和动态游标演示

2.4.0 说明

游标在声明的时候，可以定义是静态游标还是动态游标，游标默认是动态游标。

静态游标在打开时会将数据集存储在tempdb中，因此显示的数据与游标打开时的数据集保持一致，在游标打开以后对数据库的更新不会显示在游标中。

动态游标在打开后会反映对数据库的更改。所有UPDATE、INSERT 和 DELETE 操作都会显示在游标的结果集中，结果集中的行数据值、顺序和成员在每次提取时都会改变。

简而言之：静态游标的数据是固定的，不会因为基表的改变而改变；动态游标的数据是随着基表变化而变化的。

2.4.1 示例-静态游标
DECLARE @userId INT , @userName NCHA(10)                    --声明变量，存储行数据
DECLARE test_cur CURSOR STATIC				    --声明静态游标
FOR SELECT  * FROM    szmUser				    --游标遍历的结果集
OPEN test_cur					            --打开游标
FETCH NEXT FROM test_cur INTO @userId,@userName             --取数据
WHILE ( @@fetch_status = 0)                                 --判断是否还有据
    BEGIN
        PRINT RTRIM(@userId) +':'+ @userName
		UPDATE szmUser SET UserName='测试' WHEREid=4   --测试静态动态用
        FETCH NEXT FROM test_cur INTO @userId,@userName        --游标进入下一行
    END
CLOSE test_cur
DEALLOCATE test_cur

运行结果：

2:李四        
3:王五        
4:赵六        
5:Tom       
6:Jerry     
7:Bob       
8:Mark

【说明】：我们定义的是静态游标，所以一旦当结果集进游标区后，基表的数据发生改变游标读取数据依旧是最初入游标区的数据。
所以在这里，当游标提取一行数据后，我们就把基表中id=的userName改为“测试”，但是游标继续执行，读取的还是初进入游标区的数据，即id=4,userName=赵六

2.4.2 示例-动态游标

声明游标的时候，默认就是动态游标，所以这里我们只要把上面的代码中的STATIC删除即可，运行结果如下,你好发现在基表中对数据的修改，直接是反应到已声明的游标中。我们修改的id=4的用户名，直接显示在游标的数据中。

2:李四        
3:王五        
4:测试  --修改基表数据直接作用在已声明的游标中      
5:Tom       
6:Jerry     
7:Bob       
8:Mark
2.4.3 动态和静态区别

  • 声明游标默认是动态游标，对基表中数据的改变影响已声明的动态游标，不影响已声明的静态游标。

    原则是应该尽量避免使用静态游标

  • 动态游标的打开速度比静态游标的打开速度快。当打开静态游标时，必须生成内部临时工作表，而动态游标则不需要。

  • 在联接中，静态游标的速度可能比动态游标的速度快。因为动态游标在滚动时反应对结果集内的各行数据所做的更改，它会消耗资源去检测基表的更改，因此对于复杂的查询，且不需要反映基表的更新的游标的处理应将其定义为静态游标。


3.使用原则

  • Rule 1：能不用游标则不用游标
  • 用完之后是一定要及时的关闭和释放游标
  • 不要在有大量数据的结果集中定义游标
  • 尽量避免使用静态游标
  • 尽量不要在游标上更新数据
  • 只进游标（First-Forward)若是只读，可以使用Fast-Forward定义游标

4.参考

posted @ 2020-05-27 21:36  shanzm  阅读(1198)  评论(0)  编辑  收藏  举报
TOP