第7章--存储过程和触发器-Oracle实用教程(第3版)课件

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,Oracle,实用教程,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第,7,章,存储过程和触发器,7.1,存储过程,7.2,触发器,7.3,事务,7.4,锁,7.1,存储过程,使用存储过程的优点如下。,（,1,）过程在服务器端运行，执行速度快。,（,2,）过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行，提高了系统性能。,（,3,）确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问这些表。,（,4,）自动完成需要预先执行的任务。过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。,7.1.1,存储过程的创建和执行,1,SQL,命令创建存储过程,创建存储过程的语句是,CREATE PROCEDURE,语句。,语法格式：,CREATE OR REPLACE PROCEDURE schema.procedure_name,/*,定义过程名,*,/,(parameter parameter_mode date_type DEFAULT expr ,n)/*,定义参数类型及属性,*,/,IS|AS,declare_section/*,变量声明部分,*,/,BEGIN,sql_statement /*PL/SQL,过程体,*,/,END procedure_name;,7.1.1,存储过程的创建和执行,【,例,7.1】,输出,hello world,。,CREATE PROCEDURE proc,AS,BEGIN,DBMS_OUTPUT.PUT_LINE(hello world);,END;,【,例,7.2】,计算指定学生的总学分。,CREATE OR REPLACE PROCEDURE totalcredit,(xh IN varchar2),AS,xf number;,BEGIN,SELECT ZXF,INTO xf,FROM XSB,WHERE XH=xh AND ROWNUM=1;,DBMS_OUTPUT.PUT_LINE(xf);,END;,7.1.1,存储过程的创建和执行,2,调用存储过程,调用存储过程一般使用,EXEC,语句，但在,PL/SQL,块中可以直接使用存储过程的名称来调用。,语法格式：,EXEC|EXECUTE procedure_name,(parameter=value|variable,n);,【,例,7.4】,调用例,7.1,中的存储过程,proc,。,SET SERVEROUT ON;,EXEC proc;,输出结果：,hello world,以下命令运行的结果与之相同：,BEGIN,proc;,END;,7.1.1,存储过程的创建和执行,【,例,7.5】,从,XSCJ,数据库的,XSB,表中查询某人的总学分，根据总学分写评语。,CREATE OR REPLACE PROCEDURE update_info,(xh in char),AS,xf number;,BEGIN,SELECT ZXF INTO xf,FROM XSB,WHERE XH=xh AND ROWNUM=1;,IF xf50 THEN,UPDATE XSB SET BZ=,三好学生,WHERE XH=xh;,END IF;,IF xf101101);,7.1.1,存储过程的创建和执行,【,例,7.6】,统计表,XSB,中男女同学的人数。,CREATE OR REPLACE PROCEDURE count_number,(sex IN char,num OUT number),AS,BEGIN,IF sex=,男,THEN,SELECT COUNT(XB)INTO num,FROM XSB,WHERE XB=,男,;,ELSE,SELECT COUNT(XB)INTO num,FROM XSB,WHERE XB=,女,;,END IF;,END;,7.1.1,存储过程的创建和执行,3,利用,OEM,创建过程,如果要通过,OEM,定义一个存储过程,count_grade,查询,XSCJ,数据库中某个专业的学生人数，步骤如下。,（,1,）在,OEM,界面的“方案”属性页中选择“过程”，单击鼠标左键，进入“过程搜索”页面,。,（,2,）单击“创建”按钮，进入“创建过程”页面。在“名称”文本框中指定过程名称,count_grade,；在“方案”中选择建立过程的用户方案,SYSTEM,。然后在“源”代码区域，编辑,PL/SQL,过程语句块，如图,7.1,所示。,（,3,）代码输入编辑完成后，单击“确定”按钮完成过程创建。,7.1.1,存储过程的创建和执行,4,利用,SQL Developer,创建过程,如果要通过,SQL Developer,定义存储过程,count_grade,，步骤如下。,（,1,）启动,SQL Developer,，选择,system_ora,连接的,Procedures,节点，右击鼠标，选择“,New Procedure,”菜单项进入“,Create PL/SQL Procedure,”对话框，如图,7.2,所示。,7.1.2,存储过程的编辑修改,修改存储过程和修改视图一样，虽然也有,ALTER PROCEDURE,语句，但是它是用于重新编译或验证现有过程的。如果要修改过程定义，仍然使用,CREATE OR REPLACE PROCEDURE,命令，语法格式一样。,其实，修改已有过程本质就是使用,CREATE OR REPLEACE PROCEDURE,重新创建一个新的过程，保持名字和原来的相同。,使用界面方式也可很方便地修改存储过程定义。在,OEM,的“搜索过程”页面中，搜索出要修改的存储过程，选中后单击“编辑”按钮，在“编辑过程”页面中修改定义存储过程的,PL/SQL,语句，单击“应用”按钮就完成修改了,。,7.2,触发器,7.2.1,利用,SQL,语句创建触发器,创建触发器都使用,CREATE TRIGGER,语句，但创建,DML,触发器和替代触发器与创建系统触发器的语法略有不同。,1,创建,DML,触发器,语法格式：,CREATE OR REPLACE TRIGGER schema.trigger_name,/*,指定触发器名称,*,/,BEFORE,AFTER,INSTEAD OF,DELETE|INSERT|UPDATE OF column,n,/*,定义触发器种类,*,/,OR DELETE|INSERT|UPDATE OF column,n,ON schema.table_name,view_name,/*,指定操作对象,*,/,FOR EACH ROW WHEN(condition),sql_statementn,/*,PL/SQL,块,*,/,7.2.1,利用,SQL,语句创建触发器,有关,DML,触发器，还有以下几点说明。,（,1,）创建触发器的限制。,（,2,）触发器触发次序。,（,3,）示例如下。,【,例,7.8】,创建一个表,table1,，其中只有一列,a,。在表上创建一个触发器，每次插入操作时，将变量,str,的值设为“,TRIGGER IS WORKING,”并显示。,创建表,table1,：,CREATE TABLE table1(a number);,创建,INSERT,触发器,table1_insert,CREATE OR REPLACE TRIGGER table1_insert,AFTER INSERT ON table1,DECLARE,str char(100):=TRIGGER IS WORKING;,BEGIN,DBMS_OUTPUT.PUT_LINE(str);,END;,7.2.1,利用,SQL,语句创建触发器,【,例,7.9】,假设,XSCJ,数据库中增加一个新表,XSB_HIS,，表结构和表,XSB,相同，用来存放从,XSB,表中删除的记录。创建一个触发器，当,XSB,表被删除一行，把删除的记录写到日志表,XSB_HIS,中。,CREATE OR REPLACE TRIGGER del_xs,BEFORE DELETE ON XSB FOR EACH ROW,BEGIN,INSERT INTO XSB_HIS(XH,XM,XB,CSSJ,ZY,ZXF,BZ),VALUES(:OLD.XH,:OLD.XM,:OLD.XB,:OLD.CSSJ,:OLD.ZY,:OLD.ZXF,:OLD.BZ);,END;,7.2.1,利用,SQL,语句创建触发器,【,例,7.10】,利用触发器在数据库,XSCJ,的,XSB,表执行插入、更新和删除三种操作后给出相应提示。,CREATE TRIGGER cue_xs,AFTER INSERT OR UPDATE OR DELETE ON XSB FOR EACH ROW,DECLARE,Infor char(10);,BEGIN,IF INSERTING THEN/*INSERT,语句激活了触发器,*,/,Infor:=,插入,;,ELSIF UPDATING THEN/*UPDATE,语句激活了触发器,*,/,Infor:=,更新,;,ELSIF DELETING THEN/*DELETE,语句激活了触发器,*,/,Infor:=,删除,;,END IF;,DBMS_OUTPUT.PUT_LINE(Infor);,END;,7.2.1,利用,SQL,语句创建触发器,2,创建替代触发器,创建替代触发器使用,INSTEAD OF,关键字，一般用于对视图的,DML,触发。由于视图有可能由多个表进行关联而成，因而并非所有的关联都是可更新的。,INSTEAD OF,触发器触发时只执行触发器内部的,SQL,语句，而不执行激活该触发器的,SQL,语句。,例如，若在一个多表视图上定义了,INSTEAD OF INSERT,触发器，视图各列的值可能允许为空也可能不允许为空。若视图某列的值不允许为空，则,INSERT,语句必须为该列提供相应的值。,【,例,7.11】,在,XSCJ,数据库中创建视图,stu_view,，包含学生学号、专业、课程号、成绩。该视图依赖于表,XSB,和,CJB,，是不可更新视图。可以在视图上创建,INSTEAD OF,触发器，当向视图中插入数据时分别向表,XSB,和,CJB,插入数据，从而实现向视图插入数据的功能。,首先创建视图：,CREATE VIEW stu_view,AS,SELECT XSB.XH,ZY,KCH,CJ,FROM XSB,CJB,WHERE XSB.XH=CJB.XH,7.2.1,利用,SQL,语句创建触发器,创建,INSTEAD OF,触发器：,CREATE TRIGGER InsteadTrig,INSTEAD OF INSERT ON stu_view FOR EACH ROW,DECLARE,xm char(8);,xb char(2);,cssj date;,BEGIN,xm:=,佚名,;,xb:=,男,;,cssj:=01-1,月,-90;,INSERT INTO XSB(XH,XM,XB,CSSJ,ZY),VALUES(:NEW.XH,xm,xb,cssj,:NEW.ZY);,INSERT INTO CJB VALUES(:NEW.XH,:NEW.KCH,:NEW.CJ);,END;,7.2.1,利用,SQL,语句创建触发器,向视图插入一行数据：,INSERT INTO stu_view VALUES(091102,计算机,101,85);,查看数据是否插入：,SELECT*FROM stu_view WHERE XH=091102;,执行结果如