1、2018年10月12日,第1页,第6章 存储过程和触发器,教学目标： 1.掌握存储过程的创建与使用 2.掌握触发器的创建与使用,2018年10月12日,第2页,6.1 存储过程在Oracle中，可以在数据库中定义子程序，这种程序块称为存储过程 (procedure)。可以在不同用户和应用程序之间共享，并可实现程序的优化和重用。使用存储过程的优点是： (1) 过程在服务器端运行，执行速度快。 (2) 过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高 速缓冲存储器中调用已编译代码执行，提高了系统性能。 (3) 确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授 权
2、用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问 这些表。 (4) 自动完成需要预先执行的任务。过程可以在系统启动时自动执行，而不必在 系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预 先执行的任务。,2018年10月12日,第3页,用户存储过程只能定义在当前数据库中，可以使用SQL命令 语句或OEM创建存 6.1.1 存储过程的创建和执行 1. SQL命令创建存储过程 语法格式：CREATE OR REPLACE PROCEDURE schema.procedure_name /*定义过程名*/ (parameter parameter_mode d
3、ate_type , n) /*定义参数类型及属性*/IS | ASBEGINsql_statement /*PL/SQL过程体，要执行的操作*/END procedure_name,2018年10月12日,第4页,Parameter_mode是参数的类型，过程参数和函数参数一样，也有3种类型，分别为IN、OUT和IN OUT。 IN：表示参数是输入给过程的； OUT：表示参数在过程中将被赋值，可以传给过程体的外部； IN OUT：表示该类型的参数既可以向过程体传值，也可以在过程体中赋值。,2018年10月12日,第5页,2. 调用存储过程 直接输入存储过程的名字就可以执行一个已定义 的存储过
4、程。 语法格式：EXECUTE procedure_name(parameter,n) 其中，procedure_name为要调用的存储过程的 名字，parameter为参数值,2018年10月12日,第6页,【例6.1】计算指定系总学分大于40的人数。CREATE OR REPLACE PROCEDURE count_grade( zym in char,person_num out number )ASBEGINSELECT COUNT(ZXF)INTO person_numFROM XSWHERE ZYM=zym AND ZXF40;END count_grade;,2018年10月12
5、日,第7页,在调用过程count_grade时，需要先定义OUT类型参数，调用如下：DECLARE man_num NUMBER;BEGINcount_grade(计算机,man_num); dbms_output.put_line(总人数为： | TO_CHAR(man_num);END;,2018年10月12日,第8页,【例6.4】统计表XS中男女同学的人数，存储过程使用了一个输入参数和一个输出参数。,2018年10月12日,第9页,3. 利用OEM创建过程 (1) 在OEM界面中，如图6.1所示，选择“过程”，单击鼠标左键，进入“过程搜索”界面,2018年10月12日,第10页,6.1.
6、2存储过程的编辑修改同创建,2018年10月12日,第11页,6.1.3 存储过程的删除,当某个过程不再需要时，应将其从内存中删除，以释放它占用的内 存资源。 语法格式：DROP PROCEDURE schema. procedure_name; 其中，schema是包含过程的用户；procedure_name是将要删除的存储过程名称。 【例6.7】删除XSCJ数据库中的count_num1存储过程。DROP PROCEDURE count_num1; 也可在OEM中选择要删除的存储过程，在如图6.2所示的界面，搜索并选择要删 除的存储过程，单击“删除”，出现确认删除界面，单击“是”即可删除该
7、存储过程。,2018年10月12日,第12页,6.2 触发器,触发器(trigger)是一些过程，与表关系密切，用于保护表中的数据 触发器的类型有三种： (1) DML触发器。由INSERT、UPDATE和DELETE语句所触发的触发器。DML触发器可以为这些事件创建BEFORE触发器（事前触发器）和AFTER触发器（事后触发器）。DML触发器还可以根据是对于每一个SQL语句触发一次还是对每行数据的处理都分别触发一次而创建对应的语句级触发器和行级触发器。,2018年10月12日,第13页,(2)替代触发器。由于在Oracle中不能直接对有两个以上的表建立的视图进行操作，所以给出了替代触发器。它
8、是Oracle专门为进行视图操作的一种处理方法。 (3)系统触发器。在Oracle8i时，提供了第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的时间中进行触发，如Oracle数据库的关闭或打开等。,2018年10月12日,第14页,一般情况下，对表数据的操作有插入、修改、删除，因而维护数据的触发器也可 分为INSERT、UPDATE和DELETE。每张基表最多可建立12个触发器，它们是： (1) BEFORE INSERT； (2) BEFORE INSERT FOR EACH ROW； (3) AFTER INSERT； (4) AFTER INSERT FOR EACH R
9、OW； (5) BEFORE UPDATE； (6) BEFORE UPDATE FOR EACH ROW； (7) AFTER UPDATE； (8) AFTER UPDATE FOR EACH ROW； (9) BEFORE DELETE； (10) BEFORE DELETE FOR EACH ROW； (11) AFTER DELETE； (12) AFTER DELETE FOR EACH ROW。,2018年10月12日,第15页,6.2.1 利用SQL语句创建触发器,1. 语法格式CREATE OR REPLACE TRIGGER schema. trigger_name BEF
10、OREAFTERINSTEAD OF DELETE OR INSERTE OR UPDATE OF column,n /*定义触发器种类*/ON schema. table_nameview_name /*指定操作对象*/ FOR EACH ROW WHEN(condition) sql_statementn,2018年10月12日,第16页,使用触发器时要注意： （1）触发器与过程、函数有所不同，不能接受参数，不能显式调用。 （2）如果通过约束能完成相应的功能，则尽量使用约束。 （3）触发器大小不能超过32KB，否则可设计合适的存储过程替代触发器或在触发器代码中调用存储过程。 （4）不能对S
11、YS拥有的表创建触发器。,2018年10月12日,第17页,创建DML触发器【例6.8】假设XSCJ数据库中增加一新表XS_HIS，表结构和表XS相同，用来存放从XS表中删除的记录。创建一个触发器，当XS表被删除一行，把删除的记录写到日志表XS_HIS中。CREATE OR REPLACE TRIGGER del_xsBEFORE DELETE ON XS FOR EACH ROWBEGININSERT INTO XS_HIS (XH,XM,ZYM,XB,CSSJ,ZXF,BZ)VALUES(:OLD.XH,:OLD.XM, :OLD.ZYM, :OLD.XB, :OLD.CSSJ,:OLD.
12、ZXF, :OLD.BZ);END del_xs; OLD修饰访问操作完成前列的值，NEW修饰访问操作完成后列的值。,2018年10月12日,第18页,5.创建替代(Instead_of)触发器Instead_of用于对视图的DML触发。由于视图有可能由多个表进行关联(Join)而 成，因而并非所有的关联都是可更新的。但是可以按如下例子来创建触发器。 【例6.10】在XSCJ数据库中创建视图和触发器，以说明替代触发器。CREATE OR REPLACE VIEW cs_kc_avgASSELECT XH,AVG(CJ) AS AVG_CJFROM XS_KCGROUP BY XH; 创建替代触
13、发器：CREATE TRIGGER cs_kc_avg_delINSTEAD OF DELETE ON cs_kc_avg FOR EACH ROWBEGINDELETE FROM XS_KC WHERE XH=:OLD.XH;END cs_kc_avg_del;,2018年10月12日,第19页,例 CREATE OR REPLACE VIEW Xs_kc_KCASSELECT XH,KC.KCH,KCM FROM XS_KC,KCWHERE XS_KC.KCH=KC.KCH; INSERT INTO Xs_kc_KC VALUES(061101,303,C语言),2018年10月12日,第
14、20页,CREATE TRIGGER XS_kc_KC_INS INSTEAD OF INSERT ON XS_kc_KC FOR EACH ROW BEGIN INSERT INTO KC VALUES(:new.xh ,:new.kch ,1，80，5); INSERT INTO Xs_kc VALUES(061101,303,75); END XS_kc_KC_INS;,2018年10月12日,第21页,6.2.2利用OEM创建触发器 6.2.3 触发器的修改 1. 利用SQL语句修改触发器 和过程和视图一样，Oracle也提供ALTER TRIGGER语 句，同样，该语句只是用于重新编
15、译或验证现有触发器或 是设置触发器是否可用。需要修改触发器，还是使用 CREATE OR REPLACE语句来实现，在此不在赘述。 2. 利用OEM修改触发器 在OEM中修改触发器的步骤和创建的步骤基本相同。,2018年10月12日,第22页,6.2.4 触发器的删除,1. 利用SQL命令删除触发器 语法格式：DROP TRIGGER schema. trigger_name 其中：schema指定触发器的用户方案。Trigger_name指定要删除的触发器的名 称。 【例6.13】删除触发器del_xs。DROP TRIGGER ADMIN.del_xs; 2. 通过OEM删除触发器 在如图6.5所示界面中，搜索选择要删除触发器，单击“删除”，出现确认删除界 面，单击“是”即可删除该触发器。,2018年10月12日,第23页,作业,P294 实验6 存储过程和触发器的使用,
