SQL Server 创建游标（cursor）

soyizi

　　游标的定义：
　　游标则是处理结果集的一种机制，它可以定位到结果集中的某一行，也可以移动游标定位到你所需要的行中进行操作数据。与 select 语句的不同是，select 语句面向的是结果集，游标面向的是结果集的行。 游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行。
　　游标的分类：
　　静态游标（static）：当游标被建立时，将会创建 FOR 后面的 SELECT 语句所包含数据集的副本存入 tempdb 数据库中，任何对于底层表内数据的更改不会影响到游标的内容。
　　即打开游标之后，对游标查询的数据表的数据进行增删改操做之后，静态游标中 select 的数据依旧显示的为没有操作之前的数据。
　　如果想与操作之后的数据一致，则关闭之后重新打开游标即可。
　　动态游标（dynamic）：动态游标与静态游标相反，当底层数据表的数据更改时，游标的内容也随之得到反映，在下一次 fetch 中， 行的数据值、顺序和成员身份在每次提取时都会更改。
　　只进游标（fast_forward）：只进游标不支持滚动，只支持从头到尾按顺序读取数据，对数据执行增删改操作，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。
　　键集游标（keyset）：打开键集驱动游标时，结果集的每行数据被一组唯一标识符进行标识，被标识的列做删改时，用户滚动游标是可见的，其他用户执行的插入是不可见的（不能通过 Transact-SQL 服务器游标执行插入）。如果删除了某行，尝试读取的行返回 @@FETCH_STATUS为-2。 从游标外部更新键值类似于删除旧行后再插入新行。 具有新值的行不可见，并且尝试提取具有旧值的行返回 @@FETCH_STATUS为-2。如果通过指定 WHERE CURRENT OF 子句来通过游标执行更新，则新值可见。
　　游标的生命周期：
　　游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。
　　语法：
　　

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：游标的名称
　　Local：局部游标，只在定义它的批处理，存储过程或触发器中有效。
　　Global：全局游标，在由此连接执行的任何存储过程或批处理中，都可以引用该游标。该游标仅在断开连接时隐式释放。
　　如果未指定游标作用域，那么默认为全局游标。
　　Forward_Only：指定游标只能从第一行滚动到最后一行。 FETCH NEXT 是唯一支持的提取选项。
　　Scroll：指定游标在定义的数据集中可以向任何方向，或任何位置移动。
　　如果在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET 和 DYNAMIC 关键字，则游标默认为 DYNAMIC 游标进行操作。
　　如果 FORWARD_ONLY 和 SCROLL 均未指定，那么除非指定了 STATIC、KEYSET 或 DYNAMIC 关键字，否则默认值为 FORWARD_ONLY。
　　STATIC、KEYSET 和 DYNAMIC 游标默认为 SCROLL。
　　Static：指定为静态游标
　　KeySet：指定为键集游标
　　Dynamic：指定为动态游标，动态游标不支持 ABSOLUTE 提取选项。
　　Fast_Forward：指定为启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。 如果指定了 SCROLL 或 FOR_UPDATE，则不能同时指定 FAST_FORWARD。
　　Read_Only：只读，即不能通过游标对数据进行更新操作。
　　Scroll_Locks：将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功。如果还指定了FAST_FORWARD或STATIC，则不能指定SCROLL_LOCKS。
　　Optimistic：不锁定任何数据，当需要在游标中更新数据时，如果底层表数据更新，则游标内数据更新不成功，如果底层表数据未更新，则游标内表数据可以更新。如果指定了 Fast_Forward ，则不能指定它。
　　Type_Warning：指定如果游标从所请求的类型隐式转换为另一种类型，则向客户端发送警告消息。
　　select_statement：定义游标结果集的标准 SELECT 语句。
　　For Update[of column_name ,....]：定义游标中可更新的列。如果指定了 UPDATE，也指定了列，仅指定的列进行修改。如果指定了 UPDATE，但未指定列，则除非指定了 READ_ONLY 并发选项，否则可以更新所有的列。
　　在声明游标后，可使用下列系统存储过程确定游标的特性：

　　定义一个局部动态的游标：
　　

　　
declare test_cursor cursor local
　　
scroll dynamic optimistic
　　
for select S_Id,S_StuNo,S_Name,S_Sex,S_Height from Student where C_S_Id='2'
　　

　　打开游标：
　　

　　
open [ Global ] cursor_name | cursor_variable_name
　　

　　cursor_name：定义的游标名称。
　　cursor_variable_name：游标变量名称，即引用了游标的变量的名称。
　　

　　
open test_cursor
　　

　　

　　
open local test_cursor
　　

　　

　　
open global test_cursor
　　

　　PS：如果未指定 local 和 global，优先打开局部游标，如果没有这个局部游标，则再打开全局游标。也就是说，当全局游标和局部游标重名时，默认会打开局部游标。
　　提取数据：
　　

　　
FETCH   

[ [ NEXT | PRIOR | FIRST | LAST   

　　| ABSOLUTE { n | @nvar }   

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

　　Frist：结果集的第一行
　　Prior：当前位置的上一行
　　Next：当前位置的下一行
　　Last：最后一行
　　Absolute：直接跳到指定的行（n）
　　Relative：相对于当前位置跳指定的行数（n）
　　Into @variable_name[,...]：将提取的数据存入到变量中，@variable_name 为变量名。
　　

fetch first from test_cursor into @Name　　
select @Name
　　
fetch prior from test_cursor into @Name
　　
select @Name
　　
fetch next from test_cursor into @Name
　　
select @Name
　　
fetch last from test_cursor into @Name
　　
select @Name
　　
fetch absolute 1 from test_cursor into @Name
　　
select @Name

　　
fetch>　　
select @Name
　　

　　PS：动态游标不支持 ABSOLUTE 提取选项，对于未指定 SCROLL 选项的游标来说，只支持 NEXT 取值。
　　游标经常会和全局变量 @@FETCH_STATUS 与 WHILE 循环来共同使用，以达到遍历游标所在数据集的目的。
　　@@FETCH_STATUS 有三种值：
　　0：Fetch 语句成功。
　　-1：Fetch 语句失败或行不在结果集中。
　　-2：提取的行不存在。
　　使用系统全局变量 @@cursor_rows 查询游标中结果集中的行数：
　　n：表示返回的实际行数。
　　-1：表示游标是动态的。
　　0：表示空集游标。
　　使用游标删除、修改数据：
　　

　　
update 基表名 Set 列名=值[,...] where current of 游标名
　　

　　
update Student set S_Name='233' where current of test_cursor
　　

　　

　　
delete 基表名  where current of 游标名
　　

　　
delete Student where current of test_cursor
　　

　　关闭游标：
　　

　　
close [ Global ] cursor_name | cursor_variable_name
　　

　　

　　
close test_cursor
　　

　　

　　
close local test_cursor
　　

　　

　　
close global test_cursor
　　

　　释放游标：
　　

　　
deallocate  [ Global ] cursor_name | cursor_variable_name
　　

　　

　　
deallocate test_cursor
　　

　　

　　
deallocate local test_cursor
　　

　　

　　
deallocate global test_cursor
　　

　　下面是一个完整的例子：

　　下面来定义一个游标用来修改每一个学生的学号，在学号前面添加一位 1。
　　

declare @StuNo nvarchar(50)　　

declare test_cursor cursor local　　

for select S_StuNo from Student　　

open test_cursor　　

fetch next from test_cursor into @StuNo　　
while(@@FETCH_STATUS=0)
　　
begin
　　set @StuNo='1'+@StuNo
　　if(@StuNo is not null and @StuNo <> '')
　　begin
　　update Student set S_StuNo=@StuNo where current of test_cursor
　　end
　　fetch next from test_cursor into @StuNo
　　
end
　　
close test_cursor
　　
deallocate test_cursor
　　

　　
select * from Student
　　

　　如果只是用于循环之类的，最好不要使用游标，游标是不好的，反正能不使用就尽量不用游标，改用其他方法实现。
　　比如上面的例子，我们也可以用自定义循环来实现，如下：
　　

declare @I    int　　
declare @Num int
　　
declare @SID int
　　
declare @StuNo nvarchar(50)
　　

　　
select @Num=COUNT(1) from Student
　　

　　
if(@Num>0)
　　
begin
　　set @I=0
　　while(@I<@Num)
　　begin
　　set @I=@I+1
　　

　　select @StuNo=t.S_StuNo,@SID=t.S_Id from
　　(select S_Id,S_StuNo,ROW_NUMBER() over(order by S_StuNo) RowNum from Student) t
　　where t.RowNum=@I
　　

　　set @StuNo=SUBSTRING(@StuNo,2,len(@StuNo)-1)
　　

　　update Student set S_StuNo=@StuNo where S_Id=@SID
　　end
　　
end
　　

　　
select * from Student
　　

　　把上面改掉的学生的学号又给改回来了，效果是一样的。当然，这样一条一条的改明显是有点欠扁的。。。不过只是一个循环的例子。
　　当然，存在就有它的意义，游标也有它的好处，比如用自定义的循环解决不了的问题，它就能排上用场了。比如下面一个例子，当我选择一个父节点的时候，我要删除它下面的所有子孙节点（不管多少层级）。
　　演示数据：

　　

  declare @NodeId int　　declare @NID int
　　set @NodeId=1        
　　

　　declare @temp_value table
　　(

　　ID int>　　value int
　　)
　　

　　insert into @temp_value
　　select D_ID from Department where D_ID=@NodeId
　　declare one_curr cursor local scroll dynamic        
　　for select value from @temp_value
　　

　　open one_curr
　　fetch next from one_curr into @NID
　　while(@@FETCH_STATUS=0)
　　begin
　　if exists(select D_ID from Department where D_ParentID=@NID)   
　　begin
　　insert into @temp_value
　　select D_ID from Department where D_ParentID=@NID        
　　end
　　delete from Department where D_ID=@NID        
　　

　　fetch next from one_curr into @NID
　　end
　　close one_curr
　　deallocate one_curr
　　

　　select * from Department
　　

　　在这里容许我装个逼：游标永远只是你最后无奈之下的选择，而不是首选！！！
　　参考：
　　http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html#!comments
　　http://www.cnblogs.com/knowledgesea/p/3699851.html
　　https://msdn.microsoft.com/zh-cn/library/ms180169.aspx