第八章数据库编程-补充材料-SQL游标

ver.01/06/94labs\handson.ppt游标简介关系数据库中的操作会对整个行集产生影响。由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。游标通过以下方式扩展结果处理：ver.01/06/94labs\handson.ppt游标简介允许定位在结果集的特定行。从结果集的当前位置检索一行或多行。支持对结果集中当前位置的行进行数据修改为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中使用的访问结果集中的数据的SQL语句。ver.01/06/94labs\handson.ppt游标简介请求游标支持两种请求游标的方法：Transact-SQL对根据SQL-92游标语法制定的游标，SQL语言支持使用它们的语法。数据库应用程序编程接口（API）游标函数SQLServer支持这些数据库API的游标功能：ver.01/06/94labs\handson.ppt游标简介ADO（MicrosoftActiveX®数据对象)OLEDBODBC（开放式数据库连接）DB-Library应用程序不能混合使用这两种请求游标的方法。已经使用API指定游标行为的应用程序不能再执行Transact-SQLDECLARECURSOR语句请求一个SQL游标。只有在将所有API游标特性均设回默认值后，应用程序才可以执行DECLARECURSOR。ver.01/06/94labs\handson.ppt游标简介游标进程SQL游标和API游标有不同的语法，但下列一般进程可用于所有SQLServer游标：1.把游标与SQL语句的结果集相关联，并且定义游标的特征，如是否能够更新游标中的行。2.执行SQL语句以填充游标。3.检索想要查看的游标中的行。从游标中检索一行或多行的操作称为提取。执行一系列的提取操作以向前或向后检索行的操作称为滚动。4.根据需要，对游标中当前位置的行执行修改操作（更新或删除）。5.关闭游标。ver.01/06/94labs\handson.ppt游标简介DECLARECURSOR定义Transact-SQL服务器游标的特性，例如游标的滚动行为和用于生成游标对其进行操作的结果集的查询。DECLARECURSOR接受基于SQL-92标准的语法和使用一组Transact-SQL扩展的语法。SQL-92语法DECLAREcursor_name[INSENSITIVE][SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]ver.01/06/94labs\handson.ppt游标简介SQLServer扩展语法DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]]ver.01/06/94labs\handson.ppt游标简介SQL-92参数cursor_name是所定义的SQLServer服务器游标名称。cursor_name必须遵从标识符规则。INSENSITIVE定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用SQL-92语法时，如果省略INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。ver.01/06/94labs\handson.ppt游标简介SCROLL指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果在SQL-92DECLARECURSOR中未指定SCROLL，则NEXT是唯一支持的提取选项。如果指定SCROLL，则不能也指定FAST_FORWARD。select_statement是定义游标结果集的标准SELECT语句。如果select_statement中的子句与所请求的游标类型的功能发生冲突，则SQLServe隐性地将游标转换为另一种类型。ver.01/06/94labs\handson.ppt游标简介READONLY在UPDATE或DELETE语句的WHERECURRENTOF子句中不能引用游标。该选项替代要更新的游标的默认功能。UPDATE[OFcolumn_name[,...n]]定义游标内可更新的列。如果指定OFcolumn_name[,...n]参数，则只允许修改所列出的列。如果在UPDATE中未指定列的列表，则可以更新所有列。SQLServer扩展参数ver.01/06/94labs\handson.ppt游标简介LOCAL指定该游标的作用域对在其中创建它的批处理、存储过程或触发器是局部的。游标将在批处理、存储过程或触发器终止时隐性释放。GLOBAL指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在脱接时隐性释放。ver.01/06/94labs\handson.ppt游标简介FORWARD_ONLY指定游标只能从第一行滚动到最后一行。FETCHNEXT是唯一受支持的提取选项。STATIC定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。ver.01/06/94labs\handson.ppt游标简介KEYSET指定当游标打开时，游标中行的成员资格和顺序已经固定。对行进行唯一标识的键集内置在tempdb内一个称为keyset的表中。DYNAMIC定义一个游标，以反映在滚动游标时对结果集内的行所做的所有数据更改。行的数据值、顺序和成员在每次提取时都会更改。ver.01/06/94labs\handson.ppt游标简介FAST_FORWARD指定启用了性能优化的FORWARD_ONLY、READ_ONLY游标。如果指定FAST_FORWARD，则不能也指定SCROLL或FOR_UPDATE。FAST_FORWARD和FORWARD_ONLY是互斥的；如果指定一个，则不能指定另一个。SCROLL_LOCKS指定确保通过游标完成的定位更新或定位删除可以成功。当将行读入游标以确保它们可用于以后的修改时，SQLServer会锁定这些行。ver.01/06/94labs\handson.ppt游标简介OPTIMISTIC指定如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。TYPE_WARNING指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。UPDATE[OFcolumn_name[,...n]]定义游标内可更新的列。如果提供了OFcolumn_name[,...n]，则只允许修改列出的列。如果在UPDATE中未指定列的列表，则所有列均可更新。ver.01/06/94labs\handson.ppt游标简介OPEN打开SQL服务器游标，然后通过执行在DECLARECURSOR或SETcursor_variable语句中指定的SQL语句填充游标。语法OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}ver.01/06/94labs\handson.ppt游标简介FETCH从SQL服务器游标中检索特定的一行。语法FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|@cursor_variable_name}[INTO@variable_name[,...n]]ver.01/06/94labs\handson.ppt游标简介CLOSE通过释放当前结果集并且解除定位游标的行上的游标锁定，关闭一个开放的游标。CLOSE使得数据结构可以重新打开，但不允许提取和定位更新，直到游标重新打开为止。语法CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}ver.01/06/94labs\handson.ppt游标简介DEALLOCATE删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由SQLServer释放。语法DEALLOCATE{{[GLOBAL]cursor_name}|@cursor_variable_name}ver.01/06/94labs\handson.ppt游标简介示例A.在简单的游标中使用FETCH下例为authors表中姓以字母B开头的行声明了一个简单的游标，并使用FETCHNEXT逐个提取这些行。FETCH语句以单行结果集形式返回由DECLARECURSOR指定的列的值。FETCH语句的输出存储于局部变量。PRINT语句将变量组合成单一字符串并将其返回到客户端。ver.01/06/94labs\handson.ppt游标简介USEpubsGODECLARE@au_lnamevarchar(40),@au_fnamevarchar(20)DECLAREauthors_cursorCURSORFORSELECTau_lname,au_fnameFROMauthorsWHEREau_lnameLIKEB%“ORDERBYau_lname,au_fnamever.01/06/94labs\handson.ppt游标简介OPENauthors_cursorFETCHNEXTFROMauthors_cursorINTO@au_lname,@au_fnameWHILE@@FETCH_STATUS=0BEGINPRINTAuthor:+@au_fname++@au_lnameFETCHNEXTFROMauthors_cursorINTO@au_lname,@au_fnameENDver.01/06/94labs\handson.ppt游标简介CLOSEauthors_cursorDEALLOCATEauthors_cursorGOB.声明SCROLL游标并使用其它FETCH选项下例创建一个SCROLL游标，使其通过LAST、PRIOR、RELATIVE和ABSOLUTE选项支持所有滚动能力。ver.01/06/94labs\handson.ppt游标简介USEpubsGOSELECTau_lname,au_fnameFROMauthorsORDERBYau_lname,au_fnameDECLAREauthors_cursorSCROLLCURSORFORSELECTau_lname,au_fnameFROMauthorsORDERBYau_lname,au_fnamever.01/06/94labs\handson.ppt游标简介OPENauthors_cursorFETCHLASTFROMauthors_cursorFETCHPRIORFROMauthors_cursorFETCHABSOLUTE2FROMauthors_cursorFETCHRELATIVE3FROMauthors_cursorFE