数据库编程-补充材料-SQL游标.ppt

游标简介 关系数据库中的操作会对整个行集产生影响 由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行 由语句所返回的这一完整的行集被称为结果集 应用程序 特别是交互式联机应用程序 并不总能将整个结果集作为一个单元来有效地处理 这些应用程序需要一种机制以便每次处理一行或一部分行 游标就是提供这种机制的结果集扩展 游标通过以下方式扩展结果处理 游标简介 允许定位在结果集的特定行 从结果集的当前位置检索一行或多行 支持对结果集中当前位置的行进行数据修改为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持 提供脚本 存储过程和触发器中使用的访问结果集中的数据的SQL语句 游标简介 请求游标支持两种请求游标的方法 Transact SQL对根据SQL 92游标语法制定的游标 SQL语言支持使用它们的语法 数据库应用程序编程接口 API 游标函数SQLServer支持这些数据库API的游标功能 游标简介 ADO MicrosoftActiveX 数据对象 OLEDBODBC 开放式数据库连接 DB Library应用程序不能混合使用这两种请求游标的方法 已经使用API指定游标行为的应用程序不能再执行Transact SQLDECLARECURSOR语句请求一个SQL游标 只有在将所有API游标特性均设回默认值后 应用程序才可以执行DECLARECURSOR 游标简介 游标进程SQL游标和API游标有不同的语法 但下列一般进程可用于所有SQLServer游标 把游标与SQL语句的结果集相关联 并且定义游标的特征 如是否能够更新游标中的行 执行SQL语句以填充游标 检索想要查看的游标中的行 从游标中检索一行或多行的操作称为提取 执行一系列的提取操作以向前或向后检索行的操作称为滚动 根据需要 对游标中当前位置的行执行修改操作 更新或删除 关闭游标 游标简介 DECLARECURSOR定义Transact SQL服务器游标的特性 例如游标的滚动行为和用于生成游标对其进行操作的结果集的查询 DECLARECURSOR接受基于SQL 92标准的语法和使用一组Transact SQL扩展的语法 SQL 92语法DECLAREcursor name INSENSITIVE SCROLL CURSORFORselect statement FOR READONLY UPDATE OFcolumn name n 游标简介 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 游标简介 SQL 92参数cursor name是所定义的SQLServer服务器游标名称 cursor name必须遵从标识符规则 INSENSITIVE定义一个游标 以创建将由该游标使用的数据的临时复本 对游标的所有请求都从tempdb中的该临时表中得到应答 因此 在对该游标进行提取操作时返回的数据中不反映对基表所做的修改 并且该游标不允许修改 使用SQL 92语法时 如果省略INSENSITIVE 任何用户 对基表提交的删除和更新都反映在后面的提取中 游标简介 SCROLL指定所有的提取选项 FIRST LAST PRIOR NEXT RELATIVE ABSOLUTE 均可用 如果在SQL 92DECLARECURSOR中未指定SCROLL 则NEXT是唯一支持的提取选项 如果指定SCROLL 则不能也指定FAST FORWARD select statement是定义游标结果集的标准SELECT语句 如果select statement中的子句与所请求的游标类型的功能发生冲突 则SQLServe隐性地将游标转换为另一种类型 游标简介 READONLY在UPDATE或DELETE语句的WHERECURRENTOF子句中不能引用游标 该选项替代要更新的游标的默认功能 UPDATE OFcolumn name n 定义游标内可更新的列 如果指定OFcolumn name n 参数 则只允许修改所列出的列 如果在UPDATE中未指定列的列表 则可以更新所有列 SQLServer扩展参数 游标简介 LOCAL指定该游标的作用域对在其中创建它的批处理 存储过程或触发器是局部的 游标将在批处理 存储过程或触发器终止时隐性释放 GLOBAL指定该游标的作用域对连接是全局的 在由连接执行的任何存储过程或批处理中 都可以引用该游标名称 该游标仅在脱接时隐性释放 游标简介 FORWARD ONLY指定游标只能从第一行滚动到最后一行 FETCHNEXT是唯一受支持的提取选项 STATIC定义一个游标 以创建将由该游标使用的数据的临时复本 对游标的所有请求都从tempdb中的该临时表中得到应答 因此 在对该游标进行提取操作时返回的数据中不反映对基表所做的修改 并且该游标不允许修改 游标简介 KEYSET指定当游标打开时 游标中行的成员资格和顺序已经固定 对行进行唯一标识的键集内置在tempdb内一个称为keyset的表中 DYNAMIC定义一个游标 以反映在滚动游标时对结果集内的行所做的所有数据更改 行的数据值 顺序和成员在每次提取时都会更改 游标简介 FAST FORWARD指定启用了性能优化的FORWARD ONLY READ ONLY游标 如果指定FAST FORWARD 则不能也指定SCROLL或FOR UPDATE FAST FORWARD和FORWARD ONLY是互斥的 如果指定一个 则不能指定另一个 SCROLL LOCKS指定确保通过游标完成的定位更新或定位删除可以成功 当将行读入游标以确保它们可用于以后的修改时 SQLServer会锁定这些行 游标简介 OPTIMISTIC指定如果行自从被读入游标以来已得到更新 则通过游标进行的定位更新或定位删除不成功 TYPE WARNING指定如果游标从所请求的类型隐性转换为另一种类型 则给客户端发送警告消息 UPDATE OFcolumn name n 定义游标内可更新的列 如果提供了OFcolumn name n 则只允许修改列出的列 如果在UPDATE中未指定列的列表 则所有列均可更新 游标简介 OPEN打开SQL服务器游标 然后通过执行在DECLARECURSOR或SETcursor variable语句中指定的SQL语句填充游标 语法OPEN GLOBAL cursor name cursor variable name 游标简介 FETCH从SQL服务器游标中检索特定的一行 语法FETCH NEXT PRIOR FIRST LAST ABSOLUTE n nvar RELATIVE n nvar FROM GLOBAL cursor name cursor variable name INTO variable name n 游标简介 CLOSE通过释放当前结果集并且解除定位游标的行上的游标锁定 关闭一个开放的游标 CLOSE使得数据结构可以重新打开 但不允许提取和定位更新 直到游标重新打开为止 语法CLOSE GLOBAL cursor name cursor variable name 游标简介 DEALLOCATE删除游标引用 当释放最后的游标引用时 组成该游标的数据结构由SQLServer释放 语法DEALLOCATE GLOBAL cursor name cursor variable name 游标简介 示例A 在简单的游标中使用FETCH下例为authors表中姓以字母B开头的行声明了一个简单的游标 并使用FETCHNEXT逐个提取这些行 FETCH语句以单行结果集形式返回由DECLARECURSOR指定的列的值 FETCH语句的输出存储于局部变量 PRINT语句将变量组合成单一字符串并将其返回到客户端 游标简介 USEpubsGODECLARE au lnamevarchar 40 au fnamevarchar 20 DECLAREauthors cursorCURSORFORSELECTau lname au fnameFROMauthorsWHEREau lnameLIKE B ORDERBYau lname au fname 游标简介 OPENauthors cursorFETCHNEXTFROMauthors cursorINTO au lname au fnameWHILE FETCH STATUS 0BEGINPRINT Author au fname au lnameFETCHNEXTFROMauthors cursorINTO au lname au fnameEND 游标简介 CLOSEauthors cursorDEALLOCATEauthors cursorGOB 声明SCROLL游标并使用其它FETCH选项下例创建一个SCROLL游标 使其通过LAST PRIOR RELATIVE和ABSOLUTE选项支持所有滚动能力 游标简介 USEpubsGOSELECTau lname au fnameFROMauthorsORDERBYau lname au fnameDECLAREauthors cursorSCROLLCURSORFORSELECTau lname au fnameFROMauthorsORDERBYau lname au fname 游标简介 OPENauthors cursorFETCHLASTFROMauthors cursorFETCHPRIORFROMauthors cursorFETCHABSOLUTE2FROMauthors cursorFETCHRELATIVE3FROMauthors cursorFETCHRELATIVE 2FROMauthors cursorCLOSEauthors cursorDEALLOCATEauthors cursorGO 游标简介 C 下面显示游标如何持续到最后的名称或持续到引用它们的变量已释放 USEpubsGO Createandopenaglobalnamedcursorthat isvisibleoutsidethebatch DECLAREabcCURSORGLOBALSCROLLFORSELECT FROMauthorsOPENabc 游标简介 GO Referencethenamedcursorwitha cursorvariable DECLARE MyCrsrRef1CURSORSET MyCrsrRef1 abc Nowdeallocatethecursorreference DEALLOCATE MyCrsrRef1 Cursorabcstillexists FETCHNEXTFROMabcGO 游标简介 Referencethenamedcursoragain DECLARE MyCrsrRef2CURSORSET MyCrsrRef2 abc Nowdeallocatecursornameabc DEALLOCATEabc Cursorstillexists referencedby MyCrsrRef2 FETCHNEXTFROM MyCrsrRef2 Cursorfinallyisdeallocatedwhenlast refer