游标的使用

游标主要用于存储过程和触发器中：

1.声明变量，用于包含游标返回的数据

2. 使用游标声明语句（declare cursor) 将游标与select 语句关联；

3. 使用open语句执行select语句并填充游标；

4. 使用fetch into 语句提取单个行，并将其保存到指定变量中；

5. 使用close 语句关闭游标（此时重新open仍然可以使用），或者使用deallocate  释放游标资源(要重新使用必须重新declare）

（1）游标声明语句

ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
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 ] ] ]
[;]

example：

SET NOCOUNT ON;

DECLARE @vendor_id int, @vendor_name nvarchar(50),
    @message varchar(80), @product nvarchar(50);

PRINT '-------- Vendor Products Report --------';

DECLARE vendor_cursor CURSOR FOR 
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    SELECT @message = '----- Products From Vendor: ' + 
        @vendor_name

    PRINT @message

    -- Declare an inner cursor based   
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR 
    SELECT v.Name
    FROM Purchasing.ProductVendor pv, Production.Product v
    WHERE pv.ProductID = v.ProductID AND
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor

    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @product

    IF @@FETCH_STATUS <> 0 
        PRINT '         <<None>>'     

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product
        END

    CLOSE product_cursor
    DEALLOCATE product_cursor
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor 
    INTO @vendor_id, @vendor_name
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
（2）对于可更新游标
可以通过where  current  of cursor_name  来进行定位
declare  OrderHeaderCursor   scroll cursor 
for
select SalesOrderID, OrderDate,SubTotal
from Sales.SalesOrderHeader
where  SalesOrderID > 75118
order by SalesOrderID
for update
open OrderHeaderCursor
fetch first from OrderHeaderCursor
update Sales.SalesOrderHeader
set SubTotal = 189.96
where current of OrderHeaderCursor
close OrderHeaderCursor
deallocate OrderHeaderCursor
（3） 滚动游标

在cursor前加上关键字scroll，就可以出创建滚动游标了，

 对于fetch语句，除了next外，还可以使用其他
FETCH 
          [ [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
               ] 
               FROM 
          ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ] 

  
  
   
   NEXT
  
  
  
  

   
   
Returns the result row immediately following the current row and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.
  
  
  
  
   
   PRIOR
  
  
  
  

   
   
Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.
  
  
  
  
   
   FIRST
  
  
  
  

   
   
Returns the first row in the cursor and makes it the current row.
  
  
  
  
   
   LAST
  
  
  
  

   
   
Returns the last row in the cursor and makes it the current row.
  
  
  
  
   
   ABSOLUTE {
   
    
   
   n |
   
    
   
   @
   
   nvar}
  
  
  
  

   
   
If n or @nvar is positive, returns the row n rows from the front of the cursor and makes the returned row the new current row. If n or @nvaris negative, returns the row n rows before the end of the cursor and makes the returned row the new current row. If n or @nvar is 0, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.
  
  
  
  
   
   RELATIVE {
   
    
   
   n |
   
    
   
   @
   
   nvar}
  
  
  
  

   
   
If n or @nvar is positive, returns the row n rows beyond the current row and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows prior to the current row and makes the returned row the new current row. If n or @nvar is 0, returns the current row. If FETCH RELATIVE is specified with n or @nvar set to negative numbers or 0 on the first fetch done against a cursor, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.
  
  
  
  
   
   GLOBAL
  
  
  
  

   
   
Specifies that cursor_name refers to a global cursor.
  
  
  
  
   
   cursor_name
  
  
  
  

   
   
Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name to the global cursor if GLOBAL is specified and to the local cursor if GLOBAL is not specified.
  
  
  
  
   
   @
   
   cursor_variable_name 
  
  
  
  

   
   
Is the name of a cursor variable referencing the open cursor from which the fetch should be made.
  
  
  
  
   
   INTO
   
    
   
   @
   
   variable_name[
   
    
   
   ,...
   
   n]
  
  
  
  

   
   
Allows data from the columns of a fetch to be placed into local variables. Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list.
   
   

  
  

（4）声明游标变量

declare   @OrderHeaderCursor   cursor   -- 声明游标变量

set @OrderHeaderCursor =   cursor  scroll  -- 为游标赋值

for

select SalesOrderID, OrderDate,SubTotal

from Sales.SalesOrderHeader

where  SalesOrderID > 75118

order by SalesOrderID

for update

open @OrderHeaderCursor  --打开游标

fetch last from @OrderHeaderCursor  -- 使用游标

update Sales.SalesOrderHeader

set SubTotal = 20.89

where current of @OrderHeaderCursor

close @OrderHeaderCursor   -- 关闭游标

deallocate @OrderHeaderCursor  --释放资源