游标管理课件

游标管理 2 回顾 PL SQL是一种可移植的高性能事务处理语言PL SQL引擎驻留在Oracle服务器中PL SQL块由声明部分 可执行部分和异常处理部分组成PL SQL支持的数据类型包括标量数据类型 LOB数据类型和属性类型控制结构包括条件控制 循环控制和顺序控制PL SQL支持动态SQL运行时出现的错误叫做异常异常可以分为预定义异常和用户定义的异常 3 目标 掌握游标管理技巧 4 游标简介2 1 执行PL SQL程序 内存单元 保存到游标中 一次处理一行 检索行 提取行 5 游标简介2 2 逐行处理查询结果 以编程的方式访问数据游标的类型 隐式游标 显式游标 在PL SQL程序中执行DMLSQL语句时自动创建隐式游标 显式游标用于处理返回多行的查询 REF游标用于处理运行时才能确定的动态SQL查询的结果 游标类型 6 隐式游标4 1 在PL SQL中使用DML语句时自动创建隐式游标隐式游标自动声明 打开和关闭 其名为SQL通过检查隐式游标的属性可以获得最近执行的DML语句的信息隐式游标的属性有 FOUND SQL语句影响了一行或多行时为TRUE NOTFOUND SQL语句没有影响任何行时为TRUE ROWCOUNT SQL语句影响的行数 ISOPEN 游标是否打开 始终为FALSE 7 隐式游标4 2 SQL SETSERVEROUTPUTONSQL BEGINUPDATEtoysSETtoyprice 270WHEREtoyid P005 IFSQL FOUNDTHENDBMS OUTPUT PUT LINE 表已更新 ENDIF END 只有在DML语句影响一行或多行时 才返回True 8 隐式游标4 3 SQL SETSERVEROUTPUTONSQL DECLAREv TOYIDTOYS ID type 如果DML语句不影响任何行 则返回True 9 隐式游标4 4 SQL SETSERVEROUTPUTONSQL BEGINUPDATEvendor masterSETvenname RobMathew WHEREvencode V004 DBMS OUTPUT PUT LINE SQL ROWCOUNT END 返回DML语句影响的行数 10 SELECTINTO语句2 1 SQL SETSERVEROUTPUTONSQL DECLAREempidVARCHAR2 10 desigVARCHAR2 10 BEGINempid 如果没有与SELECTINTO语句中的条件匹配的行 将引发NO DATA FOUND异常 11 SELECTINTO语句2 2 SQL SETSERVEROUTPUTONSQL DECLAREempidVARCHAR2 10 BEGINSELECTempnoINTOempidFROMemployee EXCEPTIONWHENTOO MANY ROWSTHENDBMS OUTPUT PUT LINE 该查询提取多行 END 如果SELECTINTO语句返回多个值 将引发TOO MANY ROWS异常 12 显式游标2 1 显式游标在PL SQL块的声明部分定义查询 该查询可以返回多行显式游标的操作过程 数据库 打开游标 提取行 变量 关闭游标 13 显式游标2 2 SQL SETSERVEROUTPUTONSQL DECLAREmy toy pricetoys toyprice TYPE CURSORtoy curISSELECTtoypriceFROMtoysWHEREtoyprice 250 BEGINOPENtoy cur LOOPFETCHtoy curINTOmy toy price EXITWHENtoy cur NOTFOUND DBMS OUTPUT PUT LINE TOYPRICE 玩具单价 my toy price ENDLOOP CLOSEtoy cur END 声明游标 打开游标 提取行 关闭游标 14 带参数的显式游标 声明显式游标时可以带参数以提高灵活性声明带参数的显式游标的语法如下 CURSOR ISselect statement SQL SETSERVEROUTPUTONSQL DECLAREdesigVARCHAR2 20 emp codeVARCHAR2 5 empnmVARCHAR2 20 CURSORemp cur desigparamVARCHAR2 ISSELECTempno enameFROMemployeeWHEREdesignation desigparam BEGINdesig 15 使用显式游标更新行2 1 允许使用游标删除或更新活动集中的行声明游标时必须使用SELECT FORUPDATE语句CURSORISSELECTstatementFORUPDATE UPDATESETWHERECURRENTOF 更新的语法 DELETEFROMWHERECURRENTOF 删除的语法 16 使用显式游标更新行2 2 SQL SETSERVEROUTPUTONSQL DECLAREnew priceNUMBER CURSORcur toyISSELECTtoypriceFROMtoysWHEREtoyprice 100FORUPDATEOFtoyprice BEGINOPENcur toy LOOPFETCHcur toyINTOnew price EXITWHENcur toy NOTFOUND UPDATEtoysSETtoyprice 1 1 new priceWHERECURRENTOFcur toy ENDLOOP CLOSEcur toy COMMIT END 17 游标的使用方法 FOR2 1 FOR用于简化游标处理代码当用户需要从游标中提取记录时使用循环游标的语法如下 FORINLOOPENDLOOP 18 循环游标2 2 SQL SETSERVEROUTPUTONSQL DECLARECURSORmytoy curISSELECTtoyid toyname toypriceFROMtoys BEGINFORtoy recINmytoy curLOOPDBMS OUTPUT PUT LINE 玩具编号 toy rec toyid 玩具名称 toy rec toyname 玩具单价 toy rec toyprice ENDLOOP END 19 REF游标和游标变量3 1 REF游标和游标变量用于处理运行时动态执行的SQL查询创建游标变量需要两个步骤 声明REF游标类型声明REF游标类型的变量用于声明REF游标类型的语法为 TYPEISREFCURSOR RETURN 20 REF游标和游标变量3 2 TYPEmy curtypeISREFCURSORRETURNstud det ROWTYPE order curmy curtype 声明强类型的REF游标 打开游标变量的语法如下 OPENcursor nameFORselect statement TYPEmy ctypeISREFCURSOR stud curmy ctype 声明弱类型的REF游标 21 REF游标和游标变量3 3 SQL DECLARETYPEtoys curtypeISREFCURSORRETURNtoys ROWTYPE toys curvartoys curtype toys rectoys ROWTYPE BEGINOPENtoys curvarFORSELECT FROMtoys FETCHtoys curvarINTOtoys rec CLOSEtoys curvar END opencur prize multifor selectnvl prz lvl 0 nvl prz amt 0 decode paid dt null 0 1 prz type descrfromprizewheregame draw aandser num b usingv game draw i p ser num 声明REF游标类型 声明游标变量 22 游标变量的优点和限制 游标变量的功能强大 可以简化数据处理 游标变量的优点有 可从不同的SELECT语句中提取结果集可以作为过程的参数进行传递可以引用游标的所有属性可以进行赋值运算使用游标变量的限制 不能在程序包中声明游标变量FORUPDATE子句不能与游标变量一起使用不能使用比较运算符 23 使用游标变量执行动态SQL 可以使用游标变量执行动态构造的SQL语句打开执行动态SQL的游标变量的语如下 OPENcursor nameFORdynamic sqlstring USINGbind argument list DECLAREr empemp ROWTYPE TYPEc typeISREFCURSOR curc type p salaryNUMBER BEGINp salary 2500 OPENcurFOR select fromempwheresal 1orderbysaldesc USINGp salary DBMS OUTPUT PUT LINE 薪水大于 p salary 的员工有 LOOPFETCHcurINTOr emp EXITWHENcur NOTFOUND DBMS OUTPUT PUT LINE