《oracle10g经典实战第6章存储过程和触发器》由会员分享，可在线阅读，更多相关《oracle10g经典实战第6章存储过程和触发器（27页珍藏版）》请在金锄头文库上搜索。
1、oracle10goracle10g经典实战第经典实战第6 6章章- -存储过程和触发器存储过程和触发器6.1.1存储过程的创建和执行存储过程的创建和执行用户存储过程只能定义在当前数据库中，可以使用SQL命令语句或OEM创建存储过程。缺省情况下，用户创建的存储过程归登录数据库的用户所拥有，DBA可以把许可授权给其他用户。在用户的定义中不能使用下列对象创建语句：CREATE VIEWCREATE DEFAULT CREATE RULE CREATE PROCEDURE CREATE TRIGGER6.1.1存储过程的创建和执行存储过程的创建和执行1.SQL命令创建存储过程命令创建存储过程语法格式
2、：语法格式：CREATE OR REPLACE PROCEDURE schema.procedure_name /* 定义过程名*/ (parameter parameter_mode date_type , n) /*定义参数类型及属性*/IS | ASBEGIN sql_statement /*PL/SQL过程体，要执行的操作*/END procedure_name其中：其中：procedure_name：是过程名，必须符合标识符规则。关键字REPLACE表示在创建过程时，如果已存在同名的过程，则重新创建。如果使用CREATE关键字，则需将原有的过程删除后才能创建。schema.：是指定过
3、程属于的用户方案。parameter：是过程的参数。参数名必须符合标识符规则，创建过程时，可以声明一个或多个参数，执行过程时应提供相对应的参数。Parameter_mode是参数的类型，过程参数和函数参数一样，也有3种类型，分别为IN、OUT和IN OUT。 IN：表示参数是输入给过程的； OUT：表示参数在过程中将被赋值，可以传给过程体的外部； IN OUT：表示该类型的参数既可以向过程体传值，也可以在过程体中赋值。sql_statement：代表过程体包含的PL/SQL语句。6.1.1存储过程的创建和执行存储过程的创建和执行2. 调用存储过程调用存储过程直接输入存储过程的名字就可以执行一个
4、已定义的存储过程。语法格式：语法格式：EXECUTE procedure_name(parameter,n)其中，procedure_name为要调用的存储过程的名字，parameter为参数值。【例【例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;END count_grade;6.1.1存储过程的创建和执行存储过程的创建和执行【
5、例【例6.2】从XSCJ数据库的XS表中查询某人的总学分，根据总学分写评语。CREATE OR REPLACE PROCEDURE update_info( xm in char )ASXf number;BEGIN SELECT ZXF INTO XF FROM XS WHERE XM=xm; IF XF60 THEN UPDATE XS SET BZ=三好学生 WHERE XM=xm; END IF; IF XF60 THEN UPDATE XS SET BZ=三好学生 WHERE XM=xm; END IF; IF XF35 THEN UPDATE XS SET BZ=学分未修满 WHE
6、RE XM=xm; END IF;END update_info;6.1.2 存储过程的编辑修改存储过程的编辑修改【例【例6.6】创建名为select_students的存储过程，默认情况下，该过程可查询所有学生的信息。当该过程需要改为能检索计算机专业的学生信息时，用CREATE OR REPLACE PROCEDURE重新定义。(1) 定义过程CREATE OR REPLACE PROCEDURE select_students( CUR OUT select.cur_07 )ASBEGINOPEN CUR FORSELECT xh,xm,zym,xb,cssj,zxf,bz FROM XS
7、ORDER BY XH;END;注意：注意：使用查询包头时在SELECT语句中不能使用*标识符。(2) 修改过程CREATE OR REPLACE PROCEDURE select_students( CUR OUT select.cur_07 )ASBEGINOPEN CUR FORSELECT xh,xm,zym,xb,cssj,zxf,bz FROM XSWHERE ZYM=计算机 ORDER BY XH;END;6.1.3 存储过程的删除存储过程的删除当某个过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。语法格式：语法格式：DROP PROCEDURE schema. pr
8、ocedure_name;其中，schema是包含过程的用户；procedure_name是将要删除的存储过程名称。【例【例6.7】删除XSCJ数据库中的count_num存储过程。DROP PROCEDURE count_num;也可在OEM中选择要删除的存储过程，在如图6.2所示的界面，搜索并选择要删除的存储过程，单击“删除”，出现确认删除界面，单击“是”即可删除该存储过程。6.2 触发器触发器触发器(trigger)是一些过程，与表关系密切，用于保护表中的数据，当一个基表被修改(INSERT、UPDATE或DELETE)时，触发器自动执行，例如通过触发器可实现多个表间数据的一致性和完整性
9、。触发器和应用程序无关。例如，对于XSCJ数据库有XS表、XS_KC表和KC表，当插入某一学号的学生某一课程成绩时，该学号应是XS表中已存在的，课程号应是KC表中已存在的，此时，可通过定义INSERT触发器实现上述功能。触发器的类型有三种： (1) DML触发器。Oracle可以在DML(数据操纵语句)语句进行触发，可以在DML操作前或操作后进行触发，并且可以在每个行或该语句操作上进行触发。(2) 替代触发器。由于在Oracle中不能直接对有两个以上的表建立的视图进行操作，所以给出了替代触发器。它是Oracle专门为进行视图操作的一种处理方法。(3) 系统触发器。在Oracle8i时，提供了第
10、三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的时间中进行触发，如Oracle数据库的关闭或打开等。 6.2 触发器触发器一般情况下，对表数据的操作有插入、修改、删除，因而维护数据的触发器也可分为INSERT、UPDATE和DELETE。每张基表最多可建立12个触发器，它们是：(1) BEFORE INSERT；(2) BEFORE INSERT FOR EACH ROW；(3) AFTER INSERT；(4) AFTER INSERT FOR EACH ROW；(5) BEFORE UPDATE；(6) BEFORE UPDATE FOR EACH ROW；(7) AFTER
11、 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。6.2.1 利用利用SQL语句创建触发器语句创建触发器1.语法格式语法格式CREATE OR REPLACE TRIGGER schema. trigger_name /*指定触发器名称*/ BEFOREAFTERINSTEAD OF DELETE OR INSERTE OR UPDATE OF column,n /*定义触
12、发器种类*/ON schema. table_nameview_name /*指定操作对象*/ FOR EACH ROW WHEN(condition) sql_statementn6.2.1 利用利用SQL语句创建触发器语句创建触发器2.创建触发器的限制创建触发器的限制创建触发器有以下限制：(1) 代码大小。触发器代码大小必须小于32K。(2) 触发器中有效语句可以包括DML语句，但不能包括DDL语句。ROLLBACK、COMMIT、SAVEPOINT也不能使用。但是，对于系统触发器(system trigger)可以使用CREATE、ALTER、DROP TABLE和ALTERCOMPIL
13、E语句。(3) LONG、LONG RAW和LOB的限制： 不能插入数据到LONG或LONG RAW； 来自LONG或LONG RAW的数据可以转换成字符型(如char、varchar2)，但是不能超过32K； 使用LONG或LONG RAW不能声明变量； 在LONG或LONG RAW列中不能使用:NEW和:PARENT； 在LOB中的:NEW变量不能修改。(4) 引用包变量的限制。如果UPDATE或DELETE语句检测到当前的UPDATE冲突，则Oracle执行ROLLBACK到SAVEPOINT上并重新启动更新，这样可能需要多次才能成功。3.触发器触发次序触发器触发次序Oracle对事件的
14、触发有16种，它们按照一定次序执行：(1) 执行BEFORE语句的触发器；(2) 对于受语句影响的每一行：执行BEFORE语句行级触发器执行DML语句执行AFTER行级触发器。(3) 执行AFTER语句级触发器。6.2.1 利用利用SQL语句创建触发器语句创建触发器4.创建创建DML触发器触发器触发器与过程名和包的名字不一样，它有单独的名字空间，因而触发器名可以和表名或过程名同名，但在同一个schema(方案)中的触发器名不能相同。DML触发器也叫表级触发器，因为对某个表进行DML操作时会触发该触发器运行而得名。【例【例6.8】假设XSCJ数据库中增加一新表XS_HIS，表结构和表XS相同，用
15、来存放从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.ZXF, :OLD.BZ);END del_xs;OLD修饰访问操作完成前列的值，NEW修饰访问操作完成后列的值。6.2.1 利用利用SQL语句创建触
16、发器语句创建触发器【例【例6.9】利用触发器在数据库XSCJ的XS表执行插入、更新和删除3种操作后给出相应提示。CREATE TRIGGER cue_xsAFTER INSERT OR UPDATE OR DELETE ON XS FOR EACH ROWDECLAREInfor char(10);BEGINIF INSERTING THENInfor:=插入;ELSIF UPDATING THENInfor:=更新;ELSEInfor:=删除;END IF;INSERT INTO SQL_INFO VALUES(infor);END cue_xs;6.2.1 利用利用SQL语句创建触发器语句
17、创建触发器5. 创建替代创建替代(Instead_of)触发器触发器Instead_of用于对视图的DML触发。由于视图有可能由多个表进行关联(Join)而成，因而并非所有的关联都是可更新的。但是可以按如下例子来创建触发器。【例【例6.10】在XSCJ数据库中创建视图和触发器，以说明替代触发器。 CREATE OR REPLACE VIEW cs_kc_avg AS SELECT XH,AVG(CJ) AS AVG_CJ FROM XS_KC GROUP BY XH;创建替代触发器：CREATE TRIGGER cs_kc_avg_delINSTEAD OF DELETE ON cs_kc_a
18、vg FOR EACH ROWBEGINDELETE FROM XS_KC WHERE XH=:OLD.XH;END cs_kc_avg_del;6.2.1 利用利用SQL语句创建触发器语句创建触发器6. 创建系统触发器创建系统触发器Oracle8i开始提供的系统触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语句，如CREATE、ALTER和DROP等。而数据库系统事件包括数据库服务器的启动或关闭，用户登录与退出等。语法格式：语法格式：CREATE OR REPLACE TRIGGER scache. trigger_name BEFOREAFTER ddl_event_list
19、databse_event_list ON DATABASEschema. SCHEMA when_clausetigger_body其中：其中：ddl_event_list：表示一个或多个DDL事件，事件间用OR分开。database_event_list：表示一个或多个数据库事件，事件间用OR分开。DATABASE：表示是数据库级触发器，而scache表示是用户级触发器。Schema表示用户方案。Trigger_body：触发器的PL/SQL语句。6.2.1 利用利用SQL语句创建触发器语句创建触发器【例【例6.11】创建当一个用户userA登录时自动记录一些信息的触发器。CREATE T
20、RIGGER loguserAconnectsAFTER LOGON ON SCHEMABEGININSERT INTO LOGIN VALUES(userA,loguserAconnects fired);END loguserAconnects;6.2.2 利用利用OEM创建触发器创建触发器(1) 如图6.1所示的界面中选择触发器，鼠标单击左键，打开“触发器搜索”界面，如图6.5所示。(2) 单击“创建”按钮，进入“触发器创建”界面，如图6.6所示。该界面包括三个选项页面：一般信息、事件和高级选项页面。 图图6.5触发器搜索界面触发器搜索界面 图图6.6创建触发器创建触发器 一般信息界面一
21、般信息界面6.2.2 利用利用OEM创建触发器创建触发器3) 一般信息选项页面：在“名称”文本框中设置触发器名称del_xs；在“方案”中选择创建触发器的方案；选择“若存在则替换”复选框，则在创建语句中加入替换关键字，相当于使用了REPLACE关键字；在“触发器主体”文本编辑框中输入触发器的PL/SQL语句。(4) 事件选项页面：进入事件选项界面，如图6.7所示。 图图6.7创建触发器创建触发器事件界面事件界面 6.2.2利用利用OEM创建触发器创建触发器(5) 单击高级选项页面，切换到高级选项页面，如图6.8所示。选中“行级触发”复选框，单击“确定”按钮，完成创建触发器。 6.8创建触发器创
22、建触发器高级界面高级界面 6.2.3触发器的修改触发器的修改1. 利用利用SQL语句修改触发器语句修改触发器和过程和视图一样，Oracle也提供ALTER TRIGGER语句，同样，该语句只是用于重新编译或验证现有触发器或是设置触发器是否可用。需要修改触发器，还是使用CREATE OR REPLACE语句来实现，在此不在赘述。2. 利用利用OEM修改触发器修改触发器在OEM中修改触发器的步骤和创建的步骤基本相同。在如图6.5所示界面中，选择要修改触发器，进入如图6.6所示的界面，在三个选项卡中修改触发器的定义，然后单击“应用”，即完成修改。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所示界面中，搜索选择要删除触发器，单击“删除”，出现确认删除界面，单击“是”即可删除该触发器。结束结束
