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

《第10章存储过程、函数和触发器精选PPT.ppt》由会员分享，可在线阅读，更多相关《第10章存储过程、函数和触发器精选PPT.ppt（40页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

3、 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;第5页，此课件共40页哦其中其中:OR

4、 RELACEOR 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页哦例：例：不带参数的简单存储过程不带参数的简单存储过程CREA

6、TE 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 sal2000 for select*from emp where com

7、m 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;第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(&v_ap1(&v_a的值的

8、值,&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页哦练一练练一练编写一个存储过程，可以根据用户输入的姓名返回编写一个存储过程，可以根据用户输入的姓名返回其所在的部门名称以及所在地，使用参数传递。其所在的部门名称以及所在地，使用参数传递。在匿名块中调用该过程。在匿名块中调用该过程。第13页，此

9、课件共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 nameFUNCTION functi

11、on 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(NEW.sal

13、OLD.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),uname varch

15、ar2(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 value

16、s(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);i

17、nsert 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 create

18、 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 el

19、sif 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;第34页，此课件共40页哦例：例：增强数据的完整

20、性管理增强数据的完整性管理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

21、eptno;end;end;可见，通过使用行级触发器可以实现级联更新。可见，通过使用行级触发器可以实现级联更新。第35页，此课件共40页哦查询触发器查询触发器可以使用数据字典中的可以使用数据字典中的user_triggers,all_triggers,dba_triggersuser_triggers,all_triggers,dba_triggers视视图来查询触发器的定义及其状态信息。图来查询触发器的定义及其状态信息。第36页，此课件共40页哦禁止或者激活触发器禁止或者激活触发器为了改善性能，并且在大量转载数据时避免触发相应的触发器为了改善性能，并且在大量转载数据时避免触发相应的触发器（如

22、（如,进行完整性校验、约束校验等），应该禁止触发器，使进行完整性校验、约束校验等），应该禁止触发器，使其暂时失效。其暂时失效。例：例：Alter tirgger tr_emp_time disable;Alter tirgger tr_emp_time disable;为了使被禁止的触发器重新发挥作用，需要将其激活。为了使被禁止的触发器重新发挥作用，需要将其激活。例：例：Alter trigger tr_emp_time enable;Alter trigger tr_emp_time enable;也可以一次禁止或激活一个某个表上的所有触发器。也可以一次禁止或激活一个某个表上的所有触发器。A

23、lter 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表员工的工龄情况，输入表员工的工

24、龄情况，输入参数为参数为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页哦