- 目录
1.游标简介
1.0 理解定义
SQL游标(cursor)是一个数据库对象，用于从结果集中检索某一行的数据。
游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。
在编程中，我们使用诸如for或while之类的循环一次遍历一项，游标遵循相同的方法。当在SQL中，应用程序逻辑需要一次只处理一行，而不是一次处理整个结果集。可以使用游标完成此操作。
怎么理解“为了处理查询的结果集中特定行的数据，我们使用游标处理”? 其实，游标的英文单词是cursor,也可以翻译为光标，其实类比我们编辑文档，当想要编辑具体的某一行的时候，我们需要使用光标移到该行进行编辑，在SQL中游标的作用是一样的。
当然，本质上就是个定义在结果集上的指针，我们可以控制该指针遍历结果集。
这里补充一下：理论上SQL编写是按照面向集合的思维模式，而我们使用游标则又回到了面向过程的思维模式。此中思想非三言二语可说明白的，相关知识可以参考《SQL进阶教程》2.6章节！
1.1 游标的主要作用
- 定位到结果集中的某一行。
- 对当前位置的数据进行读写。
- 可以对结果集中的数据单独操作，而不是整行执行相同的操作。
- 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。
1.2 游标的优缺点
- 优点：参考上文中游标的作用
- 缺点：滥用游标会影响系统性能。
一般来说，有一个共识：能不用游标就不要用游标。
事实上，编写SQL语句的时候大多数的情形下是没有必要使用游标的。
1.3 游标生命周期
游标的生命周期：
- 声明游标(Declare Cursor)
- 打开游标(Open Cursor)
- 提取游标(Fetch Cursor)
- 关闭游标(Close Cursor)
- 释放游标(Deallocate 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 ] ] ]
【说明】方括号中的关键之是可选的，具体作用如下：
-
作用域
- Local：游标作用域为局部，只在定义它的批处理、函数和存储过程中有效。
- Global：游标作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
- 默认值是Local
-
游标方向
- Forward_Only:指定游标智能从第一行滚到最后一行，种游标称为：只进游标。
- Scroll：指定游标在定义的数据集中向任何方向，或任何位置移动。
- 默认是Forward_Only
-
游标读取的数据和基表数据关系
-
Static表明：游标一旦指定了select查询出的结果集，之后任何对于基表（即：select语句所查询的表）内数据的更改不会影响到游标的内容。该种游标称为静态游标
-
Dynamic和Static完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变。该种游标称为动态游标。
-
KeySet:指明当再游标被打开时游标中的列的顺序时固定的，游标只维持其所依赖的基表的键
-
Fast_Forward:指明一个Forward_Only且Read_Only型游标。注意：一旦声明了Fast_Forward,则之前就不可以选择Scroll类型的游标。同样，在之后也就不能使用Scroll_Locks和Optimistic选项。
-
默认值是Dynamic
-
-
游标是否锁定数据
-
Read_Only意味着声明的游标只能读取数据,游标不能做任何更新操作
-
Scroll_Locks是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功
-
Optimistic是相对比较好的一个选择，不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新或删除会不成功，如果，底层表数据未更新，则游标内表数据可以更新或删除
-
-
Type_Warning:指明若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端。
-
Update[Of colunm_name[,...n]]:定义利用游标可更新的列。若果列出了Of colunm_name[,...n]，则只允许修改列出的列
-
其实，从上面可以看出游标的声明是有许多的可选项。
但是一般来说，只要记住游标声明的默认值。一般实际开发中，如无必要则使用默认值即可。
②打开游标
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 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 @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定义游标