【SQL Server】游标原理、优缺点及使用

1 基本介绍

1.1 原理

        一般情况下，SQL查询结果都是多条纪录的结果集，而高级语言一次只能处理一条纪录，用游标机制，将多条纪录一次一条读取出来处理。从而把对集合的操作转化为对单个纪录的处理。游标使用的步骤如下：

1. 说明游标：说明游标的时候并不执行select语句。
   declare <游标名> cursor for <select语句>;

2. 打开游标：打开游标实际上是执行相应的select语句，把查询结果读取到缓冲区中。这时候游标处于活动状态，指针指向查询结果集的第一条纪录。
   open <游标名>;

3. 推进游标指针并读取当前纪录：用fetch语句把游标指针向前推进一条纪录，同时将缓冲区中的当前纪录读取出来送到变量中。fetch语句通常用在一个循环结构体中，通过循环执行fetch语句逐条取出结果集中的行进行处理。现在好多数据库中，还允许任意方向任意步长易懂游标指针，而不仅仅是把游标指针向前推进一行了。
   fetch <游标名> into <变量1>,<变量2>...

4. 关闭游标：用close语句关闭游标，释放结果集占用的缓冲区及其他资源。游标关闭后，就不再和原来的查询结果集相联系。但游标可以再次打开，与新的查询结果相联系。
   close <游标名>;

1.2 分类

MS SQL SERVER 支持三种类型的游标：Transact_SQL 游标，API 服务器游标和客户游标。

1. Transact_SQL 游标
   Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。**Transact_SQL **游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL 语句或是批处理、存储过程、触发器中的Transact_SQL 进行管理。Transact_SQL 游标不支持提取块或多行。

2. API 游标
   API游标支持在OLE DB， ODBC 以及DB_library 中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API 游标函数，MS SQL SEVER 的OLE DB 提供者、ODBC驱动器或DB_library 的动态链接库（DLL） 都会将这些客户请求传送给服务器以对API游标进行处理。

3. 客户游标
   客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact-SQL 语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。

   由于API 游标和Transact-SQL 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。

1.3 优缺点

1. 优点
   1）允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。

   2）提供对基于游标位置的表中的行进行删除和更新的能力。

   3）游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。

2. 缺点
   处理大数据量时，效率低下，占用内存大；一般来说，能使用其他方式处理数据时，最好不要使用游标，除非是当你使用while循环,子查询，临时表，表变量,自建函数或其他方式都无法处理某种操作的时候，再考虑使用游标。

2 使用说明

        使用游标的顺序： 声明游标、打开游标、读取数据、关闭游标、删除游标。

1.1 声明游标

1. 语句
   最简单游标声明：DECLARE <游标名>CURSOR FOR<SELECT语句>；其中select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询。
2. 例子：[已表2 AddSalary为例子]

Declare mycursor cursor for select * from AddSalary

这样我就对表AddSalary申明了一个游标mycursor

【高级备注】
DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT语句>
这里我说一下游标中级应用中的[INSENSITIVE]和[SCROLL]

（1）INSENSITIVE
表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。
另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。

1. 在SELECT语句中使用DISTINCT、GROUP BY、HAVING UNION语句；
2. 使用OUTER JOIN；
3. 所选取的任意表没有索引；
4. 将实数值当作选取的列。

（2）SCROLL

表明所有的提取操作（如FIRST、 LAST、PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT提取操作。由此可见，SCROLL极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再重开游标。

1.2 打开游标

OPEN mycursor

1.3 读取数据

1. 语句
   FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名 | @游标变量名 } [ INTO @变量名 [，…] ]

2. 参数说明
   NEXT 取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。
   INTO @变量名[,…]把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。

   现在我们就取出mycursor游标的数据吧！

   当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。就本例而言，可以使用下列操作读取第1行数据：
   **Eg: **Fetch next from mycursor 或则 Fetch first from mycursor 这样我就取出了游标里的数据，但是光光这样可不够，我们还需要将取出的数据赋给变量。

--声明2个变量
declare @O_ID NVARCHAR(20)
declare @A_Salary float
--将取出的值传入刚才声明的2个变量
Fetch next from mycursor into @ O_ID,@ A_Salary

1.4 关闭游标

CLOSE mycursor

1.5 删除游标

DEALLOCATE mycursor

1.6 实例训练

CREATE PROCEDURE PK_Test
AS
	--声明2个变量
	declare @ClNo varchar(20)  
	declare @ClName nvarchar(20)	 
	--声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
	declare mycursor cursor for select ClNo,ClName from Class	 
	--打开游标
	open mycursor	 
	--从游标里取出数据赋值到我们刚才声明的2个变量中
	fetch next from mycursor into @ClNo,@ClName	 
	--判断游标的状态
	-- 0 fetch语句成功    
	---1 fetch语句失败或此行不在结果集中    
	---2 被提取的行不存在
	while (@@fetch_status=0)
	begin	 
	--显示出我们每次用游标取出的值
	   print '游标成功取出一条数据'
	   print @ClNo
	   print @ClName	 
	--用游标去取下一条记录
	   fetch next from mycursor into @ClNo,@ClName
	end
	--关闭游标
	close mycursor
	--撤销游标
	DEALLOCATE mycursor 
GO

--执行存储过程PK_Test
exec PK_Test