《第10章存储过程、函数和触发器精.ppt》由会员分享，可在线阅读，更多相关《第10章存储过程、函数和触发器精.ppt（40页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第10章存储过程、函数和触发器第1页，本讲稿共40页过程和函数概述过程和函数概述匿名的匿名的PL/SQL PL/SQL 块的缺点是，每次执行的时候都要被重新编块的缺点是，每次执行的时候都要被重新编译，并且不能被存储在数据库中（因此不能被其他译，并且不能被存储在数据库中（因此不能被其他PL/SQLPL/SQL块使用）。块使用）。过程与函数是命名的过程与函数是命名的PL/SQLPL/SQL块，被存储在数据库中，块，被存储在数据库中，并且可以被其他并且可以被其他PL/SQLPL/SQL块使用。过程与函数也称为块使用。过程与函数也称为子子程序程序。第2页，本讲稿共40页过程过程(PROCEDUER):
2、(PROCEDUER):是模块化程序设计的基本概念，它是模块化程序设计的基本概念，它将一些相关的将一些相关的SQLSQL语句，流程控制语句组合在一起，用语句，流程控制语句组合在一起，用于于执行某些特定的操作或者任务执行某些特定的操作或者任务。函数函数(FUNCTION):(FUNCTION):用于用于计算和返回计算和返回特定的数据，可以特定的数据，可以将经常需要进行的计算写成函数。将经常需要进行的计算写成函数。第3页，本讲稿共40页图：过程和函数的PL/SQL块结构第4页，本讲稿共40页10.1 创建过程创建过程CREATE OR REPLACE PROCEDURE CREATE OR REP
3、LACE 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;第5页，本讲稿共40页其中其中:OR RELA
4、CEOR RELACE是可选的。如果省略，则创建时不允许数据库是可选的。如果省略，则创建时不允许数据库中有同名的过程；如果使用，则会先删除同名的过程，然后中有同名的过程；如果使用，则会先删除同名的过程，然后创建新的过程。创建新的过程。Arg1,arg2,argnArg1,arg2,argn是过程的形参的名称，是可选的。是过程的形参的名称，是可选的。arg_type1arg_type1，arg_type2 arg_type2，,arg_typen,arg_typen是对应的型参的数据是对应的型参的数据类型。类型。注意：此处的数据类型后面不能带参数，即精度、注意：此处的数据类型后面不能带参数，即精
5、度、范围等，如范围等，如NUMBERNUMBER（1212，2 2）只能写成）只能写成NUMBERNUMBER。IN|OUT|IN OUTIN|OUT|IN OUT是形参的模式。如果省略则为是形参的模式。如果省略则为ININ模式。模式。第6页，本讲稿共40页ININ用于接受调用程序的值用于接受调用程序的值默认的参数模式默认的参数模式OUTOUT用于向调用程序返回值用于向调用程序返回值 IN OUTIN OUT用于接受调用程序的值，并向调用程序返回更新的值用于接受调用程序的值，并向调用程序返回更新的值第7页，本讲稿共40页例：例：不带参数的简单存储过程不带参数的简单存储过程CREATE OR R
6、EPLACE 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 update;select*from emp where comm is null and sal2000 for update;select*from emp wh
7、ere comm is null and sal2000 for update;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;第11页，本讲稿共40页主程序：主程序：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(&
8、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;第12页，本讲稿共40页练一练练一练编写一个存储过程，可以根据用户输入的姓名返编写一个存储过程，可以根据用户输入的姓名返回其所在的部门名称以及所在地，使用参数传递。回其所在的部门名称以及所在地，使用参数传递。在匿名块中调用该过程。在匿名块中
9、调用该过程。第13页，本讲稿共40页删除过程删除过程语法语法DROP PROCEDURE procedure_name;DROP PROCEDURE procedure_name;其中，其中，procedure_name procedure_name 是过程的名称。是过程的名称。示例示例Drop procedure find_emp;Drop procedure find_emp;第14页，本讲稿共40页10.2 创建函数创建函数函数是一种数据库对象函数是一种数据库对象,同样也是一个命名的同样也是一个命名的PL/SQLPL/SQL程程序块序块,被存储在数据库中被存储在数据库中,可以被反复的使用
10、可以被反复的使用.函数用来执行复杂的计算，并返回计算的结果。函数用来执行复杂的计算，并返回计算的结果。在调用的时候在调用的时候,可以被作为表达式的一部分。必须要有返回可以被作为表达式的一部分。必须要有返回值。值。这个返回值既可以是这个返回值既可以是numbernumber或或varchar2varchar2这样简单的这样简单的数据类型，也可以是数据类型，也可以是PL/SQLPL/SQL数组或对象这样复杂的数组或对象这样复杂的数据类型。数据类型。第15页，本讲稿共40页CREATE OR REPLACE CREATE OR REPLACE FUNCTION function nameFUNCTI
11、ON 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子句说明函数返回值的数据
12、类型子句说明函数返回值的数据类型。这是与过程。这是与过程的区别之一。的区别之一。第16页，本讲稿共40页例：例：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
13、(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子句中的条件得到满足时，子句中的条件得到满足
14、时，才激活才激活trig_sal 触发器触发器如果如果WHEN子句中的条件得到满足，子句中的条件得到满足，将执行将执行BEGIN 块中的代码块中的代码触发事件触发事件触发器限制触发器限制触发器语句触发器语句第30页，本讲稿共40页语句级触发器语句级触发器语句级触发器是指当执行语句级触发器是指当执行DMLDML操作时，以语句为单位执行操作时，以语句为单位执行的触发器。的触发器。例例:为了显示触发情况创建一张用于记录信息的表。为了显示触发情况创建一张用于记录信息的表。create table emp2_logcreate table emp2_log(uname varchar2(20),unam
15、e varchar2(20),action varchar2(10),action varchar2(10),atime date atime date););第31页，本讲稿共40页 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
16、 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,sysd
17、ate);insert into emp2_log values(USER,delete,sysdate);end if;end if;end;end;没有分号没有分号第32页，本讲稿共40页行级触发器行级触发器行级触发器是指当执行行级触发器是指当执行DMLDML操作时，以数据行为单位执行操作时，以数据行为单位执行的触发器，即每一行都执行一次触发器。的触发器，即每一行都执行一次触发器。方法：方法：在定义在定义trrigertrriger语句的最后加上语句的最后加上 for each row for each row第33页，本讲稿共40页create or replace trigger tr
18、ig after insert or delete or update on emp for each rowbegin if inserting then insert into emp2_log values(USER,insert,sysdate);elsif updating then insert into emp2_log values(USER,update,sysdate);elsif deleting then insert into emp2_log values(USER,delete,sysdate);end if;end;第34页，本讲稿共40页例：例：增强数据的完整
19、性管理增强数据的完整性管理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.d
20、eptno;end;end;可见，通过使用行级触发器可以实现级联更新。可见，通过使用行级触发器可以实现级联更新。第35页，本讲稿共40页查询触发器查询触发器可以使用数据字典中的可以使用数据字典中的user_triggers,all_triggers,dba_triggersuser_triggers,all_triggers,dba_triggers视视图来查询触发器的定义及其状态信息。图来查询触发器的定义及其状态信息。第36页，本讲稿共40页禁止或者激活触发器禁止或者激活触发器为了改善性能，并且在大量转载数据时避免触发相应的触发为了改善性能，并且在大量转载数据时避免触发相应的触发器（如器（如
21、,进行完整性校验、约束校验等），应该禁止触发器，进行完整性校验、约束校验等），应该禁止触发器，使其暂时失效。使其暂时失效。例：例：Alter tirgger tr_emp_time disable;Alter tirgger tr_emp_time disable;为了使被禁止的触发器重新发挥作用，需要将其激活。为了使被禁止的触发器重新发挥作用，需要将其激活。例：例：Alter trigger tr_emp_time enable;Alter trigger tr_emp_time enable;也可以一次禁止或激活一个某个表上的所有触发器。也可以一次禁止或激活一个某个表上的所有触发器。Alt
22、er table emp disable all triggers;Alter table emp disable all triggers;Alter table emp enable all tirggers;Alter table emp enable all tirggers;第37页，本讲稿共40页删除触发器删除触发器如果不需要某个触发器了，可以将其删除。如果不需要某个触发器了，可以将其删除。Drop trigger tr_emp_time;第38页，本讲稿共40页上机练习上机练习1 1、编写一个过程、编写一个过程,实现查询实现查询empemp表员工的工龄情况，输入参数为表员工的工龄
23、情况，输入参数为empno,empno,输出参数为工龄。（工龄输出参数为工龄。（工龄=当前时间当前时间-雇佣日期）。创雇佣日期）。创建完成之后，进行测试。建完成之后，进行测试。2 2、编写过程、编写过程proc_addsalproc_addsal，实现涨工资的功能，输入参数为职工，实现涨工资的功能，输入参数为职工编号，如果他的工作年限超过编号，如果他的工作年限超过1010年，工资增加年，工资增加2000 2000，工作年，工作年限超过限超过2020年工资增加年工资增加30003000；输出参数为职工姓名和现在的工；输出参数为职工姓名和现在的工资数目。创建完成之后，进行测试。资数目。创建完成之后，进行测试。第39页，本讲稿共40页3 3、编写一个函数编写一个函数get_salary,get_salary,计算某部门的工资总和计算某部门的工资总和,输入输入参数为部门编号，返回值为工资总和。参数为部门编号，返回值为工资总和。4 4、编写一个数据库触发器，当任何时候某个部门从、编写一个数据库触发器，当任何时候某个部门从“dept”“dept”中中删除时，该触发器将从删除时，该触发器将从“emp”“emp”表中删除该部门的所有雇员。表中删除该部门的所有雇员。第40页，本讲稿共40页