sql server 游标

「爱情、让人受尽委屈。」 2022-01-15 08:57 257阅读 0赞

[为什么80%的码农都做不了架构师？>>> ][80_] ![hot3.png][]

**一、遇到的问题**

实际上，也不算什么太大的问题O(∩\_∩)O：我们有时候可能希望在批处理或者存储过程中**直接对select结果集进行加工**，这个时候，我们需要一种能够让我们**逐条处理每一行记录**的数据库对象。

**二、游标的概念**

解决上面的问题，我们可以使用一种叫做“游标”的数据库对象。

**游标(Cursor)**可以看做一种数据类型，它可以用来遍历结果集，相当于指针，或者是数组中的下标。它处理结果集的方法有以下几种：

Ø 定位到结果集的某一行

Ø 从当前结果集的位置搜索一行或一部分行

Ø 对结果集中的当前行进行数据修改

**三、游标的使用方法（创建、打开、读取、关闭、删除）**

**【创建游标】**

和定义各种数据类型的方法有点像，但是注意，不要加“@”（实际上也有“游标类型的变量”，和“游标”的用法几乎完全相同，[而且定义时使用@符号）。下面是定义游标的语句][Link 1]：

declare 游标名 cursor [local|global] [forward_only|scroll]
    for
    select查询语句

游标分为局部游标和全局游标两种，local表示局部游标，global表示全局游标（默认值，可以省略）。当指定forward\_only（默认值，可以省略）时，游标是只进的，也就是说只能从头到尾地提取记录，如果需要在行之间来回跳跃，需要指定为scroll。

【使用游标】

只创建游标但是不使用它，就没有任何意义了。下面我们先举个最简单的例子来演示创建好游标之后的几步使用过程：

--【创建游标】
    declare C1 cursor for select xingming from yiren 
    declare @xingming varchar(20) 
    --【打开游标】
    open C1
    --【读取游标】
    fetch next from C1 into @xingming --while的特点就是要先写一次 
    while(@@FETCH_STATUS=0)
    begin
        print '姓名：'+@xingming
        fetch next from C1 into @xingming
    end 
    --【关闭游标】
    close C1 
    --【删除游标】
    deallocate C1

游标的使用方法是不是和Java中的 whle(rs.next())\{\}很像呢？实际上rs.next()执行时就直接在结果集中向后移动一条了，如果没有到达结果集的末端，仍然会执行循环体。在这里使用游标也是一样，@@FETCH\_STATUS的值为0时，游标尚未走到结尾。当它不为0了，游标就走到了结尾，将退出循环。

**fetch next from 游标名 into 变量名列表** 是一种固定形式的读取游标内容的方法。当查询语句选择了多个字段的时候，读取时也需要借助这句话向多个变量赋值。于是写成变量名列表。

**【全局游标和scroll游标】**

前面提到全局游标和scroll游标，下面举个例子：

if(CURSOR_STATUS('global','CURSOR_2')!=-3) deallocate CURSOR_2
    declare CURSOR_2 cursor scroll --全局的scroll游标
    for select xingming,nicheng,xingbie from yiren

\--第一个T-SQL批开始

open CURSOR_2
    declare @seq int,
    @xingming varchar(20),@nicheng varchar(50),@xingbie nchar
    set @seq=4
    fetch absolute @seq from CURSOR_2 into @xingming,@nicheng,@xingbie
    if(@@FETCH_STATUS=0)
    begin
        print '第'+cast(@seq as varchar)+'个艺人是：'+@xingming
        print case @xingbie when '男' then '他' when '女' then '她' end
    +'的昵称是：'+@nicheng
    end
    close CURSOR_2
    go

\--第二个T-SQL批开始

open CURSOR_2
    declare @seq int,
    @xingming varchar(20),@nicheng varchar(50),@xingbie nchar
    set @seq=5 --分成了两个批，需要再次定义@seq
    fetch absolute @seq from CURSOR_2 into @xingming,@nicheng,@xingbie
    if(@@FETCH_STATUS=0)
    begin
        print '第'+cast(@seq as varchar)+'个艺人是：'+@xingming
        print case @xingbie when '男' then '他' when '女' then '她' end
    +'的昵称是：'+@nicheng
    end
    close CURSOR_2
    go

\--在第三个批中删除游标

deallocate CURSOR_2

**当开启了scroll选项后，fetch可以用于读next（后移）、prior（前移）、first（第一行）、last（最后一行）、absolute（以数值定位到绝对行）、relative（以数值定位到相对行）**。

全局游标一旦被定义就会一直存在，所以每个批处理都能看到它。直到使用deallocate来删除它，它才会消失。CURSOR\_STATUS('global','CURSOR\_2')可以检查它的状态。

**【游标的嵌套】**

由于大大影响系统性能，简单了解一下即可。

if(CURSOR_STATUS('global','CURSOR_3')!=-3) deallocate CURSOR_3
    declare CURSOR_3 cursor for
        select yanchuid from yanchu
     
    open CURSOR_3
    declare @ycid int
    fetch next from CURSOR_3
        into @ycid
    while(@@FETCH_STATUS=0)
    begin
     
        print '参加第'+cast(@ycid as varchar)+'次演出的艺人是：'
     
        declare CURSOR_4 cursor for
           select xingming from yiren where yirenid in
           (select yirenid from yanchuyiren where yanchuid=@ycid)
        --这句使用了子查询，实际上可以再嵌套一个游标
        declare @xingming varchar(50)
        open CURSOR_4
        fetch next from CURSOR_4 into @xingming
        while(@@FETCH_STATUS=0)
        begin
           print @xingming
           fetch next from CURSOR_4 into @xingming
        end
        close CURSOR_4
        deallocate CURSOR_4
     
        fetch next from CURSOR_3
           into @ycid
        print ''
    end
    close CURSOR_3
    deallocate CURSOR_3

**【游标变量】**

游标变量是真正的把游标当做数据类型来使用的一种方法，游标变量和游标对象的区别就在于是否有@。创建游标变量的时候，首先declare @游标变量名 cursor，然后set @游标变量名=cursorfor select语句。

declare @c1 CURSOR
    set @c1=cursor for select xingming from yiren
    open @c1
    declare @xingming varchar(50)
    fetch next from @c1 into @xingming
    print @xingming
    close @c1
    deallocate @c1

**四、游标的注意事项**

**【游标的缺点】**

使用游标会把结果集一条条取出来处理，增加了服务器的负担，再者使用游标的效率远远没有使用默认结果集的效率高。所以，能不用游标就尽量不要用。

**【游标的补充说明】**

当我们刚刚打开一个游标的时候，它并不指向第一条记录，而是指向第一条记录的前边。我们可以拿书做比喻，游标不仅仅可以指向记录集中的记录（书内容的每一页），也可以指向记录集外部没有记录的地方（书的封面和封底）。

@@fetch\_status有3种取值：0表示fetch 正常执行、-1表示fetch超出了结果集、-2表示fetch所指向的行已经不存在了。

**五、修改分页查询存储过程，使用游标**

将第一个分支修改成如下代码：

if @currentpage >1
    begin
        if @currentpage>@totalpages
        begin
           set @currentpage = @totalpages
        end
        declare    @start int,@count int
        set @count = 0
        set @start = @currentpage*@pagesize+1
        set @sql='declare cursor_1 cursor scroll for select * from '
        +@tablename+' order by '+@idname
        exec(@sql)
        open cursor_1
        fetch relative @start,@pagesize from cursor_1
        while @@fetch_status=0
        begin
           set @count = @count+1
           fetch next from cursor_1
           if @count=@pagesize-1
               break
        end
        close cursor_1
        deallocate cursor_1
    end
    并去掉原来go前面的
    exec(@sql)

即可。如果不去掉这句，会在存储过程的最后额外再执行一次这句话，从而错误地再次生成@cursor\_1游标。

原文地址： [点击打开链接][Link 2]

转载于:https://my.oschina.net/cjkall/blog/195854

[80_]: https://my.oschina.net/u/2663968/blog/3051541
[hot3.png]: /images/20220115/bc63e99408b8432e90ba3d1af4bff975.png
[Link 1]: mailto:%E8%80%8C%E4%B8%94%E5%AE%9A%E4%B9%89%E6%97%B6%E4%BD%BF%E7%94%A8@%E7%AC%A6%E5%8F%B7%EF%BC%89%E3%80%82%E4%B8%8B%E9%9D%A2%E6%98%AF%E5%AE%9A%E4%B9%89%E6%B8%B8%E6%A0%87%E7%9A%84%E8%AF%AD%E5%8F%A5
[Link 2]: http://wenku.baidu.com/link?url=MJ6wybvW_q39IklZwQH6a_3PY2a9zLDKroLYk_Eq_Jqjus2PZwvvXIKDltnoNPDVjHP732lkVuL4vN9q6vJ6fGmWNSGuQo82lv9F2O1CJE3