第十五课_Oracle_过_程_函数

复习 游标 游标由结果集和结果集中指向特定记录的游标位置组成 游标充当指针的作用 游标的构成 Oracle中游标的分类 在Oracle中游标可以分为两种类型 显式游标和隐式游标 显式游标 用于处理select语句返回的多行数据 隐式游标 用于处理单行selectinto语句和DML语句 显式游标的使用 使用显式游标分四个步骤 2 打开游标OPENcursor name 1 声明游标CURSORcursor nameISselect statement 3 从游标中查找信息FETCHcursor nameINTOvar1 var2 4 关闭游标CLOSEcursor name 参数游标 参数游标是指带有参数的游标 2 打开游标OPENcursor name 参数值 1 声明参数游标CURSORcursor name 参数名参数数据类型 ISselect from where 显式游标属性 当使用显示游标时 需要使用游标属性确定显示游标的执行信息 显示游标包括 isopen found notfound rowcount四种属性 注 当引用显示游标属性时 需要带有游标名作为前缀 例 emp cursor rowcount 显式游标属性 found是一个布尔属性 如果前一个fetch语句返一个行 那么它返回true 否则返回false 如果在相关联的游标还没有打开进行引用 那么会返回错误 notfound的属性与 found相反 isopen也是布尔属性 用来确定相关的游标是否被打开 rowcount此数字属性返回目前为止由游标返回行的数目 如果在相关联的游标还没有打开或者已经关闭的时候进行引用 会返回错误 cursorfor循环 forrecord namein corsor name 参数1 参数2 loopstatementsendloop 为了简化游标处理 可以使用游标for循环 当使用游标for循环时 Oracle会隐含的打开游标 提取数据并关闭游标 更新或删除游标行 通过使用显式游标 不仅可以取得游标结果集的数据 而且可以更新或删除游标结果集的当前行 DELETEFROMtable nameWHERECURRENTOFcursor name CURSORcursor nameISselect statementFORUPDATE UPDATEtable nameSET WHERECURRENTOFcursor name 隐式游标属性 隐式游标也称SQL游标 用于处理单行selectinto语句和DML语句 一个SQL游标不能使用open fetch close来操作 与显式游标相同 SQL游标也有 isopen found notfound rowcount四种属性 注 当引用隐式游标属性时 需要带有SQL作为前缀 例 SQL rowcount 异常处理 异常 Exception 是一种PL SQL标识符 它包括预定义异常 非预定义异常 自定义异常 异常简介 当编写PL SQL块时 应该捕捉并处理各种可能出现的异常 如果不捕捉和处理异常 那么Oracle会将错误传递到调用环境 如果捕捉并处理异常 那么Oracle会在PL SQL块内解决运行错误 异常处理部分的语法如下 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 为了在PL SQL块处理Oracle错误 应用开发人员可以使用预定义异常或者非预定义异常 注 当执行内嵌update和delete语句时 如果没有更新或删除行 那么PL SQL块不会给出任何提示信息 为了使得用户可以获得更有用 更有意义的消息 需要使用自定义异常 三 自定义异常 1 在declare部分定义异常标识符2 在执行部分使用raise语句显示触发异常 3 捕捉并处理异常 使用自定义异常步骤 语法 declareexception nameexception begin raiseexception name exception end 在一个异常中 SQLCODE返回Oracle错误的序号 而SQLERRM返回的是相应的错误消息 错误消息首先显示是错误代码 SQLCODE和SQLERRM PL SQL子程序 NEW 子程序可以独立编译并存储在数据库中 任何与数据库相连接的应用程序都可以访问这些子程序 子程序可以在执行部分引用 也可以在异常处理部分引用 PL SQL子程序 Oracle提供了四种类型的子类型 过程 函数 包 触发器 过程 过程是存储在数据库中的一段存储程序 有时也定义为存储过程 当创建过程时 系统会对其进行编译 并将执行代码存储到数据库中 定义过程 语法如下 CREATE ORREPLACE PROCEDUREprocedure name arg1 mode datatype IS AS localdeclaration BEGIN ENDprocedure name 注 mode有三种形式 in out inout 默认为in 调用过程 方法一 在SQL 提示符下调用过程 语法如下 execute过程名 参数 方法二 在PL SQL块中调用过程 语法如下 过程名 参数 CREATEORREPLACEPROCEDUREscott out timeISBEGINdbms session set nls NLS DATE FORMAT YYYY MM DDHH24 MI SS dbms output put line sysdate ENDout time 示例1 建立无参数的过程 如何调用 CREATEORREPLACEPROCEDUREscott add emp empnoscott emp empno TYPE enamescott emp ename TYPE jobscott emp job TYPE mgrscott emp mgr TYPE hiredatescott emp hiredate TYPE salscott emp sal TYPE m TYPE deptnoscott emp deptno TYPE ISBEGININSERTINTOscott empVALUES empno ename job mgr hiredate sal comm deptno ENDadd emp 示例2 建立带有输入参数的过程 为emp表插入数据 方法二 beginscott add emp 1118 mary cleck 7369 sysdate 1200 null 30 end 方法一 executescott add emp 1117 mary cleck 7369 sysdate 1200 null 30 当调用带有输入参数的过程时 需要为输入参数提供数据值 CREATEORREPLACEPROCEDUREscott update sal enoNUMBER salaryNUMBER nameOUTVARCHAR2 ISBEGINUPDATEscott empSETsal salaryWHEREempno enoRETURNINGenameINTOname ENDupdate sal 示例3 建立带有输出参数的过程 输出emp表中修改指定编号的员工工资的员工姓名 DECLAREv nameemp ename TYPE v salaryemp sal type 当调用带有输出参数的过程时 需要使用变量接收输出参数的数据值 CREATEORREPLACEPROCEDUREscott divide num1INOUTNUMBER num2INOUTNUMBER ISv1NUMBER v2NUMBER BEGINv1 TRUNC num1 num2 v2 MOD num1 num2 num1 v1 num2 v2 ENDdivide 练习 建立带有输入输出参数的过程 求任意输入的两个值的商和余数 并显示商和余数的值 DECLAREn1NUMBER 当调用带有输入输出参数的过程时 在调用之前需要定义变量存放输入值 并且在调用后使用变量输出数据 函数 函数 函数与过程的相同点 1 函数和过程都以编译后的形式存放在数据库中 2 函数和过程均可以没有参数或有多个参数 3 函数和过程都可以通过参数列表接受或返回零个或多个值 函数与过程的主要区别在于它们的调用方式 过程是作为一个独立执行语句调用的 而调用函数后需要将函数返回值赋值给某一变量 创建函数 CREATE ORREPLACE FUNCTIONfunction name arg1 mode datatype RETURNDATATYPEIS AS localdeclaration BEGIN ENDfunction name 注 1 return子句用于指定返回值的数据类型 2 在函数体内至少要包含一条return语句 CREATEORREPLACEFUNCTIONscott get info enoNUMBER titleOUTVARCHAR2 RETURNVARCHAR2ASnamescott emp ename TYPE BEGINSELECTename jobINTOname titleFROMscott empWHEREempno eno RETURNname ENDget info 示例 DECLAREv namescott emp ename TYPE v jobscott emp job TYPE BEGINv name scott get info 调用 例7 5 根据不同职务 显示在工资总额中的百分比 createorreplacefunctionget sal percent v jobemp job type returnnumberascursorc empisselectsalfromempwherejob v job v empc emp rowtype v salemp sal type 0 v sal sumemp sal type beginopenc emp loopfetchc empintov emp exitwhenc emp notfound v sal v sal v emp sal endloop closec emp selectsum sal intov sal sumfromemp v sal v sal v sal sum returnv sal endget sal percent 练习 1 建立过程ADD EMP 输入雇员号 姓名 岗位 工资 部门号 为emp表插入数据 3 建立函数GET SAL 根据输入的雇员号返回雇员名和工资 调用函数VALID ID确定雇员是否存在 不存在则显示消息 ORA 20010 该雇员不存在 2 建立函数VALID ID 根据输入的雇员号 检查雇员是否存在 如果雇员存在 返回TRUE 否则返回FALSE 1 CREATEORREPLACEPROCEDUREscott add emp idscott emp empno TYPE namescott emp ename TYPE jobscott emp job TYPE salscott emp sal TYPE deptnoscott emp deptno TYPE ISBEGININSERTINTOscott emp empno ename job sal deptno VALUES id name job sal deptno ENDadd emp CREATEORREPLACEFUNCTIONscott valid id enoNUMBER RETURNBOOLEANAStempint BEGINSELECTcount INTOtempFROMscott empWHEREempno eno iftemp0thenreturntrue elsereturnfalse endif ENDvalid id 2 调用 DECLAREv enoscott emp empno TYPE 不能使用书上图7 3的调用方式 因为调用中定义变量的数据类型不包括boolean型 CREATEOR