ORACLE

1. ORACLE

2. 单元十三Oracle数据库访问技术

3. 存储过程和触发器 在Oracle中，可以在数据库中定义子程序，这种程序块称为存储过程 (procedure)。它存放在数据字典中，可以在不同用户和应用程序之间共享，并可实现程序的优化和重用。使用存储过程的优点是： (1) 过程在服务器端运行，执行速度快。 (2) 过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行，提高了系统性能。 (3) 确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问这些表。 (4) 自动完成需要预先执行的任务。过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。

4. 存储过程和触发器 案例：计算指定系总学分大于40的人数。 CREATE OR REPLACE PROCEDURE count_grade ( vzym in char) AS person_num number; BEGIN SELECT COUNT(*) INTO person_num FROM XS WHERE ZYM=vzym and zxf>40; DBMS_OUTPUT.PUT_LINE(person_num); END count_grade; EXEC count_grade('计算机'); SELECT COUNT(*) FROM XS WHERE ZYM='计算机' and zxf>40;

5. 存储过程和触发器 案例：从XSCJ数据库的XS表中查询某人的总学分，根据总学分写评语。 CREATE OR REPLACE PROCEDURE update_info (vxm in VARCHAR2) AS Xf number; BEGIN SELECT ZXF INTO Xf FROM XS WHERE XM=vxm; IF Xf>40 THEN UPDATE XS SET BZ='三好学生' WHERE XM=vxm; END IF; IF Xf<35 THEN UPDATE XS SET BZ='学分未修满' WHERE XM=vxm; END IF; END update_info; 调用: EXEC update_info('马林林'); select * from xs where xm='马林林';

6. 存储过程和触发器 案例：计算指定学生的总学分，存储过程使用了一个输入参数和一个输出参数。 CREATE OR REPLACE PROCEDURE totalcredit ( name IN varchar2, total OUT number ) AS BEGIN SELECT SUM(XF) INTO total FROM XS, XS_KC WHERE XM=name AND XS.XH=XS_KC.XH; END; select * from xs where xm='程明'; select * from xs_kc where xh=061102 ; update xs_kc set xf=3 where xh=061102 and kch=102; update xs_kc set xf=4 where xh=061102 and kch=206;

7. 存储过程和触发器 调用： DECLARE total NUMBER; BEGIN totalcredit('程明',total ); dbms_output.put_line(total); END;

8. 存储过程和触发器 案例：统计表XS中男女同学的人数，存储过程使用了一个输入参数和一个输出参数。 CREATE OR REPLACE PROCEDURE count_sex ( sex IN char, num OUT number ) AS BEGIN IF sex='男' THEN SELECT COUNT(XB) INTO num FROM XS WHERE XB='男'; ELSE SELECT COUNT(XB) INTO num FROM XS WHERE XB='女'; END IF; END count_sex;

9. 存储过程和触发器 在调用过程count_num时，需要先定义OUT类型参数，调用如下： DECLARE man_num NUMBER; BEGIN count_sex('男',man_num); dbms_output.put_line(man_num); END;

10. 存储过程和触发器 触发器(trigger)是一些过程，与表关系密切，用于保护表中的数据，当一个基表被修改(INSERT、UPDATE或DELETE)时，触发器自动执行，例如通过触发器可实现多个表间数据的一致性和完整性。触发器和应用程序无关。例如，对于XSCJ数据库有XS表、XS_KC表和KC表，当插入某一学号的学生某一课程成绩时，该学号应是XS表中已存在的，课程号应是KC表中已存在的，此时，可通过定义INSERT触发器实现上述功能。

11. 存储过程和触发器 触发器的类型有三种： (1) DML触发器。Oracle可以在DML(数据操纵语句)语句进行触发，可以在DML操作前或操作后进行触发，并且可以在每个行或该语句操作上进行触发。 (2) 替代触发器。由于在Oracle中不能直接对有两个以上的表建立的视图进行操作，所以给出了替代触发器。它是Oracle专门为进行视图操作的一种处理方法。 (3) 系统触发器。在Oracle8i时，提供了第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的时间中进行触发，如Oracle数据库的关闭或打开等。

12. 存储过程和触发器 每张基表最多可建立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 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。

13. 存储过程和触发器 案例：假设XSCJ数据库中增加一新表XS_HIS，表结构和表XS相同，用来存放从XS表中删除的记录。创建一个触发器，当XS表被删除一行，把删除的记录写到日志表XS_HIS中。 CREATE TABLE XS_his ( XH CHAR(6) NOT NULL, XM VARCHAR2(8) NOT NULL, ZYM CHAR(10) NULL, XB CHAR(2) NOT NULL, CSSJ DATE NOT NULL, ZXF NUMBER(2) NULL, BZ VARCHAR(200) NULL );

14. 存储过程和触发器 select * from XS_his; CREATE OR REPLACE TRIGGER del_xs BEFORE DELETE ON XS FOR EACH ROW BEGIN INSERT 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; select * from XS; DELETE FROM XS WHERE xh=061202; select * from XS_his;

15. 存储过程和触发器 案例：在XSCJ数据库中创建视图和触发器，以说明替代触发器。 CREATE OR REPLACE VIEW xs_kc_avg AS SELECT XH,AVG(CJ) AS AVG_CJ FROM XS_KC GROUP BY XH; select * from xs_kc_avg; 创建替代触发器： CREATE TRIGGER xs_kc_avg_del INSTEAD OF DELETE ON xs_kc_avg FOR EACH ROW BEGIN DELETE FROM XS_KC WHERE XH=:OLD.XH; END xs_kc_avg_del; select * from xs_kc; delete from cs_kc_avg where xh=061111;