第十四课Oracle游标和异常处理.ppt

复习 动态SQL包括本地动态SQL和DBMS SQL动态SQL两种实现方法 动态SQL简介 为了在动态SQL中处理DDL DCL DML以及单行selectinto语句 需要使用executeimmediate语句 Executeimmediate语句 语法如下 executeimmediatedyn string 1 executeimmediate语句处理DDL和DCL语句 注 dyn string 用于指定存放DDL或DCL文本的字符串变量 1 处理无占位符和returning子句的DML语句语法如下 executeimmediatedyn string 2 使用executeimmediate语句处理DML语句 注 dyn string 用于指定存放DML文本的字符串变量 2 处理包含占位符的DML语句 注 1 占位符必须以冒号开始 可以使用任何名称 2 bind argument用于为占位符提供数据 语法如下 executeimmediatedyn stringusingbind argument 3 处理包含returning子句的DML语句 注 1 当使用returninginto子句接收数据时 只能接受DML语句返回的单行数据 2 当在动态DML语句中指定returninginto子句时 需要在into后使用占位符 语法如下 executeimmediatedyn stringreturningintovar1 语法如下 executeimmediatedyn stringintovar1 3 使用executeimmediate语句处理单行语句 游标变量是基于REFCURSOR类型所定义的变量 使用显示游标只能定义静态游标 而通过使用游标变量可以在打开游标时指定游标所对应的select语句 从而实现动态游标 游标变量 游标变量包括定义游标变量 打开游标变量 提取数据和关闭游标四个阶段 游标变量 1 定义REFCURSOR类型和游标变量 语法如下 TYPEref type nameISREFCURSOR RETURNreturn type cursor variableref type name 2 打开游标变量 语法如下 OPENcursor variableFORselect 3 提取数据 语法如下 FETCHcursor variableINTOvar1 4 关闭游标变量 语法如下 CLOSEcursor variable 游标 NEW 为什么要使用游标 关系数据库管理系统实质是面向集合的 在Oracle中并没有一种描述表中单一记录的表达形式 除非使用where子句来限制只有一条记录被选中 游标允许应用程序对查询语句返回的行结果集中的每一行进行相同或不同的操作 而不是一次对整个结果集进行同一种操作 什么是游标 就本质而言 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制 游标由结果集和结果集中指向特定记录的游标位置组成 游标充当指针的作用 尽管游标能够遍历查询结果中的所有行 但它一次只能指向一行 游标的构成 Oracle中游标的分类 在Oracle中游标可以分为两种类型 显式游标和隐式游标 显式游标 用于处理select语句返回的多行数据 隐式游标 用于处理单行selectinto语句和DML语句 两者区别 p129 显式游标的使用 使用显式游标分四个步骤 2 打开游标OPENcursor name 1 声明游标CURSORcursor nameISselect statement 3 从游标中查找信息FETCHcursor nameINTOvar1 var2 4 关闭游标CLOSEcursor name 例 声明游标 对应的集合是表emp中所有的员工姓名 DECLAREcursorc emp enameisselectenamefromscott emp 显式游标的使用 例 显示游标c emp ename中的第一行信息 DECLAREcursorc emp enameisselectenamefromscott emp v enamescott emp ename type BEGINopenc emp ename fetchc emp enameintov ename dbms output put line v ename closec emp ename END 显式游标的使用 思考 如何显示游标c emp ename中的所有信息 练习 显示游标c emp ename中的所有信息 DECLAREcursorc emp enameisselectenamefromscott emp v enameemp ename type v countbinary integer BEGINselectcount rowid intov countfromscott emp openc emp ename foriin1 v countloopfetchc emp enameintov ename dbms output put line v ename endloop closec emp ename END 显式游标的使用 思考 如何获取指定职工编号的员工姓名 参数游标 参数游标是指带有参数的游标 2 打开游标OPENcursor name 参数值 1 声明参数游标CURSORcursor name 参数名参数数据类型 ISselect from where 注 1 定义参数只能指定数据类型 不能指定长度 2 必须在游标select语句的where子句中引用游标参数 否则失去了定义参数游标的意义 DECLAREcursorc emp v empnonumber isselect fromscott empwhereempno v empno v empscott emp rowtype BEGINopenc emp 7369 fetchc empintov emp dbms output put line v emp ename closec emp END 例 使用参数游标获取7369员工的所有信息 思考 使用游标获取任何编号员工的所有信息 openc emp number DECLAREcursorc empisselect fromscott empwhereempno 练习 使用非参数游标 替代变量 获取任何编号员工的所有信息 思考 如何处理编号不存在的情况 显式游标属性 当使用显示游标时 需要使用游标属性确定显示游标的执行信息 显示游标包括 isopen found notfound rowcount四种属性 注 当引用显示游标属性时 需要带有游标名作为前缀 例 emp cursor rowcount 显式游标属性 found是一个布尔属性 如果前一个fetch语句返一个行 那么它返回true 否则返回false 如果在相关联的游标还没有打开进行引用 那么会返回错误 notfound的属性与 found相反 isopen也是布尔属性 用来确定相关的游标是否被打开 rowcount此数字属性返回目前为止由游标返回行的数目 即fetch语句后的得到数字 如果在相关联的游标还没有打开或者已经关闭的时候进行引用 会返回错误 DECLAREcursorc empisselect fromscott empwhereempno 例 使用游标获取任何编号员工的所有信息 对于不存在的编号显示 您输入的编号不存在 练习 使用参数游标获取任何部门编号的员工姓名信息 并返回记录条数 对于不存在的编号显示 您输入的编号不存在 DECLAREcursorc e dept dnonumber isselect fromscott empwheredeptno dno v emp deptscott emp rowtype v dnoemp deptno type 必须先获取数据才能有found的返回值 cursorfor循环 forrecord namein corsor name 参数1 参数2 loopstatementsendloop 为了简化游标处理 可以使用游标for循环 当使用游标for循环时 Oracle会隐含的打开游标 提取数据并关闭游标 注 record name是基于游标隐含定义的记录变量 在执行循环之前 Oracle会隐含的打开游标 并且每循环一次自动提取一行数据 当所有数据提取后自动退出循环并关闭游标 例 编写PL SQL块 定义游标 使用替代变量输入部门号 并以工资降序显示该部门所有雇员的名称和工资 DECLAREcursoremp cursorisselectename salfromscott empwheredeptno 练习 编写PL SQL块 定义参数游标 参数 岗位 使用替代变量输入岗位 并显示该岗位所有雇员的名称和工资 DECLAREcursoremp cursor titlevarchar2 isselectename salfromscott empwherejob title BEGINforemp recordinemp cursor 分析 DECLAREcursorc e dept dnonumber isselect fromscott empwheredeptno dno BEGINforemp recordinc e dept 练习 使用参数游标获取任何部门编号的员工姓名信息 并返回记录条数 对于不存在的编号显示 您输入的编号不存在 更新或删除游标行 通过使用显式游标 不仅可以取得游标结果集的数据 而且可以更新或删除游标结果集的当前行 DELETEFROMtable nameWHERECURRENTOFcursor name CURSORcursor nameISselect statementFORUPDATE UPDATEtable nameSET WHERECURRENTOFcursor name 例 要求输入部门号 输出该部门所有雇员姓名及原工资 并将雇员的工资提高10 DECLARECURSORemp cursorISSELECTename sal deptnoFROMscott empWHEREdeptno 练习 要求输入雇员号 输出该雇员姓名及原工资 并将雇员删除 DECLARECURSORemp cursorISSELECTename salFROMscott empWHEREempno 隐式游标属性 隐式游标也称SQL游标 用于处理单行selectinto语句和DML语句 一个SQL游标不能使用open fetch close来操作 与显式游标相同 SQL游标也有 isopen found notfound rowcount四种属性 具体看书 注 当引用隐式游标属性时 需要带有SQL作为前缀 例 SQL rowcount 书例6 11 declaretypeename typeistableofemp ename typenotnullindexbybinary integer typedname typeistableofdept dname typeindexbybinary integer enamelistename type dnamelistdname type subscriptbinary integer 1 beginforvarin selectename dnamefromemp deptwhereemp deptno dept deptno loopenamelist subscript var ename dnamelist subscript var dname dbms output put line employeenameis enamelist subscript departmentnameis dnamelist subscript subscript subscript 1 endloop dbms output put line totalnumberofemployeesare subscript end cursorfor循环 书例6 12 SQL found属性 declarev empnoemp empno type 书例6 13 SQL notfound属性 declarev empnoemp empno type 7000 beginupdateemp1setename sem108 whereempno v empno ifSQL foundthendbms output put line DeletedOK endif ifSQL notfoundthendbms output put line employeenumber v empno doesnotexists endif end 书例6 14 SQL isopen属性SQL rowcount属性 declarev deptnoemp deptno type 10 begindeleteemp1wheredeptno v deptno ifSQL foundthendbms output put line SQL rowcount deletedOK endif ifSQL notfoundthendbms output put line deptnonumber v deptno doesnotexists endif end 练习 1 当使用显示游标时 在执行了哪条语句后应该检查游标是否包含行 A openB fetchC closeD cursor 2 在以下哪些语句中可以包含wherecurrentof子句 A openB fetchC deleteD selectE updateF cursor 练习 3 请查看以下游标定义语句 哪行会引起错误 DECLARECURSORcust cursor p cust id p last name ISSELECTcust id first name last nameFROMcustomerWHEREcust id p cust idANDlast name p last name 定义游标变量需指明变量的数据类型A 2B 3C 4D 5E 6 4 课后习题第二 三题 课本149页 小结 游标概念游标分类显示游标的使用参数游标游标属性Cursorfor循环更新 删除游标行隐式游标的使用 异常处理 为了提高应用程序的健壮性 使得应用程序可以安全正常的运行 应用开发人员应该考虑到PL SQL块可能出现的各种异常情况 并进行相应的处理 通常使用异常 Exception 可以处理PL SQL块的各种异常情况 引言 异常 Exception 是一种PL SQL标识符 它包括预定义异常 非预定义异常 自定义异常 异常简介 当编写PL SQL块时 应该捕捉并处理各种可能出现的异常 如果不捕捉和处理异常 那么Oracle会将错误传递到调用环境 如果捕捉并处理异常 那么Oracle会在PL SQL块内解决运行错误 DECLAREv enameemp ename TYPE BEGINSELECTenameINTOv enameFROMscott empWHEREempno 未捕捉异常 传递错误到调用环境 DECLAREv enameemp ename TYPE BEGINSELECTenameINTOv enameFROMempWHEREempno 捕捉异常并处理 异常处理部分的语法如下 BEGIN EXCEPTIONwhenexception namethencodeforhandingexception name whenanother exceptionthencodeforhandinganother exception whenothersthencodeforhandinganyotherexception END 预定义异常是指PL SQL所提供的系统异常 Oracle为应用开发人员提供了21个预定义异常 每个预定义异常对应一个特定的Oracle错误 当PL SQL块出现这些Oracle错误时 会隐含的处罚相应的预定义异常 一 预定义异常 应用预定义异常 非预定义异常用于处理与预定义异常无关的Oracle错误 预定义异常只能用于处理21种Oracle错误 而PL SQL块还会遭遇其他Oracle错误 例如 完整性约束ORA 02291 二 非预定义异常 例 beginupdatescott empsetdeptno 1 在declare部分定义异常标识符 2 在declare部分建立Oracle错误号与异常之间的关联 3 捕捉并处理异常 使用非预定义异常的步骤 语法如下 declareexception nameexception pragmaexception init exception name 2291 例如 DECLAREe integrityEXCEPTION PRAGMAEXCEPTION INIT e integrity 2291 namescott emp ename TYPE LOWER 为了在PL SQL块处理Oracle错误 应用开发人员可以使用预定义异常或者非预定义异常 注 当执行内嵌update和delete语句时 如果没有更新或删除行 那么PL SQL块不会给出任何提示信息 为了使得用户可以获得更有用 更有意义的消息 需要使用自定义异常 三 自定义异常 1 在declare部分定义异常标识符 2 在执行部分使用raise语句显示触发异常 3 捕捉并处理异常 使用自定义异常步骤 语法 declareexception nameexception begin raiseexception name exception end 例如 DECLAREe integrityEXCEPTION e no rowsEXCEPTION PRAGMAEXCEPTION INIT e integrity 2291 namescott emp ename TYPE LOWER 练习 1 RAISE语句应该放在PL SQL块的哪个部分 A 定义部分B 执行部分C 异常处理部分 2 当在异常和Oracle错误之间建立关联时 应该在哪个部分完成 A 定义部分B 执行部分C 异常处