存储过程——游标

2020-05-27
2020年5月27日 21:10:00

1. 游标简介

1.0 理解定义

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

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

在编程中,我们使用诸如forwhile之类的循环一次遍历一项,游标遵循相同的方法。当在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 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. 参考