MS SQL 常用SQL语句（五）：创建、使用、关闭和释放游标等操作sql

5、游标操作：

--1、创建游标
--语法结构：
/*
declare 游标名称 [Insensitive] cursor
[local|global]
[forward_only|scroll]
[static|keyset|dynamic|fast_forward]
[read_only|scroll_locks|optimistic]
[type_waring]
for SQL语句
[For [read only|update]]
*/
--只读游标
declare testCursor cursor for
select * from employee
for read only;--该游标只读

--普通游标
declare testCursor2 cursor local scroll for
select * from employee;
--(sql server2005中不能添加local关键字，否则“游标可以创建成功open的时候会提示游标不存在”)

--可更新游标
declare testCursor3 cursor local scroll for
select * from employee
for update;


--2、打开游标
open testCursor2;

--3、使用游标
--一条一条往后获取
fetch next from testCursor2;

--一条一条往前获取（当前游标不能为只进游标）
fetch prior from testCursor2;
--报错：提取类型 prior 不能与只进游标一起使用。因为testCursor为只进游标

--获取第一条记录
fetch first from testCursor2;

--获取最后一条记录
fetch last from testCursor2;

--跳转到指定条数:获取游标的第二条记录，这个第二条以整个游标中的数据为起点
fetch absolute 1 from testCursor2;

--使用变量进行跳转
declare @index int;
set @index = 3;
fetch absolute @index from testCursor2;


--相对定位：以当前位置为起点，正数表示向前，负数表示向后
--目前游标的位置为4，relative 1表示向前一条也就是第5条，-1表示向后一条也就是第3条
fetch relative 5 from testCursor2;

--从游标中获取值存入到变量中
declare @empId int;
declare	@empName varchar(50);
declare @empAge smallint;
declare	@empPay money;
declare	@empJob varchar(50);
declare	@empAddress varchar(200);
declare	@telephone varchar(50);
declare	@empReward money;
declare	@total money;

fetch absolute 1 from testCursor2 into @empId,@empName,@empAge,@empPay,@empJob,@empAddress,@telephone,@empReward,@total;

print '编号：'+cast(@empId as varchar(30));
print '姓名：'+@empName;
print '年龄：'+cast(@empAge as varchar(30));
print '工资：'+cast(@empPay as varchar(30));
print '职务：'+@empJob;
print '住址：'+@empAddress;
print '电话：'+@telephone;
print '奖金：'+cast(@empReward as varchar(30));
print '总共收入：'+cast(@total as varchar(30));

--4、关闭游标：游标关闭之后还可以再打开
--语法结构：close 游标名称
close testCursor;

--5、释放游标:游标释放之后不能再打开
--语法结构：deallocate 游标名称
deallocate testCursor

创建游标的语法结构参数说明：

[local|global]：游标的作用范围,分别表示局部(即游标所在的过程/触发器/批处理程序中使用)和全局(即除了可以在过程/触发器/批处理程序中使用之外整个会话层都可以使用),默认为local

[forward_only|scroll]：只进游标和上下调整游标,若定义成只进游标那么该游标只能从前往后获取数据不能从后往前或跳着读取数据,而scroll游标可以随意获取数据。默认为forward_only。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only

[static|keyset|dynamic|fast_forward]：Static:静态游标。KeySet：键集游标。Dynamic：动态游标，不支持Absolute提取选项。Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。

[read_only|scroll_locks|optimistic]：Read_Only:不能通过游标对数据进行删改。Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。

[type_waring]:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。

[For [read only|update]]：只读游标和更新游标，若不定义就是普通游标

 

游标的分类：

根据游标检测结果集变化的能力和消耗资源的情况不同，SQL Server支持的API服务器游标分为一下4种：
静态游标: 静态游标的结果集，在游标打开的时候建立在TempDB中，不论你在操作游标的时候，如何操作数据库，游标中的数据集都不会变。例如你在游标打开的时候，对游标查询的数据表数据进行增删改，操作之后，静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致，则重新关闭打开游标即可。

动态游标:这个则与静态游标相对，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新，他们将立即可见。在游标外部所做的更新直到提交时才可见。

 

只进游标：只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。
 

键集驱动游标：打开键集驱动游标时，该有表中的各个成员身份和顺序是固定的。打开游标时，结果集这些行数据被一组唯一标识符标识，被标识的列做删改时，用户滚动游标是可见的，如果没被标识的列增该，则不可见，比如insert一条数据，是不可见的，若可见，须关闭重新打开游标。

静态游标在滚动时检测不到表数据变化，但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化，但消耗的资源却较多。键集驱动游标则处于他们中间，所以根据需求建立适合自己的游标，避免资源浪费。
关于游标更详细的讲解可参考：http://www.cnblogs.com/knowledgesea/p/3699851.html