Oracle数据库教程（第3版 微课视频版） 课件 第10章 触发器和程序包

1第10章触发器和程序包10.1触发器概述10.2触发器的创建、删除、启用或禁用10.3程序包概述10.4程序包的创建、调用和删除Oracle数据库教程(第3版•微课视频版)210.1触发器概述触发器（Trigger）是一种特殊的存储过程，与表的关系密切，其特殊性主要体现在不需要用户调用，而是在对特定表（或列）进行特定类型的数据修改时激发。触发器用于实现数据库的完整性，触发器具有以下优点：●可以提供比CHECK约束、FOREIGNKEY约束更灵活、更复杂、更强大的约束。●可对数据库中的相关表实现级联更改。●可以评估数据修改前后表的状态，并根据该差异采取措施。●

强制表的修改要合乎业务规则。Oracle的触发器有以下三类：1．DML触发器当数据库中发生数据操纵语言(DML)事件时将调用DML触发器。2．INSTEADOF触发器ORACLE专门为进行视图操作的一种处理方法。3．系统触发器系统触发器由数据定义语言(DDL)事件、数据库系统事件、用户事件触发。

Oracle数据库教程(第3版•微课视频版)310.2触发器的创建、删除、启用或禁用10.2.1创建触发器1.创建DML触发器语法格式：CREATE[ORREPLACE]TRIGGER[<用户方案名>.]<触发器名>/*触发器定义*/{BEFORE∣AFTER∣INSTEADOF}/*指定触发时间*/{DELETE|INSERT|UPDATE[OF<列名>[,…n]]}/*指定触发事件*/[OR{DELETE|INSERT|UPDATE[OF<列名>[,…n]]}]ON{<表名>∣<视图名>}/*指定表触发对象*/[FOREACHROW[WHEN(<条件表达式>)]]/*指定触发级别*/<PL/SQL语句块>/*触发体*/说明：●触发器名：指定触发器名称。

Oracle数据库教程(第3版•微课视频版)410.2触发器的创建、删除、启用或禁用●BEFORE：执行DML操作之前触发。●

AFTER：执行DML操作之后触发。●

INSTEADOF：替代触发器，触发时触发器指定的事件不执行，而执行触发器本身的操作。●

DELETE、INSERT、UPDATE：指定一个或多个触发事件，多个触发事件之间用OR连接。●

FOREACHROW：由于DML语句可能作用于多行，因此触发器的PL/SQL语句可能为作用的每一行运行一次，这样的触发器称为行级触发器(row-leveltrigger)；也可能为所有行只运行一次，这样的触发器称为语句级触发器(statement-leveltrigger)。如果未使用FOREACHROW子句，指定为语句级触发器，触发器激活后只执行一次。如果使用FOREACHROW子句，指定为行级触发器，触发器将针对每一行执行一次。WHEN子句用于指定触发条件。在行级触发器执行过程中，PL/SQL语句可以访问受触发器语句影响的每行的列值。“:OLD.列名”表示变化前的值，“:NEW.列名”表示变化后的值。

Oracle数据库教程(第3版•微课视频版)510.2触发器的创建、删除、启用或禁用【例10.1】在score表上创建一个INSERT触发器T_InsertCourseName，插入数据的课程为英语时，显示“该课程已经考试结束，不能添加成绩”。（1）创建触发器CREATEORREPLACETRIGGERT_InsertCourseName--(a)BEFOREINSERTONscoreFOREACHROWDECLARECourseNameame%TYPE;--(b.1)BEGINSELECTcnameINTOCourseName

--(b.2)FROMcourseWHEREcid=:NEW.cid;IFCourseName='英语'THEN

--(b.3)RAISE_APPLICATION_ERROR(-20001,'该课程已经考试结束，不能添加成绩');ENDIF;END;（2）测试触发器INSERTINTOscore(cid)VALUES((SELECTcidFROMcourseWHEREcname='英语'));--(b)

Oracle数据库教程(第3版•微课视频版)610.2触发器的创建、删除、启用或禁用运行结果：在行:1上开始执行命令时出错-INSERTINTOscore(cid)VALUES((SELECTcidFROMcourseWHEREcname='英语'))错误报告-ORA-20001:该课程已经考试结束，不能添加成绩ORA-06512:在"SYSTEM.T_INSERTCOURSENAME",line8ORA-04088:触发器'SYSTEM.T_INSERTCOURSENAME'执行过程中出错

Oracle数据库教程(第3版•微课视频版)710.2触发器的创建、删除、启用或禁用【例10.2】在teacher表上创建一个DELETE触发器T_DeleteRecord，禁止删除已任课教师的记录。（1）创建触发器CREATEORREPLACETRIGGERT_DeleteRecord--(a)BEFOREDELETEONteacherFOREACHROWDECLARELectureCountNUMBER;

--(b.1)BEGINSELECTCOUNT(*)INTOLectureCount

--(b.2)FROMlectureWHEREtid=:OLD.tid;IFLectureCount>=1THEN

--(b.3)RAISE_APPLICATION_ERROR(-20003,'不能删除该教师');ENDIF;END;（2）测试触发器DELETEFROMteacher

--(b)WHEREtname='郭莉君';

Oracle数据库教程(第3版•微课视频版)810.2触发器的创建、删除、启用或禁用运行结果：在行:1上开始执行命令时出错-DELETEFROMteacherWHEREtname='郭莉君'错误报告-ORA-20003:不能删除该教师ORA-06512:在"SYSTEM.T_DELETERECORD",line8ORA-04088:触发器'SYSTEM.T_DELETERECORD'执行过程中出错

Oracle数据库教程(第3版•微课视频版)910.2触发器的创建、删除、启用或禁用【例10.3】规定8:00-18:00为工作时间，要求任何人不能在非工作时间对课程表进行操作，创建一个触发器T_OperationCourse。（1）创建触发器CREATEORREPLACETRIGGERT_OperationCourse

--(a)BEFOREINSERTORUPDATEORDELETEONcourseBEGINIF(TO_CHAR(SYSDATE,'HH24:MI')NOTBETWEEN'08:00'AND'18:00')THEN--(b.1)RAISE_APPLICATION_ERROR(-20004,'不能在非工作时间对course表进行操作');ENDIF;END;（2）测试触发器UPDATEcourse--(b)SETcredit=4WHEREcid='4002';

Oracle数据库教程(第3版•微课视频版)1010.2触发器的创建、删除、启用或禁用运行结果：在行:1上开始执行命令时出错-UPDATEcourseSETcredit=4WHEREcid='4002'错误报告-ORA-20004:不能在非工作时间对course表进行操作ORA-06512:在"SYSTEM.T_OPERATIONCOURSE",line3ORA-04088:触发器'SYSTEM.T_OPERATIONCOURSE'执行过程中出错

Oracle数据库教程(第3版•微课视频版)1110.2触发器的创建、删除、启用或禁用2.创建INSTEADOF触发器【例10.4】创建视图V_StudentScore，包含学生学号、专业、课程号、成绩，创建一个INSTEADOF触发器T_Instead，当用户向student表或score表插入数据时，不执行激活触发器的插入语句，只执行触发器内部的插入语句。（1）创建视图、创建触发器CREATEVIEWV_StudentScore--(a)ASSELECTa.sid,speciality,cid,gradeFROMstudenta,scorebWHEREa.sid=b.sid;

Oracle数据库教程(第3版•微课视频版)1210.2触发器的创建、删除、启用或禁用CREATETRIGGERT_InsteadINSTEADOFINSERTONV_StudentScoreFOREACHROW--(b)DECLAREv_namechar(8);--(c.1)v_sexchar(2);v_birthdaydate;BEGINv_name:='Name';--(c.2)v_sex:='男';v_birthday:=TO_DATE('20020101','YYYYMMDD');INSERTINTOstudent(sid,sname,ssex,sbirthday,speciality)--(c.3)VALUES(:NEW.sid,v_name,v_sex,v_birthday,:NEW.speciality);INSERTINTOscoreVALUES(:NEW.sid,:NEW.cid,:NEW.grade);--(c.4)END;（2）测试触发器INSERTINTOV_StudentScoreVALUES('221007','计算机','1004',91);--(c)

Oracle数据库教程(第3版•微课视频版)1310.2触发器的创建、删除、启用或禁用运行结果：1行已插入。查看基表student表的情况。SELECT*FROMstudentWHEREsid='221007';显示结果：SIDSNAMESSEXSBIRTHDAYSPECIALITYTC------------------------------------------------------------------------------------------221007Name男2002-01-01计算机查看基表score表的情况。SELECT*FROMscoreWHEREsid='221007';显示结果：SIDCIDGRADE-------------------------------------221007100491

Oracle数据库教程(第3版•微课视频版)1410.2触发器的创建、删除、启用或禁用3.创建系统触发器语法格式：CREATEORREPLACETRIGGER[<用户方案名>.]<触发器名>/*触发器定义*/{BEFORE︱AFTER}/*指定触发时间*/{<DDL事件>︱<数据库事件>}、/*指定触发事件*/ON{DATABASE︱[用户方案名.]SCHEMA}[when_clause]/*指定触发对象*/<PL/SQL语句块>/*触发体*/说明：

●

DDL事件：可以是一个或多个DDL事件。●数据库事件：可以是一个或多个数据库事件。

●DATABASE：数据库触发器，由数据库事件激发。

●SCHEMA：用户触发器，由DDL事件激发。【例10.5】创建一个系统触发器T_DropObjects，记录用户SYSTEM所删除的对象。（1）创建表、创建触发器

Oracle数据库教程(第3版•微课视频版)1510.2触发器的创建、删除、启用或禁用CREATETABLEsco--(a)(sidchar(6)NOTNULL,cidchar(4)NOTNULL,gradenumberNULL,PRIMARYKEY(sid,cid)

);CREATETABLEDropObjects(ObjectNamevarchar2(30),ObjectTypevarchar2(20),DroppedDatedate);CREATEORREPLACETRIGGERT_DropObjects--(b)BEFOREDROPONSYSTEM.SCHEMABEGININSERTINTODropObjects--(c.1)VALUES(ora_dict_obj_name,ora_dict_obj_type,SYSDATE);END;（2）测试触发器DROPTABLEsco;--(c)

Oracle数据库教程(第3版•微课视频版)1610.2触发器的创建、删除、启用或禁用运行结果：TableSCO已删除。查看DropObjects表记录的信息。SELECT*FROMDropObjects;显示结果：OBJECTNAMEOBJECTTYPEDROPPEDDAT------------------------------------------------------------------------SCOTABLE2023-08-18

Oracle数据库教程(第3版•微课视频版)1710.2触发器的创建、删除、启用或禁用10.2.2删除触发器语法格式：DROPTRIGGER[<用户方案名>.]<触发器名>【例10.6】删除DML触发器T_OperationCourse。DROPTRIGGERT_OperationCourse;

Oracle数据库教程(第3版•微课视频版)1810.2触发器的创建、删除、启用或禁用10.2.3启用或禁用触发器语法格式：ALTERTRIGGER[<用户方案名>.]<触发器名>DISABLE|ENABLE;其中，DISABLE表示禁用触发器，ENABLE表示启用触发器。【例10.7】使用ALTERTRIGGER语句禁用触发器T_DeleteRecord。ALTERTRIGGERT_DeleteRecordDISABLE;【例10.8】使用ALTERTRIGGER语句启用触发器T_DeleteRecord。ALTERTRIGGERT_DeleteRecordENABLE;

Oracle数据库教程(第3版•微课视频版)1910.3程序包概述程序包(Package)用于将逻辑相关的PL/SQL块或元素(过程、函数、游标、变量和常量等)组织在一起，作为一个完整的单元存储在数据库中，用名称来标识程序包。程序包有两个独立的部分：包规范(Specification)和包体(Body)。包规范(包说明,规范)是包与应用程序的接口，它是过程、函数、游标等的名称或首部。包体是过程、函数、游标等的具体实现。包规范和包体这两个部分独立的存储在数据字典中。使用了程序包组织过程、函数和游标后，可以使程序设计模块化，提高程序的编写和执行效率。

Oracle数据库教程(第3版•微课视频版)2010.4程序包的创建、调用和删除1.程序包的创建（1）创建包规范语法格式：CREATE[ORREPLACE]PACKAGE[<用户方案名>]<包名>/*包规范名称*/IS∣AS<PL/SQL程序序列>/*定义过程、函数等*/说明：<PL/SQL程序序列>：过程、函数的定义和参数列表返回类型等，游标、变量和常量等的定义。（2）创建包体语法格式：CREATE[ORREPLACE]PACKAGEBODY[<用户方案名>]<包名>IS∣AS<PL/SQL程序序列>

Oracle数据库教程(第3版•微课视频版)2110.4程序包的创建、调用和删除说明：<PL/SQL程序序列>：过程、函数、游标等的具体实现。2.包的调用语法格式：包名.函数名(过程名)包名.游标名包名.变量名(常量名)3.删除包删除包体，使用以下命令：DROPPACKAGEBODY<包名>;需要同时删除包说明和包体，使用以下命令：DROPPACKAGE<包名>;

Oracle数据库教程(第3版•微课视频版)2210.4程序包的创建、调用和删除【例10.9】创建包Pkg_Score，求1201课程的平均成绩。（1）创建包规范和包体CREATEORREPLA