然而不能通过在整个行集中修改或者选取数据来获得所需Word文件下载.docx
《然而不能通过在整个行集中修改或者选取数据来获得所需Word文件下载.docx》由会员分享，可在线阅读，更多相关《然而不能通过在整个行集中修改或者选取数据来获得所需Word文件下载.docx（21页珍藏版）》请在冰点文库上搜索。
（3）从一个游标中查找信息。
（4）关闭游标。
（5）释放游标。
13.1.2游标的类型
SQLServer提供了4种类型的游标：
静态游标、动态游标、只进游标和键集驱动的游标。
这些游标的检测结果集变化的能力和内存占用的情况都有所不同，数据源没有办法通知游标当前提取行的更改。
游标检测这些变化的能力也受事务隔离级别的影响。
1．静态游标
静态游标的完整结果集在游标打开时建立在tempdb中。
静态游标总是按照游标打开时的原样显示结果集。
静态游标在滚动期间很少或根本检测不到变化，虽然它在tempdb中存储了整个游标，但消耗的资源很少。
尽管动态游标使用tempdb的程度最低，在滚动期间它能够检测到所有变化，但消耗的资源也更多。
键集驱动游标介于二者之间，它能检测到大部分的变化，但比动态游标消耗更少的资源。
2．动态游标
动态游标与静态游标相对。
当滚动游标时，动态游标反映结果集中所做的所有更改。
结果集中的行数据值、顺序和成员在每次提取时都会改变。
所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标可见。
3．只进游标
只进游标不支持滚动，它只支持游标从头到尾顺序提取。
只有从数据库中提取出来后才能进行检索。
对所有由当前用户发出或由其他用户提交、并影响结果集中的行的INSERT、UPDATE和DELETE语句，其效果在这些行从游标中提取时是可见的。
4．键集驱动游标
打开游标时，键集驱动游标中的成员和行顺序是固定的。
键集驱动游标由一套被称为键集的唯一标识符（键）控制。
键由以唯一方式在结果集中标识行的列构成。
键集是游标打开时来自所有适合SELECT语句的行中的一系列键值。
键集驱动游标的键集在游标打开时建立在tempdb中。
对非键集列中的数据值所做的更改（由游标所有者更改或其他用户提交）在用户滚动游标时是可见的。
在游标外对数据库所做的插入在游标内是不可见的，除非关闭并重新打开游标。
13.2游标的基本操作
光盘\TM\lx\13\游标的基本操作.mp4
游标的基本操作包括声明游标、打开游标、读取游标中的数据、关闭游标和释放游标。
本节就详细介绍如何操作游标。
13.2.1声明游标
声明游标可以使用DECLARECURSOR语句。
此语句有两种语法声明格式，分别为ISO标准语法和Transact-SQL扩展的语法，下面将分别介绍声明游标的两种语法格式。
1．ISO标准语法
语法如下：
DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR
FORselect_statement
FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]
参数说明如下。
☑DECLAREcursor_name：
指定一个游标名称，其游标名称必须符合标识符规则。
☑INSENSITIVE：
定义一个游标，以创建将由该游标使用的数据的临时复本。
对游标的所有请求都从tempdb中的临时表中得到应答；
因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。
使用SQL-92语法时，如果省略INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。
☑SCROLL：
指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。
Ø
FIRST：
取第一行数据。
LAST：
取最后一行数据。
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．Transact-SQL扩展的语法
DECLAREcursor_nameCURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[STATIC|KEYSET|DYNAMIC|FAST_FORWARD]
[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
[TYPE_WARNING]
[FORUPDATE[OFcolumn_name[,...n]]]
DECLARECURSOR语句的参数及说明如表13.1所示。
表13.1DECLARECURSOR语句的参数及说明
参数
描述
DECLAREcursor_name
指定一个游标名称，其游标名称必须符合标识符规则
LOCAL
定义游标的作用域仅限在其所在的批处理、存储过程或触发器中。
当建立游标在存储过程执行结束后，游标会被自动释放
GLOBAL
指定该游标的作用域对连接是全局的。
在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。
该游标仅在脱接时隐性释放
FORWARD_ONLY
指定游标只能从第一行滚动到最后一行。
FETCHNEXT是唯一受支持的提取选项非指定STATIC、KEYSET或DYNAMIC关键字，否则默认为FORWARD_ONLY。
STATIC、KEYSET和DYNAMIC游标默认为SCROLL。
与ODBC和ADO这类数据库API不同，STATIC、KEYSET和DYNAMIC
Transact-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
指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息
【例13.1】创建一个名为Cur_Emp的标准游标。
（实例位置：
光盘\TM\sl\13\1）
SQL语句如下：
USEdb_2012
DECLARECur_EmpCURSORFOR
SELECT*FROMEmployee
GO
运行结果如图13.1所示。
【例13.2】创建一个名为Cur_Emp_01的只读游标。
光盘\TM\sl\13\2）
DECLARECur_Emp_01CURSORFOR
FORREADONLY--只读游标
运行结果如图13.2所示。
图13.1创建标准游标
图13.2创建只读游标
【例13.3】创建一个名为Cur_Emp_02的更新游标。
光盘\TM\sl\13\3）
DECLARECur_Emp_02CURSORFOR
SELECTName,Sex,AgeFROMEmployee
FORUPDATE--更新游标
运行结果如图13.3所示。
图13.3创建更新游标
13.2.2打开游标
打开一个声明的游标可以使用OPEN命令。
OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}
☑GLOBAL：
指定cursor_name为全局游标。
☑cursor_name：
已声明的游标名称，如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，cursor_name指的是全局游标，否则，cursor_name指的是局部游标。
☑cursor_variable_name：
游标变量的名称，该名称引用一个游标。
【例13.4】首先声明一个名为Emp_01的游标，然后使用OPEN命令打开该游标。
光盘\TM\sl\13\4）
DECLAREEmp_01CURSORFOR--声明游标
WHEREID='
1'
OPENEmp_01--打开游标
运行结果如图13.4所示。
图13.4打开游标
13.2.3读取游标中的数据
当打开一个游标之后，就可以读取游标中的数据了。
可以使用FETCH命令读取游标中的某一行数据。
FETCH
[[NEXT|PRIOR|FIRST|LAST
|ABSOLUTE{n|@nvar}
|RELATIVE{n|@nvar}
]
FROM
{{[GLOBAL]cursor_name}|@cursor_variable_name}
[INTO@variable_name[,...n]]
FETCH命令的参数及说明如表13.2所示。
表13.2FETCH命令的参数及说明
NEXT
返回紧跟当前行之后的结果行，并且当前行递增为结果行。
如果FETCHNEXT为对游标的第一次提取操作，则返回结果集中的第一行。
NEXT为默认的游标提取选项
PRIOR
返回紧临当前行前面的结果行，并且当前行递减为结果行。
如果FETCHPRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前
FIRST
返回游标中的第一行并将其作为当前行
LAST
返回游标中的最后一行并将其作为当前行
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
指定cursor_name为全局游标
cursor_name
要从中进行提取的开放游标的名称。
如果同时有以cursor_name作为名称的全局和局部游标存在，若指定为GLOBAL，则cursor_name对应于全局游标，未指定GLOBAL，则对应于局部游标
@cursor_variable_name
游标变量名，引用要进行提取操作的打开的游标
INTO@variable_name[,...n]
允许将提取操作的列数据放到局部变量中。
列表中的各个变量从左到右与游标结果集中的相应列相关联。
各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。
变量的数目必须与游标选择列表中的列的数目一致
@@FETCH_STATUS
返回上次执行FETCH命令的状态。
在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH操作是否成功，决定如何进行下一步处理。
@@FETCH_STATUS变量有3个不同的返回值，说明如下：
（1）返回值为0，FETCH语句成功；
（2）返回值为-1，FETCH语句失败或此行不在结果集中；
（3）返回值为-2，被提取的行不存在
【例13.5】用@@FETCH_STATUS控制一个WHILE循环中的游标活动，SQL语句及运行结果如图13.5所示。
光盘\TM\sl\13\5）
图13.5从游标中读取数据
USEdb_2012--引入数据库
DECLAREReadCursorCURSORFOR--声明一个游标
SELECT*FROMStudent
OPENReadCursor--打开游标
FETCHNEXTFROMReadCursor--执行取数操作
WHILE@@FETCH_STATUS=0--检查@@FETCH_STATUS，以确定是否还可以继续取数
BEGIN
FETCHNEXTFROMReadCursor
END
13.2.4关闭游标
当游标使用完毕之后，使用CLOSE语句可以关闭游标，但不释放游标占用的系统资源。
CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}
开放游标的名称。
如果全局游标和局部游标都使用cursor_name作为它们的名称，那么当指定GLOBAL时，cursor_name引用全局游标；
否则，cursor_name引用局部游标。
与开放游标关联的游标变量名称。
【例13.6】声明一个名为CloseCursor的游标，并使用Close语句关闭游标。
光盘\TM\sl\13\6）
USEdb_2012
DECLARECloseCursorCursorFOR
FORREADONLY
OPENCloseCursor
CLOSECloseCursor
运行结果如图13.6所示。
图13.6关闭游标
13.2.5释放游标
当游标关闭之后，并没有在内存中释放所占用的系统资源，所以可以使用DEALLOCATE命令删除游标引用。
当释放最后的游标引用时，组成该游标的数据结构由SQLServer释放。
DEALLOCATE{{[GLOBAL]cursor_name}|@cursor_variable_name}
已声明游标的名称。
当全局和局部游标都以cursor_name作为它们的名称存在时，如果指定GLOBAL，则cursor_name引用全局游标，如果未指定GLOBAL，则cursor_name引用局部游标。
☑@cursor_variable_name：
cursor变量的名称。
@cursor_variable_name必须为cursor类型。
当使用DEALLOCATE@cursor_variable_name来删除游标时，游标变量并不会被释放，除非超过使用该游标的存储过程和触发器的范围。
【例13.7】使用DEALLOCATE命令释放名为FreeCursor的游标。
光盘\TM\sl\13\7）
DECLAREFreeCursorCursorFOR
OPENFreeCursor
CloseFreeCursor
DEALLOCATEFreeCursor
运行结果如图13.7所示。
图13.7释放游标
13.3使用系统过程查看游标
光盘\TM\lx\13\使用系统过程查看游标.mp4
创建游标后，通常使用sp_cursor_list和sp_describe_cursor查看游标的属性。
sp_cursor_list用来报告当前为连接打开的服务器游标的属性，sp_describe_cursor用于报告服务器游标的属性。
本节就详细介绍这两个系统过程。
13.3.1sp_cursor_list
sp_cursor_list报告当前为连接打开的服务器游标的属性。
sp_cursor_list[@cursor_return=]cursor_variable_nameOUTPUT
[@cursor_scope=]cursor_scope
☑[@cursor_return=]cursor_variable_nameOUTPUT：
已声明的游标变量的名称。
cursor_variable_name的数据类型为cursor，无默认值。
游标是只读的可滚动动态游标。
☑[@cursor_scope=]cursor_scope：
指定要报告的游标级别。
cursor_scope的数据类型为int，无默认值，可取值如表13.3所示。
表13.3cursor_scope可取的值
值
说明
1
报告所有本地游标
2
报告所有全局游标
3
报告本地游标和全局游标
【例13.8】声明一个游标Cur_Employee，并使用sp_cursor_list报告该游标的属性。
光盘\TM\sl\13\8）
DECLARECur_EmployeeCURSORFOR
SELECTName
FROMEmployee
WHERENameLIKE'
王%'
OPENCur_Employee
DECLARE@ReportCURSOR
EXECmaster.dbo.sp_cursor_list@cursor_return=@ReportOUTPUT,
@cursor_scope=2
FETCHNEXTfrom@Report
WHILE（@@FETCH_STATUS<
>
-1）
FETCHNEXTfrom@Report
CLOSE@Report
DEALLOCATE@Report
CLOSECur_Employee
DEALLOCATECur_Employee
运行结果如图13.8所示。
图13.8sp_cursor_list属性
13.3.2sp_describe_cursor
sp_describe_cursor用于报告服务器游标的属性。
sp_describe_cursor[@cursor_return=]output_cursor_variableOUTPUT
{[,[@cursor_source=]N'
local'
[@cursor_identity=]N'
local_cursor_name'
|[,[@cursor_source=]N'
global'
global_cursor_name'
variable'
input_cursor_variable'
}
升级会员 