SQL高级编程7.ppt

1、第七章：PL/SQL高级编程 第一节 存储过程函数的概念 第二节 包 第三节 触发器,第一节 存储过程函数的概念 开发一个存储过程或函数目的是把一个 PLSQL块存进数据库中，并在以后重复使用。 例如：创建一个记录用户名和当前日期的 无参数过程 CREATE PROCEDURE log_execution IS BEGIN INSERT INTO log_table(user_id, log_date) VALUES(user,sysdate)； END； /,可以在不同的环境调用同一个存储过程。 注意：语法有区别 在 SQL*Plus中记录用户名和当前日期 SQL EXECUTE log_e

2、xecution； 从存储过程 FIRE_EMP中记录用户名和当前日期 CREATE PROCEDUER fire_emp (v_emp_no IN emp.empnoTYPE) IS BEGIN log_execution； DELETE FROM emp WHERE empno=v_emp_no； END； /,一、存储过程与应用程序的区别,二、开发存储过程和函数 1、开发一个存储过程和函数的步骤 （1）用文本编辑器编写一个含有Create Procedure或Create Function语句的PL/SQL脚本文件。（如：c:procedure1.sql） （2）在 SQL*Plus中用

3、命令（如：SQLstart c:procedure1.sql;）编译脚本文件，调试编译错误。系统就将创建过程或函数的源代码存入数据字典user_source中 。 （3） 编译成功后将编译代码存入数据字典 （4） 调用存储过程和函数，在运行期间调试存储过程、函数的逻辑错误。,2、创建存储过程的语法 CREATE OR REPLACE PROCEDURE 过程名 (参数名 IN | OUT | IN OUT 数据类型, ) IS | AS 说明部分 BEGIN 语句序列 EXCEPTION 出错处理 END 过程名；,注意：IS 后面是一个完整的PL/SQL块的三部分（参见第六章），可以定义局部

4、变量、游标等，但不能以 DECLARE开始。, 形式参数可以有三种模式-IN、OUT、IN OUT。如果没有为形式参数指定模式，那么缺省的模式是IN。 类型 描述 - IN(缺省)参数 用来从调用环境中向过程传递值 OUT参数 用来从过程中返回值给调用者 IN OUT参数 既可从调用者向过程中传递值， 也可以从过程中返回可能改变了 的值给调用者 局部变量 在过程内部存放值,例：下面的过程说明了三种模式参数的区别 create or replace procedure modetest( p_inparameter in number, p_outparameter out number, p_

5、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; /,例：下列存储过程给某一指定的员工涨指定数量的工资。 Create p

6、rocedure raise_salary (emp_id integer, v_increase real) is begin update emp set sal= sal + v_increase where empno=emp_id ; commit; end ; /,例：下列过程根据给定的员工号返回员工的姓名、工资和奖金等信息。 CREATE OR REPLACE PROCEDURE query_emp (v_emp_no IN emp.empnoTYPE， V_emp_name OUT emp.enameTYPE， v_emp_sal OUT emp.salTYPE， V_emp_

7、comm OUT mTYPE) 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； /,例：利用 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,

8、3) |-|SUBSTR (v_phone_no，4，4)； END add_dash； /,3、创建存储函数的语法 CREATE OR REPLACE FUNCTION 函数名 （参数名 IN 数据类型 .） RETURN 数据类型 IS | AS 说明部分 BEGIN 语句序列 RETURN （表达式） EXCEPTION 例外处理程序 END 函数名；,例：根据员工号获取该员工工资的查询 CREATE OR REPLACE FUNCTION get_sal (p_emp_no IN emp.empnoTYPE) RETURN NUMBER IS v_emp_sal emp.salTYPE

9、: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； /,例：编写一个函数，计算几个人的平均工资，并在函数体的循环过程中输出结果。 CREATE OR REPLACE FUNCTION averag

10、e_sal(v_n IN NUMBER(3) RETURN NUMBER IS CURSOR c_emp IS SELECT empno，sal FROM emp； v_total_sal emp.salTYPE:=0； v_counter number; v_emp_no emp.empno%type;,BEGIN FOR r_emp IN c_emp LOOP EXIT WHEN c_emp ROWCOUNT v_n OR c_empNOTFOUND； v_total_sal:=v_total_sal + r_emp.sal； v_counter:=c_empROWCOUNT； v_emp

11、_no:=r_emp.empno； DBMS_OUTPUT.PUT_LINE(loop=| v_counter|；Empno=| v_emp_no)； END LOOP； RETURN(v_total_salv_counter)； END average_sal； /,4、存储过程与函数的区别 存储过程和函数的主要差别有两个： 一是返回值的方法不同 二是调用方法不同 （1）返回值的方法不同 存储函数：有零个或多个参数，但不能有OUT参数。函数只返回一个值，靠RETURN子句返回。 存储过程：有零个或多个参数，过程不返回值，其返回值是靠OUT参数带出来的。,（2）调用方法不同 调用过程的语句可以

12、作为单独的可执行语句在PL/SQL块中单独出现。 如：过程名（实际参数1，实际参数2.）； 函数可以在任何表达式能够出现的地方被调用，调用函数的语句不能作为可执行语句单独出现在PL/SQL块中。 如：变量名：=函数名（实际参数1，实际参数2.）,5、存储过程和函数中的出错处理 存储过程和函数就是一个PL/SQL块，所以在过程函数体内应该考虑出错处理。 不管是哪种类型的出错情况，只要在过程和函数体内考虑了出错处理的方法，即使在运行过程中出现了错误，过程和函数都能成功地执行，程序不会被中断。 如果在过程和函数体内忽略了出错处理，过程和函数执行时以交互方式通知用户出错，让用户自行处理，程序被中断。,

13、例：根据给定的员工号，删除该员工记录。 CREATE OR REPLACE PROCEDURE fire_emp(p_emp_no IN emp.empnoTYPE) IS invalid_employee EXCEPTION； （定义错误） BEGIN DELETE FROM emp WHERE empno=p_emp_no; IF SQLNOTFOUND THEN RAISE invalid_employee； （触发错误） END IF； COMMIT WORK；,EXCEPTION WHEN invalid_employee THEN （处理错误） ROLLBACK WORK； INS

14、ERT 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)； PLSQL procedure successfully completed 即调用过程成功。虽然在EMP表中根本没有员工号为9999的员工，但过程成功执行，没有被中断。,三、存储过程和函数的管理 、过程和函数的

15、管理命令,例：删除存储过程FIRE_EMP SQL DROP PROCEDURE FIRE_EMP;,2、查看过程和函数的文档信息,SQL show errors;,四、存储过程和函数的调用和测试 1、参数传值 一般采用位置对应法向形式参数传值，要求实际参数与形式参数保持次序、类型、个数一致。 例：从 SQL*Plus命令中通过位置对应法调用 HIRE_EMP过程。 SQL variable v_ename varchar2(12)； SQL EXECUTE hire_emp(9999，:v_ename)；,注意： 如果形式参数是IN模式的参数，实际参数可以是一个具体的值，或一个有值的变量。

16、如果形式参数是OUT模式的参数，实际参数必须是一个变量。当调用过程后，此变量就被赋值了。 如果形式参数是IN OUT模式的参数，则实际参数必须是一个预先已经赋值的变量。执行完过程后，该变量被重新赋值。 可以调用系统内置的DBMS_OUTPUT包中的过程输出此变量的值，测试过程执行的结果。,2、存储过程和函数的调用方法,各种环境调用过程和函数的语法和例子,（1） 在PL/SQL块中如何调用 过程的调用 例：从 PLSQL块中调用过程QUERY_EMP。执行过程后，输出变量测试结果。 DECLARE V_empno emp.empno%type:=7654; V_ename emp.ename%t

17、ype; V_sal emp.sal%type; V_comm m%type; Begin Query_emp(v_empno,v_ename,v_sal,v_comm); Dbms_output.put_line(v_ename| |v_sal| |v_comm); /*测试结果*/ End; /, 函数的调用 例：从一个 PLSQL块中调用 GET_SAL函数。 DECLARE v_empno NUMBER:7654； v_sal NUMBER； BEGIN v_sal:=get_sal(v_empno)； DBMS_OUTPUT.PUT_LINE(v_sal)； /*测试结果*/ END

18、； /,（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( EXECUTE

19、DBMS_OUTPUT.PUT_LINE(Information for employee：| TO_CHAR( 或者用SELECT语句查询函数的结果。 SQL SELECT GET_SAL(7654) FROM DUAL；,（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：) ；

20、scanf(d， /*给7788员工涨1000元*/ 例：从SQL*Plus工具中调用包中的过程 SQL EXECUTE sal_package.raise_sal(7788,1000)；,五、系统包,六、包的优点 （1）方便对存储过程和函数的组织 将相关的过程和函数组织在一起。 在一个用户环境中解决命名的冲突问题 （2） 方便对存储过程和函数的管理 在不改变包的说明定义时可以改变包体的定义 限制过程依赖性。 （3）方便对存储过程和函数的安全性管理 整个包的访问权限只需一次性授权。 区分公用过程和私有过程。 （4）改善性能 在包被首次调用时作为一个整体全部调入内存 减少多次调用时的磁盘 IO次

21、数。,第三节：触发器 一、触发器的基本概念 触发器类似于存储过程和函数，都是有说明部分、语句执行部分和出错处理部分三部分组成的PL/SQL有名块，触发器存储在数据库数据字典中。但是，对于存储过程而言，可以在另一个程序中调用过程，显式地执行一个过程，同时在调用时可以往存储过程传递参数。对于触发器而言，当触发事件发生时隐式地（自动地）执行该触发器，不能在程序中调用触发器，并且触发器不接受参数。,1、触发事件和触发器的功能 （1）可以触发触发器的事件包括：在数据库表上执行的INSERT、UPDATE、DELETE操作。 （2）使用触发器可以做许多事情，包括： 维护不可能在表创建时通过说明性约束进行的

22、复杂的完整性约束限制。 通过记录所进行的修改以及谁做了修改等信息对表进行审计。 当表被修改的时候，自动给需要执行操作的程序发信号。 等等,例：制作一个数据库触发器。将那些超过其工种工资范围的员工信息记录到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.minsalTYPE； v_

23、maxsal sal_guide.maxsalTYPE； e_sal_out_of_range EXCEPTION；,BEGIN SELECT minsal，maxsal INTO v_minsal，v_maxsal FROM sal_guide WHERE job = ：new. job； IF :new.salv_minsal OR :new:salv_maxsal THEN RAISE e_sal_out_of_range; END IF； EXCEPTION WHEN e_sal_out_of_range THEN INSERT INTO audit_message(line_nr，l

24、ine) VALUES(l,Salary| TO_CHAR(:new.sal)| is out of range for employee|TO_CHAR(:new.empno)； END； /,2、触发器的组成,3、触发器的类型 触发器分语句级触发器和行级触发器两个级别。 行级触发器与语句级触发器的区别主要在于其触发的次数不同，如果该DML语句只影响一行，则语句级与行级触发器效果一样。如果该DML语句影响多行，则行级触发器触发的次数比语句级触发器触发的次数多。,例：下列DML语句对语句级触发器和行级触发器效果一样。 SQL INSERT INTO DEPT(deptno，dname) VAL

25、UES(50，EDUCATION)； SQL UPDATE DEPT SET LOCMAUI WHERE DEPTNO50； SQL DELETE FROM DEPT WHERE DEPTNO50； 例：下列DML语句的行级触发器与语句级触发器效果不同。 SQL INSERT INTO EMP(empno，ename) SELECT empno，ename FROM EMP_BACKUP； SQL UPDATE DEPT SET SAL SAL*1.1 WHERE DEPTNO10；,根据触发的时间、类型不同，可以组合为四种DML触发器。当执行一条SQL语句时，这四种触发器的触发顺序如箭头所示

26、。,4、触发器与存储过程的区别,二、创建触发器 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语句）对表进行操作，

27、查看触发器是否按要求执行。,2、语句级触发器 用 CREATE TRIGGER语句创建一个语句级触发器，该触发器在一个数据操作语句发生时只触发一次。 （1）创建一个语句级触发器的语法 CREATE OR REPLACE TRIGGER trigger_name BEFORE|AFTER event1 OR event2. ON table_name PL/SQL block,例：创建一个 BEFORE型语句级触发器。限制一周内往 EMP表插入数据的时间。 CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON emp BEGIN IF(TO_

28、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； /,（2）使用触发器谓词（INSERTING、UPDATING、DELETING） DML触发器是一个INSERT、UPDATE、DELETE触发器。可以创建一个包含多个触发事件的触发器，在触发器体中使用谓词（ INSERTING，UPDATIN

29、G及 DELETING）判断是哪个触发事件触发了触发器，从而把多种触发事件组成一个触发器。,触发器谓词的行为和值,例：对前一例子进行扩展，不但限制插入数据的时间，还限制进行数据修改和删除的时间。 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_APPLI

30、CATION_ERROR(-20502， You may only delete from EMP during normal hours.) ；,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； /,3、行

31、级触发器 （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;,例：创建一个行级触发器。将每个用户对数据库EMP表进行数据操纵（插入、更新、删除）的次数记录到audit_table表中。 CREATE OR

32、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_nameEMP AND colun_name IS NULL；,ELSIF INSERTING THEN UPDATE audit_table SET ins=ins+l WHERE user_name=user AND table_name=EMP AND colu

33、mn_name IS NULL； EISE UPDATE audit_table SET upd=upd+1 WHERE user_name= user AND table_nameEMP AND column_name IS NULL； END IF； END； /,（2）使用行级触发器的标识符:OLD和:NEW,在行级触发器中，在列名前加上 :OLD标识符表示该列变化前的值，加上 :NEW标识符表示变化后的值。 :OLD和:NEW的意义,例：在行级触发器中获取某列的新值和旧值，为 EMP表中的所有数据保留一个历史档案 CREATE OR REPLACE TRIGGER audit_emp_

34、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； /,例：在行级触发器加WHEN限制条件。根据销售员工资的改变自动计

35、算销售员的奖金。 CREATE OR REPLACE TRIGGER derive_comm BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN （new.jobSALESMAN) BEGIN :m ：=:m * (:new.sal:old.sal)； END； /,三、 触发器的管理 1、触发器的管理命令,打开和关闭触发器的语法如下： ALTER TRIGGER 触发器名 ENABLE | DISABLE,注意：关闭触发器与删除触发器的区别,注意： 当触发器刚创建时，它被自动设置为有效。可查看数据字典视图USER_TRRIGERS的STATUS列了解触

36、发器的状态，看其是否有效？ 为了改善性能，以及在大量装载数据时(例如使用 SQL* Loader)避免进行数据的完整性校验，可以使数据库触发器暂时失效。 当发生网络连接故障、磁盘损坏，数据文件脱机或表空间脱机，从而导致触发器中涉及到的表不能再被访问时，应使触发器失效。,2、管理触发器的命令与管理存储过程的命令的比较,四、测试触发器 可以使用多种方法测试触发器： （1）用多种触发事件(如：INSERT、UPDATE、DELETE等)分别对触发器进行测试，以确保触发器的正确运行。 （2）用满足 WHEN条件的触发事件进行测试。 （3）在触发器内利用 DBMS_OUTPUT包输出运行结果进行测试。,

37、例：用下列命令测试 DERIVE_COMM触发器。 假如查询结果有员工号7788，工资2000，奖金800，则用UPDATE语句将7788的工资乘2。 SQL select empno，sal, comm from emp where job=SALESMAN; SQL UPDATE EMP SET salsal*2 WHERE empno=7788; 再用SELECT语句查看奖金是否也乘2？如果发现奖金变成1600，则表示触发器触发了。 SQL select empno，sal, comm from emp where job=SALESMAN;,例：对audit_emp_values触发器

38、，可以用下列命令进行测试。 （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);,（2）对EMP表执行INSERT、UPDATE、DELETE

39、操作 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;,五、触发器的应用,创建数据库触发器，可以大大增强 Oracle系统的性能，完成一些 Oracle

40、系统本身提供的服务所不能完成的功能。 触发器的应用主要是以下几个方面： 安全性 审计 数据完整性 参考完整性 数据复制,1、 安全性 在 Oracle中通过给用户授权完成对表中数据进行操作的安全性控制。 例： 使用权限控制安全性。 SQL GRANT SELECT，INSERT， UPDATE，DELETE ON EMP TO CLERK； 但如果希望限制CLERK用户只能在特定时间内访问EMP表，非规定时间内不允许访问，则可以使用数据库触发器，实现进一步的安全性控制。,例： 在触发器中控制数据的安全性。限制只允许在某周的特定时间内进行数据操作。 CREATE OR REPLACE TRIGG

41、ER 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 ; /,2、审计 Oracle系统本身提供了审计功能，可以审计用户插入、更新、删除、查询语句、DDL语句的执行次

42、数，也可以审计操作的成功和失败，并把审计情况写入审计表中(数据字典表中)。但不能审计数据操纵时值的变化，。 例: 利用系统本身提供的审计，审计EMP表上所有成功的数据操作的次数。 SQL AUDIT INSERT，UPDATE， DELETE ON emp BY ACCESS WHENEVER SUCCESSFUL；,利用触发器可以审计操纵语句所操作的值的变化，可以将值的变化情况写入用户定义的档案表。 例：用触发器进行审计。将EMP表上值的变化记录到audit_emp表中。 CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR I

43、NSERT 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； /,3、数据完整性 在建表时使用Oracle系统提供的完整性约束条件，可以保证标准的数据完整性：标准的数据完整性包

44、括五种约束条件：非空(NOT NUIL)约束、主键约束、外键约束、CHECK约束、唯一性约束 。 标准完整性只能保证静态约束。,例： 利用系统提供的约束条件保证数据完整性，并保证雇员的薪水不低于$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), /*ch

45、eck约束*/ Comm number, Deptno number(4) constraint fk1 foreign key(deptno) /*外键*/ references dept (deptno) on delete cascade );,可以利用触发器加强非标准的完整性检查，提供动态约束。 例： 用触发器实现非标准的数据完整性。确保薪水不能降低，但同时增长幅度不能超过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