目录
前言
当检索的数据只有一条记录时，所编写的事务语句代码往往使用 SELECT语句。但是常常会遇到这样的情况，当我们需要从某一结果集中逐一地读取一条记录，那么我们应该怎么办呢？游标（Cursor）为我们提供了一种极为优秀的解决方案。实际上，游标是一种能够从包括多条数据记录的结果集中每次提取一条记录的机制。
本文主要是对服务器游标进行学习和记录。
一、什么是游标
游标是处理数据的一种方法，它允许应用程序对查询语句 SELECT返回的结果集中的每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作。为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或多行向前或向后浏览数据的能力，我们可以游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。因此，正式游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，是两个数据处理方式能够沟通。
游标的实现功能：
- 允许定位在结果集的特定位置。
- 从结果集的当前位置检索一行或多行。
- 支持对结果集中当前位置的行进行数据处理。
- 为由其它用户对显示在结果集中的数据库数据所作的更改提供不同级别的可见性支持。
- 提供脚本、存储过程和触发器中使用的访问结果集中的数据的 T-SQL 语句。
二、游标的类型
SQL SERVER 支持三种类型的游标： T-SQL 游标、API 服务器游标和客户游标。
1、T-SQL 游标
T-SQL游标是由 DECLARE CURSOR 语法定义，主要用在 T-SQL脚本、存储过程和触发器中。T-SQL游标主要用在服务器上，由从客户端发送给服务器的 T-SQL语句或批处理、存储过程、触发器中的 T-SQL进行管理。T-SQL游标不支持提取数据块或多行数据。
2、API 服务器游标
API 游标支持在 OLE DB、ODBC 以及 DB_library 中使用游标函数，主要用在服务器上。每一次客户端应用程序调用 API 游标函数，SQL SERVER的 OLE DB 提供者、ODBC 驱动器或DB_library 的动态链接库（DLL）都会将这些客户请求传送给服务器以对 API 游标进行处理。
3、客户游标
客户游标主要是当在客户机上缓存结果时才使用。客户游标仅支持静态游标而非动态游标。由于服务器不支持所有的 T-SQL语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为，通常情况下服务器游标支持绝大多数的游标操作。
服务器游标，也称为后台游标，包含T-SQL 游标 和API 服务器游标。而客户游标被称为前台游标。
服务器游标通常包含 静态游标、动态游标、只进游标和键集驱动游标 4种。
- 静态游标：静态游标的完整结果集将打开游标时建立的结果集存储在临时表中。静态游标始终是只读的，总是按照打开游标时的原样返回结果集。静态游标不会反映数据库种做的任何修改。
- 动态游标：与静态游标恰好相反，当滚动游标时动态游标反映结果集中的所有更改。
- 只进游标：只进游标不支持滚动，只支持游标从头到尾提取数据行，也可反映结果集所作的所有更改。
- 键集驱动游标：键集驱动游标同时具有静态游标和动态游标的特点。
三、游标的操作
游标的生命周期有5个阶段：声明游标、打开游标、提取数据、关闭游标、释放游标。
1、声明游标
声明游标是指用 DECLARE 语句创建一个游标。声明游标主要包括以下内容：游标名称、数据来源、选取条件和属性。在使用一个游标之前，首先需要声明一个游标。
-- 游标声明语法格式
DECLARE 游标名称 CURSOR
[ LOCAL | GLOBAL ] /* 游标的作用域 */
[ FORWORD_ONLY | SCROLL ] /* 游游标的移动方向 */
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] /* 游标的类型 */
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] /* 游标的访问类型 */
[ TYPE_WARNING] /* 类型转换警告语句 */
FOR SELECT 语句 /* SELECT查询语句 */
[ FOR { READ ONLY | UPDATE [OF 列名称]}][,...n] /* 可修改的列 */
-- LOCAL：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
-- GLOBAL：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
-- FORWORD_ONLY：指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only。
-- STATIC：静态游标
-- KEYSET：键集游标
-- DYNAMIC：动态游标，不支持Absolute提取选项
-- FAST_FORWARD：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
-- READ_ONLY：不能通过游标对数据进行删改。
-- SCROLL_LOCKS：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
-- OPTIMISTIC：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
-- TYPE_WARNING：指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
-- FOR { READ ONLY | UPDATE [OF 列名称]}][,...n：定义游标中可更新的列。
-- 示例：声明一个名为 Sh_Cursor 的游标，用以查询计算机专业所有学生的信息。要求改游标是动态的、可向后滚动，其中的 birthday 列数据可以修改。
declare Sh_Cursor cursor
dynamic for
select * from student where specialty = '计算机'
for update of birthday
2、打开游标
声明一个游标后，如果想要使用游标，必须先要打开这个游标，打开游标使用 OPEN命令。
-- 语法格式
OPEN { { [ GLOBAL ] 游标名称 | @游标变量名称 } }
-- @游标变量名称：如果游标声明中有变量，则将变量值带入。
- 如果声明游标使用 STATIC 类型，则 打开游标时产生一个临时表来存放结果集；
- 如果声明游标使用 KEYSET 类型，则 OPEN 产生一个临时表来存放键值。
- 所有的临时表都存放在 tempdb 数据库中。
在游标被打开后，全局变量 @@CURSOR_ROWS 用来记录游标内的数据行数，其返回值有4 种。
| 返回值 | 描述 |
|---|---|
| -m | 表示仍在从基础表向游标读入数据，m表示当前在游标中的数据行数 |
| -1 | 该游标是一个动态游标，其返回值无法确定 |
| 0 | 无符合调剂的记录或游标已经关闭 |
| n | 从基础表向游标读入数据已结束，n 为游标中已有的数据记录行数 |
-- 示例：打开游标 Sh_cursor，输出游标中的行数。
open Sh_cursor
select '游标Sh_cursor数据行数' = @@CURSOR_ROWS
go
3、读取游标
当游标被打开后，就可以使用 FETCH 命令从游标中读取数据了。
-- 语法格式
FETCH
[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE{ n | @nvar } | RELATIVE { n | @nvar } ] FROM ] /* 读取数据的位置 */
{ { [ GLOBAL] 游标名称} | @游标变量名称 }
[ INTO @游标变量名称 ] [,...n] /* 将读取的游标数据存放到指定变量中 */-- NEXT：当前位置的下一行，并增加当前行数为返回行行数。如果 FETCH NEXT 是第一次读取游标中的数据，则返回第一行而不是第二行。
-- PRIOR：返回当前行的上一行，并减少当前行数为返回行行数。如果 FRTCH PRIOR 是第一次读取游标中的数据，则没有返回数据，并把游标设置为第一行。
-- FIRST：返回游标中的第一行。
-- LAST：返回游标中的最后一行。
-- ABSOLUTE：从游标的第一行开始数，第 n | @nvar 行数据。
-- RELATIVE：从当前位置数，第 n | @nvar 行数据。
-- INTO：将提取到的数据，放到 @游标变量名称 中。
注：@@FETCH_STATUS 全局变量返回上次执行 FETCH 命令的状态。在每次用 FETCH 从游标中读取数据时，都应该检查该变量，以确定上次 FETCH 操作是否成功，来决定任何进行下一步处理。
-- 示例：从游标 Sh_Cursor中读取数据，并查看 FETCH命令的执行状态。
fetch next from Sh_Cursor
select 'next_fetch的执行情况' = @@FETCH_STATUS
go
4、关闭游标
在读取完游标中的数据之后，必须关闭游标来释放游标所占用的资源。使用 CLOSE 命令关闭游标，但关闭游标本身不释放资源。
-- 语法格式
CLOSE { { [ GLOBAL ] 游标名称 | @游标变量名称 } }
-- 参数的含义与 OPEN命令相同。
-- 示例：关闭游标 Sh_Cursor。
close Sh_Cursor
go
5、释放游标
在游标被关闭后，游标的定义还在，需要使用时可以再 OPEN打开游标进行使用。确定游标不再使用了，就要使用 DEALLOCATE 命令来释放数据结构和游标所加的锁。
-- 语法格式
DEALLOCATE { { [ GLOBAL ] 游标名称 | @游标变量名称 } }
-- 示例：释放游标 Sh_Cursor。
deallocate Sh_Cursor
go
6、游标的完整实例
-- 示例：声明一个游标 Sh1_Cursor，只显示储户表中第三行和第五行数据（表 depositor）。
declare Sh1_Cursor cursor
static for
select * from depositor
open Sh1_Cursor
fetch absolute 3 from Sh1_Cursor
fetch absolute 5 from Sh1_Cursor
close Sh1_Cursor
deallocate Sh1_Cursor
-- 示例：首先显示身份证号为 130*****20120的储户的全部账号信息；声明游标 Sh2_Cursor，将此储户的第一个账户的余额加500，第二个账户的余额减500；再次显示该储户的全部账号信息（表 account）。
select * from account where IDNO = '130*****20120'
declare Sh2_Cursor cursor
dynamic for
select * from account where IDNO = '130*****20120'
for update of balance
open Sh2_Cursor
fetch next from Sh2_Cursor
update account set balance = balance + 500 where current of Sh2_Cursor
fetch next from Sh2_Cursor
update account set balance = balance - 500 where current of Sh2_Cursor
close Sh2_Cursor
deallocate Sh2_Cursor
select * from account where IDNO = '130*****20120'
若有不足之处，欢迎大佬斧正。
2895
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
