第7章_存储过程、触发器与程序包-zhy

1、第7章存储过程、触发器和程序包l存储过程(重点重点)l函数(重点重点)l触发器l程序包2 在很多时候，都需要保存PL/SQL程序块，以便随后可以重新使用。这也意味着，程序块需要一个名称，这样需才可以调用或者引用它。 命名的PL/SQL程序块可被独立编译并存储在数据库中，任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块。 Oracle提供了四种类型的可存储的程序：过程、过程、函数、触发器和程序包。函数、触发器和程序包。37.1 存储过程存储过程l 存储过程是一种命名的PL/SQL程序块，它可以接受零个或多个参数作为输入、输出，或者既作输入又作输出的参数。l 过程被存储在数据库中

2、，并且存储过程没有返回值。l 存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用。l 由于存储过程是已经编译好的代码，所以在调用的时候不必再次进行编译，从而提高了程序的运行效率。7.1.1 创建存储过程创建存储过程l 定义存储过程的语法：4create procedure procedure_name (parameter,parameter,.) islocal declarationsbegin execute statementsexception exception handlers end procedure _name7.1.1 创建存储过程创建

3、存储过程l 注意：l （1）存储过程与匿名程序块类似，也包括三部分：声明部分、执行部分和异常处理部分。l （2）不能使用DECLARE关键字表示声明部分，IS关键字后声明的变量为过程体内的局部变量。l （3）不能指定参数类型的长度和精度不能指定参数类型的长度和精度。l （4）重新定义存储过程（同名），必须使用OR REPLACE选项，使新版本覆盖旧版本。l （5）可以在PL/SQL程序块中调用存储过程，也可以直接在SQL*PLUS中使用EXECUTE语句调用。57.1.1 创建存储过程创建存储过程6注意：注意：若在创建存储过程若在创建存储过程时出现了错误，则使用：时出现了错误，则使用：SHOW

4、 ERRORS 命令命令显示创建时产生的错误。显示创建时产生的错误。7.1.2 参数参数lOracle有三种参数模式：IN、OUT和和IN OUT。lIN参数参数例：将例：将“向向scott.emp表中插入记录表中插入记录”这一功能用过程完成。这一功能用过程完成。7IN参数参数7.1.2 参数参数l 在调用存储过程时，有三种向其传递参数的方法：名称表示法、位置表示法和混合表示法。l 1）名称表示法l 名称表示法是指为各个参数传递参数值时指定传入数值的参数名。具体格式如下：8IN参数参数procedure_name(param_name=value, param_name=value);7.1.

5、2 参数参数l 2）位置表示法l 当参数比较多时，通过名称表示法调用过程会非常长，此时可以采用位置表示法。采用位置表示法传递参数时，用户提供的参数值顺序必须与过程定义中的参数顺序一致。9IN参数参数7.1.2 参数参数l 3）混合表示法l 名称表示法和位置表示法各有优缺点，为了弥补这两者的不足，还可以采用混合表示法，以发挥两者的优点。10IN参数参数注意：当切换为名称表示法传递参数后，后续的参数也必须使用名称表示法。7.1.2 参数参数l OUT类型的参数由存储过程传入值由存储过程传入值，然后由调用者接调用者接收参数值收参数值。l 例：通过例：通过empno雇员号检索雇员号检索emp表，并获得

6、雇员的表，并获得雇员的ename和和sal，将这一功能用过程完成。，将这一功能用过程完成。11OUT参数参数7.1.2 参数参数l 在使用SQL*PLUS调用具有OUT参数的过程时，需要使用VARIABLE命令绑定参数值。l 为了查看执行结果，可以使用PRINT命令显示变量值，也可以通过SELECT语句检索绑定的变量值。12OUT参数参数7.1.2 参数参数l 由于过程要通过OUT参数返回值，所以在调用时必须提供能够接收返回值的变量。即使OUT参数在定义过程时没有设置返回值，调用时也必须为其提供接收变量。13OUT参数参数7.1.2 参数参数l IN OUT类型的参数同时具有IN参数和OUT参

7、数的特性，在调用过程时既可以向该类型的参数传入值，也可以从该参数接收值；而在过程的执行过程中，既可以读取又可以写入该类型参数。14IN OUT参数参数7.1.3 默认值默认值l 存储过程的参数也可以有默认值，这样当调用该过程时，如果未向参数传入值，则该参数将使用定义的默认值。例如，下面修改了ADD_EMPLOYEE存储过程，为其中的参数提供了默认值：l 注意：只能给IN参数设置默认值。15167.1.4 过程中的事务处理过程中的事务处理l 当在SQL*Plus中进行操作时，用户可以使用COMMIT语句将在事务中的所有操作“保存”到数据库中。如果用户需要撤销所有的操作，则可以使用ROLLBACK

8、语句回退事务中未提交的操作，使数据库返回到事务处理开始前的状态。在PL/SQL过程中，不仅可以包括插入和更新这类的DML操作，还可以包括事务处理语句COMMIT和ROLLBACK。l Oracle支持事务的嵌套，即在事务处理中进行事务处理。在嵌套的事务处理过程中，子事务可以独立于父事务处理进行提交和回滚。177.1.4 过程中的事务处理过程中的事务处理187.2 函数函数l 函数与过程非常类似，它也是一种存储在数据库中的命名程序块，并且函数也可以接受零个或多个输入参数。l 函数与过程之间的主要区别在于，函数必须有返回值，并且可以作为一个表达式的一部分，函数不能作为一个完整的语句使用。函数返回值

9、的数据类型在创建函数时定义，定义函数的基本语法如下：create or replace function function_name (parameter ,parameter) returne data_type is local declarationsbegin execute statementsexceptionexception handlersend function_name197.2 函数函数7.3 触发器触发器l 触发器是关系数据库系统提供的一项技术，触发器类似过程和函数，它们都包括声明部分，执行逻辑处理部分和异常处理部分，并且都被存储在数据库中。207.3.1 触发器的概

10、述触发器的概述l 触发器是与一个表或数据库事件联系在一起的，当特定事件出现时将自动执行触发器的代码块。l 触发器与过程的区别在于： 过程是由用户或应用程序显式调用的，而触发器是不能被直接调用的。 在创建触发器时需要指定触发器的执行时间和触发事件。l 创建触发器的语法规则如下（P157）21create or replace trigger trigger_name before | after | instead of trigger_event on table_name for each row when trigger_condition begin trigger_bodyend tr

11、igger_name;7.3.1 触发器的概述触发器的概述l Oracle对触发器的功能进行了扩展，不仅对表和视图的DML操作会引起触发器的运行，而且对Oracle系统的操作也会引发触发器的运行。l 根据触发器的触发事件和执行情况，可以将Oracle所支持的触发器分为如下几种类型：l （1）行级触发器l （2）语句级触发器l （3）INSTEAD OF触发器：视图上l （4）系统事件触发器:系统事件触发（如启动与关闭）l （5）用户事件触发器：与DDL或用户的登录/注销等事件相关22237.3.2 语句级触发器语句级触发器l 如果在创建触发器时未使用FOR EACH ROW子句，则创建的触发器

12、为语句级触发器。语句级触发器在被触发后只执行一次，而不管这一操作会影响到数据库中多少行记录。如何确定是哪个语句（操作类型）激活了触发器？247.3.2 语句级触发器语句级触发器l 为了确定触发事件的类型，可以使用条件谓词。条件谓词是由一个关键字IF和谓词INSERTING、UPDATING和DELETING组成。如果值为真，那么就是相应类型的语句触发了触发器。l 此外，还可以在UPDATE触发器中使用条件谓词，判断特定列是否被更新。begin if inserting then -insert语句触发 elsif updating then -update语句触发 elsif deleting

13、 then -delete语句触发 end if;end;if updating(job) then -do somethingend if;257.3.2 语句级触发器语句级触发器267.3.3 行级触发器行级触发器l 在创建触发器时，如果使用了FOR EACH ROW选项，则创建的该触发器为行级触发器。对于行级触发器而言，当一个DML语句操作影响到数据库中的多行数据时，行级触发器会针对于每一行执行一次。277.3.3 行级触发器行级触发器287.3.3 行级触发器行级触发器7.3.4 INSTEAD OF 触发器触发器l INSTEAD OF 触发器也称替代触发器，定义INSTEAD OF

14、触发器后，用户对表的DML操作将不再被执行，而是执行触发器主体中的操作。通常情况下，INSTEAD OF触发器是定义在视图上的，而不是在表上定义的触发器，它是用来替换所使用实际语句的触发器。297.3.4 INSTEAD OF 触发器触发器307.3.5 用户事件触发器用户事件触发器l 用户事件触发器是建立在模式级的操作上的触发器。激活该类型触发器的用户事件包括：CREATE、ALTER、DROP、ANALYZE、ASSOCIATE STATISTICS、DISASSOCIATE 、STATISTICS、COMMENT、GRANT、REVOKE、RENAME、TRUNCATE、LOGOFF、S

15、USPEND和LOGON。317.4 程序包程序包l 程序包其实就是被组合在一起的相关对象的集合，当程序包中任何函数或存储过程被调用时，程序包就被加载入到内存中，这样程序包中的任何函数或存储过程的子程序访问速度将大大加快。例如，在PL/SQL程序中，为了输出运行结果，在程序的代码中使用了DBMS_OUTPT.PUT_LINE语句。事实上，这是调用程序包DBMS_OUTPUT中的PUT_LINE过程。DBMS_OUTPUT程序包的主要功能就是在PL/SQL程序中的输入和输出。327.4.1 程序包规范程序包规范l 对于程序包，规范就像一个说明书，它说明了在程序包中哪些过程或函数可以使用，如何使用。程序包规范必需的，并且必须在程序包主体之前创建。l 创建程序包规范的语法形式如下：33create or replace package package_name is public_variable_declarations public_type_declarations public_exception_declarations public_cursor_declarations function_declarations procedure_specificationsend package_name 347.4.2 主体主体l 程序包主体