Oracle数据库开发实用教程第7章

第7章PL/SQL包与触发器学习要点创建包调用和执行包组件建立DML触发器建立INSTEADOF触发器本章主要内容创建包调用和执行包组件触发器介绍建立DML触发器建立INSTEADOF触发器触发器的维护7.1创建包包是由存储在一起的相关对象组成的PL/SQL存储程序。它可以把相关的过程、函数组织在一起，这样便于管理，提高了代码性能。包由两个分离的部件组成：包头和包体。包头也叫包说明或包规范，它包括函数头、过程头、和外部可视数据结构。在创建包时，一般先建立包头，再建立包体。7.1.1包头包头用于定义包的公用组件，如：常量、类型、变量、游标、过程和函数等。创建包必须首先创建包规范，创建包规范的语法如下：CREATE[ORREPLACE]PACKAGEpackage_name{AS|IS}public_variable_declarations|public_type_declarations|public_exception_declarations|function_declarations|procedure_specificationsEND[package_name]其中：package_name：包名。public_variable_declarations：公共变量声明。public_type_declarations：公共类型声明public_exception_declarations：公共异常声明。function_declarations：函数声明。procedure_specifications：过程声明。7.1.2包体包体用于实现包头所定义的过程和函数。关于包的基本规则如下：（1）在包头声明过的是外部可以调用的过程、函数、变量。在包头中定义的所有的常量、变量、过程和函数等都是公有的；就是说在包头内定义的公有组件（常量、变量、过程和函数）既可以在包内使用也可以在包外部调用。（2）在包体内也可以定义过程、函数、变量，但这些定义只能在包体内可见。在包体内的各个过程和函数本身定义的处理和变量都是私有的。就是说在包体中定义的私有组件（常量、变量、过程和函数）都只能在包内使用，不能由其他子程序调用。（3）在包中声明的时候，必须是前向声明的，也就是对象需要引用的另一个对象必须在前面首先声明。（4）包头是必须存在的，包体是可选的。（5）包头必须在包体前创建，否则创建不了包体。（6）只在包头中包括公用变量、过程、函数的声明创建包体使用CREATEPACKAGEBODY语句：CREATE[ORREPLACE]PACKAGEBODYpackage_name{AS|IS}private_variable_declarations|private_type_declarations|private_exception_declarations|private_cursor_declarations|function_declarations|procedure_specificationsEND[package_name]7.2调用和执行包组件调用方法分为包内调用和包外调用。1．包内调用【例】在过程addemp中对validate过程进行包内调用。CREATEORREPLACEPACKAGEBODYmypackageISPROCEDUREaddemp(enoNUMBER,nameVARCHAR2,salNUMBER,dnoNUMBER)ISBEGINIFvalidate(dno)THENINSERTINTOemp(empno,ename,sal,deptno)VALUES(eno,name,sal,dno);ELSEraise_application_error(-200010,'不存在该部门');ENDIF;EXCEPTIONWHENDUP_VAL_ON_INDEXTHENraise_application_error(-200012,'该员工已经存在,重复插入');ENDmypackage;2．调用包的公共变量包名：mypackage，公共变量pub_pSQLEXECmypackage.pub_p:=20;3．调用包的公共过程包名：mypackage，公共过程：addemp(enoNUMBER,nameVARCHAR2,salNUMBER,dnoNUMBER)SQLEXECmypackage.addemp(1111,'SCOTT',5600);SQLEXECmypackage.addemp(1111,'SCOTT',5600,88);4．调用包的公共函数包名：mypackage，公共函数：get_sal(enoNUMBER)功能：把员工号eno作为输入参数，得到此员工的工资，返回一个NUMBER类型的数据。SQLVARsalaryNUMBERSQLEXEC:salary:=mypackage.get_sal(9012);SQLPRINTsalary执行结果：SALARY---------56007.3触发器介绍7.3.1触发器的作用与特点触发器的主要作用是能够实现由主建和外键所不能保证的参照完整性和数据的一致性。除此之外，触发器还有许多不同的作用：（1）强化约束。触发器能够实现比CHECK语句更为复杂的约束。（2）跟踪变化。触发器可以侦测数据库内部的操作，在没有更新权限的情况下不允许对数据库进行更新操作。（3）安全性。可以基于数据库的值使用户具有操作数据库的某种权利。例如：①可以基于时间限制用户的操作，例如，不允许下班后和节假日修改数据库数据；②可以基于数据库中的数据限制用户的操作，例如，不允许股票的价格的升幅一次超过10%；③审计。可以跟踪用户对数据库的操作，审计用户操作数据库的语句，把用户对数据库的更新写入审计表。（4）自动生成导出的列值。（5）防止无效的事务。（6）在分布式数据库中实施跨越结点的参照完整性。（7）提供透明事件日志。7.3.2触发器的类型和组成1．触发器的类型对每一触发语句可有四种类型触发器：（1）行触发器。对受触发语句所影响的每一行进行触发。（2）语句触发器。定义语句触发器时要指定触发时间，即触发器是在触发语句执行之后触发还是在之前触发。（3）BEFORE触发器。该触发器执行触发器动作是在触发语句执行之前。（4）AFTER触发器。该触发器执行触发器动作是在触发语句执行之后。2．触发器的组成一个触发器有三个基本部件：触发事件或语句、触发器条件、触发器动作。触发事件是指引起触发器激发的事件。触发语句指数据操纵（DML）语句，这些语句是INSERT、UPDATE或DELETE语句。触发器条件是一个布尔表达式。触发器的动作是一个PL/SQL块（过程），由SQL语句和PL/SQL语句组成。触发语句发出后，当触发器的条件为TRUE时，它被执行。7.4建立DML触发器DML事件触发器可以是语句或行级触发器。DML语句触发器在触发语句之前或之后触发。DML行级触发器在语句影响的行变化之前或之后触发。用户可以给单一事件和类型定义多个触发器，但没有任何方法可以增强多触发器触发的命令。7.4.1语句触发器创建触发器的语法如下：CREATE[ORREPLACE]TRIGGER触发器名触发时间触发事件ON表名[FOREACHROW]PL/SQL语句其中：（1）触发器名。触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。触发器的名称与过程名称不一样，它有单独的命名空间，触发器可以与表名或过程名同名，但是在同一个SCHEMA中的触发器名称不能相同。（2）触发时间。指明触发器何时执行。BEFORE表示在数据库动作之前触发器执行。AFTER表示在数据库动作之后触发器执行。（3）触发事件。指明哪些数据库动作会触发此触发器：INSERT：数据库插入会触发此触发器。UPDATE：数据库修改会触发此触发器。DELETE：数据库删除会触发此触发器。（4）表名。数据库触发器所在的表。FOREACHROW：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。1．建立BEFORE语句触发器【例】现在向数据库SCHOOL中增加一个新表DEL_TCH_PT，表的结构同表TCH_PT一样，主要存放从TCH_PT表中删除的记录。因此需要创建一个触发器，当表TCH_PT每删除一行，就把删除的记录写到日志DEL_TCH_PT中。触发器创建如下：CREATEORREPLACETRIGGERTCH_HISBEFOREDELETEONTCH_PTFROEACHROWBEGININSERTINTODEL_TCH_PT(XH,XM,XB)VALUES(:OLD.XH,:OLD.XM,:OLD.XB)ENDTCH_HIS;其中OLD修饰访问操作前列的值。2．使用条件谓词触发器同时包含多个事件（插入、更新、删除），为了区分具体哪个事件可以使用相应的三个条件谓词：INSERTING：当触发事件为INSERT，该谓词返回TRUE，否则为FALSE。UPDATING：当触发事件为UPDATE，该谓词返回TRUE，否则为FALSE。DELETING：当触发事件为DELETE，该谓词返回TRUE，否则为FALSE。3．建立AFTER语句触发器【例】利用触发器在数据库SCHOOL的TCH_PT中执行插入、更新和删除3种操作后给出相应的提示。CREATETRIGGERTISHI_TCHAFTERINSERTORUPDATEORDELETEONTCH_PTFOREACHROWDECLAREINFORCHAR(10);BEGINIFINSETINGTHENINFOR:=插入;ELSIFUPDATINGTHENINFOR:=更新;ELSEINFOR:=删除;ENDIF;INSERTINTOSQL_INFORVALUES(INFOR);ENDTISHI_TCH;7.4.2行触发器行触发器是执行DML操作时，每作用一行就触发一次的触发器，主要用于审计数据变化。建立行触发器的语法如下：CREATE[ORREPLACE]TRIGGERtrigger_nameTimingevent1[ORevent2ORevent3]ONtable_namePL/SQLblobk;其中：trigger_name是触发器名称，Timing是触发时机，event是触发事件，table_name指DML操作的表名。1．建立BEFORE行触发器【例】保障职工的月薪不低于5000元。触发器创建如下：CREATEORREPLACETRIGGERtrigger_up_salBEFOREUPDATEOFsalONEMPFOREACHROWBEGINIF:new.sal5000THENRaise_application_error(-20010,'工资不能低于5000');ENDIF;END;【例】把职工进行修改的记录全部写入审计表，以便于审计。审计表如下：AUDIT(nameVARCHAR2(10),oldsalNUMBER,newsalNUMBER,timeDATE)。创建触发器如下：CREATEORREPLACETRIGGERtrigger_up_salAFTERUPDATEOFsalONEMPFOREACHROWDECLAREcountINT;BEGINSELECTcount(*)INTOcount//查询审计表的记录个数存入变量CountFROMauditWHEREname:old.ename;IFcount=0THEN//如果审计表为空,插入记录INSERTINTOauditVALUES(:old.ename,:old.sal,: