oracle 第15章 PLSQL程序设计ppt课件VIP

1 第15章PL SQL程序设计 2 本章内容 PL SQL概述PL SQL基础控制结构游标异常处理存储子程序包触发器 3 本章要求 掌握PL SQL程序设计基础知识掌握存储过程 函数 包 触发器的应用 4 15 1PL SQL概述 PL SQL特点PL SQL功能特性PL SQL执行过程与开发工具 5 15 1 1PL SQL特点 与SQL语言紧密集成 减小网络流量 提高应用程序的运行性能 模块化的程序设计功能 提高了系统可靠性 服务器端程序设计 可移植性好 6 15 1 2PL SQL功能特性 语句块结构异常处理变量和类型条件语句循环结构游标过程 函数和触发器包集合动态SQL对象特性 7 15 1 3PL SQL执行过程与开发工具 PL SQL块 SQL语句 客户端应用程序 PL SQL引擎 数据库服务器 过程化语句执行器 SQL执行器 块中SQL语句 PL SQL执行过程 8 PL SQL开发工具SQL PLUSProcedureBuilderOracleForm OracleReportsPL SQLDeveloper 9 15 2PL SQL基础 PL SQL程序结构词法单元数据类型变量与常量编译指示PL SQL中的SQL语句 10 PL SQL程序结构 PL SQL块的组成PL SQL块分类 11 PL SQL块的组成PL SQL语言以块为单位 块中可以嵌套子块 一个基本的PL SQL块由3部分组成 声明 DECLARE 可执行部分 BEGIN 异常处理部分EXCEPTION 12 声明部分声明部分以关键字DECLARE开始 BEGIN结束 主要用于声明变量 常量 数据类型 游标 异常处理名称以及本地 局部 子程序定义等 可执行部分执行部分是PL SQL块的功能实现部分 以关键字BEGIN开始 EXCEPTION或END结束 如果PL SQL块中没有异常处理部分 则以END结束 该部分通过变量赋值 流程控制 数据查询 数据操纵 数据定义 事务控制 游标处理等实现块的功能 异常处理部分异常处理部分以关键字EXCEPTION开始 END结束 该部分用于处理该块执行过程中产生的异常 13 注意 执行部分是必需的 而声明部分和异常部分是可选的 可以在一个块的执行部分或异常处理部分嵌套其他的PL SQL块 所有的PL SQL块都是以 END 结束 14 PL SQL块分类匿名块命名块函数存储过程包触发器 15 15 5 2词法单元 字符集标识符分隔符常量值注释 16 字符集大小写字母 A Z a z数字 0 9空白 制表符 空格和回车数字符号 标点符号 17 标识符标识符以字母开头 后边可以跟字母 数字 货币符号 下划线和 标识符的最大长度为30字符 并且所有字符都是有效的 合法 Xv studentIDTempVar非法 X y temp 18 分隔符 19 常量值字符型常量数字型常量布尔型常量 TURE FALSE NULL日期型常量 20 15 2 3数据类型 数字类型字符类型日期 区间类型行标识类型布尔类型原始类型LOB类型记录类型集合类型 21 PL SQL中常用的基本数据类型 22 记录类型的定义TYPErecord typeISRECORD field1datatype1 NOTNULL DEFAULT expr1 field2datatype2 NOTNULL DEFAULT expr2 fieldndatatypen NOTNULL DEFAULT exprn 23 15 2 4变量与常量 变量与常量的定义变量的作用域 24 变量声明 变量与常量的定义 变量定义的一般格式 CONSTANT NOTNULL DEFAULT 说明每行只能定义一个标识符 如果加上关键字CONSTANT 则表示所定义的标识符为一个常量 必须为它赋初值 如果定义的标识符不能为空 则必须加上关键字NOTNULL 并赋初值 为标识符赋值时 使用赋值符号 默认值为空 25 DECLAREv1NUMBER 4 v2NUMBER 4 NOTNULL 10 v3CONSTANTNUMBER 4 DEFAULT100 BEGINIFv1ISNULLTHENDBMS OUTPUT PUT LINE V1ISNULL ENDIF DBMS OUTPUT PUT LINE v2 v3 END 26 声明一个变量 使它的类型与某个变量或数据库基本表中某个列的数据类型一致 可以使用 TYPE 示例v empno1emp empno TYPE v empno2v empno1 TYPE 27 变量的作用域 变量的作用域是指变量的有效作用范围 从变量声明开始 直到块结束 如果PL SQL块相互嵌套 则在内部块中声明的变量是局部的 只能在内部块中引用 而在外部块中声明的变量是全局的 既可以在外部块中引用 也可以在内部块中引用 如果内部块与外部块中定义了同名变量 则在内部块中引用外部块的全局变量时需要使用外部块名进行标识 28 DECLAREv enameCHAR 15 v outerNUMBER 5 BEGINv outer 10 DECLAREv enameCHAR 20 v innerDATE BEGINv inner sysdate v ename INNERV ENAME OUTER v ename OUTERV ENAME END DBMS OUTPUT PUT LINE v ename END 29 15 2 5编译指示 编译指示是对编译程序发出的特殊指令 也称伪指令 关键字 PRAGMAPL SQL提供以下四种编译指示 EXCEPTION INIT告诉编译程序将一个特定的错误号与程序中所声明的异常标识符关联起来 RESTRICT REFERENCES告诉编译程序打包程序的纯度 即对函数中可以使用的SQL语句和包变量进行限制 30 SERIALLY REUSEABLE告诉PL SQL运行时引擎 在数据引用之间不要保持包级数据 AUTONOMOUS TRANSACTION告诉编译程序 该程序块为自治事务 即该事务的提交和回滚是独立进行的 31 15 2 6PL SQL中SQL语句 可以在PL SQL中执行的SQL语句包括SELECTDML UPDATE DELETE INSERT 事务控制语句 COMMIT ROLLBACK SAVEPOINT 注意DDL语句不可以直接使用 32 SELECT INTOSELECT INTO语句只能查询一个记录的信息 如果没有查询到任何数据 则会产生NO DATA FOUND异常 如果查询到多个记录 则会产生TOO MANY ROW异常 INTO句子后的变量用于接收查询的结果 变量的个数 顺序应该与查询的目标数据相匹配 也可以是记录类型的变量 DML语句 33 DECLAREv empemp ROWTYPE v enameemp ename type v salemp sal type BEGINSELECT INTOv empFROMempWHEREename SMITH DBMS OUTPUT PUT LINE v emp empno v emp sal selectename salINTOv ename v salFROMempWHEREempno 7900 DBMS OUTPUT PUT LINE v ename v sal END 34 DML语句PL SQL中DML语句对标准SQL语句中的DML语句进行了扩展 允许使用变量 示例DECLAREv empnoemp empno TYPE 7500 BEGININSERTINTOemp empno ename sal deptno VALUES v empno JOAN 2300 20 UPDATEempSETsal sal 100WHEREempno v empno DELETEFROMempWHEREempno v empno END 35 WHERE标识符的区分系统首先查看WHERE子句中的标识符是否与表中的列名相同 如果相同 则该标识符被解释为列名 如果没有同名列 系统检查该标识符是不是PL SQL语句块的变量 字符串比较填充比较 通过在短字符串后添加空格 使两个字符串达到相同长度 然后根据每个字符的ASCII码进行比较 非填充比较 根据每个字符的ASCII码进行比较 最先结束的字符串为小 PL SQL中规定 对定长的字符串 CHAR类型的字符串和字符串常量 采用填充比较 如果比较的字符串中有一个是变长字符串 VARCHAR2类型的字符串 则采用非填充比较 36 RETURNING如果要查询当前DML语句操作的记录的信息 可以在DML语句末尾使用RETURNING语句返回该记录的信息 RETURNING语句的基本语法 RETURNINGselect list itemINTOvariable list record variable 37 DECLAREv salemp sal TYPE BEGINUPDATEempSETsal sal 100WHEREempno 7844RETURNINGsalINTOv sal DBMS OUTPUT PUT LINE v sal END 38 15 3控制结构 选择结构循环结构跳转结构 39 15 3 1选择结构 IF语句IFcondition1THENstatements1 ELSIFcondition2THENstatements2 ELSEelse statements ENDIF 注意条件是一个布尔型变量或表达式 取值只能是TRUE FALSE NULL 40 例如 输入一个员工号 修改该员工的工资 如果该员工为10号部门 工资增加100 若为20号部门 工资增加150 若为30号部门 工资增加200 否则增加300 41 DECLAREv deptnoemp deptno type v incrementNUMBER 4 v empnoemp empno type BEGINv empno 42 搜索式CASE语句 基本语法CASEWHENcondition1THENstatements1 WHENcondition2THENstatements2 WHENconditionnTHENstatementsn ELSEelse statements ENDCASE 43 等值比较的CASE语句 基本语法CASEtest valueWHENvalue1THENstatements1 WHENvalue2THENstatements2 WHENvaluenTHENstatementsn ELSEelse statements ENDCASE 44 DECLAREv deptnoemp deptno type v incrementNUMBER 4 v empnoemp empno type BEGINv empno 45 根据输入的员工号 修改该员工工资 如果该员工工资低于1000 则工资增加200 如果工资在1000 2000之间 则增加150 如果工资在2000 3000之间 则增加100 否则增加50 46 DECLAREv salemp sal type v incrementNUMBER 4 v empnoemp empno type BEGINv empno 47 15 3 2循环结构 简单循环WHILE循环FOR循环 48 简单循环 语法LOOPsequence of statement EXIT WHENcondition ENDLOOP 注意 在循环体中一定要包含EXIT语句 否则程序进入死循环 49 例如 执行CREATETABLEtemp table num colNUMBER info colCHAR 10 语句创建temp table表 然后利用循环向temp table表中插入50条记录 程序为 DECLAREv counterBINARY INTEGER 1 BEGINLOOPINSERTINTOtemp tableVALUES v Counter Loopindex v counter v counter 1 EXITWHENv counter 50 ENDLOOP END 50 WHILE循环 基本语法WHILEconditionLOOPsequence of statement ENDLOOP 51 例如 利用WHILE循环向temp table表中插入50条记录 程序为 DECLAREv counterBINARY INTEGER 1 BEGINWHILEv counter 50LOOPINSERTINTOtemp tableVALUES v counter Loopindex v counter v counter 1 ENDLOOP END 52 FOR循环 基本语法FORloop counterIN REVERSE low bound high boundLOOPsequence of statement ENDLOOP 注意 循环变量不需要显式定义 系统隐含地将它声明为BINARY INTEGER变量 系统默认时 循环变量从下界往上界递增计数 如果使用REVERSE关键字 则表示循环变量从上界向下界递减计数 循环变量只能在循环体中使用 不能在循环体外使用 53 例如 利用FOR循环向temp table表中插入50条记录 程序为 BEGINFORv counterIN1 50LOOPINSERTINTOtemp tableVALUES v counter LoopIndex ENDLOOP END 54 15 3 3跳转结构 语法格式 标号 GOTO标号 说明 块内可以跳转 内层块可以跳到外层块 但外层块不能跳到内层 IF语句不能跳入 不能从循环体外跳入循环体内 不能从子程序外部跳到子程序中 由于goto语句的缺点 建议尽量少用甚至不用goto语句 55 15 4游标 游标的概念及类型显式游标隐式游标 56 DECLAREv counterBINARY INTEGER 1 BEGIN INSERTINTOtemp tableVALUES v counter Loopindex v counter v Counter 1 IFv counter 50THENGOTOLABEL ENDIF END 57 15 4游标 游标的概念及类型显式游标隐式游标 58 15 4 1游标的及类型 游标的概念游标 CURSOR 是Oracle系统在内存中开辟的一个工作区 在其中存放SELECT语句返回的查询结果 使用游标时 SELECT语句查询的结果可以是单条记录 多条记录 也可以是零条记录 游标工作区中 存在着一个指针 POINTER 在初始状态它指向查询结果的首记录 59 游标的类型显式游标由用户定义 操作 用于处理返回多行数据的SELECT查询 隐式游标由系统自动进行操作 用于处理DML语句和返回单行数据的SELECT查询 60 15 4 2显式游标 显式游标的操作显式游标的属性参数化显式游标显式游标的检索利用游标更新或删除数据 61 显式游标的操作 步骤定义游标打开游标检索游标关闭游标 62 定义游标 语法格式CURSORcursor nameISselect statement 说明游标必须在PL SQL块的声明部分进行定义 游标定义时可以引用PL SQL变量 但变量必须在游标定义之前定义 定义游标时并没有生成数据 只是将定义信息保存到数据字典中 游标定义后 可以使用cursor name ROWTYPE定义游标类型变量 63 打开游标 语法格式OPENcursor name 说明检查变量的值执行游标定义时对应的SELECT语句 将查询结果检索到工作区中 游标指针指向第一个元组一旦游标打开 就无法再次打开 除非先关闭如果游标定义中的变量值发生变化 则只能在下次打开游标时才起作用 64 检索游标 语法格式FETCHcursor nameINTOvariable list record variable 说明在使用FETCH语句之前必须先打开游标对游标第一次使用FETCH语句时 游标指针指向第一条记录 因此操作的对象是第一条记录 使用后 游标指针指向下一条记录 游标指针只能向下移动 不能回退INTO子句中的变量个数 顺序 数据类型必须与工作区中每行记录的字段数 顺序以及数据类型一一对应 65 关闭游标 语法格式CLOSEcursor name 说明游标所对应的内存工作区变为无效 释放与游标相关的系统资源 66 根据输入的部门号查询某个部门的员工信息 部门号在程序运行时指定 67 DECLAREv deptnoemp deptno TYPE CURSORc empISSELECT FROMempWHEREdeptno v deptno v empc emp ROWTYPE BEGINv deptno 68 显式游标的属性 ISOPEN布尔型 如果游标已经打开 返回TRUE 否则为FALSE FOUND布尔型 如果最近一次使用FETCH语句 有返回结果则为TRUE 否则为FALSE NOTFOUND布尔型 如果最近一次使用FETCH语句 没有返回结果则为TRUE 否则为FALSE ROWCOUNT数值型 返回到目前为止从游标缓冲区检索的元组数 69 参数化显式游标 参数化游标定义语法格式 CURSORcursor name parameter1datatype parameter2datatype ISselect statement打开参数化游标的方法OPENcursor name parameter1 parameter2 70 注意 定义游标时 只能指定参数的类型 而不能指定参数的长度 精度 刻度 打开带参数的游标时 实参的个数和数据类型等必须与游标定义时形参个数和数据类型等相匹配 71 DECLARECURSORc emp p deptnoemp deptno TYPE ISSELECT FROMempWHEREdeptno p deptno v empc emp ROWTYPE BEGINOPENc emp 10 LOOPFETCHc empINTOv emp EXITWHENc emp NOTFOUND DBMS OUTPUT PUT LINE v emp empno v emp ename ENDLOOP CLOSEc emp OPENc emp 20 LOOPFETCHc empINTOv emp EXITWHENc emp NOTFOUND DBMS OUTPUT PUT LINE v emp empno v emp ename ENDLOOP CLOSEc emp END 72 显式游标的检索 利用简单循环检索游标利用WHILE循环检索游标利用FOR循环检索游标 73 利用简单循环检索游标 DECLARECURSORcursor nameISSELECT BEGINOPENcursor name LOOPFETCH INTO EXITWHENcursor name NOTFOUND ENDLOOP CLOSEcursor name END 74 利用简单循环统计并输出各个部门的平均工资 DECLARECURSORc dept statISSELECTdeptno avg sal avgsalFROMempGROUPBYdeptno v deptc dept stat ROWTYPE BEGINOPENc dept stat LOOPFETCHc dept statINTOv dept EXITWHENc dept stat NOTFOUND DBMS OUTPUT PUT LINE v dept deptno v dept avgsal ENDLOOP CLOSEc dept stat END 75 利用WHILE循环检索游标 DECLARECURSORcursor nameISSELECT BEGINOPENcursor name FETCH INTO WHILEcursor name FOUNDLOOPFETCH INTO ENDLOOP CLOSEcursor END 76 利用WHILE循环统计并输出各个部门的平均工资 DECLARECURSORc dept statISSELECTdeptno avg sal avgsalFROMempGROUPBYdeptno v deptc dept stat ROWTYPE BEGINOPENc dept stat FETCHc dept statINTOv dept WHILEc dept stat FOUNDLOOPDBMS OUTPUT PUT LINE v dept deptno v dept avgsal FETCHc dept statINTOv dept ENDLOOP CLOSEc dept stat END 77 利用FOR循环检索游标 DECLARECURSORcursor nameISSELECT BEGINFORloop variableINcursor nameLOOP ENDLOOP END 78 FOR循环说明 系统隐含地定义了一个数据类型为 ROWTYPE的变量 并以此作为循环的计算器 系统自动打开游标 不用显式地使用OPEN语句打开 系统重复地自动从游标工作区中提取数据并放入计数器变量中 系统自动进行 FOUND属性检查以确定是否有数据当游标工作区中所有的记录都被提取完毕或循环中断时 系统自动地关闭游标 79 利用FOR循环统计并输出各个部门的平均工资 DECLARECURSORc dept statISSELECTdeptno avg sal avgsalFROMempGROUPBYdeptno BEGINFORv deptINc dept statLOOPDBMS OUTPUT PUT LINE v dept deptno v dept avgsal ENDLOOP END 80 隐式FOR游标BEGINFORv empIN select fromempwheredeptno 10 LOOPDBMS OUTPUT PUT LINE v emp empno v emp ename ENDLOOP END 81 利用游标更新或删除数据 游标定义语法CURSORcursor nameISSELECTselect list itemFROMtableFORUPDATE更新或修改数据的语法为 UPDATE DELETE WHERECURRENTOFcursor name注意由于COMMIT语句会释放会话拥有的任何锁 因此如果在检索游标的循环内使用COMMIT语句会释放定义游标时对数据加的锁 从而导致利用游标修改或删除数据的操作失败 82 修改员工的工资 如果员工的部门号为10 工资提高100 部门号为20 工资提高150 部门号为30 工资提高200 否则工资提高250 83 DECLARECURSORc empISSELECT FROMempFORUPDATE v incrementNUMBER BEGINFORv empINc empLOOPCASEv emp deptnoWHEN10THENv increment 100 WHEN20THENv increment 150 WHEN30THENv increment 200 ELSEv increment 250 ENDCASE UPDATEempSETsal sal v incrementWHERECURRENTOFc emp ENDLOOP END 84 15 4 3隐式游标 用于处理INSERT UPDATE DELETE和SELECT INTO语句没有OPEN FETCH CLOSE命令属性SQL ISOPENSQL FOUNDSQL NOTFOUNDSQL ROWCOUNT 85 修改员工号为1000的员工工资 将其工资增加100 如果该员工不存在 则向emp表中插入一个员工号为1000 工资为1500的员工 BEGINUPDATEempSETsal sal 100WHEREempno 1000 IFSQL NOTFOUNDTHENINSERTINTOemp empno sal VALUES 1000 1500 ENDIF END 或BEGINUPDATEempSETsal sal 100WHEREempno 1000 IFSQL ROWCOUNT 0THENINSERTINTOemp empno sal VALUES 1000 1500 ENDIF END 86 15 5异常处理 异常概述异常处理过程异常的传播 87 15 5 1异常概述 Oracle错误处理机制异常的类型 88 Oracle错误处理机制概念一个错误对应一个异常 当错误产生时抛出相应的异常 并被异常处理器捕获 程序控制权传递给异常处理器 由异常处理器来处理运行时错误 89 异常的类型预定义的Oracle异常非预定义的Oracle异常用户定义的异常 90 预定义的异常 91 92 93 非预定义异常 在语句块的声明部分声明一个异常名称e integrityEXCEPTION 通过PRAGMAEXCEPTION INIT将异常与一个Oracle错误号相关联 PRAGMAEXCEPTION INIT e integrity 2291 在异常处理部分捕捉并处理异常 WHENe integrityTHEN 94 用户自定义的异常 用户自定义异常必须在声明部分进行声明 当异常发生时 系统不能自动触发 需要用户使用RAISE语句 在异常处理部分捕捉并处理异常 95 15 5 2异常处理过程 在声明部分为错误定义异常 包括非预定义异常和用户定义异常 e exceptionEXCEPTION PRAGMAEXCEPTION INIT e exceptioin 在执行过程中当错误产生时抛出与错误对应的异常 RAISEuser define exception 在异常处理部分通过异常处理器捕获异常 并进行异常处理 96 异常的捕获与处理 异常处理器的基本形式为EXCEPTIONWHENexception1 ORexcetpion2 THENsequence of statements1 WHENexceptioin3 ORexception4 THENsequence of statements2 WHENOTHERSTHENsequence of statementsn END 97 注意 一个异常处理器可以捕获多个异常 只需要在WHEN子句中用OR连接即可 一个异常只能被一个异常处理器捕获 并进行处理 98 查询名为SMITH的员工工资 如果该员工不存在 则输出 Thereisnotsuchanemployee 如果存在多个同名的员工 则输出其员工号和工资 DECLAREv salemp sal type BEGINSELECTsalINTOv salFROMempWHEREename SMITH DBMS OUTPUT PUT LINE v sal EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE Thereisnotsuchanemplyee WHENTOO MANY ROWSTHENFORv empIN SELECT FROMempWHEREename SMITH LOOPDBMS OUTPUT PUT LINE v emp empno v emp sal ENDLOOP END 99 删除dept表中部门号为10的部门信息 如果不能删除则输出 Therearesubrecordsinemptable DECLAREe deptno fkEXCEPTION PRAGMAEXCEPTION INIT e deptno fk 2292 BEGINDELETEFROMdeptWHEREdeptno 10 EXCEPTIONWHENe deptno fkTHENDBMS OUTPUT PUT LINE Therearesubrecordsinemptable END 100 修改7844员工的工资 保证修改后工资不超过6000 DECLAREe highlimitEXCEPTION v salemp sal TYPE BEGINUPDATEempSETsal sal 100WHEREempno 7844RETURNINGsalINTOv sal IFv sal 6000THENRAISEe highlimit ENDIF EXCEPTIONWHENe highlimitTHENDBMS OUTPUT PUT LINE Thesalaryistoolarge ROLLBACK END 101 OTHERS异常处理器 OTHERS异常处理器是一个特殊的异常处理器 可以捕获所有的异常 通常 OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器 负责处理那些没有被其他异常处理器捕获的异常 102 DECLAREv salemp sal TYPE e highlimitEXCEPTION BEGINSELECTsalINTOv salFROMempWHEREename JOAN UPDATEempSETsal sal 100WHEREempno 7900 IFv sal 6000THENRAISEe highlimit ENDIF EXCEPTIONWHENe highlimitTHENDBMS OUTPUT PUT LINE Thesalaryistoolarge ROLLBACK WHENOTHERSTHENDBMS OUTPUT PUT LINE Thereissomewronginselecting END 103 可以通过两个函数来获取错误相关信息 SQLCODE 返回当前错误代码 如果是用户定义错误返回值为1 如果是ORA 1403 NODATAFOUND错误 返回值为100 其他Oracle内部错误返回相应的错误号 SQLERRM 返回当前错误的消息文本 如果是Oracle内部错误 返回系统内部的错误描述 如果是用户定义错误 则返回信息文本为 User definedException 104 DECLAREv salemp sal TYPE e highlimitEXCEPTION v codeNUMBER 6 v textVARCHAR2 200 BEGINSELECTsalINTOv salFROMempWHEREename JOAN UPDATEempSETsal sal 100WHEREempno 7900 IFv sal 6000THENRAISEe highlimit ENDIF EXCEPTIONWHENe highlimitTHENDBMS OUTPUT PUT LINE Thesalaryistoolarge ROLLBACK WHENOTHERSTHENv code SQLCODE v text SQLERRM DBMS OUTPUT PUT LINE v code v text END 105 15 5 3异常的传播 可执行部分异常的传播如果当前语句块有该异常的处理器 则执行之 并且成功完成该语句块 然后 控制权传递到外层语句块 如果当前语句块没有该异常的处理器 则通过在外层语句块中产生该异常来传播该异常 然后 执行对外层语句块执行步骤1 如果没有外层语句块 则该异常将传播到调用环境 106 DECLAREv salemp sal TYPE BEGINBEGINSELECTsalINTOv salFROMempWHEREename JOAN EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE Thereisnotsuchanemployee END DBMS OUTPUT PUT LINE Nowthisisoutputtedbyouterblock END Thereisnotsuchanemployee Nowthisisoutputtedbyouterblock 107 DECLAREv salemp sal TYPE BEGINBEGINSELECTsalINTOv salFROMempWHEREdeptno 10 EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE Thereisnotsuchanemployee END DBMS OUTPUT PUT LINE Nowthisisoutputtedbyouterblock EXCEPTIONWHENTOO MANY ROWSTHENDBMS OUTPUT PUT LINE Therearemorethanoneemployee END Therearemorethanoneemployee 108 声明部分异常的传播声明部分的异常立刻传播到外层语句块 即使当前语句块有异常处理器 异常处理部分的异常的传播异常处理器中产生的异常 可以有RAISE语句显式产生 也可以通过运行时错误而隐含产生 异常立即被传播到外层语句块 109 BEGINDECLAREv numberNUMBER 6 ABC BEGINv number 10 EXCEPTIONWHENOTHERSTHENDBMS OUTPUT PUT LINE Thisisoutputtedbyinnerblock END EXCEPTIONWHENOTHERSTHENDBMS OUTPUT PUT LINE Thisisoutputtedbyouterblock END Thisisoutputtedbyouterblock 110 15 6存储子程序 存储过程函数局部子程序 111 存储子程序是指被命名的PL SQL块 以编译的形式存储在数据库服务器中 可以在应用程序中进行调用 是PL SQL程序模块化的一种体现 存储子程序是以独立对象的形式存储在数据库服务器中 因此是一种全局结构 与之对应的是局部子程序 即嵌套在PL SQL块中的局部过程和函数 其存储位置取决于其所在的父块的位置 112 存储过程 存储过程的创建存储过程的调用存储过程的管理 113 存储过程的创建 CREATE ORREPLACE PROCEDUREprocedure name parameter1 name mode datatype DEFAULT value parameter2 name mode datatype DEFAULT value AS IS Declarativesectionishere BEGIN Executablesectionishere EXCEPTION Exceptionsectionishere END procedure name PROCEDUREBODY 114 参数模式IN当过程被调用时 实参值被传递给过程 在过程内 该参数起常数作用 可读不可写 调用结束 实参值不变 默认参数类型 OUT当过程被调用时 实参值被忽略 在过程内 该参数起未初始化的变量作用 值为NULL 过程内 该参数可读可写 调用结束 形参赋给实参 INOUT当过程被调用时 实参值被传递给过程 在过程内 该参数起已初始化变量作用 过程内 该参数可读可写 调用结束 形参赋给实参 115 参数限制声明形参时不能定义形参的长度或精度 刻度参数传递IN参数为引用传递 即实参的指针被传递给形参 OUT INOUT参数为值传递 即实参的值被复制给形参 116 创建一个存储过程 以部门号为参数 查询该部门的平均工资 并输出该部门中比平均工资高的员工号 员工名 CREATEORREPLACEPROCEDUREshow emp p deptnoemp deptno TYPE ASv salemp sal TYPE BEGINSELECTavg sal INTOv salFROMempWHEREdeptno p deptno DBMS OUTPUT PUT LINE p deptno averagesalaryis v sal FORv empIN SELECT FROMempWHEREdeptno p deptnoANDsal v sal LOOPDBMS OUTPUT PUT LINE v emp empno v emp ename ENDLOOP EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE Thedepartmentdoesn texists ENDshow emp 117 通常 存储过程不需要返回值 如果需要返回一个值可以通过函数调用实现 但是 如果希望返回多个值 可以使用OUT或INOUT模式参数来实现 118 创建一个存储过程 以部门号为参数 返回该部门的人数和最高工资 CREATEORREPLACEPROCEDUREreturn deptinfo p deptnoemp deptno TYPE p avgsalOUTemp sal TYPE p countOUTemp sal TYPE ASBEGINSELECTavg sal count INTOp avgsal p countFROMempWHEREdeptno p deptno EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE Thedepartmentdon texists ENDreturn deptinfo 119 存储过程的调用 在SQL PLUS中调用EXECprocedure name parameter list EXECUTEshow emp 10 在PL SQL块中调用BEGINprocedure name parameter list END 120 DECLAREv avgsalemp sal TYPE v countNUMBER BEGINshow emp 20 return deptinfo 10 v avgsal v count DBMS OUTPUT PUT LINE v avgsal v count END 121 存储过程的管理 修改存储过程CREATEORREPLACEPROCEDURE重新编译存储过程ALTERPROCEDUREprocedure nameCOMPILE 删除存储过程DROPPROCEDUREprocedure name名 查看过程源代码selecttextfromuser sourcewherename procedure name 122 15 6 2函数 函数概述函数的创建函数的调用函数的管理 123 函数概述 函数用于返回特定数据 可以返回一个或多个值 在一个函数中必须包含一个或多个RETURN语句函数调用是PL SQL表达式的一部分 而过程调用可以是一个独立的PL SQL语句 124 函数的创建 CREATE ORREPLACE FUNCTIONfunction name parameter1 name mode datatype DEFAULT value parameter2 name mode datatype DEFAULT value RETURNreturn datatypeAS IS Declarativesectionishere BEGIN Executablesectionishere EXCEPTION Exceptionsectionishere END function name FUNCTIONBODY 125 创建一个以部门号为参数 返回该部门最高工资的函数 CREATEORREPLACEFUNCTIONreturn maxsal p deptnoemp deptno TYPE RETURNemp sal TYPEASv maxsalemp sal TYPE BEGINSELECTmax sal INTOv maxsalFROMempWHEREdeptno p deptno RETURNv maxsal EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE Thedeptnoisinvalid ENDreturn maxsal 126 函数的调用 在SQL语句中调用函数在PL SQL中调用函数 127 DECLAREv salemp sal TYPE BEGINFORv deptIN SELECTDISTINCTdeptnoFROMemp LOOPv sal return maxsal v dept deptno DBMS OUTPUT PUT LINE v dept deptno v sal ENDLOOP END 128 函数的管理 修改函数CREATEORREPLACEFUNCTIONfunction name重新编译存储过程ALTERFUNCTIONfunction nameCOMPILE 删除存储过程DROPFUNCTIONfunction name 查看过程源代码selecttextfromuser sourcewherename function name 129 15 6 3局部子程序 局部子程序嵌套在其他PL SQL块中的子程序 只能在其定义的块内部被调用 而不能在其父块外被调用 使用局部子程序时需要注意 局部子程序只在当前语句块内有效 局部子程序必须在PL SQL块声明部分的最后进行定义 局部子程序必须在使用之前声明 如果是子程序间相互引用 则需要采用预先声明 局部子程序可以重载 130 在一个块内部定义一个函数和一个过程 函数以部门号为参数返回该部门的平均工资 过程以部门号为参数 输出该部门中工资低于部门平均工资的员工的员工号 员工名 131 DECLAREv deptnoemp deptno TYPE v avgsalemp sal TYPE FUNCTIONreturn avgsal p deptnoemp deptno TYPE RETURNemp sal TYPEASv salemp sal TYPE BEGINSELECTavg sal INTOv salFROMempWHEREdeptno p deptno RETURNv sal ENDreturn avgsal PROCEDUREshow emp p deptnoemp deptno TYPE ASCURSORc empISSELECT FROMempWHEREsal return avgsal p deptno BEGINFORv empINc empLOOPDBMS OUTPUT PUT LINE v emp empno v emp ename ENDLOOP ENDshow emp BEGINv deptno 132 存储子程序与局部子程序区别在于 存储子程序己经编译好放在数据库服务器端 可以直接调用 而局部子程序存在于定义它的语句块中 在运行时先进行编译 存储子程序不能重载