SQL Server从入门到项目实践（超值版）读书笔记 24

第11章 游标的应用

🎉

学习指引：

用户在数据库中查询时，结果都是一组数据或者说是一个数据集合。如果数据量非常大，就需要使用游标来逐条读取查询结果集中的记录。

本章就来介绍游标的基本操作：包括游标的概念、游标的分类、游标的操作等。

11.1 什么是游标

游标是一种临时的数据库对象，既可以用来存放在数据库表中的数据行副本，也可以只想存储在数据库中的数据行的指针，游标提供了在逐行的基础上操作表中数据的方法。

11.1.1 游标的概念

在数据库中，游标是一个非常重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实质上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标总是与一条SQL选择语句相关联，因为游标由结果集（可以是零条，一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

另外，游标的一个常见用途就是保存查询结果，以便下次使用。游标的结果集是由SELECT语句产生的，如果处理过程需要重复使用同一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快得多。

默认情况下，游标可以返回当前执行的行记录，但只能返回一行记录。如果想要返回多行，需要不断地滚动游标，把需要的数据查询一遍。用户可以操作游标所在位置行的记录。例如，把返回记录作为另一个查询的条件等。

11.1.2 游标的优点

游标提供了一种机制，它能从包括多条数据记录的结果集中每次提取一条记录，从而解决数据库中面向单条记录数据处理的难题。

使用游标处理数据记录的有点有以下几点：

1. 允许应用程序对查询语句SELECT返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；
2. 提供对基于游标位置而对表中数据进行删除或更新的能力；
3. 游标能够把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。

11.1.3 游标的类型

SQL SERVER提供了4种类型的游标，分别为静态游标、动态游标、只进游标和键集驱动游标。这些游标的检测结果集变化的能力和内存占用的情况都有所不同，数据源没有办法通知当前提取行的更改，游标检测这些变化的能力也受事务隔离级别的影响。

11.1.3.1 静态游标

SQL SERVER静态游标始终是只读的，其完整结果集在打开游标时建立在tempdb中。静态游标总是按照打开游标时的原样显示结果集。静态游标不反映在数据库中所所做的任何影响结果集成员身份的更改，也不反映对组成结果集的行的列值所做的更改。

静态游标不会显示打开游标以后在数据库中新插入的行，即使这些行符合游标SELECT语句的搜索条件。如果组成结果集的行被其他用户更新，则新的数据值不会显示在静态游标中。静态游标会显示打开游标以后从数据库中删除的行。静态游标中不反映UPDATE\INSERT或者DELETE操作（除非关闭游标然后重新打开），甚至不反映使用打开游标的同一连接所做的修改。

11.1.3.2 动态游标

动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE\INSERT和DELETE语句均通过游标可见。如果使用API函数（如SQLSetPos）或Transact-SQL WHERE CURRENT OF子句通过游标进行更新，它们将立即可见。在游标外部所做的更新直到提交时才可见，除非将游标的事务隔离级别设为未提交读。

11.1.3.3 只进游标

只进游标不支持滚动，它只支持游标从头到尾的顺序提取。行只在从数据库中提取出来后才能检索。对所有由当前用户发出或由其他用户提交、并影响结果集中的行的UPDATE\INSERT和DELETE语句，其效果在这些行从游标中提取时是可见的。

由于游标无法向后滚动，则在提取行后对数据库中的行进行的大多数更改通过游标均不可见。当值用于确定所修改的结果集（例如更新聚集索引涵盖的列）中行的位置时，修改后的值通过游标可见。

11.1.3.4 键集驱动游标

该游标中各行的成员身份和顺序是固定的，键集驱动的游标由一组唯一标识符（键）控制，这组键称为键集，键是根据以唯一方式标识结果集中各行的一组列生成的。键集是打开游标时来自符合SELECT语句要求的所有行中的一组键值，键集驱动的游标对应的键集是打开该游标时在tempdb中生成的。

11.1.4 游标的属性

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。游标具有以下属性：

1. 游标是只读的，也就是不能更新它；
2. 游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；
3. 避免在已经打开游标的表上更新数据。

11.1.5 游标的实现

游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。一个完整的游标由5部分组成，实现的过程应符合以下顺序：

1. 声明游标：DECLARE
2. 打开游标：OPEN
3. 读取游标：FETCH
4. 关闭游标：CLOSE
5. 删除游标：DEALLOCATE

使用游标的优点在于它可以定位到结果集中的某一行，并可以对该行数据执行操作，为用户在处理数据的过程中提供了很大的方便。

11.2 游标的基本操作

介绍完游标的概念和分类等内容之后，下面将介绍如何操作游标，对于游标的操作主要由以下内容：声明游标、打开游标、读取游标、关闭游标和删除游标。

11.2.1 声明游标

在SQL SERVER中，声明游标可以使用DECLARE CURSOR语句，该语句有两种语法声明格式，分别为ISO标准语法和SQL扩展语法，一般常用的是SQL扩展语法：

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]]]

主要参数说明：

• DECLARE cursor_name：指定一个游标的名称，其游标名称必须符合标识符规则。
• LOCAL：定义游标的作用域仅限在其所在的批处理、储存过程或触发器中，当建立游标在存储过程执行结束后，游标会自动释放。
• GLOBAL：指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标尽在脱接时隐形释放。
• FORWARD_ONLY：指定游标只能从第一行滚动到最后一行。
  • FETCH NEXT是唯一支持的提取选项。如果在指定FORWARD_ONLY时不指定STATIC\KEYSET和DYNAMIC关键字，则游标作为DYNAMIC游标进行操作。
  • 如果FORWARD_ONLY和SCROLL均未指定，则除非指定STATIC\KEYSET或DYNAMIC关键字，否则默认为FORWARD_ONLY。
  • STATIC\KEYSET和DYNAMIC游标默认为SCROLL。
  • 与ODBC和ADO这类数据库API不同，STATIC\KEYSET和DYNAMIC Transact-SQL游标支持FORWARD_ONLY。
• STATIC：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的这一临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。
• KEYSET：指定当游标打开时，游标中行的成员身份和顺序已经固定。对行进行唯一标识的键集内置在tempdb内一个称为keyset的表中。对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交），可以在用户滚动游标时看到。其他用户执行的插入是不可见的（不能通过Transact-SQL服务器游标执行插入）。如果删除行，则在尝试提取行时返回值为-2的@@FETCH_STATUS。从游标以外更新键值类似于删除旧行然后再插入新行。具有新值的行是不可见的，并在尝试提取具有旧值的行时，将返回值为-2的@@FETCH_STATUS。如果通过指定WHERE CURRENT OF子句利用游标来完成更新，则新值是可见的。
• DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持ABSOLUTE提取选项。
• FAST_FORWARD：指定启用了性能优化的FORWARD_OMLY\READ_ONLY游标。如果指定了SCROLL或FOR_UPDATE，则不能指定FAST_FORWARD。
• SCROLL_LOCKS：指定通过游标进行的定位更新或删除一定会成功。将行读入游标时SQL SERVER将锁定这些行，以确保随后可对它们进行修改。如果还指定了FAST_FORWARD或STATIC，则不能指定SCROLL_LOCKS。
• OPTIMISTIC：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入时，SQL SERVER不锁定行。它改用timestamp列值的比较结果来确定行读入游标后什邡发生了修改，如果表不含timestamp列，它改用校验和值进行确定。如果已修改该行，则尝试进行的定位更新或删除将失败。如果还指定了FAST_FORWARD，则不能指定OPTIMISTIC。
• TYPE_WARNING：指定将游标从所请求的类型隐式转换为另一种类型时，向客户端发送警告信息。
• select_statement：是定义游标结果集的标准SELECT语句。

例：声明名称为cursor_emp的标准游标

DECLARE cursor_emp CURSOR FOR
SELECT * FROM employee

例：声明名称为cursor_emp_01的只读游标

DECLARE cursor_emp_01 CURSOR FOR
SELECT * FROM employee
FOR READ ONLY

例：声明名称为cursor_emp_02的更新游标

DECLARE cursor_emp_02 CURSOR FOR
SELECT * FROM employee
FOR UPDATE

11.2.2 打开游标

在使用游标之前，必须先打开游标，用户可以使用OPEN命令打开游标

OPEN [GLOBAL] cursor_name|cursor_variable_name

主要参数说明：

• GLOBAL：指定cursor_name是全局游标；
• cursor_name：已声明的游标名称。如果全局游标和局部游标都是用cursor_name作为其名称，那么，如果指定了GLOBAL，则为全局游标，否则，就是局部游标；
• cursor_variable_name：游标变量的名称，该变量引用一个游标；

例：打开上一小节中声明的名为cursor_emp的游标

OPEN cursor_emp

11.2.3 读取游标

读取游标，就是读取游标中的数据，当打开游标之后，就可以读取游标中的数据，使用FETCH命令可以读取游标中的某一行数据，

FETCH
[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]
{{[GLOBAL] cursor_name}|@cursor_variable_name}
[INTO @varibale_name [,...n]]

主要参数说明：

• NEXT：紧跟当前行返回结果行，并且当前行递增为返回行。
  • 如果FETCH NEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。
• PRIOR：返回紧邻当前行前面的结果行，并且当前行递减为返回行。
  • 如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。
• FIRST：返回游标中的第一行并将其作为当前行。
• LAST：返回游标中的最后一行并将其作为当前行。
• ABSOLUTE{n|@nvar}：如果n或@nvar为正，则返回从游标头开始向后的第n行，并将返回行变成新的当前行。如果n或@nvar为负，则返回从游标末尾向前的第n行，并将返回行变成新的当前行。如果n或@nvar为0，则不返回行。n必须是整数常量，并且@nvar的数据类型必须为smallint\tinyint或int。
• RELATIVE{n|@nvar}：如果n或@nvar为正，则返回从当前行开始向后的第n行，并将返回行变成新的当前行。如果n或@nvar为负，则返回从当前行向前的第n行，并将返回行变成新的当前行。如果n或@nvar为0，则不返回行。在对游标进行第一次提取时，如果在将n或@nvar设置为负数或0的情况下指定FETCH RELATIVE，则不返回行。n必须是整数常量，并且@nvar的数据类型必须为smallint\tinyint或int。
• GLOBAL：指定cursor_name是全局游标。
• cursor_name：要从中进行提取的打开的游标的名称。如果全局游标和局部游标都使用cursor_name作为它们的名称，那么指定GLOBAL时，cursor_name指的时全局游标；否则为局部游标。
• @cursor_variable_name：游标变量名，引用要从中进行提取操作的打开的游标。
• INTO@variable_name[,...n]：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型匹配，或时结果集列数据类型所支持的隐式转换。变量的数目必须与游标选择列表中的列数一致。

例：使用名为cursor_emp的游标，检索employee表中的记录

FETCH NEXT FROM cursor_emp
WHILE @@FETCH_STATUS = 0  --全局变量，成功返回0
BEGINFETCH NEXT FROM cursor_emp
END

11.2.4 关闭游标

当游标使用完毕后，可以使用CLOSE语句关闭游标，但是不释放游标占用的系统资源：

CLOSE [GLOBAL] cursor_name|cursor_variable_name

主要参数说明：

• GLOBAL：指定cursor_name为全局游标。
• cursor_name：已声明的游标名称。如果是全局游标和局部游标都使用cursor_name作为其名称，那么指定了GLOBAL，则说明是全局游标，否则，就是局部游标。
• cursor_variable_name：游标变量的名称，该变量引用一个游标。

例：关闭名称为cursor_emp的游标

CLOSE cursor_emp

11.2.5 删除游标

当游标被关闭后，并没有在内存中释放所占用的系统资源。要想释放游标所占用的系统资源，可以使用DEALLOCATE命令释放游标。

DEALLOCATE [GLOBAL] cursor_name|@cursor_variable_name

主要参数说明：

• cursor_name：已声明的游标名称。当同时存在以cursor_name作为名称的全局游标和局部游标时，如果指定GLOBAL，则表明指向全局游标，否则，指向局部游标；
• @sursor_variable_name：游标变量名称。必须为cursor类型。
• DEALLOCATE@cursor_variable_name语句只删除对游标变量名称的引用。直到批处理、存储过程或触发器结束时变量离开作用域，才释放变量。

例：使用DEALLOCATE语句释放名称为cursor_emp的变量

DEALLOCATE cursor_emp

/***************************************************************/

下面，我们举个综合例子，来更好的理解游标的作用。

/***************************************************************/

我们依然用数据表employee来演示游标综合案例

游标：定位到结果集中的某一行。

游标分类：

1. 静态游标(Static)：在操作游标的时候，数据发生变化，游标中数据不变；
2. 动态游标(Dynamic)：在操作游标的时候，数据发生变化，游标中数据改变，声明时不主动标注，默认动态游标；
3. 键集驱动游标(KeySet)：在操作游标的时候，被标识的列发生改变，游标中数据改变，其他列游标中数据不变；

• 创建一个名为mycur的滚动游标，在表employee的name字段中移动

--声明游标：（scroll：滚动游标，没有scroll，只进游标）
DECLARE  mycur CURSOR SCROLL
FOR select name FROM employee

执行后，游标mycur被创建成功。创建好的游标是不能被直接使用的，在使用游标前，还必须先打开游标

OPEN mycur

游标打开后，可以做以下几个动作

• 提取第一行数据：

FETCH FIRST FROM mycur

• 提取最后一行数据

FETCH LAST FROM mycur

• 提取绝对第n行

FETCH ABSOLUTE 4 FROM mycur

• 提取相对第n行(当前行下移）

FETCH RELATIVE 4 FROM mycur

• 下移一行

FETCH NEXT FROM mycur

• 上移一行

FETCH PRIOR FROM mycur

• 提取游标数据存入变量，进行查询所有列信息

declare @name varchar(20)
FETCH ABSOLUTE 4 FROM mycur into @name
SELECT * FROM employee WHERE name=@name

• 遍历游标

DECLARE @NAME VARCHAR(20)
FETCH ABSOLUTE 1 FROM mycur INTO @NAME --首先调整游标位置到第一行
--@@fetch_status：0 提取成功；-1 提取失败；-2 不存在
WHILE @@FETCH_STATUS = 0
BEGINPRINT'提取成功：'+@NAMEFETCH NEXT FROM mycur INTO @NAME
END

• 利用游标进行数据的修改

DECLARE @NAME VARCHAR(20)
FETCH ABSOLUTE 2 FROM mycur  INTO @NAME
UPDATE employee SET job='销售经理',salary=4000 
WHERE CURRENT OF mycur --游标停哪行，就修改哪行SELECT * FROM employee WHERE NAME=@NAME

• 利用游标进行数据的删除

FETCH ABSOLUTE 7 FROM mycur
DELETE FROM employee WHERE CURRENT OF mycur  --游标停哪里，就删哪里

编号108的王古林已被删除

• 创建一行多列的游标，循环显示多列数据

DECLARE MYCUR1 CURSOR SCROLL
FOR SELECT code,name,salary FROM employeeOPEN MYCUR1DECLARE @CODE VARCHAR(20),@NAME VARCHAR(20),@SALARY INT
FETCH ABSOLUTE 1 FROM MYCUR1 INTO @CODE,@NAME,@SALARY
WHILE @@FETCH_STATUS = 0
BEGINPRINT'编号：'+@code+',姓名：'+@name+',工资：'+CAST(@salary AS VARCHAR(20))FETCH NEXT FROM MYCUR1 INTO @CODE,@NAME,@SALARY
END

不使用后需要关闭游标，关闭后的游标如果要使用，需要再次打开

CLOSE mycur

最后删除游标。删除后的游标如果要使用，需要再次声明

DEALLOCATE mycur