游标的定义:
    游标则是处理结果集的一种机制,它可以定位到结果集中的某一行,也可以移动游标定位到你所需要的行中进行操作数据。与 select 语句的不同是,select 语句面向的是结果集,游标面向的是结果集的行。 游标其实可以理解成一个定义在特定数据集上的指针,我们可以控制这个指针遍历数据集,或者仅仅是指向特定的行。

    游标的分类:
    静态游标(static):当游标被建立时,将会创建 FOR 后面的 SELECT 语句所包含数据集的副本存入 tempdb 数据库中,任何对于底层表内数据的更改不会影响到游标的内容。
    即打开游标之后,对游标查询的数据表的数据进行增删改操做之后,静态游标中 select 的数据依旧显示的为没有操作之前的数据。
    如果想与操作之后的数据一致,则关闭之后重新打开游标即可。
    动态游标(dynamic):动态游标与静态游标相反,当底层数据表的数据更改时,游标的内容也随之得到反映,在下一次 fetch 中, 行的数据值、顺序和成员身份在每次提取时都会更改。
    只进游标(fast_forward):只进游标不支持滚动,只支持从头到尾按顺序读取数据,对数据执行增删改操作,在提取时是可见的,但由于该游标只能进不能向后滚动,所以在行提取后对行做增删改是不可见的。
    键集游标(keyset):打开键集驱动游标时,结果集的每行数据被一组唯一标识符进行标识,被标识的列做删改时,用户滚动游标是可见的,其他用户执行的插入是不可见的(不能通过 Transact-SQL 服务器游标执行插入)。如果删除了某行,尝试读取的行返回 @@FETCH_STATUS为-2。 从游标外部更新键值类似于删除旧行后再插入新行。 具有新值的行不可见,并且尝试提取具有旧值的行返回 @@FETCH_STATUS为-2。如果通过指定 WHERE CURRENT OF 子句来通过游标执行更新,则新值可见。

    游标的生命周期:
    游标的生命周期包含有五个阶段:声明游标、打开游标、读取游标数据、关闭游标、释放游标。

    语法:
    创建游标(cursor) - 图1
    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) - 图2
    参数:
    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 并发选项,否则可以更新所有的列。

    在声明游标后,可使用下列系统存储过程确定游标的特性:
    创建游标(cursor) - 图3

    定义一个局部动态的游标:
    — 定义一个局部动态游标
    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:游标变量名称,即引用了游标的变量的名称。
    创建游标(cursor) - 图4
    — 打开游标
    open test_cursor

    — 打开局部游标
    open local test_cursor

    — 打开全局游标
    open global test_cursor
    创建游标(cursor) - 图5
    PS:如果未指定 local 和 global,优先打开局部游标,如果没有这个局部游标,则再打开全局游标。也就是说,当全局游标和局部游标重名时,默认会打开局部游标。
    提取数据:
    创建游标(cursor) - 图6
    — 游标提取数据语法
    FETCH
    [ [ NEXT | PRIOR | FIRST | LAST
    | ABSOLUTE { n | @nvar }
    | RELATIVE { n | @nvar }
    ]
    FROM
    ]
    { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
    [ INTO @variable_name [ ,…n ] ]
    创建游标(cursor) - 图7
    Frist:结果集的第一行
    Prior:当前位置的上一行
    Next:当前位置的下一行
    Last:最后一行
    Absolute:直接跳到指定的行(n)
    Relative:相对于当前位置跳指定的行数(n)
    Into @variable_name[,…]:将提取的数据存入到变量中,@variable_name 为变量名。
    创建游标(cursor) - 图8
    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 relative 1 from test_cursor into @Name
    select @Name
    创建游标(cursor) - 图9
    PS:动态游标不支持 ABSOLUTE 提取选项,对于未指定 SCROLL 选项的游标来说,只支持 NEXT 取值。

    游标经常会和全局变量 @@FETCH_STATUS 与 WHILE 循环来共同使用,以达到遍历游标所在数据集的目的。
    @@FETCH_STATUS 有三种值:
    0:Fetch 语句成功。
    -1:Fetch 语句失败或行不在结果集中。
    -2:提取的行不存在。
    使用系统全局变量 @@cursor_rows 查询游标中结果集中的行数:
    n:表示返回的实际行数。
    -1:表示游标是动态的。
    0:表示空集游标。

    使用游标删除、修改数据:
    创建游标(cursor) - 图10
    —使用游标修改当前数据语法
    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
    创建游标(cursor) - 图11
    关闭游标:
    —关闭游标语法
    close [ Global ] cursor_name | cursor_variable_name
    创建游标(cursor) - 图12
    —关闭游标
    close test_cursor

    —关闭局部游标
    close local test_cursor

    —关闭全局游标
    close global test_cursor
    创建游标(cursor) - 图13
    释放游标:
    —释放游标语法
    deallocate [ Global ] cursor_name | cursor_variable_name
    创建游标(cursor) - 图14
    —释放游标
    deallocate test_cursor

    —释放局部游标
    deallocate local test_cursor

    —释放全局游标
    deallocate global test_cursor
    创建游标(cursor) - 图15

    下面是一个完整的例子:
    创建游标(cursor) - 图16
    下面来定义一个游标用来修改每一个学生的学号,在学号前面添加一位 1。
    创建游标(cursor) - 图17
    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
    创建游标(cursor) - 图18
    创建游标(cursor) - 图19
    如果只是用于循环之类的,最好不要使用游标,游标是不好的,反正能不使用就尽量不用游标,改用其他方法实现。
    比如上面的例子,我们也可以用自定义循环来实现,如下:
    创建游标(cursor) - 图20
    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

    1. select @StuNo=t.S_StuNo,@SID=t.S_Id from <br /> (select S_Id,S_StuNo,ROW_NUMBER() over(order by S_StuNo) RowNum from Student) t<br /> where t.RowNum=@I
    2. set @StuNo=SUBSTRING(@StuNo,**2**,len(@StuNo)-**1**)
    3. update Student set S_StuNo=@StuNo where S_Id=@SID<br /> end<br />end

    select from Student
    创建游标(cursor) - 图21
    创建游标(cursor) - 图22
    把上面改掉的学生的学号又给改回来了,效果是一样的。当然,这样一条一条的改明显是有点欠扁的。。。不过只是一个循环的例子。
    当然,存在就有它的意义,游标也有它的好处,比如用自定义的循环解决不了的问题,它就能排上用场了。比如下面一个例子,当我选择一个父节点的时候,我要删除它下面的所有子孙节点(不管多少层级)。
    演示数据:
    创建游标(cursor) - 图23
    创建游标(cursor) - 图24
    declare @NodeId int
    declare @NID int
    set @NodeId=*1
    — 表示选择节点的ID

    declare @temp_value table
    (
    ID int identity(1,1),
    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 —存在就把所有的子节点的ID插入表变量,后面循环使用
    end

    delete from Department where D_ID=@NID —删除相应的节点

        fetch next from one_curr into @NID<br />  end<br />  close one_curr<br />  deallocate one_curr
    

    select * from Department
    创建游标(cursor) - 图25
    创建游标(cursor) - 图26

    在这里容许我装个逼:游标永远只是你最后无奈之下的选择,而不是首选!!!

    参考:
    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