[转载]T-SQL学习笔记-程序设计基础-游标

游标的引入
1 .在数据的开发过程中，从某一结果集中逐一读取一条记录，用游标解决，游标占资源大，确定不用后将其释放。
2 .声明游标（四个组成部分）
( 1 ).declare游标
( 2 ).open游标
( 3 ).从一个游标中fetch信息
( 4 ).close或deallocate游标
一、声明游标主要内容：
( 1 ).游标名字
( 2 ).数据来源表和列
( 3 ).选取条件
( 4 ).属性仅读或可修改
1 .语法格式：
declare 游标名称 [ scroll ] cursor
[ local|global ]
[ forward_only|scroll ]
for 选择语句
[ for[read_only|update [of 字段名称1，字段名称2， ] ]]
其中：
1 > .local | global指定该游标的作用域是局部的还是全局的。
2 > .如果把forward_only选择为forward_only, 则游标只能从第一行滚动到最后一行。
3 > .scroll表明所有的提取操作，如first,last,prior, next ,relative,absolute都可用。如不使用该保留字，那么只能进行next提取操作。
4 > .选择语句：是定义结果集的select语句，应该注意的是在游标中不能使用compute, compute by for browse into语句.
5 > . read only :表明不允许游标内的数据被更新。
6 > . update [ of 字段名1[,n ] ]:定义在游标中可被修改的列。

例1：标准游标
declare taihang cursor
for
select id,name,address,city,state
from table
例2：只读游标
declare taihang cursor
for
select id,name,address,city,atate
from table
for read only
例3：更新游标
declare taihang cursor
for
select name,address
from table
for update

二、打开游标
1 .声明之后，如要从游标中读取数据，必须打开游标，使用open命令。
语法：
open 　游标名称
注意:
1 > .在打开游标时，如果游标声明语句中使用了insensitive保留字，则open产生一个临时表来存放结果集。如果在结果集中任何一行数据的大小超过SQL Server定义的最大行尺寸时，open命令将失败。
2 > .insensitive:表明SQL Server会将游标定义所选取出来的数据记录存放在一临时表内，（建立在tempdb数据库下）对该游标的操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取数据，即游标不会随着基本表内容的改变面改变，同时也不法通过游标来更新基本表。

如果不使用该保留字，那么对基本表的更新，删除都会公映到游标中。

三、读取游标中的数据－ fetch
1 .当游标被成功打开以后就可以从游标中逐行地读取数据以时行相关处理。从游标中读取数据主要使用fetch命令。
语法：
fetch
[ [next|prior|first|last
|absolute{n|@nvar}
|relative{n|@nvar} ]
from ] cursor_name
[ into @variable_name1,@variable_name2 ]
注：
1 > . next :返回结果集中当前行的下一行，并增加当前行数为返回行行数。如果fetch next是第一次读取游标中数据，则返回结果集中的第一行而不是第二行。
2 > .prior:返回结果集中当前行的前一行，并减少当前行数为返回行行数。如果fetch prior是第一次读取游标中的数据，刚无数据记录返回，并把游标位置设为第一行。
3 > .first:返回游标中的第一行。
4 > .last:返回游标中的最后一行。
5 > .absolute{n | @nvar }:如果N或 @nvar为正数 ，则表示从游标中返回的数据行数。如果N或 @nvar为负数 ，则返回游标内从最后一行数据算起的第N或 @nvar行数据 。若N或 @nvar超过游标的数据子集范畴 ，则 @@fetch_status返回 - 1 。在该情况下，如果N或 @nvar为负数 ，则再执行fetch next命令会得到第一行数据；如果N或 @nvar为正值 ，如执行fetch prior命令刚会得到最后一行数据。N或 @nvar可以是固定值 ，也可以是smallint,tinyint或int类型的变量。
6 > .relative{N | @nvar }:若N或 @nvar为正数 ，则读取游标当前位置起向后的第N或 @nvar行数据 。如果N或 @nvar为负数 ，则返回游标当前位置起向前的第N或 @nvar行数据 。若N或 @nvar超过游标的数据子集范畴 ，则 @@fetch_status返回 - 1 。在该情况下，如果N或 @nvar为负数 ，则再执行fetch next命令会得到第一行数据；如果N或 @nvar为正值 ，如执行fetch prior命令刚会得到最后一行数据。N或 @nvar可以是固定值 ，也可以是smallint,tinyint或int类型的变量。
7 > . into @variable_name [ ,n ] :允许使用fetch命令读取的数据存放在多个变量中；在变量行中的每个变量必须与游标结果集中相应的列相对应，每一变量的数据类型也要与游标中的数据列的数据类型相匹配。

2 .检查游标状态
@@fetch_status :全局变量，返回上次执行fetch命令的状态，在每次用fetch从游标中读取数据时，都应检查该变量以确定上次fetch操作是否成功，来决定如何进行下一步处理。 @@fetch_status变量有三个不同返回值 。
1 > . 0 ：表示成功取出了一行。
2 > . - 1表示未取到数据。游标位置超出结果集。
3 > . - 2表示返回的行已经不再是结果集的一个成员，这种情况只有在游标不是insensitive的情况下出现，即其它进程已删除了行或改变了游标打开的关键值。

3 .编辑当前游标行
通常，用游标来从基础表中检索数据，以实现对数据行处理，在修改游标中的数据，即进行定位更新或删除游标所包含的数据，所以必须执行另外的更新或删除命令，并在where子句中重新给定条件才能修改到该行数据，但是如果在声明游标时使用了for update语句那么就可以在update或delete命令中以where current of关键字直接修改或删除当前游标中所存储的数据，而不必使用where子句重新给出指定条件。当改变游标中数据时，这种变化会自动地影响到游标的基础表。但是如果在声明游标时选择了insensitive选项时，该游标中的数据不能被修改。

进行定位修改或删除游标中的数据的语法规则语法：
update table_name
set column_name1 = {expression1 | null (select_statement)}
[ ,column_name2={expression2|null(select_statement)} ]
where current of cursor_name

delete from table_name
where current of cursor_name
其中：
1 > .table_name：是update或delete的表名。
2 > .column_name：uqdate的列名
3 > .cursor_name:游标名

例1：首先声明一个游标
declare authors_cur scroll cursor
for
select * from authors
for update of au_lname,au_fname

更新authors表中的au_lname和au_fname列
update authors
set au_lname = ' china ' ,au_fname = ' asia '
where current of authors_cur

删除authors表中的一行数据
delete from authors
where current of authors_cur
注：以上的更新或删除操作总是在游标的当前位置，

例：下面是一个完整的定位更新的例子。
declare @au_id int ( 11 ), @au_lname varchar ( 40 ), @au_fname varchar ( 20 )
declare authors_cur cursor
for
select au_id,au_lname,au_fname from authors
for update of au_id,aulname,au_fname
open authors_cur /* open then cursor */
fetch next from authors_cer into @au_id , @au_lname , @au_fname
while @@fetch_status = 0 /* loop the rows in the cursor */
begin
if @au_id = ' 172-32-1176 '
update authors
set au_lname = ' smith ' ,au_fname = ' jake '
where current of authors_cur
/* get next row */
fetch next from authors_cer into @au_id , @au_lname , @au_fname
end
deallocate authors_cur /* close the cursor */

四、释放游标
1 .关闭游标
使用close命令关闭游标，在处理完游标中数据之后，发布关闭游标来释放数据结果集和定位于数据记录上的锁，close语句关闭游标但不释放游标占用的数据结构。如果准备在随后的使用中再次打开游标，则应使用open命令。
语法： close 游标名称
2 .释放游标
在使用游标时各种针对游标的操作或者引用游标各或者引用指向游标的游标变量，当close命令关闭游标时并没有释放游标占用的数据结构。因此常使用deallocate命令删除掉游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。
语法： deallocate 游标名称

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/443058/viewspace-926981/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/443058/viewspace-926981/