存储过程——游标

• 目录
  • 1. 游标简介
    • 1.0 理解定义
    • 1.1 游标的主要作用
    • 1.2 游标的优缺点
    • 1.3 游标生命周期
    • 1.4 基本语法
  • 2. 游标示例
    • 2.0 准备测试数据
    • 2.1 示例1-FORWARD_ONLY类型游标
    • 2.2 示例2-SCROLL类型游标
    • 2.3 示例3-使用游标进行更新和删除数据

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 游标的优缺点

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种值：

• 这里声明的游标定义为FORWARD_ONLY类型，所以只能使用FETCH NEXTQ提取数据，若是使用其他的提取数据的方式则会报错，比如使用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	test_cur CURSOR SCROLL
FOR  SELECT * FROM szmUser


OPEN test_cur

DECLARE @userId int ,@userName nchar(10)

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. 参考

• Cursors In SQL Server

• SQL Server CURSOR

• SQL游标(cursor)详细说明及内部循环使用示例

• T-SQL查询进阶-10分钟理解游标

• SQL SERVER 游标使用

• SQL中的游标

• SQL Server游标