您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:始建游标,基础知识梳理

新葡亰496net:始建游标,基础知识梳理

发布时间:2019-07-15 02:48编辑:网络数据库浏览(200)

    转载自:

    游标的概念

    游标的概念

    游标的定义:

    什么是游标                                                                                   

    结果集,结果集就是select查询之后返回的所有行数据的集合。

    游标则是处理结果集的一种机制吧,它可以定位到结果集中的某一行,多数据进行读写,也可以移动游标定位到你所需要的行中进行操作数据。

    一般复杂的存储过程,都会有游标的出现,他的用处主要有:

    1. 定位到结果集中的某一行。
    2. 对当前位置的数据进行读写。
    3. 可以对结果集中的数据单独操作,而不是整行执行相同的操作。
    4. 新葡亰496net:始建游标,基础知识梳理。是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

    结果集,结果集就是select查询之后返回的所有行数据的集合。

    结果集,结果集就是select查询之后返回的所有行数据的集合。

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

    游标的分类                                                                                    

    根据游标检测结果集变化的能力和消耗资源的情况不同,SQL Server支持的API服务器游标分为一下4种:

    • 静态游标: 静态游标的结果集,在游标打开的时候建立在TempDB中,不论你在操作游标的时候,如何操作数据库,游标中的数据集都不会变。例如你在游标打开的时候,对游标查询的数据表数据进行增删改,操作之后,静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致,则重新关闭打开游标即可。
    • 动态游标:这个则与静态游标相对,滚动游标时,动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新,他们将立即可见。在游标外部所做的更新直到提交时才可见。
    • 只进游标:只进游标不支持滚动,只支持从头到尾顺序提取数据,数据库执行增删改,在提取时是可见的,但由于该游标只能进不能向后滚动,所以在行提取后对行做增删改是不可见的。
    • 键集驱动游标:打开键集驱动游标时,该有表中的各个成员身份和顺序是固定的。打开游标时,结果集这些行数据被一组唯一标识符标识,被标识的列做删改时,用户滚动游标是可见的,如果没被标识的列增该,则不可见,比如insert一条数据,是不可见的,若可见,须关闭重新打开游标。

    静态游标在滚动时检测不到表数据变化,但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化,但消耗的资源却较多。键集驱动游标则处于他们中间,所以根据需求建立适合自己的游标,避免资源浪费。

    游标(Cursor):

    游标(Cursor):

     

    游标的生命周期                                                                             

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

     1.声明游标,语法

    新葡亰496net 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 ] ] ]
    

    新葡亰496net 2

    参数说明:

    • 新葡亰496net,cursor_name:游标名称。
    • Local:作用域为局部,只在定义它的批处理,存储过程或触发器中有效。
    • Global:作用域为全局,由连接执行的任何存储过程或批处理中,都可以引用该游标。
    • [Local | Global]:默认为local。
    • Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字,默认为Dynamic游标。如果Forward_Only和Scroll没有指定,Static、KeySet、Dynamic游标默认为Scroll,Fast_Forward默认为Forward_Only
    • Static:静态游标
    • KeySet:键集游标
    • Dynamic:动态游标,不支持Absolute提取选项
    • Fast_Forward:指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update,就不能指定他啦。
    • Read_Only:不能通过游标对数据进行删改。
    • Scroll_Locks:将行读入游标是,锁定这些行,确保删除或更新一定会成功。如果指定啦Fast_Forward或Static,就不能指定他啦。
    • Optimistic:指定如果行自读入游标以来已得到更新,则通过游标进行的定位更新或定位删除不成功。当将行读入游标时,sqlserver不锁定行,它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改,如果表不行timestamp列,它改用校验和值进行确定。如果已修改改行,则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
    • Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
    • For Update[of column_name ,....] :定义游标中可更新的列。

    2.声明一个动态游标

    declare orderNum_02_cursor cursor scroll
    for select OrderId from bigorder where orderNum='ZEORD003402'
    

    3.打开游标

    --打开游标语法
    open [ Global ] cursor_name | cursor_variable_name
    

    cursor_name:游标名,cursor_variable_name:游标变量名称,该变量引用了一个游标。

    --打开游标
    open orderNum_02_cursor
    

    4.提取数据

    新葡亰496net 3

    --提取游标语法
    Fetch
    [ [Next|prior|Frist|Last|Absoute n|Relative n ]
    from ]
    [Global] cursor_name
    [into @variable_name[,....]]
    

    新葡亰496net 4

    参数说明:

    • Frist:结果集的第一行
    • Prior:当前位置的上一行
    • Next:当前位置的下一行
    • Last:最后一行
    • Absoute n:从游标的第一行开始数,第n行。
    • Relative n:从当前位置数,第n行。
    • Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。

    例子:

    新葡亰496net 5

    --提取数据
    fetch first from orderNum_02_cursor
    fetch relative 3 from orderNum_02_cursor
    fetch next from orderNum_02_cursor
    fetch absolute 4 from orderNum_02_cursor
    fetch next from orderNum_02_cursor
    fetch last from orderNum_02_cursor 
    fetch prior from orderNum_02_cursor
    select * from bigorder where orderNum='ZEORD003402'
    

    新葡亰496net 6

    结果(对比一下,就明白啦):

    新葡亰496net 7

    新葡亰496net:始建游标,基础知识梳理。例子:

    --提取数据赋值给变量
    declare @OrderId int
    fetch absolute 3 from orderNum_02_cursor into @OrderId
    select @OrderId as id
    select * from bigorder where orderNum='ZEORD003402'
    

    结果:

    新葡亰496net 8

    通过检测全局变量@@Fetch_Status的值,获得提取状态信息,该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后,@@Fetch_Status可能出现3种值:0,Fetch语句成功。-1:Fetch语句失败或行不在结果集中。-2:提取的行不存在。

    这个状态值可以帮你判断提取数据的成功与否。

    新葡亰496net 9

    declare @OrderId int
    fetch absolute 3 from orderNum_02_cursor into @OrderId
    while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
     begin
       select @OrderId as id
       fetch  next from orderNum_02_cursor into @OrderId  --移动游标
     end 
    

    新葡亰496net 10

    5.利用游标更新删除数据 

    --游标修改当前数据语法
    Update 基表名 Set 列名=值[,...] Where Current of 游标名
    --游标删除当前数据语法
    Delete 基表名  Where Current of 游标名
    

    新葡亰496net 11

    ---游标更新删除当前数据
    ---1.声明游标
    declare orderNum_03_cursor cursor scroll
    for select OrderId ,userId from bigorder where orderNum='ZEORD003402'
    --2.打开游标
    open orderNum_03_cursor
    --3.声明游标提取数据所要存放的变量
    declare @OrderId int ,@userId varchar(15)
    --4.定位游标到哪一行
    fetch First from orderNum_03_cursor into @OrderId,@userId  --into的变量数量必须与游标查询结果集的列数相同
    while @@fetch_status=0  --提取成功,进行下一条数据的提取操作 
     begin
       if @OrderId=122182
         begin
         Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor  --修改当前行
         end
       if @OrderId=154074
          begin
          Delete bigorder Where Current of  orderNum_03_cursor  --删除当前行
          end
       fetch next from orderNum_03_cursor into @OrderId ,@userId  --移动游标
     end  
    

    新葡亰496net 12

    6.关闭游标

     游标打开后,服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集,同时使用游标也会对某些数据进行封锁。所以游标一旦用过,应及时关闭,避免服务器资源浪费。

    --关闭游标语法
    close [ Global ] cursor_name | cursor_variable_name
    --关闭游标
    close orderNum_03_cursor
    

    7.删除游标

    删除游标,释放资源

    --释放游标语法
    deallocate  [ Global ] cursor_name | cursor_variable_name
    --释放游标
    deallocate orderNum_03_cursor
    
    • 是处理数据的一种方法。
    • 它可以定位到结果集中的某一行,对数据进行读写。
    • 也可以移动游标定位到你需要的行中进行数据操作。
    • 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁
    • 是处理数据的一种方法。
    • 它可以定位到结果集中的某一行,对数据进行读写。
    • 也可以移动游标定位到你需要的行中进行数据操作。
    • 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁

    游标的分类:

    游标的分类

    游标的分类

    静态游标(static):当游标被建立时,将会创建 FOR 后面的 SELECT 语句所包含数据集的副本存入 tempdb 数据库中,任何对于底层表内数据的更改不会影响到游标的内容。

    SQL Server支持的API服务器游标分为4种:
    静态游标( STATIC )意味着,当游标被建立时,将会创建FOR后面的SELECT语句所包含数据集的副本存入tempdb数据库中,任何对于底层表内数据的更改不会影响到游标的内容。
    动态游标( DYNAMIC )是和STATIC完全相反的选项,当底层数据库更改时,游标的内容也随之得到反映,在下一次fetch中,数据内容会随之改变。
    键集驱动游标( KEYSET )可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时,@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据。
    只进游标    可以理解成不支持滚动,只支持从头到尾顺序提取数据,数据库执行增删改,在提取时是可见的,但由于该游标只能进不能向后滚动,所以在行提取后对行做增删改是不可见的。 ( FAST_FORWARD 可以理解为FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划,而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划,大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好。

    SQL Server支持的API服务器游标分为4种:
    静态游标( STATIC )意味着,当游标被建立时,将会创建FOR后面的SELECT语句所包含数据集的副本存入tempdb数据库中,任何对于底层表内数据的更改不会影响到游标的内容。
    动态游标( DYNAMIC )是和STATIC完全相反的选项,当底层数据库更改时,游标的内容也随之得到反映,在下一次fetch中,数据内容会随之改变。
    键集驱动游标( KEYSET )可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时,@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据。
    只进游标    可以理解成不支持滚动,只支持从头到尾顺序提取数据,数据库执行增删改,在提取时是可见的,但由于该游标只能进不能向后滚动,所以在行提取后对行做增删改是不可见的。 ( FAST_FORWARD 可以理解为FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划,而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划,大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好。

    即打开游标之后,对游标查询的数据表的数据进行增删改操做之后,静态游标中 select 的数据依旧显示的为没有操作之前的数据。

    游标的语法

    游标的语法

    如果想与操作之后的数据一致,则关闭之后重新打开游标即可。

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

    动态游标(dynamic):动态游标与静态游标相反,当底层数据表的数据更改时,游标的内容也随之得到反映,在下一次 fetch 中, 行的数据值、顺序和成员身份在每次提取时都会更改。

    cursor_name:游标名称。
    Local:作用域为局部,只在定义它的批处理,存储过程或触发器中有效。
    Global:作用域为全局,由连接执行的任何存储过程或批处理中,都可以引用该游标。
    [Local | Global]:默认为local。
    Forward_Only:指定游标智能从第一行滚到最后一行。 Fetch Next是唯一支持的提取选项。如果在指定Forward_Only时不指定Static、KeySet、Dynamic关键字,默认为Dynamic游标。如果Forward_Only和Scroll没有指定,Static、KeySet、Dynamic游标默认为Scroll,Fast_Forward默认为Forward_Only
    Static:静态游标
    KeySet:键集游标
    Dynamic:动态游标,不支持Absolute提取选项
    Fast_Forward:指定启用了性能优化的Forward_Only、Read_Only游标。
    Read_Only:意味着声明的游标只能读取数据,游标不能做任何更新操作
    Scroll_Locks:将读入游标的所有数据进行锁定,防止其他程序进行更改,以确保更新的绝对成功
    Optimistic:是相对比较好的一个选择,OPTIMISTIC不锁定任何数据,当需要在游标中更新数据时,如果底层表数据更新,则游标内数据更新不成功,如果,底层表数据未更新,则游标内表数据可以更新。
    Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
    For Update[of column_name ,....] :定义游标中可更新的列。

    cursor_name:游标名称。
    Local:作用域为局部,只在定义它的批处理,存储过程或触发器中有效。
    Global:作用域为全局,由连接执行的任何存储过程或批处理中,都可以引用该游标。
    [Local | Global]:默认为local。
    Forward_Only:指定游标智能从第一行滚到最后一行。 Fetch Next是唯一支持的提取选项。如果在指定Forward_Only时不指定Static、KeySet、Dynamic关键字,默认为Dynamic游标。如果Forward_Only和Scroll没有指定,Static、KeySet、Dynamic游标默认为Scroll,Fast_Forward默认为Forward_Only
    Static:静态游标
    KeySet:键集游标
    Dynamic:动态游标,不支持Absolute提取选项
    Fast_Forward:指定启用了性能优化的Forward_Only、Read_Only游标。
    Read_Only:意味着声明的游标只能读取数据,游标不能做任何更新操作
    Scroll_Locks:将读入游标的所有数据进行锁定,防止其他程序进行更改,以确保更新的绝对成功
    Optimistic:是相对比较好的一个选择,OPTIMISTIC不锁定任何数据,当需要在游标中更新数据时,如果底层表数据更新,则游标内数据更新不成功,如果,底层表数据未更新,则游标内表数据可以更新。
    Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
    For Update[of column_name ,....] :定义游标中可更新的列。

    只进游标(fast_forward):只进游标不支持滚动,只支持从头到尾按顺序读取数据,对数据执行增删改操作,在提取时是可见的,但由于该游标只能进不能向后滚动,所以在行提取后对行做增删改是不可见的。

    如何定义游标

    如何定义游标

    键集游标(keyset):打开键集驱动游标时,结果集的每行数据被一组唯一标识符进行标识,被标识的列做删改时,用户滚动游标是可见的,其他用户执行的插入是不可见的(不能通过 Transact-SQL 服务器游标执行插入)。如果删除了某行,尝试读取的行返回 @@FETCH_STATUS为-2。 从游标外部更新键值类似于删除旧行后再插入新行。 具有新值的行不可见,并且尝试提取具有旧值的行返回 @@FETCH_STATUS为-2。如果通过指定 WHERE CURRENT OF 子句来通过游标执行更新,则新值可见。

    游标变量支持两种方式赋值,定义时赋值和先定义后赋值,定义游标变量像定义其他局部变量一样,在游标前加”@”,注意,如果定义全局的游标,只支持定义时直接赋值,并且不能在游标名称前面加“@”,两种定义方式如下:
    --定义后直接赋值
    DECLARE test_Cursor CURSOR FOR
    SELECT * FROM TABLE1
    --先定义后赋值
    DECLARE @test_Cursor2 CURSOR
    SET @test_Cursor2=CURSOR FOR
    SELECT * FROM TABLE2

    游标变量支持两种方式赋值,定义时赋值和先定义后赋值,定义游标变量像定义其他局部变量一样,在游标前加”@”,注意,如果定义全局的游标,只支持定义时直接赋值,并且不能在游标名称前面加“@”,两种定义方式如下:
    --定义后直接赋值
    DECLARE test_Cursor CURSOR FOR
    SELECT * FROM TABLE1
    --先定义后赋值
    DECLARE @test_Cursor2 CURSOR
    SET @test_Cursor2=CURSOR FOR
    SELECT * FROM TABLE2

     

    --定义后直接赋值
    DECLARE test_Cursor CURSOR LOCAL FOR
    SELECT * FROM TABLE1
    DECLARE test_Cursor2 CURSOR GLOBAL FOR
    SELECT * FROM TABLE2
    --用GO结束上面作用域
    GO
    --打开游标
    OPEN test_Cursor
    OPEN test_Cursor2
    全局游标在批处理结束后依然有效
    局部游标在批处理结束后被隐式释放,无法再其他批处理中引用
      如果不指定游标作用域,默认作用域为GLOBAL
     注意,当全局游标和局部游标变量重名时,默认会打开局部变量游标

    --定义后直接赋值
    DECLARE test_Cursor CURSOR LOCAL FOR
    SELECT * FROM TABLE1
    DECLARE test_Cursor2 CURSOR GLOBAL FOR
    SELECT * FROM TABLE2
    --用GO结束上面作用域
    GO
    --打开游标
    OPEN test_Cursor
    OPEN test_Cursor2
    全局游标在批处理结束后依然有效
    局部游标在批处理结束后被隐式释放,无法再其他批处理中引用
      如果不指定游标作用域,默认作用域为GLOBAL
     注意,当全局游标和局部游标变量重名时,默认会打开局部变量游标

    游标的生命周期:

    提取游标语法 

    提取游标语法 

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

    Fetch [ [Next|prior|Frist|Last|Absoute n|Relative n ]
    from
    [Global] cursor_name
    into @variable_name[,....]]
    参数说明:
    Frist:结果集的第一行
    Prior:当前位置的上一行
    Next:当前位置的下一行
    Last:最后一行
    Absoute n:从游标的第一行开始数,第n行。
    Relative n:从相对当前位置数,第n行。(n为负数向前数,否则向后)
    Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。
    注意:    对于未指定SCROLL选项的游标来说,只支持NEXT取值.

    Fetch [ [Next|prior|Frist|Last|Absoute n|Relative n ]
    from
    [Global] cursor_name
    into @variable_name[,....]]
    参数说明:
    Frist:结果集的第一行
    Prior:当前位置的上一行
    Next:当前位置的下一行
    Last:最后一行
    Absoute n:从游标的第一行开始数,第n行。
    Relative n:从相对当前位置数,第n行。(n为负数向前数,否则向后)
    Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。
    注意:    对于未指定SCROLL选项的游标来说,只支持NEXT取值.

     

    实战创建游标

    实战创建游标

    语法:

    准备表数据

    准备表数据

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

    新葡亰496net 13

    新葡亰496net 14

    参数:

    创建游标

    创建游标

    cursor_name:游标的名称

    --声明游标
    declare test_Cursortable3 CURSOR FOR
    SELECT id,NAME FROM TABLE3
    --打开游标
    OPEN test_Cursortable3
    --声明游标提取变量所要存放的变量
    declare @id int,@name varchar(20)
    --定位游标到哪一行
    fetch next from test_Cursortable3 into @id,@name    --into的变量数量必须需与游标查询结果的列数相同
    --fetch FIRST from test_Cursortable3 into @id,@name
    while @@FETCH_STATUS=0  --提取成功,进行下一条数据的提取操作
    begin
        if @id=2
        begin
        Update TABLE3 Set sex='0' Where Current of test_Cursortable3 --更新当前行
        end
        if  @id=10
        begin
        delete TABLE3 where current  of test_Cursortable3    --删除当前行
        end
    fetch next from test_Cursortable3 into @id,@name    --移动游标
    end
    --关闭游标
    close    test_Cursortable3
    --释放游标
    deallocate test_Cursortable3
    
    --声明游标
    declare test_Cursortable3 CURSOR FOR
    SELECT id,NAME FROM TABLE3
    --打开游标
    OPEN test_Cursortable3
    --声明游标提取变量所要存放的变量
    declare @id int,@name varchar(20)
    --定位游标到哪一行
    fetch next from test_Cursortable3 into @id,@name    --into的变量数量必须需与游标查询结果的列数相同
    --fetch FIRST from test_Cursortable3 into @id,@name
    while @@FETCH_STATUS=0  --提取成功,进行下一条数据的提取操作
    begin
        if @id=2
        begin
        Update TABLE3 Set sex='0' Where Current of test_Cursortable3 --更新当前行
        end
        if  @id=10
        begin
        delete TABLE3 where current  of test_Cursortable3    --删除当前行
        end
    fetch next from test_Cursortable3 into @id,@name    --移动游标
    end
    --关闭游标
    close    test_Cursortable3
    --释放游标
    deallocate test_Cursortable3
    

    Local:局部游标,只在定义它的批处理,存储过程或触发器中有效。

    执行结果

    执行结果

    Global:全局游标,在由此连接执行的任何存储过程或批处理中,都可以引用该游标。该游标仅在断开连接时隐式释放。

    新葡亰496net 15

    新葡亰496net 16

    如果未指定游标作用域,那么默认为全局游标。

    注释:

    注释:

    Forward_Only:指定游标只能从第一行滚动到最后一行。 FETCH NEXT 是唯一支持的提取选项。

    @@fetch_status是MicroSoft SQL SERVER的一个全局变量
    其值有以下三种,分别表示三种不同含义[返回类型integer]
    0 FETCH 语句成功
    -1 FETCH 语句失败或此行不在结果集中
    -2 被提取的行不存在

    @@fetch_status是MicroSoft SQL SERVER的一个全局变量
    其值有以下三种,分别表示三种不同含义[返回类型integer]
    0 FETCH 语句成功
    -1 FETCH 语句失败或此行不在结果集中
    -2 被提取的行不存在

    Scroll:指定游标在定义的数据集中可以向任何方向,或任何位置移动。

     

     

    如果在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET 和 DYNAMIC 关键字,则游标默认为 DYNAMIC 游标进行操作。

    使用游标时注意事项:

    使用游标时注意事项:

    如果 FORWARD_ONLY 和 SCROLL 均未指定,那么除非指定了 STATIC、KEYSET 或 DYNAMIC 关键字,否则默认值为 FORWARD_ONLY。

    游标打开后,服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集,同时使用游标也会对某些数据进行封锁。所以游标一旦用过,应及时关闭,避免服务器资源浪费。

    游标打开后,服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集,同时使用游标也会对某些数据进行封锁。所以游标一旦用过,应及时关闭,避免服务器资源浪费。

    STATIC、KEYSET 和 DYNAMIC 游标默认为 SCROLL。

    尽量不要在大量数据上定义游标
    尽量不要使用游标上更新数据
    尽量不要使用insensitive, static和keyset这些参数定义游标
    如果可以,尽量使用FAST_FORWARD关键字定义游标
    如果只对数据进行读取,当读取时只用到FETCH NEXT选项,则最好使用FORWARD_ONLY参数

    尽量不要在大量数据上定义游标
    尽量不要使用游标上更新数据
    尽量不要使用insensitive, static和keyset这些参数定义游标
    如果可以,尽量使用FAST_FORWARD关键字定义游标
    如果只对数据进行读取,当读取时只用到FETCH NEXT选项,则最好使用FORWARD_ONLY参数

    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 并发选项,否则可以更新所有的列。

     

    在声明游标后,可使用下列系统存储过程确定游标的特性:

    新葡亰496net 17

     

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

    -- 定义一个局部动态游标
    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 }   
                        | RELATIVE { 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 relative 1 from test_cursor into @Name
    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
    

     

    下面是一个完整的例子:

    新葡亰496net 18

    下面来定义一个游标用来修改每一个学生的学号,在学号前面添加一位 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
    

    新葡亰496net 19

    如果只是用于循环之类的,最好不要使用游标,游标是不好的,反正能不使用就尽量不用游标,改用其他方法实现。

    比如上面的例子,我们也可以用自定义循环来实现,如下:

    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
    

    新葡亰496net 20

    把上面改掉的学生的学号又给改回来了,效果是一样的。当然,这样一条一条的改明显是有点欠扁的。。。不过只是一个循环的例子。

    当然,存在就有它的意义,游标也有它的好处,比如用自定义的循环解决不了的问题,它就能排上用场了。比如下面一个例子,当我选择一个父节点的时候,我要删除它下面的所有子孙节点(不管多少层级)。

    演示数据:

    新葡亰496net 21

      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
      end
      close one_curr
      deallocate one_curr
    
      select * from Department
    

    新葡亰496net 22

     

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

     

    参考:

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:始建游标,基础知识梳理

    关键词: