Oracle第15章PLSQL程序设计

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2007-9,东北大学东软信息学院计算机系,*,Oracle10g,数据库基础教程,Oracle10g,数据库基础教程,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2007-9,东北大学东软信息学院计算机系,*,1,第,15,章,PL/SQL,程序设计,2,本章,内,内容,存储,过,过程,函数,局部,子,子程,序,序,包,触发,器,器,3,本章,要,要求,掌握,PL/SQL,功能,模,模块,的,的应,用,用,存储,过,过程,、,、函,数,数、,包,包、,触,触发,器,器的,创,创建,存储,过,过程,、,、函,数,数、,包,包、,触,触发,器,器的,维,维护,4,1,5,.,1,存储,子程,序,序,存储,过,过程,函数,局部,子,子程,序,序,15.1.1,存储,过,过程,存储过程,的,的创建,存储过程,的,的调用,存储过程,的,的管理,5,6,（,1,）存储过,程,程的创建,基本语法,CREATE OR REPLACEPROCEDURE procedure_name,(parameter1_namemodedatatype,DEFAULT|:=value, parameter2_name mode datatype,DEFAULT|:=value,),AS|IS,/*Declarative section ishere */,BEGIN,/*Executablesectionishere*/,EXCEPTION,/*Exceptionsectionis here*/,ENDprocedure_name;,7,参数说明,参数的模,式,式,IN,（默认参,数,数模式）,表,表示当过,程,程被调用,时,时，实参,值,值被传递,给,给形参；,在,在过程内,，,，形参起,常,常量作用,，,，只能读,该,该参数，,而,而不能修,改,改该参数,；,；当子程,序,序调用结,束,束返回调,用,用环境时,，,，实参没,有,有被改变,。,。,IN,模式参数,可,可以是常,量,量或表达,式,式。,OUT,表示当过,程,程被调用,时,时，实参,值,值被忽略,；,；在过程,内,内，形参,起,起未初始,化,化的,PL/SQL,变量的作,用,用，初始,值,值为,NULL,，可以进,行,行读,/,写操作；,当,当子程序,调,调用结束,后,后返回调,用,用环境时,，,，形参值,被,被赋给实,参,参。,OUT,模式参数,只,只能是变,量,量，不能,是,是常量或,表,表达式。,IN OUT,表示当过,程,程被调用,时,时，实参,值,值被传递,给,给形参；,在,在过程内,，,，形参起,已,已初始化,的,的,PL/SQL,变量的作,用,用，可读,可,可写；当,子,子程序调,用,用结束返,回,回调用环,境,境时，形,参,参值被赋,给,给实参。,IN OUT,模式参数,只,只能是变,量,量，不能,是,是常量或,表,表达式。,8,参数的限,制,制,在声明形,参,参时，不,能,能定义形,参,参的长度,或,或精度、,刻,刻度，它,们,们是作为,参,参数传递,机,机制的一,部,部分被传,递,递的，是,由,由实参决,定,定的。,参数传递,方,方式,当子程序,被,被调用时,，,，实参与,形,形参之间,值,值的传递,方,方式取决,于,于参数的,模,模式。,IN,参数为引,用,用传递，,即,即实参的,指,指针被传,递,递给形参,；,；,OUT,，,IN OUT,参数为值,传,传递，即,实,实参的值,被,被复制给,形,形参。,参数默认,值,值,可以为参,数,数设置默,认,认值，这,样,样存储过,程,程被调用,时,时如果没,有,有给该参,数,数传递值,，,，则采用,默,默认值。,需,需要注意,，,，有默认,值,值的参数,应,应该放在,参,参数列表,的,的最后。,9,创建一个,存,存储过程,，,，以部门,号,号为参数,，,，查询该,部,部门的平,均,均工资，,并,并输出该,部,部门中比,平,平均工资,高,高的员工,号,号、员工,名,名。,CREATE OR REPLACE PROCEDUREshow_emp(,p_deptnoemp.deptno%TYPE),AS,v_sal emp.sal%TYPE;,BEGIN,SELECT avg(sal)INTOv_sal FROMemp,WHERE deptno=p_deptno;,DBMS_OUTPUT.PUT_LINE(p_deptno|averagesalaryis:|,v_sal);,FORv_emp IN(SELECT* FROMempWHERE,deptno=p_deptnoANDsalv_sal) LOOP,DBMS_OUTPUT.PUT_LINE(v_emp.empno|,v_emp.ename);,ENDLOOP;,EXCEPTION,WHENNO_DATA_FOUND THEN,DBMS_OUTPUT.PUT_LINE(The departmentdoesn,t exists!);,ENDshow_emp;,10,通常，存,储,储过程不,需,需要返回,值,值，如果,需,需要返回,一,一个值可,以,以通过函,数,数调用实,现,现。但是,，,，如果希,望,望返回多,个,个值，可,以,以使用,OUT,或,IN OUT,模式参数,来,来实现。,11,创建一个,存,存储过程,，,，以部门,号,号为参数,，,，返回该,部,部门的人,数,数和最高,工,工资。,CREATE OR REPLACE PROCEDUREreturn_deptinfo(,p_deptnoemp.deptno%TYPE,p_avgsalOUTemp.sal%TYPE,p_countOUTemp.sal%TYPE),AS,BEGIN,SELECT avg(sal),count(*)INTO p_avgsal,p_count,FROMemp,WHERE deptno=p_deptno;,EXCEPTION,WHENNO_DATA_FOUND THEN,DBMS_OUTPUT.PUT_LINE(The departmentdon,t exists!);,ENDreturn_deptinfo;,12,（,2,）存储过,程,程的调用,在,SQL*PLUS,中调用,EXECprocedure_name(parameter_list),EXECUTEshow_emp(10),在,PL/SQL,块中调用,BEGIN,procedure_name(parameter_list);,END,；,注意,在,PL/SQL,程序中，,存,存储过程,可,可以作为,一,一个独立,的,的表达式,被,被调用。,13,DECLARE,v_avgsalemp.sal%TYPE;,v_countNUMBER;,BEGIN,show_emp(20);,return_deptinfo(10,v_avgsal,v_count);,DBMS_OUTPUT.PUT_LINE(v_avgsal|,v_count);,END;,14,（,3,）存储过,程,程的管理,修改存储,过,过程,CREATE OR REPLACE PROCEDUREprocedure_name,查看存储,过,过程及其,源,源代码,查询数据,字,字典视图,USER_SOURCE,SELECT name,textFROM user_source,WHERE type=PROCEDURE;,重新编译存储过,程,程,ALTER PROCEDURECOMPILE,ALTER PROCEDURE show_emp COMPILE;,删除存储过程,DROP PROCEDURE,DROP PROCEDUREshow_emp;,15,1,5,.,1,.2,函数,函数的创建,函数的调用,函数的管理,16,（,1,）函数的创建,基本语法为,CREATEOR REPLACE FUNCTIONfunction_name,(parameter1_name mode datatype,DEFAULT|:=value, parameter2_name mode datatype,DEFAULT|:=value,),RETURNreturn_datatype,AS|IS,/*Declarativesectionis here */,BEGIN,/*Executable sectionis here*/,EXCEPTION,/*Exception section is here*/,END function_name;,17,注意,在函数定义的头,部,部，参数列表之,后,后，必须包含一,个,个,RETURN,语句来指明函数,返,返回值的类型，,但,但不能约束返回,值,值的长度、精度,、,、刻度等。如果,使,使用,%TYPE,，则可以隐含地,包,包括长度、精度,、,、刻度等约束信,息,息；,在函数体的定义,中,中，必须至少包,含,含一个,RETURN,语句，来指明函,数,数返回值。也可,以,以有多个,RETURN,语句，但最终只,有,有一个,RETURN,语句被执行。,18,创建一个以部门,号,号为参数，返回,该,该部门最高工资,的,的函数。,CREATEOR REPLACE FUNCTION return_maxsal,(p_deptno emp.deptno%TYPE),RETURNemp.sal%TYPE,AS,v_maxsal emp.sal%TYPE;,BEGIN,SELECTmax(sal) INTOv_maxsal FROMemp,WHERE deptno=p_deptno;,RETURNv_maxsal;,EXCEPTION,WHEN NO_DATA_FOUND THEN,DBMS_OUTPUT.PUT_LINE(The deptno isinvalid!);,END return_maxsal;,19,如果需要函数返,回,回多个值，可以,使,使用,OUT,或,IN OUT,模式参数。,20,创建一个函数，,以,以部门号为参数,，,，返回部门名、,部,部门人数及部门,平,平均工资,。,CREATEOR REPLACE FUNCTION ret_deptinfo(,p_deptno dept.deptno%TYPE,p_num OUT NUMBER,p_avg OUT NUMBER),RETURNdept.dname%TYPE,AS,v_dnamedept.dname%TYPE;,BEGIN,SELECTdname INTO v_dname FROM dept,WHERE deptno=p_deptno;,SELECTcount(*),avg(sal) INTO p_num,p_avg,FROM emp WHEREdeptno=p_deptno;,RETURNv_dname;,END ret_maxsal;,21,（,2,）函数的调用,在,SQL,语句中调用函数,在,PL/SQL,中调用函数,注意,函数只能作为表,达,达式的一部分被,调,调用。,示例,通过,return_maxsal,函数的调用，输,出,出各个部门的最,高,高工资；通过,ret_deptinfo,函数调用，输出,各,各个部门名、部,门,门人数及平均工,资,资。,22,DECLARE,v_maxsal emp.sal%TYPE;,v_avgsal emp.sal%TYPE;,v_numNUMBER;,v_dnamedept.dname%TYPE;,BEGIN,FOR v_dept IN(SELECTDISTINCT deptno FROMemp) LOOP,v_maxsal:=ret_maxsal(v_dept.deptno);,v_dname:=ret_deptinfo(v_dept.deptno,v_num,v_avgsal);,DBMS_OUTPUT.PUT_LINE(v_dname| |v_maxsal| |,v_avgsal| |v_num);,END LOOP;,END;,23,函数可以在,SQL,语句的以下部分,调,调用：,SELECT,语句的目标列；,WHERE,和,HAVING,子句；,CONNECTBY,，,START WITH,，,ORDER BY,，,GROUP BY,子句；,INSERT,语句的,VALUES,子句中；,UPDATE,语句的,SET,子句中。,24,如果要在,SQL,中调用函数，那,么,么函数必须符合,下,下列限制和要求,：,：,在,SELECT,语句中的函数不,能,能修改（,INSERT,，,UPDATE,，,DELETE,）调用函数的,SQL,语句中使用的表,；,；,函数在一个远程,或,或并行操作中使,用,用时，不能读,/,写封装变量；,函数必须是一个,存,存储数据库对象,（,（或存储在包中,）,）；,函数的参数只能,使,使用,IN,模式；,形式参数类型必,须,须使用数据库数,据,据类型；,返回的数据类型,必,必须是数据库数,据,据类型；,25,（,3,）函数的管理,函数的修改,CREATEOR REPLACE FUNCTION function_name,查看函数及其源,代,代码,查询数据字典视,图,图,USER_SOURCE,SELECTname,text FROMuser_source,WHERE type=FUNCTION;,函数重编译,ALTER FUNCTION,COMPILE,ALTER FUNCTIONret_maxsal COMPILE;,删除函数,DROP FUNCTION,DROP FUNCTIONret_maxsal;,26,1,5,.,1,.3,局部子程序,局部子程序,嵌套在其他,PL/SQL,块中的子程序。,只能在其定义的,块,块内部被调用，,而,而不能在其父块,外,外被调用。,使用局部子程序,时,时需要注意：,局部子程序只在,当,当前语句块内有,效,效；,局部子程序必须,在,在,PL/SQL,块声明部分的最,后,后进行定义；,局部子程序必须,在,在使用之前声明,，,，如果是子程序,间,间相互引用，则,需,需要采用预先声,明,明；,局部子程序可以,重,重载。,27,在一个块内部定,义,义一个函数和一,个,个过程。函数以,部,部门号为参数返,回,回该部门的平均,工,工资；过程以部,门,门号为参数，输,出,出该部门中工资,低,低于部门平均工,资,资的员工的员工,号,号、员工名。,28,DECLARE,v_deptno emp.deptno%TYPE;,v_avgsal emp.sal%TYPE;,FUNCTION return_avgsal(p_deptno emp.deptno%TYPE),RETURNemp.sal%TYPE,AS,v_sal emp.sal%TYPE;,BEGIN,SELECTavg(sal) INTOv_sal FROM emp,WHEREdeptno=p_deptno;,RETURNv_sal;,END return_avgsal;,29,PROCEDURE show_emp(p_deptnoemp.deptno%TYPE),AS,CURSORc_emp IS,SELECT* FROMemp WHERE deptno=p_deptno;,BEGIN,FOR v_emp IN c_emp LOOP,IF v_emp.salreturn_avgsal(v_emp.deptno) THEN,DBMS_OUTPUT.PUT_LINE(v_emp.empno|,v_emp.ename);,END IF;,END LOOP;,END show_emp;,BEGIN,v_deptno:=,v_avgsal:=return_avgsal(v_deptno);,show_emp(v_deptno);,END;,30,存储子程序与,局,局部子程序区,别,别在于：,存储子程序己,经,经编译好放在,数,数据库服务器,端,端，可以直接,调,调用，而局部,子,子程序存在于,定,定义它的语句,块,块中，在运行,时,时先进行编译,；,；,存储子程序不,能,能重载，而局,部,部子程序可以,进,进行重载；,存储子程序可,以,以被任意的,PL/SQL,块调用，而局,部,部子程序只能,在,在定义它的块,中,中被调用。,31,在一个,PL/SQL,块中重载两个,过,过程，一个以,员,员工号为参数,，,，输出该员工,信,信息；另一个,以,以员工名为参,数,数，输出员工,信,信息。利用这,两,两个过程分别,查,查询员工号为,7902,，,7934,，以及员工名,为,为,SMITH,，,FORD,的员工信息,。,。,32,DECLARE,PROCEDURE show_empinfo(p_empnoemp.empno%TYPE),AS,v_empemp%ROWTYPE;,BEGIN,SELECT* INTO v_emp FROMemp,WHEREempno=p_empno;,DBMS_OUTPUT.PUT_LINE(v_emp.ename|,v_emp.deptno);,EXCEPTION,WHEN NO_DATA_FOUNDTHEN,DBMS_OUTPUT.PUT_LINE(There isnot such an,employee!);,END show_empinfo;,33,PROCEDURE show_empinfo(p_enameemp.ename%TYPE),AS,v_empemp%ROWTYPE;,BEGIN,SELECT* INTO v_emp FROMemp,WHEREename=p_ename;,DBMS_OUTPUT.PUT_LINE(v_emp.empno|,v_emp.deptno);,EXCEPTION,WHEN NO_DATA_FOUNDTHEN,DBMS_OUTPUT.PUT_LINE(There isnot such an,employee!);,WHEN TOO_MANY_ROWSTHEN,DBMS_OUTPUT.PUT_LINE(There are morethanone,employee!);,END show_empinfo;,34,BEGIN,show_empinfo(7902);,show_empinfo(7934);,show_empinfo(SMITH);,show_empinfo(FORD);,END ;,35,15.2,包,包概述,包的创建,包的调用,包重载,包的初始化,包的管理,36,包概述,包是包含一个,或,或多个子程序,单,单元（过程、,函,函数等）的容,器,器。,包是一种全局,结,结构 。,包类型,数据库内置包,用户创建的包,包构成,包规范,包体两,37,包规范声明了,软,软件包中所有,内,内容，如过程,、,、函数、游标,、,、类型、异常,和,和变量等，其,中,中过程和函数,只,只包括原型信,息,息，不包含任,何,何子程序代码,。,。,包体中包含了,在,在包头中的过,程,程和函数的实,现,现代码。包体,中,中还可以包括,在,在规范中没有,声,声明的变量、,游,游标、类型、,异,异常、过程和,函,函数，但是它,们,们是私有元素,，,，只能由同一,包,包体中其他过,程,程和函数使用,。,。,38,1,5,.,2,.1,包的创建,创建包规范,创建包体,39,（,1,）创建包规范,语法,CREATEOR REPLACEPACKAGE package_name,IS|AS,PRAGMA SERIALLY_RESUABLE,type_definition|variable_declaration|,exception_declaration|cursor_declaration|,procedure_ declaration|function_ declaration,END package_name;,40,注意：,元素声明的顺,序,序可以是任意,的,的，但必须先,声,声明后使用；,所有元素是可,选,选的；,过程和函数的,声,声明只包括原,型,型，不包括具,体,体实现。,41,创建一个软件,包,包，包括,2,个变量、,2,个过程和,1,个异常。,CREATEOR REPLACEPACKAGE pkg_emp,AS,minsalNUMBER;,maxsalNUMBER;,e_beyondbound EXCEPTION;,PROCEDURE update_sal(,p_empno NUMBER, p_sal NUMBER);,PROCEDURE add_employee(,p_empno NUMBER,p_sal NUMBER);,END pkg_emp;,42,语法,CREATEOR REPLACEPACKAGE BODYpackage_name,IS|AS,PRAGMA SERIALLY_RESUABLE,type_definition|variable_declaration|,exception_declaration|,cursor_declaration|,procedure_definition |,function_definition,END package_name;,（,2,）创建包体,43,注意：,包体中函数和,过,过程的原型必,须,须与包规范中,的,的声明完全一,致,致；,只有在包规范,已,已经创建的条,件,件下，才可以,创,创建包体；,如果包规范中,不,不包含任何函,数,数或过程，则,可,可以不创建包,体,体。,44,CREATEOR REPLACEPACKAGE BODYpkg_emp,AS,PROCEDURE update_sal(p_empno NUMBER,p_salNUMBER),AS,BEGIN,SELECTmin(sal), max(sal) INTOminsal,maxsal FROM emp;,IF p_sal BETWEEN minsalAND maxsal THEN,UPDATEemp SET sal=p_salWHEREempno=p_empno;,IF SQL%NOTFOUND THEN,RAISE_APPLICATION_ERROR(-20000,Theemployee,doesnt exist);,END IF;,ELSE,RAISEe_beyondbound;,END IF;,EXCEPTION,WHEN e_beyondboundTHEN,DBMS_OUTPUT.PUT_LINE(The salary isbeyondbound! );,END update_sal;,45,PROCEDURE add_employee(p_empnoNUMBER,p_sal NUMBER),AS,BEGIN,SELECTmin(sal), max(sal) INTOminsal,maxsal FROM emp;,IF p_sal BETWEEN minsalAND maxsal THEN,INSERTINTOemp(empno,sal) VALUES(p_empno,p_sal);,ELSE,RAISEe_beyondbound;,END IF;,EXCEPTION,WHEN e_beyondboundTHEN,DBMS_OUTPUT.PUT_LINE(The salary isbeyondbound! );,END add_employee;,END pkg_emp;,46,1,5,.,2,.2,包的调用,在包规范声明,的,的任何元素是,公,公有的，在包,外,外都是可见的,包外：通过,package.element,形式调用；,包内,：,：直,接,接通,过,过元,素,素名,进,进行,调,调用,。,。,在包,体,体中,定,定义,而,而没,有,有在,包,包头,中,中声,明,明的,元,元素,是,是私,有,有的,，,，只,能,能在,包,包体,中,中引,用,用,47,调用,软,软件,包,包,pkg_emp,中的,过,过程,update_sal,，修,改,改,7844,员工,工,工资,为,为,3000,。调,用,用,add_employee,添加,一,一个,员,员工,号,号为,1357,，工,资,资为,4000,的员,工,工。,BEGIN,pkg_emp.update_sal(7844,3000);,pkg_emp.add_employee(1357,4000);,END;,48,1,5,.,2,.3,包重,载,载,重载,子,子程,序,序必,须,须同,名,名不,同,同参,，,，即,名,名称,相,相同,，,，参,数,数不,同,同。,参,参数,不,不同,体,体现,为,为参,数,数的,个,个数,、,、顺,序,序、,类,类型,等,等不,同,同。,如果,两,两个,子,子程,序,序参,数,数只,是,是名,称,称和,模,模式,不,不同,，,，则,不,不能,重,重载,。,。,PROCEDUREoverloadme(parameter1INNUMBER);,PROCEDUREoverloadme(parameter2OUTNUMBER);,不能,根,根据,两,两个,函,函数,返,返回,类,类型,不,不同,而,而对,它,它们,进,进行,重,重载,。,。,FUNCTIONoverloadmeRETURNDATE;,FUNCTIONoverloadmeRETURNNUMBER;,重载,子,子程,序,序参,数,数必,须,须在,类,类型,系,系列,方,方面,有,有所,不,不同,。,。,PROCEDUREoverloadchar(parameterINCHAR);,PROCEDUREoverloadchar(parameterINVARCHAR2);,49,在一,个,个包,中,中重,载,载两,个,个过,程,程，,分,分别,以,以部,门,门号,和,和部,门,门名,称,称为,参,参数,，,，查,询,询相,应,应部,门,门员,工,工名,、,、员,工,工号,信,信息,。,。,50,CREATEORREPLACEPACKAGEpkg_overload,AS,PROCEDUREshow_emp(p_deptnoNUMBER);,PROCEDUREshow_emp(p_dnameVARCHAR2);,ENDpkg_overload;,51,CREATEORREPLACEPACKAGEBODYpkg_overload,AS,PROCEDUREshow_emp(p_deptnoNUMBER),AS,BEGIN,FORv_empIN(SELECT*FROMempWHEREdeptno=p_deptno)LOOP,DBMS_OUTPUT.PUT_LINE(v_emp.empno|,v_emp.ename);,ENDLOOP;,ENDshow_emp;,52,PROCEDUREshow_emp(p_dnameVARCHAR2),AS,v_deptnoNUMBER;,BEGIN,SELECTdeptnoINTOv_deptnoFROMdept,WHEREdname=p_dname;,FORv_empIN(SELECT*FROMemp,WHEREdeptno=v_deptno)LOOP,DBMS_OUTPUT.PUT_LINE(v_emp.empno|,v_emp.ename);,ENDLOOP;,ENDshow_emp;,ENDpkg_overload;,53,1,5,.,2,.4,包的,初,初始,化,化,包在,第,第一,次,次被,调,调用,时,时从,磁,磁盘,读,读取,到,到共,享,享池,，,，并,在,在整,个,个会,话,话的,持,持续,期,期间,保,保持,。,。在,此,此过,程,程中,，,，可,以,以自,动,动执,行,行一,个,个初,始,始化,过,过程,，,，对,软,软件,包,包进,行,行实,例,例化,。,。,包的,初,初始,化,化过,程,程只,在,在包,第,第一,次,次被,调,调用,时,时执,行,行，,因,因此,也,也称,为,为一,次,次性,过,过程,，,，它,是,是一,个,个匿,名,名的,PL/SQL,块，,在,在包,体,体结,构,构的,最,最后,，,，以,BEGIN,开始,。,。,示例,在,pkg_emp,包中,，,，在,包,包初,始,始化,时,时给,minsal,和,maxsal,两个,变,变量,赋,赋值,，,，在,子,子程,序,序中,直,直接,引,引用,这,这两,个,个变,量,量。,54,CREATE OR REPLACE PACKAGE pkg_emp,AS,minsalNUMBER;,maxsalNUMBER;,e_beyondbound EXCEPTION;,PROCEDURE update_sal(,p_empnoNUMBER,p_sal NUMBER);,PROCEDURE add_employee(,p_empnoNUMBER,p_salNUMBER);,ENDpkg_emp;,55,CREATE OR REPLACE PACKAGE BODY pkg_emp,AS,PROCEDURE update_sal(p_empnoNUMBER,p_sal NUMBER),AS,BEGIN,IF p_salBETWEENminsalANDmaxsal THEN,UPDATE emp SET sal=p_salWHERE empno=p_empno;,IF SQL%NOTFOUNDTHEN,RAISE_APPLICATION_ERROR(-20000,Theemployee,doesntexist);,ENDIF;,ELSE,RAISE e_beyondbound;,ENDIF;,EXCEPTION,WHENe_beyondbound THEN,DBMS_OUTPUT.PUT_LINE(The salaryisbeyond bound!);,ENDupdate_sal;,56,PROCEDURE add_employee(p_empno NUMBER,p_salNUMBER),AS,BEGIN,IF p_salBETWEENminsalANDmaxsal THEN,INSERT INTOemp(empno,sal) VALUES(p_empno,p_sal);,ELSE,RAISE e_beyondbound;,ENDIF;,EXCEPTION,WHENe_beyondbound THEN,DBMS_OUTPUT.PUT_LINE(The salaryisbeyond bound!);,ENDadd_employee;,BEGIN,SELECT min(sal),max(sal) INTO minsal,maxsalFROM emp;,ENDpkg_emp;,57,1,5,.,2,.,5,包的管理,包的修改,CREATE OR REPLACE PACKAGE package_name,查看包及,其,其源代码,查询数据,字,字典视图,USER_SOURCE,SELECT name,textFROM user_source WHEREtype=PACKAGE;,SELECT name,textFROM user_source WHEREtype=PACKAGE BODY;,重新编译,包,包,ALTER PACKAGE,COMPILE,（包规范,和,和包体）,ALTER PACKAGE,COMPILESPECIFICATION,（包规范,）,）,ALTER PACKAGE,COMPILEBODY,（包体）,58,ALTER PACKAGE pkg_emp COMPILE;,ALTERPACKAGEpkg_empCOMPILESPECIFICATION;,ALTER PACKAGE pkg_emp COMPILE BODY;,删除包,DROPPACKAGE,（包规范,和,和包体）,DROPPACKAGEBODY,（包体）,DROPPACKAGEBODY pkg_emp;,DROPPACKAGEpkg_emp;,59,15.3,触发器,触发器概,述,述,DML,触发器,INSTEAD-OF,触发器,系统触发,器,器,变异表触,发,发器,触发器的,管,管理,60,15,.,3,.1,触发器概,述,述,触发器的,概,概念与作,用,用,触发器的,类,类型,触发器组,成,成,61,（,1,）触发器,的,的概念与,作,作用,触发器是,一,一种特殊,类,类型的存,储,储过程，,编,编译后存,储,储在数据,库,库服务器,中,中。,当特定事,件,件发生时,，,，由系统,自,自动调用,执,执行，而,不,不能由应,用,用程序显,式,式地调用,执,执行。,触发器不,接,接受任何,参,参数。,触发器主,要,要用于维,护,护那些通,过,过创建表,时,时的声明,约,约束不可,能,能实现的,复,复杂的完,整,整性约束,，,，并对数,据,据库中特,定,定事件进,行,行监控和,响,响应。,62,（,2,）触发器,的,的类型,DML,触发器,建立在基,本,本表上的,触,触发器，,响,响应基本,表,表的,INSERT,，,UPDATE,，,DELETE,操作。,INSTEADOF,触发器,建立在视,图,图上的触,发,发器，响,应,应视图上,的,的,INSERT,，,UPDATE,，,DELETE,操作。,系统触发,器,器,建立在系,统,统或模式,上,上的触发,器,器，响应,系,系统事件,和,和,DDL,（,CREATE,，,ALTER,，,DROP,）操作。,63,（,3,）触发器,组,组成,触发器由,触,触发器头,部,部和触发,器,器体两个,部,部分组成,，,，主要包,括,括：,作用对象,：,：触发器,作,作用的对,象,象包括表,、,、视图、,数,数据库和,模,模式。,触发事件,：,：激发触,发,发器执行,的,的事件。,如,如,DML,、,DDL,、数据库,系,系统事件,等,等。,触发时间,：,：用于指,定,定触发器,在,在触发事,件,件完成之,前,前还是之,后,后执行。,如,如果指定,为,为,AFTER,，则表示,先,先执行触,发,发事件，,然,然后再执,行,行触发器,；,；如果指,定,定为,BEFORE,，则表示,先,先执行触,发,发器，然,后,后再执行,触,触发事件,。,。,64,触发级别,：,：触发级,别,别用于指,定,定触发器,响,响应触发,事,事件的方,式,式。默认,为,为语句级,触,触发器，,即,即触发事,件,件发生后,，,，触发器,只,只执行一,次,次。如果,指,指定为,FOREACHROW,，即为行,级,级触发器,，,，则触发,事,事件每作,用,用于一个,记,记录，触,发,发器就会,执,执行一次,。,。,触发条件,：,：由,WHEN,子句指定,一,一个逻辑,表,表达式，,当,当触发事,件,件发生，,而,而且,WHEN,条件为,TRUE,时，触发,器,器才会执,行,行。,触发操作,：,：触发器,执,执行时所,进,进行的操,作,作。,65,1,5,.,3,.2,DML,触发器,DML,触发器的,种,种类及执,行,行顺序,创建,DML,触发器,66,（,1,）,DML,触发器的,种,种类及执,行,行顺序,DML,触发器的,种,种类,语句级前,触,触发器,语句级后,触,触发器,行级前触,发,发器,行级后触,发,发器,67,DML,触发器的,执,执行顺序,如果存在,，,，则执行,语,语句级前,触,触发器。,对于受触,发,发事件影,响,响的每一,个,个记录：,如果存在,，,，则执行,行,行级前触,发,发器；,执行当前,记,记录的,DML,操作（触,发,发事件）,；,；,如果存在,，,，则执行,行,行级后触,发,发器。,如果存在,，,，则执行,语,语句级后,触,触发器。,68,（,2,）创建,DML,触发器,语法,CREATE OR REPLACETRIGGERtrigger_name,BEFORE|AFTERtriggering_event OF column_name,ON table_name,FOREACH ROW,WHEN trigger_condition,DECLARE,/*Declarative section ishere */,BEGIN,/*Exccutablesectionsihere*/,EXCEPTION,/*Exceptionsection is here*/,ENDtrigger_name;,Trigger_body,69,语句级,触,触发器,在默认,情,情况下,创,创建的,DML,触发器,为,为语句,级,级触发,器,器，即,触,触发事,件,件发生,后,后，触,发,发器只,执,执行一,次,次。,70,创建一,个,个触发,器,器，禁,止,止在休,息,息日改,变,变雇员,信,信息,CREATEORREPLACETRIGGER trg_emp_weekend,BEFOREINSERT OR UPDATEORDELETEONemp,BEGIN,IFTO_CHAR(SYSDATE, DY, nls_date_language=,american) IN(SAT,SUN),THEN,raise_application_error(-20000, Cantoperate,inweekend.);,ENDIF;,ENDtrg_emp_weekend;,71,如果触,发,发器响,应,应多个,DML,事件，,而,而且需,要,要根据,事,事件的,不,不同进,行,行不同,的,的操作,，,，则可,以,以在触,发,发器体,中,中使用,3,个条件,谓,谓词。,谓词,行为,INSERTING,如果触发语句是,INSERT,，则为,TRUE,；,否则为,FALSE,UPDATING,如果触发语句是,UPDATE,，则为,TRUE,；,否则为,FALSE,DELETING,如果触发语句是,DELETE,，则为,TRUE,；,否则为,FALSE,72,为,emp,表创建,一,一个触,发,发器，,当,当执行,插,插入操,作,作时，,统,统计操,作,作后员,工,工人数,；,；当执,行,行更新,工,工资操,作,作时，,统,统计更,新,新后员,工,工平均,工,工资；,当,当执行,删,删除操,作,作时，,统,统计删,除,除后各,个,个部门,的,的人数,。,。,73,CREATEORREPLACETRIGGER trg_emp_dml,AFTERINSERTORUPDATE OR DELETEONemp,DECLARE,v_count NUMBER;,v_salNUMBER(6,2);,BEGIN,IFINSERTINGTHEN,SELECTcount(*)INTOv_count FROMemp;,DBMS_OUTPUT.PUT_LINE(v_count);,ELSIFUPDATINGTHEN,SELECTavg(sal)INTOv_salFROM emp;,DBMS_OUTPUT.PUT_LINE(v_sal);,ELSE,FORv_dept IN (SELECTdeptno,count(*) numFROM emp,GROUPBYdeptno) LOOP,DBMS_OUTPUT.PUT_LINE(v_dept.deptno| |v_dept.num);,ENDLOOP;,ENDIF;,ENDtrg_emp_dml;,74,行级触,发,发器,行级触,发,发器是,指,指执行,DML,操作时,，,，每操,作,作一个,记,记录，,触,触发器,就,就执行,一,一次，,一,一个,DML,操作涉,及,及多少,个,个记录,，,，触发,器,器就执,行,行多少,次,次。,在行级,触,触发器,中,中可以,使,使用,WHEN,条件，,进,进一步,控,控制触,发,发器的,执,执行。,在行级,触,触发器,中,中引入,了,了,:old,和,:new,两个标,识,识符，,来,来访问,和,和操作,当,当前被,处,处理记,录,录中的,数,数据。,75,标识符,:old,和,:new,作为,triggering_table%ROWTYPE,类型的,两,两个变,量,量,在不同,触,触发事,件,件中，,:old,和,:new,的意义,不,不同,触发事件,:old,:new,INSERT,未定义，所有字段都为,NULL,当语句完成时，被插入的记录,UPDATE,更新前原始记录,当语句完成时，更新后的记录,DELETE,记录被删除前的原始值,未定义，所有字段都为,NULL,76,引用方,式,式,：,:old.field,和,:new.field,（执行,部,部分）,old.field,和,new.field(WHEN,条件中,),注意事,项,项：,是伪记,录,录，不,能,能作为,整,整个记,录,录进行,赋,赋值或,引,引用,不能传,递,递给带,triggering_table%ROWTYPE,参数的,过,过程和,函,函数,如果触,发,发器是,建,建立在,嵌,嵌套表,上,上，,;old,和,;new,都执行,嵌,嵌套表,的,的行，,:,parent,指向父,表,表中的,当,当前行,。,。,77,为,emp,表创建,一,一个触,发,发器，,当,当插入,新,新员工,时,时显示,新,新员工,的,的员工,号,号、员,工,工名；,当,当更新,员,员工工,资,资时，,显,显示修,改,改前后,员,员工工,资,资；当,删,删除员,工,工时，,显,显示被,删,删除的,员,员工号,、,、员工,名,名。,78,CREATEORREPLACETRIGGER trg_emp_dml_row,BEFOREINSERT OR UPDATEORDELETEONemp,FOREACHROW,BEGIN,IFINSERTINGTHEN,DBMS_OUTPUT.PUT_LINE(:new.empno|,:new.ename);,ELSIFUPDATINGTHEN,DBMS_OUTPUT.PUT_LINE(:old.sal| |:new.sal);,ELSE,DBMS_OUTPUT.PUT_LINE(:old.empno|,:old.ename);,END IF;,END trg_emp_dml_row;,79,在行级触发,器,器中，可以,使,使用,WHEN,子句进一步,控,控制触发器,的,的执行。,例如，修改,员,员工工资时,，,，保证修改,后,后的工资高,于,于修改前的,工,工资。,CREATE ORREPLACE TRIGGERtrg_emp_update_row,BEFORE UPDATE OF salON emp,FOR EACH ROW,WHEN(new.sal=old.sal),BEGIN,RAISE_APPLICATION_ERROR(,-20001,The salary is lower!);,END trg_emp_update_row;,80,1,5,.,3,.3INSTEADOF,触发器,特点,只能定义在,视,视图上,Instead-of,触发器是行,级,级触发器,Instead-of,触发器由,DML,操作激发，,而,而,DML,操作本身并,不,不执行,作用,修改一个本,来,来不可以修,改,改的视图,修改视图中,某,某嵌套表列,的,的列,81,如果视图中,包,包含下列任,何,何一项，则,该,该视图不可,修,修改,集合操作符,（,UNION,，,UNIONALL,，,MINUS,，,INTERSECT,）；,聚集函数（,SUM,，,AVG,等）；,GROUPBY,，,CONNECT BY,或,STARTWITH,子句,；,DISTINCT,操作符；,涉及多个表,的,的连接操作,。,。,82,创建,INSTEAD OF,触发器的基,本,本语法,CREATE ORREPLACETRIGGER trigger_name,INSTEAD OFtriggering_event OF column_name,ON view_name,FOR EACH ROW,WHENtrigger_condition,DECLARE,/*Declarative section ishere*/,BEGIN,/*Exccutable sectionsi here*/,EXCEPTION,/*Exception sectionis here*/,END trigger_name;,83,创建一个包,括,括员工及其,所,所在部门信,息,息的视图,empdept,，然后向视,图,图中插入一,条,条记录（,2345,TOM,3000,SALES,）。,CREATE ORREPLACE VIEW empdept,AS,SELECT empno,ename,sal,dname,FROMemp,dept WHEREemp.deptno=dept.deptno,WITHCHECKOPTION;,INSERT INTO empdept,VALUES(2345, TOM,3000,SALES);,*,ERROR,位于第,1,行,:,ORA-01733:,此处不允许,虚,虚拟列,84,CREATE ORREPLACE TRIGGERtrig_view,INSTEAD OFINSERT ONempdept,FOR EACH ROW,DECLARE,v_deptno dept.deptno%type;,BEGIN,SELECT deptno INTO v_deptno,FROMdeptWHEREdname=:new.dname;,INSERT INTO emp(empno,ename,sal,deptno),VALUES(:new.empno,:new.ename,v_deptno,:new.sal);,END trig_view;,85,1,5,.,3,.4,系统触发器,触发器事件,创建系统触,发,发器,86,（,1,）触发事件,DDL,事件,CREATE,，,ALTER,，,DROP,，,RENAME,，,GRANT,，,REVOKE,，,AUDIT,，,NOAUDIT,，,COMMENT,，,TRUNCATE,，,ANALYZE,，,ASSOCIATESTATISTICS,，,DISASSOCIATE,STATISTICS,等。,触发时间可,以,以是,BEFORE,，也可以是,AFTER,。,数据库事件,STARTUP,，,SHUTDOWN,，,SERVERERROR,，,LOGON,，,LOGOFF,等。,触发时间由,具,具体事件决,定,定，,87,事件,允许计时,描述,STARTUP,AFTER,当实例开始时激发,SHUTDOWN,BEFORE,当实例关闭时激发,SERVERERROR,AFTER,只要错误发生就激发,LOGON,AFTER,在一个用户成功连接数据库时触发,LOGOFF,BEFORE,在用户注销时开始激发,数据库事件,的,的触发时间,88,语法,CREATE ORREPLACETRIGGER trigger_name,BEFORE|AFTER ddl_event_list|database_event_list,ON DATABASE|SCHEMA,WHENtrigger_condition,DECLARE,/*Declarative section ishere*/,BEG