第10章存储过程、函数和触发器精选文档.ppt

第10章存储过程、函数和触发器本讲稿第一页，共四十页过程和函数概述过程和函数概述匿名的匿名的PL/SQL PL/SQL 块的缺点是，每次执行的时候都要被重块的缺点是，每次执行的时候都要被重新编译，并且不能被存储在数据库中（因此不能被其他新编译，并且不能被存储在数据库中（因此不能被其他PL/SQLPL/SQL块使用）。块使用）。过程与函数是命名的过程与函数是命名的PL/SQLPL/SQL块，被存储在数据库中，块，被存储在数据库中，并且可以被其他并且可以被其他PL/SQLPL/SQL块使用。过程与函数也称为块使用。过程与函数也称为子程序子程序。本讲稿第二页，共四十页过程过程(PROCEDUER):(PROCEDUER):是模块化程序设计的基本概念，是模块化程序设计的基本概念，它将一些相关的它将一些相关的SQLSQL语句，流程控制语句组合在一起，语句，流程控制语句组合在一起，用于用于执行某些特定的操作或者任务执行某些特定的操作或者任务。函数函数(FUNCTION):(FUNCTION):用于用于计算和返回计算和返回特定的数据，可以将特定的数据，可以将经常需要进行的计算写成函数。经常需要进行的计算写成函数。本讲稿第三页，共四十页图：过程和函数的PL/SQL块结构本讲稿第四页，共四十页10.1 创建过程创建过程CREATE OR REPLACE PROCEDURE CREATE OR REPLACE PROCEDURE schema.procedure_nameschema.procedure_namearg1 IN|OUT|IN OUT arg_type1,arg1 IN|OUT|IN OUT arg_type1,arg2 IN|OUT|IN OUT arg_type2,arg2 IN|OUT|IN OUT arg_type2,IS|ASIS|AS声明部分声明部分BEGINBEGIN执行部分执行部分EXCEPTIONEXCEPTION异常处理部分异常处理部分END END procedure_name;procedure_name;本讲稿第五页，共四十页其中其中:OR RELACEOR RELACE是可选的。如果省略，则创建时不允许是可选的。如果省略，则创建时不允许数据库中有同名的过程；如果使用，则会先删除同名数据库中有同名的过程；如果使用，则会先删除同名的过程，然后创建新的过程。的过程，然后创建新的过程。Arg1,arg2,argnArg1,arg2,argn是过程的形参的名称，是可选的。是过程的形参的名称，是可选的。arg_type1arg_type1，arg_type2 arg_type2，,arg_typen,arg_typen是对应的型参的数据是对应的型参的数据类型。类型。注意：此处的数据类型后面不能带参数，即精度、注意：此处的数据类型后面不能带参数，即精度、范围等，如范围等，如NUMBERNUMBER（1212，2 2）只能写成）只能写成NUMBERNUMBER。IN|OUT|IN OUTIN|OUT|IN OUT是形参的模式。如果省略则为是形参的模式。如果省略则为ININ模式。模式。本讲稿第六页，共四十页ININ用于接受调用程序的值用于接受调用程序的值默认的参数模式默认的参数模式OUTOUT用于向调用程序返回值用于向调用程序返回值 IN OUTIN OUT用于接受调用程序的值，并向调用程序返回更新的值用于接受调用程序的值，并向调用程序返回更新的值本讲稿第七页，共四十页例：例：不带参数的简单存储过程不带参数的简单存储过程CREATE OR REPLACE PROCEDURE p ISCREATE OR REPLACE PROCEDURE p ISCREATE OR REPLACE PROCEDURE p ISCREATE OR REPLACE PROCEDURE p IS cursor c is cursor c is cursor c is cursor c is select*from emp where comm is null and sal2000 for select*from emp where comm is null and salv_b)then v_ret:=v_a;else v_ret:=v_b;end if;v_temp:=v_temp+1;end;本讲稿第十一页，共四十页主程序：主程序：declaredeclare v_a number;v_a number;v_b number;v_b number;v_ret number;v_ret number;v_temp number:=5;v_temp number:=5;beginbegin p1(&v_ap1(&v_a的值的值,&v_b,&v_b的值的值,v_ret,v_temp);,v_ret,v_temp);dbms_output.put_line(v_ret);dbms_output.put_line(v_ret);dbms_output.put_line(v_temp);dbms_output.put_line(v_temp);end;end;本讲稿第十二页，共四十页练一练练一练编写一个存储过程，可以根据用户输入的姓名编写一个存储过程，可以根据用户输入的姓名返回其所在的部门名称以及所在地，使用参数返回其所在的部门名称以及所在地，使用参数传递。传递。在匿名块中调用该过程。在匿名块中调用该过程。本讲稿第十三页，共四十页删除过程删除过程语法语法DROP PROCEDURE procedure_name;DROP PROCEDURE procedure_name;其中，其中，procedure_name procedure_name 是过程的名称。是过程的名称。示例示例Drop procedure find_emp;Drop procedure find_emp;本讲稿第十四页，共四十页10.2 创建函数创建函数函数是一种数据库对象函数是一种数据库对象,同样也是一个命名的同样也是一个命名的PL/SQLPL/SQL程序块程序块,被存储在数据库中被存储在数据库中,可以被反复的使用可以被反复的使用.函数用来执行复杂的计算，并返回计算的结果。函数用来执行复杂的计算，并返回计算的结果。在调用的时候在调用的时候,可以被作为表达式的一部分。必须要可以被作为表达式的一部分。必须要有返回值。有返回值。这个返回值既可以是这个返回值既可以是numbernumber或或varchar2varchar2这样这样简单的数据类型，也可以是简单的数据类型，也可以是PL/SQLPL/SQL数组或对象这样复数组或对象这样复杂的数据类型。杂的数据类型。本讲稿第十五页，共四十页CREATE OR REPLACE CREATE OR REPLACE FUNCTION function nameFUNCTION function namearg1 IN|OUT|IN OUT arg_type1,arg1 IN|OUT|IN OUT arg_type1,arg2 IN|OUT|IN OUT arg_type2,arg2 IN|OUT|IN OUT arg_type2,RETURN return_typeRETURN return_typeIS|ASIS|AS声明部分声明部分BEGINBEGIN执行部分执行部分EXCEPTIONEXCEPTION异常处理部分异常处理部分END function name;END function name;其中：其中：RETURNRETURN子句说明函数返回值的数据类型子句说明函数返回值的数据类型。这是与过程。这是与过程的区别之一。的区别之一。本讲稿第十六页，共四十页例：例：create or replace function create or replace function sal_tax(v_sal number)sal_tax(v_sal number)return number return number is isbeginbegin if v_sal2000 then if v_sal2000 then return 0.10;return 0.10;elsif v_sal2750 then elsif v_salOLD.sal)WHEN(NEW.salOLD.sal)DECLAREDECLARE Sal_diff NUMBER;Sal_diff NUMBER;BEGIN BEGIN sal_diff:=:NEW.sal-:OLD.sal;sal_diff:=:NEW.sal-:OLD.sal;DBMS_OUTPUT.PUT_LINE(DBMS_OUTPUT.PUT_LINE(工资差工资差 额：额：sal_diff);sal_diff);END;END;为为 emp 表创建表创建 trig_sal 触发器触发器在更新在更新 sal 列之后激活触发器列之后激活触发器只有在只有在WHEN子句中的条件得到满足时，子句中的条件得到满足时，才激活才激活trig_sal 触发器触发器如果如果WHEN子句中的条件得到满足，子句中的条件得到满足，将执行将执行BEGIN 块中的代码块中的代码触发事件触发事件触发器限制触发器限制触发器语句触发器语句本讲稿第三十页，共四十页语句级触发器语句级触发器语句级触发器是指当执行语句级触发器是指当执行DMLDML操作时，以语句为单位执操作时，以语句为单位执行的触发器。行的触发器。例例:为了显示触发情况创建一张用于记录信息的表。为了显示触发情况创建一张用于记录信息的表。create table emp2_logcreate table emp2_log(uname varchar2(20),uname varchar2(20),action varchar2(10),action varchar2(10),atime date atime date););本讲稿第三十一页，共四十页 create or replace trigger create or replace trigger trigtrig after insert or delete or update on emp after insert or delete or update on empbeginbegin if inserting then if inserting then insert into emp2_log values(USER,insert,sysdate);insert into emp2_log values(USER,insert,sysdate);elsif updating then elsif updating then insert into emp2_log values(USER,update,sysdate);insert into emp2_log values(USER,update,sysdate);elsif deleting then elsif deleting then insert into emp2_log values(USER,delete,sysdate);insert into emp2_log values(USER,delete,sysdate);end if;end if;end;end;没有分号没有分号本讲稿第三十二页，共四十页行级触发器行级触发器行级触发器是指当执行行级触发器是指当执行DMLDML操作时，以数据行为单位操作时，以数据行为单位执行的触发器，即每一行都执行一次触发器。执行的触发器，即每一行都执行一次触发器。方法：方法：在定义在定义trrigertrriger语句的最后加上语句的最后加上 for each row for each row本讲稿第三十三页，共四十页create or replace trigger create or replace trigger trigtrig after insert or delete or update on emp after insert or delete or update on emp for each rowfor each rowbeginbegin if inserting then if inserting then insert into emp2_log values(USER,insert,sysdate);insert into emp2_log values(USER,insert,sysdate);elsif updating then elsif updating then insert into emp2_log values(USER,update,sysdate);insert into emp2_log values(USER,update,sysdate);elsif deleting then elsif deleting then insert into emp2_log values(USER,delete,sysdate);insert into emp2_log values(USER,delete,sysdate);end if;end if;end;end;本讲稿第三十四页，共四十页例：例：增强数据的完整性管理增强数据的完整性管理create or replace trigger tr_dept_empcreate or replace trigger tr_dept_emp after update of deptno after update of deptno on dept on dept for each row for each row begin begin update emp set deptno=:new.deptno update emp set deptno=:new.deptno where deptno=:old.deptno;where deptno=:old.deptno;end;end;可见，通过使用行级触发器可以实现级联更新。可见，通过使用行级触发器可以实现级联更新。本讲稿第三十五页，共四十页查询触发器查询触发器可以使用数据字典中的可以使用数据字典中的user_triggers,all_triggers,dba_triggersuser_triggers,all_triggers,dba_triggers视图来查询触发器的视图来查询触发器的定义及其状态信息。定义及其状态信息。本讲稿第三十六页，共四十页禁止或者激活触发器禁止或者激活触发器为了改善性能，并且在大量转载数据时避免触发相应的触发为了改善性能，并且在大量转载数据时避免触发相应的触发器（如器（如,进行完整性校验、约束校验等），应该禁止触发器，进行完整性校验、约束校验等），应该禁止触发器，使其暂时失效。使其暂时失效。例：例：Alter tirgger tr_emp_time disable;Alter tirgger tr_emp_time disable;为了使被禁止的触发器重新发挥作用，需要将其激活。为了使被禁止的触发器重新发挥作用，需要将其激活。例：例：Alter trigger tr_emp_time enable;Alter trigger tr_emp_time enable;也可以一次禁止或激活一个某个表上的所有触发器。也可以一次禁止或激活一个某个表上的所有触发器。Alter table emp disable all triggers;Alter table emp disable all triggers;Alter table emp enable all tirggers;Alter table emp enable all tirggers;本讲稿第三十七页，共四十页删除触发器删除触发器如果不需要某个触发器了，可以将其删除。如果不需要某个触发器了，可以将其删除。Drop trigger tr_emp_time;本讲稿第三十八页，共四十页上机练习上机练习1 1、编写一个过程、编写一个过程,实现查询实现查询empemp表员工的工龄情况，输入参表员工的工龄情况，输入参数为数为empno,empno,输出参数为工龄。（工龄输出参数为工龄。（工龄=当前时间当前时间-雇佣日雇佣日期）。创建完成之后，进行测试。期）。创建完成之后，进行测试。2 2、编写过程、编写过程proc_addsalproc_addsal，实现涨工资的功能，输入参数为职，实现涨工资的功能，输入参数为职工编号，如果他的工作年限超过工编号，如果他的工作年限超过1010年，工资增加年，工资增加2000 2000，工，工作年限超过作年限超过2020年工资增加年工资增加30003000；输出参数为职工姓名和现在；输出参数为职工姓名和现在的工资数目。创建完成之后，进行测试。的工资数目。创建完成之后，进行测试。本讲稿第三十九页，共四十页3 3、编写一个函数编写一个函数get_salary,get_salary,计算某部门的工资总和计算某部门的工资总和,输入参数为输入参数为部门编号，返回值为工资总和。部门编号，返回值为工资总和。4 4、编写一个数据库触发器，当任何时候某个部门从、编写一个数据库触发器，当任何时候某个部门从“dept”“dept”中删除时，该触发器将从中删除时，该触发器将从“emp”“emp”表中删除该表中删除该部门的所有雇员。部门的所有雇员。本讲稿第四十页，共四十页