sql server 游标的知识

一：认识游标

  游标是SQL Server的一种数据访问机制，它允许用户访问单独的数据行。用户可以对每一行进行单独的处理，从而降低系统开销和潜在的阻隔情况，用户也可以使用这些数据生成的SQL代码并立即执行或输出。

1.游标的概念

 游标是一种处理数据的方法，主要用于存储过程，触发器和 T_SQL脚本中，它们使结果集的内容可用于其它T_SQL语句。在查看或处理结果集中向前或向后浏览数据的功能。类似与C语言中的指针，它可以指向结果集中的任意位置，当要对结果集进行逐条单独处理时，必须声明一个指向该结果集中的游标变量。

 SQL Server 中的数据操作结果都是面向集合的，并没有一种描述表中单一记录的表达形式，除非使用WHERE子句限定查询结果，使用游标可以提供这种功能，并且游标的使用和操作过程更加灵活、高效。

2.游标的优点

 SELECT 语句返回的是一个结果集，但有时候应用程序并不总是能对整个结果集进行有效地处理，游标便提供了这样一种机制，它能从包括多条记录的结果集中每次提取一条记录，游标总是与一跳SQL选择语句相关联，由结果集和指向特定记录的游标位置组成。使用游标具有一下优点：

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

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

(3).游标作为数据库管理系统和应用程序设计之间的桥梁，将两种处理方式连接起来。

3.游标的分类

  SQL Server支持3中游标实现：

(1).Transact_SQL游标

  基于DECLARE CURSOR 语法，主要用于T_SQL脚本，存储过程和触发器。T_SQL游标在服务器上实现，并由从客户端发送到服务器的T_SQL语句管理，它们还可能包含在批处理，存储过程或触发器中。

(2).应用程序编程接口(API)服务器游标

  支持OLE DB和ODBC中的API游标函数，API服务器游标在服务器上实现。每次客户端应用程序调用API游标函数时，SQL Server Native Client OLE DB访问接口或ODBC驱动程序会把请求传输到服务器，以便对API服务器游标进行操作。

(3).客户端游标

  由SQL Server Native Client ODBC驱动程序和实现ADO API的DLL在内部实现。客户端游标通过在客户端高速缓存所有结果集中的行来实现。每次客户端应用程序调用API游标函数时，SQL Server Native Client ODBC驱动程序或ADO DLL会对客户端上告诉缓存的结果集中的行执行游标操作。

  由于T_SQL游标和服务器游标都在服务器上实现，所以它们统称为服务器游标。

  ODBC和ADO定义了 Microsoft SQL Server 支持的4种游标类型，这样就可以为T_SQL游标指定4种游标类型。

SQL Server支持的4种API服务器游标的类型是：

(i).只进游标

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

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

(ii).静态游标

  SQL Server静态游标始终是只读的。其完整结果集在打开游标时建立在tempdb中，静态游标总是按照打开游标时的原样显示结果集。

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

(iii).由键驱动的游标

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

(IV).动态游标

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

回到顶部

二：游标的基本操作

1.声明游标

 游标主要包括游标结果集和游标位置两部分，游标结果集是定义游标的SELECT语句返回的行集合，游标位置则是指向这个结果集中的某一行的指针。

 使用游标之前，要声明游标，SQL Server中声明使用DECLARE CURSOR语句，声明游标包括定义游标的滚动行为和用户生成游标所操作的结果集的查询，其语法格式如下：

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

cursor_name:是所定义的T_SQL 服务器游标的名称。

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 T_SQL游标支持FORWARD_ONLY。

STATIC：定义一个游标，以创建将又该游标使用的数据临时复本，对游标的所有请求都从tempdb中的这以临时表中不得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。

KEYSET:指定当游标打开时，游标重的行的成员身份和顺序已经固定。对行进行唯一标识的键值内置在tempdb内一个称为keyset的表中。

DYNAMIC:定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改，动态游标不支持ABSOLUTE提取选项。

FAST_FORWARD：指定启动了性能优化的FORWARD_ONLY、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_fruit的游标

USE sample_db;
GO
DECLARE cursor_fruit CURSOR FOR
SELECT f_name,f_price FROM fruits;

2.打开游标

 在使用游标之前必须先打开游标，打开游标的语法如下：

 OPEN [ GLOBAL ] cursor_name | cursor_variable_name;

GLOBAL:指定cursor_name是全局游标。

cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标，否则cursor_name指的是局部游标。

cursor_variable_name：游标变量的名称。

【例】打开上例声明的名称为cursor_fruit的游标  

USE sample_db;
GO
OPEN cursor_fruit;

3.读取游标中的数据

 打开游标之后就可以读取游标中的数据了，FETCH命令可以读取游标中的某一行数据。FETCH的语法如下：

ETCH 
[ [ NEXT | PRIOR | FIRST | LAST 

         | ABSOLUTE { n | @nvar }

         | RELATIVE { n | @nvar }
    ]
FROM
]
{ { [GLOBAL ] cursor_name } | @cursor_variable_name}
[ INTO @variable_name [ ,...n ] ]

NEXT:紧跟当前行返回结果行，并且当前行递增为返回行，如果FETCH NEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。

PRIOR：返回紧邻当前行前面的结果行，并且当前行递减为返回行，如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。

FIRST：返回游标中的第一行并将其作为当前行。

LAST:返回游标中的最后一行并将其作为当前行。

ABSOLUTE { n | @nvar }：如果n或@nvar为正，则返回从游标头开始向后n行的第n行，并将返回行变成新的当前行。如果n或@nvar为负，则返回从游标末尾开始向前的n行的第n行，并将返回行变成新的当前行。如果n或@nvar为0，则不返回行。n必须是整数常量，并且@nvar的数据类型必须为int、tinyint或smallint.

RELATIVE { n | @nvar }：如果n或@nvar为正，则返回从当前行开始向后的第n行。如果n或@nvar为负，则返回从当前行开始向前的第n行。如果n或@nvar为0，则返回当前行,对游标第一次提取时，如果在将n或@nvar设置为负数或0的情况下指定FETCH RELATIVE,则不返回行，n必须是整数常量，@nvar的数据类型必须是int、tinyint或smallint.

GLOBAL：指定cursor_name是全局游标。

cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标，否则cursor_name指的是局部游标。

@cursor_variable_name：游标变量名，引用要从中进行提取操作的打开的游标。

INTO @variable_name [ ,...n ]：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型相匹配，或是结果集列数据类型所支持的隐士转换。变量的数目必须与游标选择列表中的列数一致。

【例】使用名称为cursor_fruit的光标，检索fruits表中的记录，输入如下： 

USE sample_db;
GO
FETCH NEXT FROM cursor_fruit
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM cursor_fruit
END;

4.关闭游标

 SQL Server 在打开游标之后，服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以在不使用游标的时候，可以将其关闭，以释放游标所占用的服务器资源，关闭游标使用CLOSE语句。语法格式如下： 

CLOSE [ GLOBAL ] cursor_name | cursor_variable_name

【例】关闭名称为cursor_fruit的游标

1	CLOSE cursor_fruit;

5.释放游标

 游标操作的结果集空间虽然被释放了，但是游标本身也会占用一定的计算集资源，所以使用完游标之后，为了收回被游标占用的资源，应该将游标释放。释放游标使用DEALLOCATE语句，语法格式如下：

 DEALLOCATE [GLOBAL] cursor_name | @ccursor_variable_name

@ccursor_variable_name：游标变量的名称，@ccursor_variable_name必须为cursor类型。

DEALLOCATE @ccursor_variable_name 语句只删除对游标变量名称的引用，直到批处理、存储过程或触发器结束时变量离开作用域，才释放变量。

【例】使用DEALLOCATE语句释放名称为cursor_fruit的变量，输入如下：

 DEALLOCATE cursor_fruit;

回到顶部

三:游标的运用

1.使用游标变量

 声明变量用DECLARE，为变量赋值可以用set或SELECT语句，对于游标变量的声明和赋值，其操作基本相同。在具体使用时，首先要创建一个游标，将其打开后，将游标的值赋给游标变量，并通过FETCH语句从游标变量中读取值，最后关闭释放游标。

【例】声明名称为@varCursor的游标变量，输入如下：

DECLARE @varCursor Cursor --声明游标变量

DECLARE cursor_fruit CURSOR FOR --创建游标

SELECT f_name,f_price FROM fruits;

OPEN cursor_fruit --打开游标

SET @varCursor=cursor_fruit --为游标变量赋值

FETCH NEXT FROM @varCursor --从游标变量中读取值

WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行成功

BEGIN

FETCH NEXT FROM @varCursor --读取游标变量中的数据

END

CLOSE @varCursor --关闭游标

DEALLOCATE @varCursor; --释放游标

2.用游标为变量赋值

 在游标的操作过程中，可以使用FETCH语句将数据值存入变量，这些保持表中列值的变量可以在后面的程序中使用。

【例】创建游标cursor_variable,将fruits表中的记录f_name,f_price值赋给变量@fruitName和@fruitPrice，并打印输出。

3.用ORDER BY 子句改变游标中的执行顺序

  游标是一个查询结果集，那么能不能对结果进行排序呢？答案是否定的。与基本的SELECT语句中的排序方法相同，ORDER BY子句添加到查询中可以对游标查询的结果排序。

 注意：只有出现在游标中的SELECT语句中的列才能作为ORDER BY 子句的排序列，而对与非游标的SELECT语句中，表中任何列都可以作为ORDER BY 的排序列，即使该列没有出现在SELECT语句的查询结果列中。

【例】声明名称为cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输入语句如下： 

4.用游标修改数据

【例】声明整型变量@sid=101,然后声明一个对fruits表进行操作的游标，打开该游标，使用FETCH NEXT方法来获取游标中的每一行的数据，如果获取到的记录的s_id的字段值与@sid值相同，将s_id=@sid的记录中的f_price修改为12.2，最后关闭释放游标，输入如下：

5.用游标删除数据

 使用游标删除数据时，既可以删除游标结果集中的数据，也可以删除基本表中的数据

【例】使用游标删除fruits表中s_id=102的记录，如下

 以上例子的sql脚本：

USE sample_db;
create TABLE fruits(
f_id int IDENTITY(1,1) PRIMARY KEY,--水果id
s_id int not null,        --供应商id
f_name varchar(255) not null,--水果名称
f_price decimal(8,2) not null --水果价格
);
insert into fruits (s_id,f_name,f_price) 
        values
        (101,'apple',5.8),
        (102,'blackberry',6.8),
        (105,'orange',4.5),
        (102,'banana',3.5),
        (103,'lemon',8.0),
        (104,'grape',7.6),
        (101,'melon',10.5);
--1.声明名称为cursor_fruit的游标
USE sample_db;
GO
DECLARE cursor_fruit CURSOR FOR
SELECT  f_name,f_price FROM fruits;
--2.打开游标
OPEN cursor_fruit;
--3.读取游标中的数据
--【例】使用名称为cursor_fruit的光标，检索fruits表中的记录，输入如下：
USE sample_db;
GO
FETCH NEXT  FROM cursor_fruit 
WHILE  @@FETCH_STATUS=0
BEGIN
    FETCH NEXT  FROM cursor_fruit
END;
--4.关闭关闭名称为cursor_fruit的游标
CLOSE cursor_fruit
--5.释放游标
DEALLOCATE cursor_fruit;
--游标的运用
--1.使用游标变量
--声明名称为@varCursor的游标变量
DECLARE @varCursor Cursor  --声明游标变量
DECLARE cursor_fruit CURSOR FOR    --创建游标
SELECT  f_name,f_price FROM fruits;
OPEN cursor_fruit  --打开游标
SET  @varCursor=cursor_fruit  --为游标变量赋值
FETCH NEXT FROM @varCursor   --从游标变量中读取值
WHILE @@FETCH_STATUS=0   --判断FETCH语句是否执行成功
BEGIN
    FETCH NEXT FROM @varCursor   --读取游标变量中的数据
END
CLOSE @varCursor    --关闭游标
DEALLOCATE @varCursor;    --释放游标 
--2.用游标为变量赋值
--创建游标cursor_variable,将fruits表中的记录f_name,f_price值赋给变量@fruitName和@fruitPrice，并打印输出。
DECLARE @fruitName varchar(50),@fruitPrice DECIMAL(8,2)
DECLARE cursor_variable CURSOR FOR
SELECT f_name,f_price FROM fruits
WHERE s_id=101;
OPEN cursor_variable
FETCH NEXT FROM cursor_variable
INTO @fruitName,@fruitPrice
PRINT '编号为101的供应商提供的水果种类和价格为：'
WHILE @@FETCH_STATUS=0
BEGIN 
    PRINT @fruitName+' '+STR(@fruitPrice,8,2)
FETCH NEXT FROM cursor_variable
INTO @fruitName,@fruitPrice
END
CLOSE cursor_variable
DEALLOCATE cursor_variable;
--3.用ORDER BY子句改变游标中的执行顺序
--声明名称为cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输入语句如下：
DECLARE cursor_order CURSOR FOR
SELECT f_id,f_name,f_price FROM fruits
ORDER BY f_price DESC
OPEN cursor_order
FETCH NEXT FROM cursor_order
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM cursor_order
CLOSE cursor_order
DEALLOCATE cursor_order;
--4.用游标修改数据
--【例】声明整型变量@sid=101,然后声明一个对fruits表进行操作的游标，打开该游标，
--使用FETCH NEXT方法来获取游标中的每一行的数据，
--如果获取到的记录的s_id的字段值与@sid值相同，将s_id=@sid的记录中的f_price修改为12.2，最后关闭释放游标，输入如下：
DECLARE @sid INT,@id INT =101
DECLARE cursor_fruit CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_fruit
FETCH NEXT FROM cursor_fruit INTO @sid
WHILE @@FETCH_STATUS=0
BEGIN
    IF @sid=@id
BEGIN 
    UPDATE fruits SET f_price=11.1 WHERE s_id=@id
END
FETCH NEXT FROM cursor_fruit INTO @sid
END
CLOSE cursor_fruit
DEALLOCATE cursor_fruit;
SELECT * FROM fruits where s_id=101;
--5.使用游标删除数据
--【例】使用游标删除fruits表中s_id=102的记录，如下
DECLARE @sid1 INT,@id1 int=102
DECLARE cursor_delete CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_delete
FETCH NEXT FROM cursor_delete INTO @sid1
WHILE @@FETCH_STATUS=0
BEGIN
    IF @sid1=@id1
BEGIN 
    DELETE FROM fruits where s_id=@id1
END
FETCH NEXT FROM cursor_delete INTO @sid1
END
CLOSE cursor_delete
DEALLOCATE cursor_delete;
SELECT * FROM fruits where s_id=102;