sql server 2000/2005 游标的使用操作(转)

原文地址：  sql server 2000/2005 游标的使用操作

 

【1.定义游标】
游标语句的核心是定义了一个游标标识名，并把游标标识名和一个查询语句关联起来。
DECLARE语句用于声明游标，它通过SELECT查询定义游标存储的数据集合。

语句格式：
 DECLARE 游标名称 [INSENSITIVE] [SCROLL]
 CURSOR FOR select语句
 [FOR{READ ONLY|UPDATE[OF 列名字表]}]
 
参数说明：
INSENSITIVE选项：说明所定义的游标使用SELECT语句查询结果的拷贝，对游标的操作都基于该拷贝进行。因此，这期间对游标基本表的数据修改不能反映到游标中。这种游标也不允许通过它修改基本表的数据。
SCROLL选项：指定该游标可用所有的游标数据定位方法提取数据，游标定位方法包括PRIOR、FIRST、LAST、ABSOLUTE n 和RELATIVE n 选项。
Select语句：为标准的SELECT查询语句，其查询结果为游标的数据集合，构成游标数据集合的一个或多个表称作游标的基表。
 在游标声明语句中，有下列条件之一时，系统自动把游标定义为INSENSITIVE游标：
 SELECT语句中使用了DISTINCT、UNION、 GROUP BY或HAVING等关键字；
 任一个游标基表中不存在唯一索引。
 其他
READ ONLY选项：说明定义只读游标。
UPDATE [OF 列名字表]选项：定义游标可修改的列。如果使用OF 列名字表选项，说明只允许修改所指定的列，否则，所有列均可修改。
【2.打开游标】
打开游标语句执行游标定义中的查询语句，查询结果存放在游标缓冲区中。并使游标指针指向游标区中的第一个元组，作为游标的缺省访问位置。查询结果的内容取决与查询语句的设置和查询条件。

语句格式：
 OPEN 〈游标名〉
 
如果打开的游标为INSENSITIVE游标，在打开时将产生一个临时表，将定义的游标数据集合从其基表中拷贝过来。
SQL Server中,游标打开后，可以从全局变量@@CURSOR_ROWS中读取游标结果集合中的行数。

【3.读游标区中的当前元组】
读游标区数据语句是读取游标区中当前元组的值，并将各分量依次赋给指定的共享主变量。FETCH语句用于读取游标中的数据。

语句格式：
 FETCH [[NEXT|PRIOR|FIRST|LAST| ABSOLUTE n| RELATIVE n]
 FROM ] 游标名
 [INTO @变量1, @变量2, ….]
 
其中：
NEXT：说明读取游标中的下一行，第一次对游标实行读取操作时，NEXT返回结果集合中的第一行。
PRIOR、FIRST、LAST、ABSOLUTE n 和RELATIVE n 选项只适用于SCROLL游标。它们分别说明读取游标中的上一行、第一行、最后一行、第n 行和相对于当前位置的第n 行。
n 为负值时，ABSOLUTE n 和RELATIVE n 说明读取从游标结果集合中的最后一行或当前行倒数n行的数据。
INTO子句 说明将读取的数据存放到指定的局部变量中，每一个变量的数据类型应与游标所返回的数据类型严格匹配，否则将产生错误。

【4.利用游标修改数据】
SQL Server中的 UPDATE语句 和 DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行。

UPDATE语句的格式为：
 UPDATE table_name
 SET 列名=表达式}[,…n]
 WHERE CURRENT OF cursor_name
DELETE语句的格式为：
 DELETE FROM table_name
 WHERE CURRENT OF cursor_name
 
说明:
CURRENT OF cursor_name：
表示当前游标指针所指的当前行数据。CURRENT OF 只能在UPDATE和DELETE语句中使用。
注意：
 o使用游标修改基表数据的前提是声明的游标是可更新的。
 o对相应的数据库对象（游标的基表）有修改和删除权限。

【5.关闭游标】
关闭游标后，游标区的数据不可再读，但可以使用OPEN语句再次打开该游标。

语句格式：
 CLOSE 游标名

【6.删除游标】
DEALLOCATE语句删除定义游标的数据结构，删除后不可再用。

语句格式：
 DEALLOCATE 游标名

【7.注意问题】
(1) 尽管使用游标比较灵活，可以实现对数据集中单行数据的直接操作，但游标会在下面几个方面影响系统的性能：
--使用游标会导致页锁与表锁的增加
--导致网络通信量的增加
-增加了服务器处理相应指令的额外开销
(2) 使用游标时的优化问题：
--明确指出游标的用途：for read only或for update
--在for update后指定被修改的列

 

/*------------------------------使用示例-------------------------------*/
create table Topics
(
 topicID int identity primary key,
 topicName varchar(50) 
)
insert into Topics (topicName)
select '耐克' union
select '彪马' union
select '李宁' union
select '安踏' union
select '特步' union
select '361°' 
 
-- 声明游标
declare my_cursor insensitive cursor for select TopicID,TopicName from Topics
-- 打开游标
open my_cursor
-- 循环一个游标
declare @id varchar(30),@name varchar(30)
declare @count int
set @count = @@CURSOR_ROWS -- 获取游标集合中数据行数
-- (方法一)

fetch next from my_cursor into @id,@name;
while @@fetch_status = 0 -- 0：FETCH 语句成功；-1： FETCH 语句失败或此行不在结果集中；-2： 被提取的行不存在。@@fetch_status值的改变是通过fetch next from实现的
 begin
  print @id+'_'+@name
  fetch next from my_cursor into @id,@name;
 end

print '记录总数:'+cast(@count as varchar(10))+'(条)'

close my_cursor -- close
-- (方法二) 
open my_cursor -- open

DECLARE @continue_flag bit
SET @continue_flag = 1
WHILE @continue_flag>0
begin
 fetch next from my_cursor into @id,@name;
 If @@fetch_status=0
  PRINT @id+'_'+@name
 Else
  SET @continue_flag=0
end
-- 关闭游标
close my_cursor
-- 销毁游标
deallocate my_cursor