• 1. 第七章:PL/SQL高级编程 第一节 存储过程函数的概念 第二节 包 第三节 触发器
  • 2. 第一节 存储过程函数的概念 开发一个存储过程或函数目的是把一个 PL/SQL块存进数据库中,并在以后重复使用。 例如:创建一个记录用户名和当前日期的 无参数过程 CREATE PROCEDURE log_execution IS BEGIN INSERT INTO log_table(user_id, log_date) VALUES(user,sysdate); END; /
  • 3. 可以在不同的环境调用同一个存储过程。 注意:语法有区别  ● 在 SQL*Plus中记录用户名和当前日期 SQL> EXECUTE log_execution; ● 从存储过程 FIRE_EMP中记录用户名和当前日期 CREATE PROCEDUER fire_emp (v_emp_no IN emp.empno%TYPE) IS BEGIN log_execution; DELETE FROM emp WHERE empno=v_emp_no; END; /
  • 4. 一、存储过程与应用程序的区别 概念 存储过程 应用程序 存储位置 存储在数据库数据字典中 存储在应用程序中 调用地方 任何数据库工具或应用中都可以调用 只能在建立程序的应用中才能调用 相互调用 不可以调用应用程序 可以调用存储过程 建立程序的文档存储的位置 存储在数据库的数据字典中 存储在当前的应用中 安全性 由数据库提供安全保证,必须通过授权才能使用存储子程序 安全性靠应用程序保证,如果能执行应用程序,就能执行该程序。
  • 5. 二、开发存储过程和函数 1、开发一个存储过程和函数的步骤 (1)用文本编辑器编写一个含有Create Procedure或Create Function语句的PL/SQL脚本文件。(如:c:\procedure1.sql) (2)在 SQL*Plus中用命令(如:SQL>start c:\procedure1.sql;)编译脚本文件,调试编译错误。系统就将创建过程或函数的源代码存入数据字典user_source中 。 (3) 编译成功后将编译代码存入数据字典 (4) 调用存储过程和函数,在运行期间调试存储过程、函数的逻辑错误。
  • 6. 2、创建存储过程的语法 CREATE [OR REPLACE] PROCEDURE 过程名 [(参数名 [IN | OUT | IN OUT] 数据类型, …)] {IS | AS} [说明部分] BEGIN 语句序列 [EXCEPTION 出错处理] END [过程名];注意:IS 后面是一个完整的PL/SQL块的三部分(参见第六章),可以定义局部变量、游标等,但不能以 DECLARE开始。
  • 7. ★ 形式参数可以有三种模式----IN、OUT、IN OUT。如果没有为形式参数指定模式,那么缺省的模式是IN。   类型 描述 -------------------------------------------------------------------- IN(缺省)参数 用来从调用环境中向过程传递值 OUT参数 用来从过程中返回值给调用者 IN OUT参数 既可从调用者向过程中传递值, 也可以从过程中返回可能改变了 的值给调用者 局部变量 在过程内部存放值
  • 8. 例:下面的过程说明了三种模式参数的区别 create or replace procedure modetest( p_inparameter in number, p_outparameter out number, p_inoutparameter in out number) is v_localvariable number; begin v_localvariable:=p_inparameter; /*正确*/ (p_inparameter:=7; 错误) v_outparameter:=7; /*正确*/ (v_localvariable:=p_outparameter; 错误) v_localvariable:=p_inoutparameter; /*正确*/ v_inoutparameter:=7; /*正确*/ end; /
  • 9. 例:下列存储过程给某一指定的员工涨指定数量的工资。 Create procedure raise_salary (emp_id integer, v_increase real) is begin update emp set sal= sal + v_increase where empno=emp_id ; commit; end ; /
  • 10. 例:下列过程根据给定的员工号返回员工的姓名、工资和奖金等信息。 CREATE OR REPLACE PROCEDURE query_emp (v_emp_no IN emp.empno%TYPE, V_emp_name OUT emp.ename%TYPE, v_emp_sal OUT emp.sal%TYPE, V_emp_comm OUT emp.comm%TYPE) IS BEGIN SELECT ename,sal,comm INTO v_emp_name,v_emp_sal, v_emp_comm FROM emp WHERE empno=v_emp_no; END query_emp; /
  • 11. 例:利用 IN OUT模式参数,将一个7位数字的电话号码转换成8位数字的电话号码。 CREATE OR REPLACE PROCEDURE add_dash (v_phone_no IN OUT VARCHAR2) /*字符型的形式参数不指定长度*/ IS BEGIN v_phone_no:=SUBSTR (v_phone_no,l,3) ||‘-’||SUBSTR (v_phone_no,4,4); END add_dash; /
  • 12. 3、创建存储函数的语法 CREATE [OR REPLACE] FUNCTION 函数名 [(参数名 [ IN ] 数据类型 ...)] RETURN 数据类型 {IS | AS} [说明部分] BEGIN 语句序列 RETURN (表达式) [EXCEPTION 例外处理程序] END [函数名];
  • 13. 例:根据员工号获取该员工工资的查询 CREATE OR REPLACE FUNCTION get_sal (p_emp_no IN emp.empno%TYPE) RETURN NUMBER IS v_emp_sal emp.sal%TYPE:=0; BEGIN SELECT sal INTO v_emp_sal FROM emp WHERE empno=p_emp_no; RETURN(v_emp_sal); Exception When no_data_found or too_many_rows then Dbms_output.put_line('发生系统错误'); When others then Dbms_output.put_line(sqlerrm); END get_sal; /
  • 14. 例:编写一个函数,计算几个人的平均工资,并在函数体的循环过程中输出结果。 CREATE OR REPLACE FUNCTION average_sal(v_n IN NUMBER(3)) RETURN NUMBER IS CURSOR c_emp IS SELECT empno,sal FROM emp; v_total_sal emp.sal%TYPE:=0; v_counter number; v_emp_no emp.empno%type;
  • 15. BEGIN FOR r_emp IN c_emp LOOP EXIT WHEN c_emp% ROWCOUNT> v_n OR c_emp%NOTFOUND; v_total_sal:=v_total_sal + r_emp.sal; v_counter:=c_emp%ROWCOUNT; v_emp_no:=r_emp.empno; DBMS_OUTPUT.PUT_LINE(‘loop=’|| v_counter||‘;Empno=’|| v_emp_no); END LOOP; RETURN(v_total_sal/v_counter); END average_sal; /
  • 16. 4、存储过程与函数的区别 存储过程和函数的主要差别有两个: 一是返回值的方法不同 二是调用方法不同 (1)返回值的方法不同 ● 存储函数:有零个或多个参数,但不能有OUT参数。函数只返回一个值,靠RETURN子句返回。 ● 存储过程:有零个或多个参数,过程不返回值,其返回值是靠OUT参数带出来的。
  • 17. (2)调用方法不同 调用过程的语句可以作为单独的可执行语句在PL/SQL块中单独出现。 如:过程名(实际参数1,实际参数2...); 函数可以在任何表达式能够出现的地方被调用,调用函数的语句不能作为可执行语句单独出现在PL/SQL块中。 如:变量名:=函数名(实际参数1,实际参数2...)
  • 18. 5、存储过程和函数中的出错处理 存储过程和函数就是一个PL/SQL块,所以在过程函数体内应该考虑出错处理。 不管是哪种类型的出错情况,只要在过程和函数体内考虑了出错处理的方法,即使在运行过程中出现了错误,过程和函数都能成功地执行,程序不会被中断。 如果在过程和函数体内忽略了出错处理,过程和函数执行时以交互方式通知用户出错,让用户自行处理,程序被中断。
  • 19. 例:根据给定的员工号,删除该员工记录。 CREATE OR REPLACE PROCEDURE fire_emp(p_emp_no IN emp.empno%TYPE) IS invalid_employee EXCEPTION; (定义错误) BEGIN DELETE FROM emp WHERE empno=p_emp_no; IF SQL%NOTFOUND THEN RAISE invalid_employee; (触发错误) END IF; COMMIT WORK;
  • 20. EXCEPTION WHEN invalid_employee THEN (处理错误) ROLLBACK WORK; INSERT INTO exception_table(line_nr,line) VALUES(1,‘Employee does not exist.’); WHEN others THEN Dbms_output.put_line(sqlerrm); END fire_emp; / 例:在SQL*PLUS中调用该过程删除9999号员工 SQL> EXECUTE fire_emp(9999); PL/SQL procedure successfully completed 即调用过程成功。虽然在EMP表中根本没有员工号为9999的员工,但过程成功执行,没有被中断。
  • 21. 三、存储过程和函数的管理 、过程和函数的管理命令 任务 命令 创建一个新的过程或函数CREATE PROCEDURE/FUNCTION创建或修改一个已有的过程或函数CREATE OR REPLACE PROCEDURE/FUNCTION删除一个已有的过程或函数DROP PROCEDURE/FUNCTION例:删除存储过程FIRE_EMP SQL> DROP PROCEDURE FIRE_EMP;
  • 22. 2、查看过程和函数的文档信息 存储的信息描述 获得的途径 源代码过程、函数的文本 查看 USER_SOURCE数据字典编译代码 编译代码(p_code)无法获得编译错误 过程、函数的语法错误 查看 USER_ERRORS数据字典 或用 SHOW ERRORS命令 SQL> show errors;
  • 23. 四、存储过程和函数的调用和测试 1、参数传值 一般采用位置对应法向形式参数传值,要求实际参数与形式参数保持次序、类型、个数一致。 例:从 SQL*Plus命令中通过位置对应法调用 HIRE_EMP过程。 SQL> variable v_ename varchar2(12); SQL> EXECUTE hire_emp(9999,:v_ename);
  • 24. 注意: ● 如果形式参数是IN模式的参数,实际参数可以是一个具体的值,或一个有值的变量。 ● 如果形式参数是OUT模式的参数,实际参数必须是一个变量。当调用过程后,此变量就被赋值了。 ● 如果形式参数是IN OUT模式的参数,则实际参数必须是一个预先已经赋值的变量。执行完过程后,该变量被重新赋值。 可以调用系统内置的DBMS_OUTPUT包中的过程输出此变量的值,测试过程执行的结果。
  • 25. 2、存储过程和函数的调用方法 程序名 返回值用法过程 无返回值(靠OUT模式参数带出结果) 出现在一个完整的可执行语句中 函数 返回一个值可以在语句中代替变量或表达式的位置
  • 26. 调用环境语法 调用过程例子 调用函数例子 SQL*Plus EXECUTE命令 SQL>execute raise_salary(7788,1000);SQL>SELECT get_sal(7788) from dual; 另外的存储过程或函数、块 直接调用 raise_salary(7788,1000);select get_sal(7788) into 变量 from dual; 预编译程序(PRO*C) EXEC SQL命令EXEC SQL raise_salary(7788,1000); EXEC SQL select get_sal(7788) into 变量 from dual; 各种环境调用过程和函数的语法和例子
  • 27. (1) 在PL/SQL块中如何调用 ● 过程的调用 例:从 PL/SQL块中调用过程QUERY_EMP。执行过程后,输出变量测试结果。 DECLARE V_empno emp.empno%type:=7654; V_ename emp.ename%type; V_sal emp.sal%type; V_comm emp.comm%type; Begin Query_emp(v_empno,v_ename,v_sal,v_comm); Dbms_output.put_line(v_ename||' '||v_sal||' '||v_comm); /*测试结果*/ End; /
  • 28. ● 函数的调用 例:从一个 PL/SQL块中调用 GET_SAL函数。 DECLARE v_empno NUMBER:=7654; v_sal NUMBER; BEGIN v_sal:=get_sal(v_empno); DBMS_OUTPUT.PUT_LINE(v_sal); /*测试结果*/ END; /
  • 29. (2)在SQL*PLUS中如何调用 ● 过程的调用 例:从SQL*PLUS中调用过程QUERY_EMP SET serveroutput ON /*激活DBMS_OUTPUT 系统包*/ ACCEPT p_emp_no PROMPT ‘please enter the employee number:’ /*接受员工号*/ VARIABLE v_emp_name VARCHAR2(14); /*定义存放OUT参数输出结果的变量*/ VARIABLE v_emp_sal NUMBER; VARIABLE v_emp_comm NUMBER; EXECUTE query_emp(&p_emp_no, :v_emp_name, :v_emp_sal, :v_emp_comm); EXECUTE DBMS_OUTPUT.PUT_LINE(‘Information for employee:’|| TO_CHAR(&p_emp_no)); EXECUTE DBMS_OUTPUT.PUT_LINE(‘The name is:’|| :v_emp_name); EXECUTE DBMS_OUTPUT.PUT_LINE(‘the salary is:’|| TO_CHAR(:v_emp_sal)); EXECUTE DBMS_OUTPUT.PUT_LINE(‘The commission is:’|| TO_CHAR(:v_emp_comm));
  • 30. ● 函数的调用 函数不能作为一条语句单独出现,只能出现在别的过程中,作为别的过程的参数。 例:调用函数get_sal SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('员工号7654的工资是:'||get_sal(7654)); 或者用SELECT语句查询函数的结果。 SQL> SELECT GET_SAL(7654) FROM DUAL;
  • 31. (3) 在PRO*C中如何调用过程 例:从一个 pro* C程序中调用过程 FIRE_EMP。 void run_fire_emp() { EXEC SQL BEGIN DECLARE SECTION; int empno; EXEC SQL END DECLARE SECTION ; printf(‘\nPlease enter the employee number:’) ; scanf(‘%d’,&empno); EXEC SQL fire_emp(:empno); /*在过程名前面加EXEC SQL关键字*/ return; }
  • 32. 五、存储过程和函数的安全性 存储过程、函数及包是数据字典中的对象,因此它们有特殊的数据库用户所拥有。其他用户在被授予了合适的权限以后也可以访问这些对象。 通过区分过程或函数的拥有者或使用者来进行安全性管理,这与它们存取的数据的安全性管理一样。 过程或函数的拥有者比其使用者有着较高的权限。特别地,拥有者需要具有直接操纵相关表或视图的权限。而调用者只需要存取过程的权限。
  • 33. 操作操作者必需权限创建过程拥有者CREATE PROCEDURE或CREATE ANY PROCEDURE系统特权从过程体中存取数据库实体 拥有者SELECT, INSERT, UPDATE, DELETE,或 EXECUTE实体特权删除过程拥有者DROP ANY PROCEDURE系统特权执行过程拥有者或使用者 EXECUTE实体特权或EXECUTE ANY PROCEDURE系统特权
  • 34. 例:过程的安全性机制   假定表 EMP是用户 personal的私有表,用户 Scott是开发者,最终用户Green.现在要求Green只能通过 Scott创建的过程 HIRE_EMP存取emp表,该过程查询或插入雇员记录。 在 personal用户环境下,为 Scott用户授予对 EMP表的相应权限。
  • 35. SQL> GRANT SELECT,INSERT, UPDATE,DELETE ON emp TO scott; Scott一旦创建完 HIRE_EMP过程,给 Green用户授予对该过程的 EXECUTE权限 SQL> GRANT EXECUTE ON HIRE_emp TO Green;
  • 36. (本页无文本内容)
  • 37. 六、存储过程和函数的优点 (1)提高数据的安全性与完整性 利用权限来控制那些没有足够权限的用户对数据库的间接访问。 (2)改善操作性能 ● 只做一次语法分析。 ● 直接调用编译代码。 ● 减少访问数据库的次数,降低网络传输量 (3)节省存储量 ● 只在数据库中存储代码的一个备份 ● 通过共享 SQL改善管理。 ● 对过程的修改可以作用于多个应用。 ● 过程利用了共享内存资源。 (4)模块化
  • 38. 第二节:包 包(package)是一个可以将相关对象存储在一起的PL/SQL结构。包包含了两个分离的组成部分:包说明(specification)和包主体(body)。每个部分都单独被存储在数据字典中。包说明是一个操作接口,对应用来说是可见;包主体是黑盒,对应用来说隐藏了实现细节。除了可以将相关对象作为一组存储在一起以外,包也是十分有用的,它们在依赖性方面的限制比较小的,并且有许多性能上的优点。
  • 39. 一、包的组成 将相关的若干程序单元组织到一块,用一个包来标识这个集合。 包中可以包含的程序单元程序单元描述过程(procedure)带有参数的程序函数(Function)带有参数的程序,该程序只返回一个值变量(Variable)用于存储变化值的存储单元游标(Cursor)指向一个 SQL语句的指针类型(TYPE)定义一个结构类型(如RECORD、VARRAY等)常量(Constant)指向常数的指针出错情况(Exception)标志一个出错情况
  • 40. 二、开发包 1、开发包的步骤 (1) 在一个文本文件中写入 CREATE PACKAGE 语句创建一个包的说明。 (2) 在另一个文本文件中用 CREATE PACKAGE BODY语句创建包主体。 (3) 在 SQL*Plus中编译这两个文本文件,以将包的源代码编译成编译代码(P-Code),并将这两种代码存入数据库数据字典中。必须先编译创建包说明的文本文件,再编译创建包主体的文本文件。 (4) 从 ORACLE环境,或者从一个独立的函数或过程中调用包内的公共元素。
  • 41. 2、创建包的说明的语法 CREATE [OR REPLACE] PACKAGE 包名 {IS | AS} 公共变量的定义   |   公共类型的定义 | 公共出错处理的定义 | 公共游标的定义 | 函数说明 | 过程说明 END; /
  • 42. 例:制作sal_package包的说明。生成一个管理雇员薪水的包sal_package,其中包括一个为雇员加薪的过程,降薪的过程,并且在包中还有两个记录所有雇员薪水增加和减少的全局变量。 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; /
  • 43. 3 、创建包主体的语法 CREATE [OR REPLACE] PACKAGE BODY 包名 {IS | AS} 私有变量的定义  |   私有类型的定义 | 私有出错处理的定义 | 私有游标的定义 | 函数定义 | 过程定义 END; /
  • 44. 例:制作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 WORK; v_raise_sal:=v_raise_sal + v_sal_increment; END;
  • 45. 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 WORK; v_reduce_sal:=v_reduce_sal + v_sal_reduce; END; END; /
  • 46. 三、包的管理 1、包的管理命令 内 容命令创建一个新的包说明信息CREATE PACKAGE创建一个新的包主体CREATE PACKAGE BODY创建或修改一个已有的包的说明CREATE OR REPLACE PACKAGE创建或修改一个已有的包主体CREATE OR REPLACE PACKAGE BODY删除包的说明和包主体DROP PACKAGE删除包主体DROP PACKAGE BODY
  • 47. 2、存储过程函数与包的比较 任务包存储过程和函数文档记录在数据字典视图USER_SOURCE中在数据字典视图SER_SOURCE中检查编译错误在数据字典视图USER_ ERRORS中在数据字典视图USER_ ERRORS中开发方法利用 SQL*Plus批文件利用 SQL* Plus批文件开发权限需要 CREATE PROCEDURE系统权限需要 CREATE PROCEDURE系统权限使用权限需要对包有EXECUTE实体特权需要对过程有 EXECUTE实体特权
  • 48. 四、包的调用 在不同的环境中调用包中过程的语法有差别 例:从一个单独存储过程中调用包中的过程(作为单独的可执行语句) sal_package.raise_sal(7788,1000); /*给7788员工涨1000元*/ 例:从SQL*Plus工具中调用包中的过程 SQL> EXECUTE sal_package.raise_sal(7788,1000);
  • 49. 五、系统包系统包功能DBMS_OUTPUT从一个存储过程中输出信息DBMS_MAIL将 Oracle系统与 Oracle*Mail连接起来DBMS_LOCK进行复杂的锁机制管理DBMS_ALERT标识数据库中发生的某个警告事件DBMS_PIPE在不同会话间传递信息(管道通信)DBMS_JOB管理作业队列中的作业DBMS_LOB操纵大对象(CLOB、BLOB、BFILE等类型的值)DBMS_SQL动态SQL语句(通过该包可在PL/SQL中执行DDL命令)
  • 50. 六、包的优点 (1)方便对存储过程和函数的组织 ● 将相关的过程和函数组织在一起。 ● 在一个用户环境中解决命名的冲突问题 (2) 方便对存储过程和函数的管理 ● 在不改变包的说明定义时可以改变包体的定义 ● 限制过程依赖性。 (3)方便对存储过程和函数的安全性管理 ● 整个包的访问权限只需一次性授权。 ● 区分公用过程和私有过程。 (4)改善性能 ● 在包被首次调用时作为一个整体全部调入内存 ● 减少多次调用时的磁盘 I/O次数。
  • 51. 第三节:触发器 一、触发器的基本概念 触发器类似于存储过程和函数,都是有说明部分、语句执行部分和出错处理部分三部分组成的PL/SQL有名块,触发器存储在数据库数据字典中。但是,对于存储过程而言,可以在另一个程序中调用过程,显式地执行一个过程,同时在调用时可以往存储过程传递参数。对于触发器而言,当触发事件发生时隐式地(自动地)执行该触发器,不能在程序中调用触发器,并且触发器不接受参数。
  • 52. 1、触发事件和触发器的功能 (1)可以触发触发器的事件包括:在数据库表上执行的INSERT、UPDATE、DELETE操作。 (2)使用触发器可以做许多事情,包括: ●维护不可能在表创建时通过说明性约束进行的复杂的完整性约束限制。 ●通过记录所进行的修改以及谁做了修改等信息对表进行审计。 ●当表被修改的时候,自动给需要执行操作的程序发信号。 等等
  • 53. 例:制作一个数据库触发器。将那些超过其工种工资范围的员工信息记录到audit_message表中。在sal_guide表中记录了每一工种的工资范围。 CREATE OR REPLACE TRIGGER check_sal BEFORE INSERT OR UPDATE OF sal,job ON emp FOR EACH ROW WHEN(new.job<> ‘PRESIDENT’) DECLAER v_minsal sal_guide.minsal%TYPE; v_maxsal sal_guide.maxsal%TYPE; e_sal_out_of_range EXCEPTION;
  • 54. BEGIN SELECT minsal,maxsal INTO v_minsal,v_maxsal FROM sal_guide WHERE job = :new. job; IF :new.sal<v_minsal OR :new:sal>v_maxsal THEN RAISE e_sal_out_of_range; END IF; EXCEPTION WHEN e_sal_out_of_range THEN INSERT INTO audit_message(line_nr,line) VALUES(l,‘Salary’|| TO_CHAR(:new.sal)|| ‘is out of range for employee’||TO_CHAR(:new.empno)); END; /
  • 55. (本页无文本内容)
  • 56. 2、触发器的组成 组成部分描述可能值触发时间与触发事件的时间次序BEFORE AFTER触发事件触发触发器的数据操作类型INSERT UPDATE DELETE触发器类型触发器体被执行的次数Statement ROW触发器体该触发器将要执行的动作完整的 PL/SQL块
  • 57. 3、触发器的类型 触发器分语句级触发器和行级触发器两个级别。 行级触发器与语句级触发器的区别主要在于其触发的次数不同,如果该DML语句只影响一行,则语句级与行级触发器效果一样。如果该DML语句影响多行,则行级触发器触发的次数比语句级触发器触发的次数多。
  • 58. 例:下列DML语句对语句级触发器和行级触发器效果一样。 SQL> INSERT INTO DEPT(deptno,dname) VALUES(50,‘EDUCATION’); SQL> UPDATE DEPT SET LOC=‘MAUI’ WHERE DEPTNO=50; SQL> DELETE FROM DEPT WHERE DEPTNO=50; 例:下列DML语句的行级触发器与语句级触发器效果不同。 SQL> INSERT INTO EMP(empno,ename) SELECT empno,ename FROM EMP_BACKUP; SQL> UPDATE DEPT SET SAL= SAL*1.1 WHERE DEPTNO=10;
  • 59. 根据触发的时间、类型不同,可以组合为四种DML触发器。当执行一条SQL语句时,这四种触发器的触发顺序如箭头所示。 触发时间级别描述BEFORE语句级在触发SQL语句执行之前执行一次BEFORE行级在受触发SQL语句影响的每条记录被修改、删除或插入之前执行一次AFTER行级在受触发SQL语句影响的每条记录被修改、删除或插入之后执行一次AFTER语句级在触发SQL语句执行之后执行一次
  • 60. 4、触发器与存储过程的区别数据库触发器存储过程当某类数据操纵DML语句发生时隐式地调用从一个应用或过程中显式地调用在触发器体内禁止使用COMMIT, BOLLBACK语句在过程体内可以使用所有 PL/SQL块中能使用的 SQL语句,包括COMMIT、ROLLBACK不能接受参数输入可以接受参数输入
  • 61. 二、创建触发器 1、开发触发器的步骤 (1)用文本编辑器编写一个含有 create trigger语句的脚本文件(如:c:\createtrigger.sql)。 (2)在 SQL*Plus中用下列命令编译脚本文件,此时源代码存入数据字典,如果编译成功,编译代码p_code也存储到数据库数据字典中。 SQL> start c:\createtrigger.sql; (3)如果编译有错,重新编辑编译直到成功为止。 (4)在SQL*Plus中测试触发器,查看触发器是否有逻辑错误。在SQL*Plus中用DML语句(INSERT、UPDATE、DELETE语句)对表进行操作,查看触发器是否按要求执行。
  • 62. 2、语句级触发器 用 CREATE TRIGGER语句创建一个语句级触发器,该触发器在一个数据操作语句发生时只触发一次。 (1)创建一个语句级触发器的语法 CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE|AFTER} event1 [OR event2...] ON table_name PL/SQL block
  • 63. 例:创建一个 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 '8' AND '18') THEN RAISE_APPLICATION_ERROR(-20500, ‘YOU may only insert into EMP during normal hours.’); END IF; END; /
  • 64. (2)使用触发器谓词(INSERTING、UPDATING、DELETING) DML触发器是一个INSERT、UPDATE、DELETE触发器。可以创建一个包含多个触发事件的触发器,在触发器体中使用谓词( INSERTING,UPDATING及 DELETING)判断是哪个触发事件触发了触发器,从而把多种触发事件组成一个触发器。
  • 65. 谓词行为和值INSERTING如果触发事件是INSERT,则谓词的值为TRUE,否则为FALSE。UPDATING如果触发事件是UPDATE,则谓词的值为TRUE,否则为FALSE。DELETING如果触发事件是DELETE,则谓词的值为TRUE,否则为FALSE。 触发器谓词的行为和值
  • 66. 例:对前一例子进行扩展,不但限制插入数据的时间,还限制进行数据修改和删除的时间。 CREATE OR REPLACE TRIGGER secure_emp BEFORE DELETE OR INSERT OR UPDATE ON emp BEGIN IF (TO_CHAR(sysdate,‘DY’ IN (‘SAT’, ‘SUN’)) OR(TO_NUMBER(sysdate,‘HH24’) NOT BETWEEN 8 AND18) THEN IF DELETING THEN RAISE_APPLICATION_ERROR(-20502, ‘You may only delete from EMP during normal hours.’) ;
  • 67. ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(-20500, ‘You may only insert into EMP during mormal hours.’) ; ELSE THEN RAISE_APPLICATION_ERROR(-20504, ‘You may only update EMP table during normal hours.’) ; END IF; END IF; END; /
  • 68. 3、行级触发器 (1) 创建行级触发器的语法 通过在 CREATE TRIGGER语句中指定 FOR EACH ROW子句创建一个行级触发器,使其在受到触发事件影响的每一行上都被触发。 CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} event1 [OR event2...] ON table_name FOR EACH ROW [WHEN restricting_condition] PL/SQL block;
  • 69. 例:创建一个行级触发器。将每个用户对数据库EMP表进行数据操纵(插入、更新、删除)的次数记录到audit_table表中。 CREATE OR REPLACE TRIGGER audit_emp AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN IF DELETING THEN UPDATE audit_table SET del= del+l WHERE user_name=user AND table_name=’EMP’ AND colun_name IS NULL;
  • 70. ELSIF INSERTING THEN UPDATE audit_table SET ins=ins+l WHERE user_name=user AND table_name=‘EMP’ AND column_name IS NULL; EISE UPDATE audit_table SET upd=upd+1 WHERE user_name= user AND table_name=’EMP’ AND column_name IS NULL; END IF; END; /
  • 71. (2)使用行级触发器的标识符:OLD和:NEW在行级触发器中,在列名前加上 :OLD标识符表示该列变化前的值,加上 :NEW标识符表示变化后的值。 :OLD和:NEW的意义 触发事件:OLD.列名:NEW.列名INSERT所有字段都是NULL当该语句完成时将要插入的数值UPDATE在更新之前该列的原始值当该语句完成时将要更新的新值DELETE在删除行之前的该列的原始值所有字段都是NULL
  • 72. 例:在行级触发器中获取某列的新值和旧值,为 EMP表中的所有数据保留一个历史档案 CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN INSERT INTO audit_emp(user_name,timestamp, empno,old_ename,new_ename, old_job,new_job,old_sal,new_sal) VALUES(USER,SYSDATE,:old.empno, :old.ename,:new.ename, :old.job,:new.job,:old.sal,:new.sal); END; /
  • 73. 例:在行级触发器加WHEN限制条件。根据销售员工资的改变自动计算销售员的奖金。 CREATE OR REPLACE TRIGGER derive_comm BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.job=‘SALESMAN’) BEGIN :new.comm :=:old.comm * (:new.sal/:old.sal); END; /
  • 74. 三、 触发器的管理 1、触发器的管理命令任务命令创建一个新的触发器CREATE TRIGGER创建或修改一个已有的触发器CREATE OR REPLACE TRIGGER删除触发器DROP TRIGGER打开和关闭触发器的语法如下: ALTER TRIGGER 触发器名 {ENABLE | DISABLE}注意:关闭触发器与删除触发器的区别
  • 75. 注意: ● 当触发器刚创建时,它被自动设置为有效。可查看数据字典视图USER_TRRIGERS的STATUS列了解触发器的状态,看其是否有效? ● 为了改善性能,以及在大量装载数据时(例如使用 SQL* Loader)避免进行数据的完整性校验,可以使数据库触发器暂时失效。 ● 当发生网络连接故障、磁盘损坏,数据文件脱机或表空间脱机,从而导致触发器中涉及到的表不能再被访问时,应使触发器失效。
  • 76. 2、管理触发器的命令与管理存储过程的命令的比较 任务触发器存储过程文档记录查看数据字典USER_TRRIGERS视图查看数据字典USER_SOURCE视图开发方法制作 SQL*Plus脚本文件制作 SQL*Plus脚本文件调试方法●利用 DBMS_OUTPUT包中的过程,或 ●用触发事件进行测试●利用 DBMS_OUTPUT包中的过程检查编译错误●用SHOW ERROR命令查看编译错误●查看数据字典USER_ERRORS视图,或 ●用SHOW ERROR命令查看编译错误开发权限需要:●CREATE TRIGGER系统权限 ●在触发器中访问实体的权限 ●对相关联表的ALTER权限需要:●CREATE PROCEDURE系统权限 ●在过程中访问实体的权限使用权限无需特殊的权限(因为隐式执行)EXECUTE执行过程的实体权限
  • 77. 四、测试触发器 可以使用多种方法测试触发器: (1)用多种触发事件(如:INSERT、UPDATE、DELETE等)分别对触发器进行测试,以确保触发器的正确运行。 (2)用满足 WHEN条件的触发事件进行测试。 (3)在触发器内利用 DBMS_OUTPUT包输出运行结果进行测试。
  • 78. 例:用下列命令测试 DERIVE_COMM触发器。 假如查询结果有员工号7788,工资2000,奖金800,则用UPDATE语句将7788的工资乘2。 SQL> select empno,sal, comm from emp where job=’SALESMAN’; SQL> UPDATE EMP SET sal=sal*2 WHERE empno=7788; 再用SELECT语句查看奖金是否也乘2?如果发现奖金变成1600,则表示触发器触发了。 SQL> select empno,sal, comm from emp where job=’SALESMAN’;
  • 79. 例:对audit_emp_values触发器,可以用下列命令进行测试。 (1)首先用CREATE TABLE语句建立保留EMP历史档案信息的audit_emp表 SQL> CREATE TABLE audit_emp ( user_name varchar2(30), timestamp date, empno number(4), old_ename varchar2(12), new_ename varchar2(12), old_job varchar2(12), new_job varchar2(12), old_sal number, new_sal number);
  • 80. (2)对EMP表执行INSERT、UPDATE、DELETE操作 SQL> INSERT INTO emp(empno, ename, job, sal, deptno) VALUES (1111, ’张三’, ’SALESMAN’, 3000, 20); SQL> UPDATE emp SET ename=’李四’, sal=5000 WHERE empno=1111; SQL> DELETE FROM emp WHERE empno=1111; (3)查看audit_emp表是否增加了记录以及增加记录的值的变化。 SQL> SELECT * FROM audit_emp;
  • 81. 五、触发器的应用 创建数据库触发器,可以大大增强 Oracle系统的性能,完成一些 Oracle系统本身提供的服务所不能完成的功能。 触发器的应用主要是以下几个方面: ●安全性 ●审计 ●数据完整性 ●参考完整性 ●数据复制
  • 82. 1、 安全性 在 Oracle中通过给用户授权完成对表中数据进行操作的安全性控制。 例: 使用权限控制安全性。 SQL> GRANT SELECT,INSERT, UPDATE,DELETE ON EMP TO CLERK; 但如果希望限制CLERK用户只能在特定时间内访问EMP表,非规定时间内不允许访问,则可以使用数据库触发器,实现进一步的安全性控制。
  • 83. 例: 在触发器中控制数据的安全性。限制只允许在某周的特定时间内进行数据操作。 CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT OR UPDATE OR DELETE ON emp BEGIN IF(TO_CHAR(sysdate,‘DY’) IN (‘SAT’, ‘SUN’)) OR (TO_NUMBER(sysdate,‘HH24’) NOT BETWEEN 8 AND 18) THEN raise_application_error(-20506,’You may only change data during normal hours.’); END IF; END ; /
  • 84. 2、审计 Oracle系统本身提供了审计功能,可以审计用户插入、更新、删除、查询语句、DDL语句的执行次数,也可以审计操作的成功和失败,并把审计情况写入审计表中(数据字典表中)。但不能审计数据操纵时值的变化,。 例: 利用系统本身提供的审计,审计EMP表上所有成功的数据操作的次数。 SQL> AUDIT INSERT,UPDATE, DELETE ON emp BY ACCESS WHENEVER SUCCESSFUL;
  • 85. 利用触发器可以审计操纵语句所操作的值的变化,可以将值的变化情况写入用户定义的档案表。 例:用触发器进行审计。将EMP表上值的变化记录到audit_emp表中。 CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN INSERT INTO audit_emp(user_name,timestamp, empno,old_ename,new_ename, old_job,new_job,old_sal,new_sal) VALUES(USER,SYSDATE, :old.empno,:old.ename,:new.ename, :old.jOb,:new.job,:old.sal,:new.sal); END; /
  • 86. 3、数据完整性 在建表时使用Oracle系统提供的完整性约束条件,可以保证标准的数据完整性:标准的数据完整性包括五种约束条件:非空(NOT NUIL)约束、主键约束、外键约束、CHECK约束、唯一性约束 。 标准完整性只能保证静态约束。
  • 87. 例: 利用系统提供的约束条件保证数据完整性,并保证雇员的薪水不低于$500。 SQL> CREATE TABLE emp( Empno number(4) constraint pk1 primary key, /*主键*/ Ename varchar2(12) not null, Job varchar2(12), Hiredate date, Mgr number(4), Sal number constraint ck1 check (sal>=500), /*check约束*/ Comm number, Deptno number(4) constraint fk1 foreign key(deptno) /*外键*/ references dept (deptno) on delete cascade );
  • 88. 可以利用触发器加强非标准的完整性检查,提供动态约束。 例: 用触发器实现非标准的数据完整性。确保薪水不能降低,但同时增长幅度不能超过10%。 CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN(new.salold.sal*1.1) BEGIN RAISE_APPIICATION_ERROR(-20508, ‘Do not decrease salary nor increase by more than 10%’); END; /
  • 89. 4、参考完整性(关联完整性) 利用Oracle系统提供的reference参考完整性约束,限制关联表中UPDATE 和DELETE操作的一致性,实现关联表的级联删除操作。 例: 利用系统本身提供的ON DELETE CASCADE从句实现参考完整性。
  • 90. SQL> CREATE TABLE emp( Empno number(4) constraint pk1 primary key, /*主键*/ Ename varchar2(12) not null, Job varchar2(12), Hiredate date, Mgr number(4), Sal number constraint ck1 check (sal>=500), /*check约束*/ Comm number, Deptno number(4) constraint fk1 foreign key(deptno) /*外键*/ references dept (deptno) on delete cascade );
  • 91. 利用触发器可以增强参考完整性,利用触发器可以实现级联的更新操作。 例:利用触发器增强参考完整性。假设在定义DEPT和EMP表时没有做参考完整性限制,则当DEPT表的deptno发生变化时,EMP表相关行的deptno也要跟着进行适当的修改。 CREATE OR REPLACE TRIGGER CASCADE_UPDATE AFTER UPDATE OF DEPTNO ON DEPT FOR EACH ROW BEGIN UPDATE EMP SET EMP.DEPTNO=:NEW.DEPTNO WHERE EMP.DEPTNO=:OLD.DEPTNO; END; /
  • 92. 5、数据的复制 Oracle系统提供了快照功能可以实现表的复制。但快照是按用户定义的时间间隔,定期异步地对表进行备份。可以在多个主表上建立快照,快照只能读,不能进行写操作。快照的数据来源可以是远程的一张表或多张表 例: 利用快照拷贝表中数据。在旧金山创建一个建立在纽约的远地表上的快照。远程访问必须建立链路。 SQL> CREATE SNAPSHOT emp_copy AS SELECT * FROM emp@ny; /*ny为链路名*/
  • 93. 利用触发器复制表可以实现实时、同步地复制表中数据。触发器只能针对一个主表进行复制。既可以从表的复制中读取数据,也可以写入数据。触发器进行复制时的数据来源只能是一张表。
  • 94. 例:用触发器为一个表做复制 CREATE OR REPLACE TRIGGER emp_replica BEFORE INSERT OR UPDATE ON emp FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO emp@sf (empno, ename, sal, comm,deptno) VALUES(:new.empno,:new.ename, :new.sal, :new.comm, :new.deptno) ; EISE UPDATE emp@sf SET ename=:new.ename , sal=:new.sal WHERE empno= :new.empno; END IF; END; /
  • 95. 本章总结1、介绍了存储过程和函数的开发步骤、语法、管理命令和测试方法。 2、介绍了包的概念、包的组成,包的开发步骤、语法、管理命令和调用命令 。 3、介绍了数据库触发器的概念、组成、种类、开发步骤、管理命令和测试方法,以及触发器的应用。