sql server 中游标的使用方法(创建、打开、读取、关闭、删除)

　一、遇到的问题

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

　　二、游标的概念

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

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

　　定位到结果集的某一行

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

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

　　三、游标的使用方法(创建、打开、读取、关闭、删除)

　　【创建游标】

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

　　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游标。