游标的概念文档格式.docx
- 文档编号:4118286
- 上传时间:2023-05-02
- 格式:DOCX
- 页数:19
- 大小:22.62KB
游标的概念文档格式.docx
《游标的概念文档格式.docx》由会员分享,可在线阅读,更多相关《游标的概念文档格式.docx(19页珍藏版)》请在冰点文库上搜索。
取最后一行数据。
PRIOR:
取前一行数据。
NEXT:
取后一行数据。
RELATIVE:
按相对位置取数据。
ABSOLUTE:
按绝对位置取数据。
如果未指定SCROLL,则NEXT是惟一支持的提取选项。
select_statement:
定义游标结果集的标准SELECT语句。
在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTEBY、FORBROWSE和INTO。
READONLY:
表明不允许游标内的数据被更新,尽管在默认状态下游标是允许更新的。
在UPDATE或DELETE语句的WHERECURRENTOF子句中不允许引用游标。
UPDATE[OFcolumn_name[,...n]]:
定义游标内可更新的列。
如果指定OFcolumn_name[,...n]参数,则只允许修改所列出的列。
如果在UPDATE中未指定列的列表,则可以更新所有列。
2.SQLServer扩展格式
DECLAREcursor_nameCURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[STATIC|KEYSET|DYNAMIC|FAST_FORWARD]
[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
[TYPE_WARNING]
[FORUPDATE[OFcolumn_name[,...n]]]
LOCAL:
定义游标的作用域仅限在其所在的批处理、存储过程或触发器中。
当建立游标在存储过程执行结束后,游标会被自动释放。
GLOBAL:
指定该游标的作用域对连接是全局的。
在由连接执行的任何存储过程或批处理中,都可以引用该游标名称。
该游标仅在脱接时隐性释放。
FORWARD_ONLY:
指定游标只能从第一行滚动到最后一行。
FETCHNEXT是惟一受支持的提取选项非指定STATIC、KEYSET或DYNAMIC关键字,否则默认为FORWARD_ONLY。
STATIC、KEYSET和DYNAMIC游标默认为SCROLL。
与ODBC和ADO这类数据库API不同,STATIC、KEYSET和DYNAMICTransact-SQL游标支持FORWARD_ONLY。
FAST_FORWARD和FORWARD_ONLY是互斥的;
如果指定一个,则不能指定另一个。
STATIC:
对游标的所有请求都从tempdb中的该临时表中得到应答;
因此,在对该游标进行提取操作时返回的数据中不反映对基表所做的修改,并且该游标不允许修改。
KEYSET:
指定当游标打开时,游标中行的成员资格和顺序已经固定。
对行进行惟一标识的键集内置在tempdb内一个称为keyset的表中。
对基表中的非键值所做的更改(由游标所有者更改或由其他用户提交)在用户滚动游标时是可视的。
其他用户进行的插入是不可视的(不能通过Transact-SQL服务器游标进行插入)。
如果某行已删除,则对该行的提取操作将返回@@FETCH_STATUS值-2。
从游标外更新键值类似于删除旧行后接着插入新行的操作。
含有新值的行不可视,对含有旧值的行的提取操作将返回@@FETCH_STATUS值-2。
如果通过指定WHERECURRENTOF子句用游标完成更新,则新值可视。
DYNAMIC:
定义一个游标,以反映在滚动游标时对结果集内的行所做的所有数据的更改。
行的数据值、顺序和成员在每次提取时都会更改。
动态游标不支持ABSOLUTE提取选项。
FAST_FORWARD:
指明一个FORWARD_ONLY、READ_ONLY型游标。
SCROLL_LOCKS:
指定确保通过游标完成的定位更新或定位删除可以成功。
将行读入游标以确保它们可用于以后的修改时,SQLServer会锁定这些行。
如果还指定了FAST_FORWARD,则不能指定SCROLL_LOCKS。
OPTIMISTIC:
指明在数据被读入游标后,如果游标中某行数据已发生变化,那么对游标数据进行更新或删除可能会导致失败。
TYPE_WARNING:
指定如果游标从所请求的类型隐性转换为另一种类型,则给客户端发送警告消息。
使用DECLARECURSOR语句创建以下几种形式的游标。
(1)示例:
创建一个名为“MyCursor”的标准游标。
USE
销售管理系统
DECLAREMyCursorCURSORFOR
SELECT*FROM操作员信息表
GO
(2)示例:
创建一个名为“MyCursor_01”的只读游标。
USE销售管理系统
DECLAREMyCursor_01CURSORFOR
FORREADONLY
--只读游标
(3)示例:
创建一个名为“MyCursor_02”的更新游标。
DECLAREMyCursor_02CURSORFOR
SELECT操作员编号,操作员姓名,操作员年龄FROM操作员信息表
FORUPDATE
--更新游标
打开游标
打开一个声明的游标使用OPEN命令。
OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}
指定cursor_name为全局游标。
cursor_name:
已声明的游标名称,如果全局游标和局部游标都使用cursor_name作为其名称,那么如果指定了GLOBAL,cursor_name指的是全局游标,否则,cursor_name指的是局部游标。
cursor_variable_name:
游标变量的名称,该名称引用一个游标。
说明:
如果使用INSENSITIV或STATIC选项声明了游标,那么OPEN将创建一个临时表以保留结果集。
如果结果集中任意行的大小超过SQLServer表的最大行大小,OPEN将失败。
如果使用KEYSET选项声明了游标,那么OPEN将创建一个临时表以保留键集。
临时表存储在tempdb中。
首先声明一个名为MyCursor_001的游标,然后使用OPEN命令打开该游标。
操作步骤如下:
(1)在操作系统中选择“开始”→“所有程序”→“MicrosoftSQLServer”→“查询分析器”命令,打开查询分析器。
(2)在查询分析器的工具栏中选择要连接的数据库,这里选择“销售管理系统”。
(3)在代码编辑区中编写如下代码。
SQL语句如下:
DECLAREMyCursor_001CURSORFOR
--声明游标
WHERE操作员编号='
CY20040604006'
OPENMyCursor_001
--打开游标
从游标中读取数据
当打开一个游标之后,就可以读取游标中的数据了。
可以使用FETCH命令读取游标中的某一行数据。
FETCH
[[NEXT|PRIOR|FIRST|LAST
|ABSOLUTE{n|@nvar}
|RELATIVE{n|@nvar}
]
FROM
{{[GLOBAL]cursor_name}|@cursor_variable_name}
[INTO@variable_name[,...n]]
返回紧跟当前行之后的结果行,并且当前行递增为结果行。
如果FETCHNEXT为对游标的第一次提取操作,则返回结果集中的第一行。
NEXT为默认的游标提取选项。
返回紧临当前行前面的结果行,并且当前行递减为结果行。
如果FETCHPRIOR为对游标的第一次提取操作,则没有行返回并且游标置于第一行之前。
返回游标中的第一行并将其作为当前行。
返回游标中的最后一行并将其作为当前行。
ABSOLUTE{n|@nvar}:
如果n或@nvar为正数,返回从游标头开始的第n行,并将返回的行变成新的当前行。
如果n或@nvar为负数,返回游标尾之前的第n行,并将返回的行变成新的当前行。
如果n或@nvar为0,则没有行返回。
RELATIVE{n|@nvar}:
如果n或@nvar为正数,返回当前行之后的第n行,并将返回的行变成新的当前行。
如果n或@nvar为负数,返回当前行之前的第n行,并将返回的行变成新的当前行。
如果n或@nvar为0,返回当前行。
如果对游标的第一次提取操作时将FETCHRELATIVE的n或@nvar指定为负数或0,则没有行返回。
n必须为整型常量且@nvar必须为smallint、tinyint或int。
说明:
在前两个参数中,包含了n和@nvar其表示游标相对与作为基准的数据行所偏离的位置。
要从中进行提取的开放游标的名称。
如果同时有以cursor_name作为名称的全局和局部游标存在,若指定为GLOBAL,则cursor_name对应于全局游标,未指定GLOBAL,则对应于局部游标。
@cursor_variable_name:
游标变量名,引用要进行提取操作的打开的游标。
INTO@variable_name[,...n]:
允许将提取操作的列数据放到局部变量中。
列表中的各个变量从左到右与游标结果集中的相应列相关联。
各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。
变量的数目必须与游标选择列表中的列的数目一致。
@@FETCH_STATUS:
返回上次执行FETCH命令的状态。
在每次用FETCH从游标中读取数据时,都应检查该变量,以确定上次FETCH操作是否成功,决定如何进行下一步处理。
@@FETCH_STATUS变量有3个不同的返回值,说明如下:
返回值为0:
FETCH语句成功。
返回值为-1:
FETCH语句失败或此行不在结果集中。
返回值为-2:
被提取的行不存在。
当使用SQL-92语法来声明一个游标时,没有选择SCROLL选项,则只能使用FETCHNEXT命令来从游标中读取数据,即只能从结果集第一行按顺序地每次读取一行。
由于不能使用FIRST、LAST、PRIOR,所以无法回滚读取以前的数据。
如果选择了SCROLL选项,则可以使用所有的FETCH操作。
通常游标取数的操作与WHILE循环紧密结合,下面将使用@@FETCH_STATUS控制在一个WHILE循环中的游标活动。
程序运行结果如图1所示。
图1
从游标中读取数据
USE销售管理系统
--引入数据库
DECLAREReadCursorCURSORFOR
--声明一个游标
SELECT操作员编号,操作员姓名,操作员性别,操作员住址
FROM操作员信息表
OPENReadCursor
FETCHNEXTFROMReadCursor
--执行取数操作
WHILE@@FETCH_STATUS=0
--检查@@FETCH_STATUS,以确定是否还可以继续取数
BEGIN
FETCHNEXTFROMReadCursor
END
关闭游标
当游标使用完毕之后,使用CLOSE语句可以关闭游标,但不释放游标占用的系统资源。
CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}
开放游标的名称。
如果全局游标和局部游标都使用cursor_name作为它们的名称,那么当指定GLOBAL时,cursor_name引用全局游标;
否则,cursor_name引用局部游标。
与开放游标关联的游标变量名称。
示例:
声明一个名为“CloseCursor”的游标,并使用Close语句关闭游标。
DECLARECloseCursorCursorFOR
SELECT*FROM销售表
FORREADONLY
OPENCloseCursor
CLOSECloseCursor
--关闭游标
释放游标
当游标关闭之后,并没有在内存中释放所占用的系统资源,所以可以使用DEALLOCATE命令删除游标引用。
当释放最后的游标引用时,组成该游标的数据结构由SQLServer释放。
DEALLOCATE{{[GLOBAL]cursor_name}|@cursor_variable_name}
已声明游标的名称。
当全局和局部游标都以cursor_name作为它们的名称存在时,如果指定GLOBAL,则cursor_name引用全局游标,如果未指定GLOBAL,则cursor_name引用局部游标。
cursor变量的名称。
@cursor_variable_name必须为cursor类型。
当使用DEALLOCATE@cursor_variable_name来删除游标时,游标变量并不会被释放,除非超过使用该游标的存储过程和触发器的范围。
使用DEALLOCATE命令释放名为“FreeCursor”的游标。
DECLAREFreeCursorCursorFOR
OPENFreeCursor
CloseFreeCursor
DEALLOCATEFreeCursor
--释放游标
创建游标变量
在批处理或过程的正文中用DECLARE语句声明变量,并用SET或SELECT语句给其指派值。
游标变量可通过该语句声明,并且可用在其他与游标相关的语句中。
所有变量在声明后均初始化为NULL。
首先创建一个游标并打开该游标,之后创建一个游标变量并将游标的值(SELECT*FROMJobs)赋给游标变量,并通过FETCH语句读取游标变量中的值,最后关闭并释放游标。
创建游标变量
USEpubs
DECLAREMyCursor_001CursorFOR
--创建游标
SELECT*FROMJobs
--打开游标DECLARE@CursorVarCursor
--创建游标变量
SET@CursorVar=MyCursor_001
--为游标变量赋值
FETCHNEXTFROM@CursorVar
--读取游标变量中的值
CLOSEMyCursor_001
DEALLOCATEMyCursor_001
静态游标
静态游标的完整结果集在游标打开时建立在tempdb中。
静态游标总是按照游标打开时的原样显示结果集。
静态游标在滚动期间很少或根本检测不到变化,虽然它在tempdb中存储了整个游标,但消耗的资源很少。
尽管动态游标使用tempdb的程度最低,在滚动期间它能够检测到所有变化,但消耗的资源也更多。
键集驱动游标介于二者之间,它能检测到大部分的变化,但比动态游标消耗更少的资源。
动态游标
动态游标与静态游标相对。
当滚动游标时,动态游标反映结果集中所做的所有更改。
结果集中的行数据值、顺序和成员在每次提取时都会改变。
所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标可见。
只进游标
只进游标不支持滚动,它只支持游标从头到尾顺序提取。
只在从数据库中提取出来后才能行检索。
对所有由当前用户发出或由其他用户提交、并影响结果集中的行的INSERT、UPDATE和DELETE语句,其效果在这些行从游标中提取时是可见的。
键集驱动游标
打开游标时,键集驱动游标中的成员和行顺序是固定的。
键集驱动游标由一套被称为键集的惟一标识符(键)控制。
键由以惟一方式在结果集中标识行的列构成。
键集是游标打开时来自所有适合SELECT语句的行中的一系列键值。
键集驱动游标的键集在游标打开时建立在tempdb中。
对非键集列中的数据值所做的更改(由游标所有者更改或其他用户提交)在用户滚动游标时是可见的。
在游标外对数据库所做的插入在游标内是不可见的,除非关闭并重新打开游标。
使用游标修改数据
本节主要介绍如何使用游标修改数据
在查询分析器中声明变量,并设置@id变量的值,然后声明一个游标并打开该游标,使用FETCHNEXT方法来获取游标的下一行数据,并将此数据赋值给变量,如果FETCH语句执行成功则判断当前游标所指定的操作员编号是否与变量@id相等,如果相等则更新数据,最后关闭游标并释放游标所占用的资源。
将编号为“CY20061010001”的操作员年龄修改为“30”岁
使用游标修改数据
Declare@idchar(20)
/*声明变量*/
declare@idschar(20)
declare@nameschar(20)
set@id='
CY20061010001'
--为变量赋值
DECLAREauthors_cursorCURSOR
FORSELECT操作员编号,操作员姓名FROM操作员信息表
OPENauthors_cursor
FETCHNEXTFROMauthors_cursor
--获取游标的下一行数据
into@ids,@names
--使变量获得当前游标指定行的操作员编号和操作员姓名
WHILE@@FETCH_STATUS=0
--FETCH语句执行成功
if@id=@ids
--判断变量的值是否与游标指定的操作员编号相等
begin
--更新指定条件的操作员年龄
update操作员信息表set操作员年龄=30where操作员编号=@ids
end
--获取游标的下一行
End
CLOSEauthors_cursor
DEALLOCATEauthors_cursor
SELECT*FROM操作员信息表
--重新选择操作员信息表
使用游标删除数据
Transact-SQL脚本、存储过程和触发器可以使用DELETE语句中的WHERECURRENTOF子句删除它们当前所处的游标行。
Deletetable_name
SETcolumn_name1-{expression1|NULL(select_statement)}
[,column_name2={expression2|NULL(select_statement)}
WHERECURRENTOFcursor_name
table_name:
用来指定UPDATE或DELETE的表名。
column_name:
用来指定UPDATE的列名。
用来指定游标的名称。
下面介绍如何使用WHERECURRENTOF方法删除数据
声明一个游标并打开,然后使用FETCHNEXT方法将游标指针下移一行,最后使用WHERECURRENTOF方法删除指定条件的数据,然后关闭并释放游标所占用的系统资源。
DECLAREdeletecursorCURSOR
FORSELECT*FROM操作员信息表
CY20061
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 游标 概念
![提示](https://static.bingdoc.com/images/bang_tan.gif)