游标概念

由select语句返回的结果集包括满足该语句的where子句中条件的所有行。但是有时候应用程序并不总能将整个结果集作为一个单元来处理，这些应用程序需要一种机制以便每次处理结果集中的一行活一部分行。游标就是提供这种机制的结果集扩展。
  游标主要用在存储过程、触发器和T-SQL脚本中。游标包括一下两个部分。

• 游标结果集(Cursor Result Set)由定义该游标的select语句返回的行的集合
• 游标位置(Cursor Position)指向这个集合中某一行的指针

游标具有以下优点：

• 允许定位在结果集中的特定行
• 从结果集的当前位置检索一行或多行
• 支持对结果集中当前位置的行进行数据修改
• 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持

使用游标

声明游标

游标在使用之前，必须先声明。其语法格式如下：

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

• LOCAL | GLOBAL (游标生存周期)
    LOCAL指定该游标的范围对在其中创建它的批处理、存储过程或触发器是局部的。
    GLOBAL指定该游标范围对连接是全局的。 在由此连接执行的任何存储过程或批处理中，都可以引用该游标名称。 该游标仅在断开连接时隐式释放。
  
• FORWARD_ONLY | SCROLL (读取方向)
    FORWARD_ONLY指定游标只能从第一行滚动到最后一行。 FETCH NEXT 是唯一支持的提取选项。
    SCROLL支持游标在定义的数据集中向任何方向，或任何位置移动（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）。
  
• STATIC | KEYSET | DYNAMIC | FAST_FORWARD(表内数据与游标读取出的数据的关系)
• STATIC：静态游标。当游标被建立时，将会创建SELECT语句所包含数据集的副本存入TempDB数据库中。你在操作游标的时候，不论如何操作数据库，游标中的数据集都不会变，并且此游标不允许修改。
• DYNAMIC：动态游标。和STATIC完全相反，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。
• FAST_FORWARD：**只进游标 **。只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。
• KEYSET：键集游标。将游标所在结果集的唯一能确定每一行的主键存入TempDB，结果集这些行数据被一组唯一标识符标识。对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交）可以在用户滚动游标时看到。 其他用户执行的插入是不可见的，因为该行没有被标识，除非重新打开游标才可见。如果查询引用了至少一个无唯一索引的表，则键集游标将转换为静态游标。
• READ_ONLY | SCROLL_LOCKS | OPTIMISTIC(是否允许在游标中修改数据)
• READ_ONLY：禁止通过该游标进行更新。 在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。
• SCROLL_LOCKS ：将行读入游标时，锁定这些行(行锁)，确保删除或更新一定会成功。
• OPTIMISTIC：当将行读入游标时，SQL Server 不锁定行。 它改为使用的比较时间戳列的值或如果表未包含任何校验和值时间戳列，来确定是否它已读入游标后已修改行。 如果读的时候该行已被修改过，尝试进行的定位更新或定位删除将失败。
• FOR UPDATE [ OF column_name [ ,...n ] ]
    定义游标中可更新的列。如果指定了 [ OF column_name [ ,...n ] ]，则只允许修改所列出的列，否则可以更新所有列。
• TYPE_WARNING：指定如果游标从所请求的类型隐式转换为另一种类型，则向客户端发送警告消息。

• STATIC 和 FAST_FORWARD 游标默认为 READ_ONLY。

• DYNAMIC 和 KEYSET 游标默认为 OPTIMISTIC。
• 如果在指定 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。
• 动态游标不支持ABSOLUTE提取选项。
• 如果指定了 SCROLL 或 FOR_UPDATE，则不能也指定FAST_FORWARD。
• FAST_FORWARD 和 FORWARD_ONLY 可以同时用在同一个 DECLARE CURSOR 语句中。
• 如果指定了 FAST_FORWARD 或 STATIC，则不能指定 SCROLL_LOCKS。

游标分为游标类型和游标变量。游标类型就是CURSRO关键字，正如下面所示。游标变量支持两种方式赋值，定义时赋值和先定义后赋值，定义游标变量像定义其他局部变量一样，在游标前加”@”。注意，如果定义全局的游标，只支持定义时直接赋值，并且不能在游标名称前面加“@”，两种定义方式如下：

-- 定义后直接赋值
DECLARE test_cursor CURSRO FOR select * from dbo.tb1

-- 先定义后赋值
DECLARE @test_cursor2 CURSRO 
SET @test_cursor2=CURSRO FOR select * from dbo.tb2

游标函数

（1）@@CURSOR_ROWS
  得到当前游标中存在的数据行数。注意：此变量为一个连接上的全局变量，因此只对应最后一次打开的游标。
  @@CURSOR_ROWS返回值为整数，意义如下：

（3）CURSOR_STATUS
  一个标量函数，它允许存储过程的调用方确定该存储过程是否已为给定的参数返回了游标和结果集。此函数的语法为：

CURSOR_STATUS   
     (  
          { 'local' , 'cursor_name' }   
          | { 'global' , 'cursor_name' }   
          | { 'variable' , 'cursor_variable' }   
     )  

• 'local'指定一个常量，该常量指示游标的源是一个本地游标名。
• 'global'指定一个常量，该常量指示游标的源是一个全局游标名。
• 'variable'指定一个常量，该常量指示游标的源是一个本地变量。
• cursor_variable：游标变量的名称。
• cursor_name：游标的名称。

CURSOR_STATUS返回类型为smallint

打开游标

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }  

• GLOBAL选项指定其后的游标为全局游标。
• cursor_name：已声明的游标的名称。
• cursor_variable_name：游标变量的名称，该变量引用一个游标。
• 当执行打开游标的语句时，服务器将执行声明游标时使用的select语句
• 如果声明游标中使用了STATIC关键字，则服务器会在TempDB中建立一个临时表，存放游标将要进行操作的结果集的副本。
• 如果使用 KEYSET 选项声明了游标，则服务器会在TempDB中建立一个临时表存放键值。
• 如果结果集中任意行的大小超过 SQL Server 表的最大行大小，OPEN 将失败。
• 利用OPEN语句打开游标后，游标位置位于查询结果集的第一行

提取数据

打开游标后，就可以利用fetch语句从查询结果集。使用fetch语句一次可以提取一条记录，具体的语法格式如下：

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

• NEXT：读取当前行的下一行，并把这个“下一行”置为当前行。next为默认的游标提取选项。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。
• PRIOR：读取当前行的上一行，并把这个“上一行”置为当前行。
• FIRST：返回游标中的第一行并将其作为当前行。
• LAST：返回游标中的最后一行并将其作为当前行。
• ABSOLUTE { n| @nvar}
• 如果n或@nvar为正数，提取从游标头开始的第n行，并将返回的行变成新的当前行；
• 如果n或@nvar为负数，提取从游标尾之前的第n行，并将返回的行变成新的当前行；
• 如果n或@nvar为0，则没有行返回；
• n必须为整型常量，@nvar必须为smallint、tinyint或nit；
• RELATIVE { n| @nvar}
• 如果n或@nvar为正数，提取当前行之后的第n行，并将返回的行变成新的当前行；
• 如果n或@nvar为负数，提取当前行之前的第n行，并将返回的行变成新的当前行；
• 如果n或@nvar为0，提取当前行；
• 如果对游标的第一次提取操作时将fetch relative的n或@nvar指定为负数或0，则没有行返回；
• n必须为整型常量，@nvar必须为smallint、tinyint或nit；
• GLOBAL：指定其后的游标是全局游标。
• @ INTOvariable_name[，...n]：允许将提取操作的列数据放到局部变量中。 列表中的各个变量从左到右与游标结果集中的相应列相关联。 各变量的数据类型必须与相应的结果集列的数据类型匹配，或是结果集列数据类型所支持的隐式转换。 变量的数目必须与游标选择列表中的列数一致。

• 如果指定了 FORWARD_ONLY 或 FAST_FORWARD，则 NEXT 是唯一受支持的 FETCH 选项。

• 如果未指定 DYNAMIC、FORWARD_ONLY 或 FAST_FORWARD 选项，并且指定了 KEYSET、STATIC 或 SCROLL 中的某一个，则支持所有 FETCH 选项。
• DYNAMIC SCROLL 游标支持除 ABSOLUTE 以外的所有 FETCH 选项。
• 如果想操作当前行的话，就用current of <游标名>|<游标变量名>

关闭游标

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }  

释放游标

关闭游标并不改变游标的定义，可以再次打开该游标。但是，释放游标就释放了与指定游标有关的一切资源，再次使用该游标必须重新声明。
  对游标进行操作的语句使用游标名称或游标变量引用游标。 DEALLOCATE 删除游标与游标名称或游标变量之间的关联。 如果一个名称或变量是最后引用游标的名称或变量，则将释放游标，游标使用的任何资源也随之释放。 用于保护提取隔离的滚动锁在 DEALLOCATE 上释放。 用于保护更新（包括通过游标进行的定位更新）的事务锁一直到事务结束才释放。

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }  

示例

A、 使用 FETCH 将值存入变量

DECLARE @LastName varchar(50), @FirstName varchar(50);  

DECLARE contact_cursor CURSOR FOR  
SELECT LastName, FirstName FROM Person.Person  
WHERE LastName LIKE 'B%'  
ORDER BY LastName, FirstName;  
  
OPEN contact_cursor;  
  
FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName;  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   PRINT 'Contact Name: ' + @FirstName + ' ' +  @LastName    
   FETCH NEXT FROM contact_cursor INTO @LastName, @FirstName;  
END  
  
CLOSE contact_cursor;  
DEALLOCATE contact_cursor;  
GO  

B、 声明 SCROLL 游标并使用其他 FETCH 选项

-- Declare the cursor.  
DECLARE contact_cursor SCROLL CURSOR FOR  
SELECT LastName, FirstName FROM Person.Person  
ORDER BY LastName, FirstName;  
  
OPEN contact_cursor;  
  
-- Fetch the last row in the cursor.  
FETCH LAST FROM contact_cursor;  
  
-- Fetch the row immediately prior to the current row in the cursor.  
FETCH PRIOR FROM contact_cursor;  
  
-- Fetch the second row in the cursor.  
FETCH ABSOLUTE 2 FROM contact_cursor;  
  
-- Fetch the row that is three rows after the current row.  
FETCH RELATIVE 3 FROM contact_cursor;  
  
-- Fetch the row that is two rows prior to the current row.  
FETCH RELATIVE -2 FROM contact_cursor;  
  
CLOSE contact_cursor;  
DEALLOCATE contact_cursor;  
GO  

C、CURSOR_STATUS
下面的示例使用 CURSOR_STATUS 函数显示游标在打开和关闭之前和之后的状态。

CREATE TABLE #TMP (ii int)  
GO  
  
INSERT INTO #TMP(ii) VALUES(1)  
INSERT INTO #TMP(ii) VALUES(2)  
INSERT INTO #TMP(ii) VALUES(3)  
GO  
  
--Create a cursor.  
DECLARE cur CURSOR FOR SELECT * FROM #TMP  
  
--Display the status of the cursor before and after opening,closing the cursor.  
SELECT CURSOR_STATUS('global','cur') AS 'After declare'  
OPEN cur  
SELECT CURSOR_STATUS('global','cur') AS 'After Open'  
CLOSE cur  
SELECT CURSOR_STATUS('global','cur') AS 'After Close'  

DEALLOCATE cur  
DROP TABLE #TMP  

下面是结果集：

After declare
---------------
-1
After Open
----------
1
After Close
-----------
-1