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

D第7章PL/SQL包与触发器学习要点创建包调用和执行包组件建立DML触发器建立INSTEADOF触发器

本章主要内容

创建包调用和执行包组件触发器介绍建立DML触发器建立INSTEADOF触发器触发器的维护B7.1创建包包是由存储在一起的相关对象组成的PL/SQL存储程序。它可以把相关的过程、函数组织在一起，这样便于管理，提高了代码性能。

包由两个分离的部件组成：包头和包体。包头也叫包说明或包规范，它包括函数头、过程头、和外部可视数据结构。在创建包时，一般先建立包头，再建立包体。

B7.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_specificationsEND[package_name]7.2调用和执行包组件调用方法分为包内调用和包外调用。1．包内调用【例】在过程addemp中对validate过程进行包内调用。CREATEORREPLACEPACKAGEBODYmypackageISPROCEDUREaddemp(enoNUMBER,nameVARCHAR2,salNUMBER,dnoNUMBER)ISBEGINIFvalidate(dno)THENINSERTINTOemp(empno,ename,sal,deptno)VALUES(eno,name,sal,dno);ELSEraise_application_error(-200010,'不存在该部门');ENDIF;EXCEPTIONWHENDUP_VAL_ON_INDEXTHENraise_application_error(-200012,'该员工已经存在,重复插入');ENDmypackage;C2．调用包的公共变量包名：mypackage，公共变量pub_pSQL>EXECmypackage.pub_p:=20;3．调用包的公共过程包名：mypackage，公共过程：addemp(enoNUMBER,nameVARCHAR2,salNUMBER,dnoNUMBER)SQL>EXECmypackage.addemp(1111,'SCOTT',5600);SQL>EXECmypackage.addemp(1111,'SCOTT',5600,88);D4．调用包的公共函数包名：mypackage，公共函数：get_sal(enoNUMBER)功能：把员工号eno作为输入参数，得到此员工的工资，返回一个NUMBER类型的数据。SQL>VARsalaryNUMBERSQL>EXEC:salary:=mypackage.get_sal(9012);SQL>PRINTsalary执行结果：SALARY---------5600C7.3触触发器介绍7.3.1触触发器的的作用与特点点触发器的主要要作用是能够够实现由主建建和外键所不不能保证的参参照完整性和和数据的一致致性。除此之之外，触发器器还有许多不不同的作用：：（1）强化约约束。触发器器能够实现比比CHECK语句更为为复杂的约束束。（2）跟踪变变化。触发器器可以侦测数数据库内部的的操作，在没没有更新权限限的情况下不不允许对数据据库进行更新新操作。（3）安全性性。可以基于于数据库的值值使用户具有有操作数据库库的某种权利利。例如：①①可以基于时时间限制用户户的操作，例例如，不允许许下班后和节节假日修改数数据库数据；；②可以基于于数据库中的的数据限制用用户的操作，，例如，不允允许股票的价价格的升幅一一次超过10%；③审计计。可以跟踪踪用户对数据据库的操作，，审计用户操操作数据库的的语句，把用用户对数据库库的更新写入入审计表。（4）自动生生成导出的列列值。（5）防止无无效的事务。。（6）在分布布式数据库中中实施跨越结结点的参照完完整性。（7）提供透透明事件日志志。A7.3.2触触发器的的类型和组成成1．触发器的的类型对每一触发语语句可有四种种类型触发器器：（1）行触发发器。对受触触发语句所影影响的每一行行进行触发。。（2）语句触触发器。定义义语句触发器器时要指定触触发时间，即即触发器是在在触发语句执执行之后触发发还是在之前前触发。（3）BEFORE触发发器。该触发发器执行触发发器动作是在在触发语句执执行之前。（4）AFTER触发器器。该触发器器执行触发器器动作是在触触发语句执行行之后。C2．触发器的的组成一个触发器有有三个基本部部件：触发事事件或语句、、触发器条件件、触发器动动作。触发事件是指指引起触发器器激发的事件件。触发语句句指数据操纵纵（DML））语句，这些些语句是INSERT、、UPDATE或DELETE语句句。触发器条件是是一个布尔表表达式。触发器的动作作是一个PL/SQL块块（过程），，由SQL语语句和PL/SQL语句句组成。触发发语句发出后后，当触发器器的条件为TRUE时，，它被执行。。D7.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_HISBEFOREDELETEONTCH_PTFROEACHROWBEGININSERTINTODEL_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_TCHAFTERINSERTORUPDATEORDELETEONTCH_PTFOREACHROWDECLAREINFORCHAR(10);BEGINIFINSETINGTHENINFOR:="插入";ELSIFUPDATINGTHENINFOR:="更更新";ELSEINFOR:="删除";ENDIF;INSERTINTOSQL_INFORVALUES(INFOR);ENDTISHI_TCH;7.4.2行行触发器器行触发器是执执行DML操操作时，每作作用一行就触触发一次的触触发器，主要要用于审计数数据变化。建立行触发器器的语法如下下：CREATE[ORREPLACE]TRIGGERtrigger_nameTimingevent1[ORevent2ORevent3]ONtable_namePL/SQLblobk;其中：trigger_name是是触发器名称称，Timing是触触发时机，event是是触发事件，，table_name指DML操作的表表名。1．建立BEFORE行行触发器【例】保障职职工的月薪不不低于5000元。触发器创建如如下：CREATEORREPLACETRIGGERtrigger_up_salBEFOREUPDATEOFsalONEMPFOREACHROWBEGINIF:new.sal<5000THENRaise_application_error(-20010,'工资不能能低于5000');ENDIF;END;【例】把职工工进行修改的的记录全部写写入审计表，，以便于审计计。审计表如下：：AUDIT(nameVARCHAR2(10),oldsalNUMBER,newsalNUMBER,timeDATE)。创建触发器如如下：CREATEORREPLACETRIGGERtrigger_up_salAFTERUPDATEOFsalONEMPFOREACHROWDECLAREcountINT;BEGINSELECTcount(*)INTOcount//查询审审计表的记录录个数存入变变量CountFROMauditWHEREname:old.ename;IFcount=0THEN//如果审计计表为空,插插入记录INSERTINTOauditVALUES(:old.ename,:old.sal,:new.sal,SYSDATE)ELSE//如如果审计表不不为空,更新新记录UPDATEauditSEToldsal=:old.sal,newsal=:new.sal,time=SYSDATEWHEREname=:old.enameENDIF;END2．建立AFTER行行触发器7.5建建立INSTEADOF触发器器INSTEADO