《PL-SQL高级编程.ppt》由会员分享，可在线阅读，更多相关《PL-SQL高级编程.ppt（33页珍藏版）》请在三一办公上搜索。
1、1,第七章：PL/SQL高级编程 第一节 存储过程函数的概念 第二节 包 第三节 触发器,2,第二节：包 包(package)是一个可以将相关对象存储在一起的PL/SQL结构。它包含了两个分离的组成部分：包说明(specification)和包主体(body)。每个部分都单独被存储在数据字典中。一、包的组成 将相关的若干程序单元组织到一块，用一个包来标识这个集合。,3,包中可以包含的程序单元,4,二、开发包1、开发包的步骤（1）在一个文本文件中写入 CREATE PACKAGE 语句创建一个包的说明。（2）在另一个文本文件中用 CREATE PACKAGE BODY语句创建包主体。（3）在 S
2、QL*Plus中编译这两个文本文件，以将包的源代码编译成编译代码(P-Code)，并将这两种代码存入数据库数据字典中。必须先编译创建包说明的文本文件，再编译创建包主体的文本文件。（4）从 ORACLE环境，或者从一个独立的函数或过程中调用包内的公共元素。,5,2、创建包的说明的语法CREATE OR REPLACE PACKAGE 包名IS|AS 公共变量的定义|公共类型的定义|公共出错处理的定义|公共游标的定义|函数说明|过程说明END；/,6,例1：制作sal_package包的说明。生成一个管理雇员薪水的包sal_package，其中包括一个为雇员加薪的过程，降薪的过程，并且在包中还有两
3、个记录所有雇员薪水增加和减少的全局变量。CREATE PACKAGE sal_package IS PROCEDURE raise_sal(v_empno emp.empno%TYPE,v_sal_increment emp.sal%TYPE);PROCEDURE reduce_sal(v_empno emp.empno%TYPE,v_sal_reduce emp.sal%TYPE);v_raise_sal emp.sal%TYPE:=0;v_reduce_sal emp.sal%TYPE:=0;END;/,7,3、创建包主体的语法CREATE OR REPLACE PACKAGE BODY
4、包名IS|AS 私有变量的定义|私有类型的定义|私有出错处理的定义|私有游标的定义|函数定义|过程定义 END；/,8,例2：制作sal_package包的包体。CREATE OR REPLACE PACKAGE BODY sal_package IS PROCEDURE raise_sal(v_empno emp.empno%TYPE,v_sal_increment emp.sal%TYPE)IS BEGIN UPDATE emp SET sal=sal+v_sal_increment WHERE empno=v_empno;COMMIT;v_raise_sal:=v_raise_sal+v
5、_sal_increment;END;,9,PROCEDURE reduce_sal(v_empno emp.empno%TYPE,v_sal_reduce emp.sal%TYPE)IS BEGIN UPDATE emp SET sal=sal-v_sal_reduce WHERE empno=v_empno;COMMIT;v_reduce_sal:=v_reduce_sal+v_sal_reduce;END;END;/,10,三、包的调用 在包的说明部分中声明的所有对象在包外可以被调用，只要指定包的名字即可访问该对象。在不同的环境中调用包中过程的语法有差别,例：从一个单独存储过程中调用包中
6、的过程（作为单独的可执行语句）sal_package.raise_sal(1002,1000);/*给1002员工涨1000元*/begin sal_package.raise_sal(1002,1000);end;/例：从SQL*Plus工具中调用包中的过程 SQL EXECUTE sal_package.raise_sal(1002,1000);,11,四、包的管理1、包的管理命令,当包的说明被删除时，要求把包主体也删除；当删除包主体时，可以不删除包的说明。,12,五、系统包 Oracle事先定义的包称为系统包，这些包可以供用户使用。,13,第三节：触发器一、触发器的基本概念 触发器类似于
7、存储过程和函数，都是由说明部分、语句执行部分和出错处理部分三部分组成的PL/SQL有名块，触发器存储在数据库数据字典中。但是，对于存储过程而言，可以在另一个程序中调用过程，显式地执行一个过程，同时在调用时可以往存储过程传递参数。对于触发器而言，当触发事件发生时隐式地（自动地）执行该触发器，不能在程序中调用触发器，并且触发器不接受参数。,14,1、触发事件和触发器的功能（1）可以触发触发器的事件包括：在数据库表上执行的INSERT、UPDATE、DELETE操作。（2）使用触发器可以做许多事情，包括：维护不可能在表创建时通过说明性约束进行的复杂的完整性约束限制。通过记录所进行的修改以及谁做了修改
8、等信息对表进行审计。当表被修改的时候，自动给需要执行操作的程序发信号。,15,2、触发器的组成,16,3、触发器的类型 触发器分语句级触发器和行级触发器两个级别。行级触发器与语句级触发器的区别主要在于其触发的次数不同，如果该DML语句只影响一行，则语句级与行级触发器效果一样。如果该DML语句影响多行，则行级触发器触发的次数比语句级触发器触发的次数多。,17,根据触发的时间、类型不同，可以组合为四种DML触发器。当执行一条SQL语句时，这四种触发器的触发顺序如箭头所示。,18,4、触发器与存储过程的区别,19,二、创建触发器 1、开发触发器的步骤（1）用文本编辑器编写一个含有 create tr
9、igger语句的脚本文件（如：c:createtrigger.sql）。（2）在 SQL*Plus中用下列命令编译脚本文件，此时源代码存入数据字典，如果编译成功，编译代码p_code也存储到数据库数据字典中。SQL start c:createtrigger.sql;（3）如果编译有错，重新编辑编译直到成功为止。（4）在SQL*Plus中测试触发器，查看触发器是否有逻辑错误。在SQL*Plus中用DML语句（INSERT、UPDATE、DELETE语句）对表进行操作，查看触发器是否按要求执行。,20,2、语句级触发器用 CREATE TRIGGER语句创建一个语句级触发器，该触发器在一个数据操
10、作语句发生时只触发一次。（1）创建一个语句级触发器的语法 CREATE OR REPLACE TRIGGER trigger_nameBEFORE|AFTER event1 OR event2.ON table_namePL/SQL block,21,例1：创建一个 BEFORE型语句级触发器。限制一周内往 EMP表插入数据的时间。CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON emp BEGIN IF(TO_CHAR(sysdate,DY)IN(SAT,SUN)OR(TO_CHAR(sysdate,HH24)NOT BETWEEN
11、12 AND 13)then RAISE_APPLICATION_ERROR(-20509,您只能在规定的时间里插入员工信息。);END IF;END;/,insert into emp values(9999,小明,27,文员,1002,2000,20),22,（2）使用触发器谓词（INSERTING、UPDATING、DELETING）DML触发器是一个INSERT、UPDATE、DELETE触发器。可以创建一个包含多个触发事件的触发器，在触发器体中使用谓词（INSERTING，UPDATING及 DELETING）判断是哪个触发事件触发了触发器，从而把多种触发事件组成一个触发器。,23,
12、触发器谓词的行为和值,24,例2：统计一下对学生信息表的数据操纵情况。create table aa(curr_user varchar2(20),curr_date date,act varchar2(20);create or replace trigger DML_aa after insert or delete or update on student begin if inserting then insert into aa values(user,sysdate,数据插入);elsif deleting then insert into aa values(user,sysda
13、te,数据删除);else insert into aa values(user,sysdate,数据更新);end if;end;/,update student set age=33 where sno=05880101;,25,3、行级触发器（1）创建行级触发器的语法 通过在 CREATE TRIGGER语句中指定 FOR EACH ROW子句创建一个行级触发器，使其在受到触发事件影响的每一行上都被触发。CREATE OR REPLACE TRIGGER trigger_nameBEFORE|AFTER event1 OR event2.ON table_nameFOR EACH ROW
14、 WHEN restricting_conditionPL/SQL block;,26,（2）使用行级触发器的标识符:OLD和:NEW,在行级触发器中，在列名前加上:OLD标识符表示该列变化前的值，加上:NEW标识符表示变化后的值。,:OLD和:NEW的意义,27,例3：因学生表中学号的变化，在行级触发器中自动修改选课信息表中该学生的学号。create or replace trigger update_sc before update on student for each row begin update sc set sno=:new.sno where sno=:old.sno;end
15、;/,update student set sno=05880177 where sno=05880107;,28,例4:创建一个行级触发器，当更新某个职员工资之后，输出此职员的姓名和修改前与改后的工资。Create or replace trigger emp_update after update on emp for each row Begin Dbms_output.put_line(the old sal is|:old.ename|:old.sal);Dbms_output.put_line(the new sal is|:old.ename|:new.sal);End;/,up
16、date emp set sal=800 where empno=5001;,29,三、触发器的管理1、触发器的管理命令,30,注意：关闭触发器与删除触发器的区别 关闭触发器和删除触发器是有区别的，删除触发器是从数据字典中永久删除，而关闭触发器只是让触发器失效，临时不能被触发，没有从数据字典中删除，在需要时可以再让其生效（即打开）。,31,打开和关闭触发器的语法如下：ALTER TRIGGER 触发器名 ENABLE|DISABLE其中：DISABLE表示关闭触发器，ENABLE表示打开触发器。,32,四、测试触发器 可以使用多种方法测试触发器：（1）用多种触发事件(如：INSERT、UPDATE、DELETE等)分别对触发器进行测试，以确保触发器的正确运行。（2）用满足 WHEN条件的触发事件进行测试。（3）在触发器内利用 DBMS_OUTPUT包输出运行结果进行测试。,33,五、触发器的应用,创建数据库触发器，可以大大增强 Oracle系统的性能，完成一些 Oracle系统本身提供的服务所不能完成的功能。触发器的应用主要是以下几个方面：安全性 审计 数据完整性 参考完整性 数据复制,
