1、第第12章章 存儲過程、函數、包、觸發存儲過程、函數、包、觸發器器黑龍江大學黑龍江大學計算機科學技術學院計算機科學技術學院2存儲子程序存儲子程序u存儲過程存儲過程u函數函數u局部子程序局部子程序3存儲子程序存儲子程序u存儲子程序是指被存儲子程序是指被命名的命名的PL/SQLPL/SQL塊塊，以編，以編譯的形式存儲在數據庫服務器中，可以在譯的形式存儲在數據庫服務器中，可以在應用程序中進行調用，是應用程序中進行調用，是PL/SQLPL/SQL程序模塊程序模塊化的一種體現。化的一種體現。u存儲子程序是以存儲子程序是以獨立對象的形式存儲在數獨立對象的形式存儲在數據庫服務器中據庫服務器中，因此是一種全局

2、結構因此是一種全局結構u與之對應的是與之對應的是局部子程序局部子程序，即，即嵌套在嵌套在PL/SQLPL/SQL塊中的局部過程和函數塊中的局部過程和函數，其存儲位，其存儲位置取決于其所在的父塊的位置。置取決于其所在的父塊的位置。 412.1 存儲過程存儲過程u存儲過程的創建存儲過程的創建u存儲過程的調用存儲過程的調用u存儲過程的管理存儲過程的管理5存儲過程的創建存儲過程的創建CREATE OR REPLACE PROCEDURE procedure_name (parameter1_name mode datatype DEFAULT|:=value , parameter2_name mod

3、e datatype DEFAULT|:=value,)AS|IS /*Declarative section is here */BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ ENDprocedure_name; PROCEDURE BODY6MODEu參數模式參數模式vIN 當過程被調用時，實參值被傳遞給過程。在過程內，該當過程被調用時，實參值被傳遞給過程。在過程內，該參數起參數起常數常數 作用作用，可讀不可寫可讀不可寫。調用結束，。調用結束，實參值不變實參值不變。（默認參數類型）。

4、（默認參數類型）vOUT 當過程被調用時，當過程被調用時，實參值被忽略實參值被忽略。在過程內，該參數起。在過程內，該參數起未初始化的變量作用，未初始化的變量作用，值為值為NULL。過程內，過程內，該參數可該參數可讀可寫。讀可寫。 調用結束，形參賦給實參調用結束，形參賦給實參。vIN OUT 當過程被調用時，實參值被傳遞給過程。在過程內，當過程被調用時，實參值被傳遞給過程。在過程內，該該參數起已初始化變量作用參數起已初始化變量作用，過程內，該參數可讀可寫。，過程內，該參數可讀可寫。調用結束，調用結束，形參賦給實參形參賦給實參。7u參數限制參數限制v聲明形參時聲明形參時不能不能 定義定義形參的形參

5、的長度或精度（如長度或精度（如：varchar(2)，number（4，2）vOUT、IN OUT參數為參數為值傳遞值傳遞，即實參的值被復，即實參的值被復制給形參。制給形參。 u說明：說明：v通常，存儲過程不需要返回值，如果需要返回通常，存儲過程不需要返回值，如果需要返回一個值可以通過函數調用實現。一個值可以通過函數調用實現。v但是，如果希望返回多個值，可以使用但是，如果希望返回多個值，可以使用OUT或或IN OUT模式模式參數來實現。參數來實現。存儲過程參數存儲過程參數8u創建一個存儲過程，以部門號為參數，查詢該部門的創建一個存儲過程，以部門號為參數，查詢該部門的平均工資，并輸出該部門中比平

6、均工資高的員工號、平均工資，并輸出該部門中比平均工資高的員工號、員工名。員工名。存儲過程舉例存儲過程舉例CREATE OR REPLACE PROCEDURE show_emp ( p_deptno emp.deptno%TYPE) AS v_sal emp.sal%TYPE;BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; DBMS_OUTPUT.PUT_LINE(p_deptno| |average salary is:|v_sal); FOR v_emp IN (SELECT * FROM emp WHERE

7、 deptno=p_deptno AND salv_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename); END LOOP;EXCEPTION WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(The department doesnt exists!);END show_emp;9創建一個存儲過程，以部門號為參數，返回該部門的人數和最創建一個存儲過程，以部門號為參數，返回該部門的人數和最高工資。高工資。CREATE OR REPLACE PROCEDURE return_deptinfo(

8、p_deptno emp.deptno%TYPE, p_avgsal OUT emp.sal%TYPE, p_count OUT emp.sal%TYPE )ASBEGIN SELECT avg(sal),count(*) INTO p_avgsal,p_count FROM emp WHERE deptno=p_deptno;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department dont exists!);END return_deptinfo; 存儲過程舉例存儲過程舉例10存儲過程的調用存儲過程的調用u在

9、在PL/SQL塊中調用塊中調用BEGIN procedure_name(parameter_list);END；11存儲過程舉例存儲過程舉例DECLARE v_avgsal emp.sal%TYPE; v_count NUMBER;BEGIN show_emp(20); return_deptinfo(10,v_avgsal,v_count); DBMS_OUTPUT.PUT_LINE(v_avgsal| |v_count);END; 12存儲過程的調用存儲過程的調用u在在SQL*PLUS中調用中調用EXEC procedure_name(parameter_list)EXECUTE show

10、_emp(10)u綁定變量綁定變量v 定義：定義：VARIABLE variable_name TYPEv 應用：應用： :variable_nameu示例示例vVARIABLE v_deptno NUMBERvEXECUTE :v_deptno :=10執行結果：執行結果：1. PRINT v_deptno3.3 SQL *Plus14存儲過程的調用存儲過程的調用u在在SQL*PLUS中利用綁定變量調用存儲過中利用綁定變量調用存儲過程程vVARIABLE v_deptno NUMBERvEXECUTE :v_deptno :=10vEXECUTE show_emp(:v_deptno)v P

11、RINT v_deptno -如果如果v_deptno得得mode是是out或或in out15存儲過程的管理存儲過程的管理u修改存儲過程修改存儲過程CREATE CREATE OR REPLACE OR REPLACE PROCEDUREPROCEDUREu重新編譯存儲過程重新編譯存儲過程ALTER PROCEDURE procedure_name ALTER PROCEDURE procedure_name COMPILECOMPILE；u刪除存儲過程刪除存儲過程DROP PROCEDURE DROP PROCEDURE procedure_nameprocedure_name；u查看過程

12、源代碼查看過程源代碼select text from user_source where name=procedure_nameprocedure_name ;1612.2 函數函數 u函數概述函數概述u函數的創建函數的創建u函數的調用函數的調用u函數的管理函數的管理17函數概述函數概述u函數用于函數用于返回特定數據返回特定數據，可以返回一個或多，可以返回一個或多個值。個值。u在一個函數中必須包含在一個函數中必須包含一個或多個一個或多個RETURN 語句語句u函數調用是函數調用是PL/SQL表達式表達式的一部分的一部分，而過，而過程調用可以是一個程調用可以是一個獨立的獨立的PL/SQL語句語句

13、 18函數的創建函數的創建CREATE OR REPLACE FUNCTION function_name (parameter1_name mode datatype DEFAULT|:=value, parameter2_name in | out | in out datatype DEFAULT|:=value,)RETURN return_datatype AS |IS /*Declarative section is here */BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/

14、 END function_name;19u創建一個以部門號為參數，返回該部門最高工資的函創建一個以部門號為參數，返回該部門最高工資的函數。數。CREATE OR REPLACE FUNCTION return_maxsal (p_deptno emp.deptno%TYPE)RETURN emp.sal%TYPEAS v_maxsal emp.sal%TYPE;BEGIN SELECT max(sal) INTO v_maxsal FROM emp WHERE deptno=p_deptno; RETURN v_maxsal;EXCEPTION WHEN NO_DATA_FOUND THEN

15、 DBMS_OUTPUT.PUT_LINE(The deptno is invalid!);END return_maxsal;函數舉例函數舉例20函數的調用函數的調用u在在SQL語句中調用函數語句中調用函數u在在PL/SQL中調用函數中調用函數21函數的調用函數的調用DECLARE v_sal emp.sal%TYPE;BEGIN FOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOP v_sal:=return_maxsal(v_dept.deptno); DBMS_OUTPUT.PUT_LINE(v_dept.deptno| |v_sal

16、); END LOOP;END; 22函數的調用函數的調用u在在SQL*PLUS中利用綁定變量調用存儲過程中利用綁定變量調用存儲過程vVARIABLE v_deptno NUMBERvVARIABLE v_sal NUMBER(10,2)vEXECUTE :v_deptno :=10vEXECUTE :v_sal:=return_maxsal(:v_deptno)v PRINT v_sal 23u按照部門號查詢該部門的工資總和，同時返回該部門的名稱。按照部門號查詢該部門的工資總和，同時返回該部門的名稱。CREATE OR REPLACE FUNCTION return_sumsal (p_de

17、ptno in emp.deptno%TYPE，p_deptname out dept.dname%type)RETURN emp.sal%TYPEAS v_sumsal emp.sal%TYPE;BEGIN SELECT sum(sal) INTO v_sumsal FROM emp WHERE deptno=p_deptno; select dname into p_deptname from dept where deptno=p_deptno; RETURN v_sumsal;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(

18、The deptno is invalid!);END return_sumsal;函數舉例函數舉例24函數的調用函數的調用DECLARE v_sal emp.sal%TYPE; v_deptname dept.dname%type;BEGIN FOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOP v_sal:=return_sumsal(v_dept.deptno,v_deptname); DBMS_OUTPUT.PUT_LINE(v_dept.deptno| |v_sal| |v_deptname); END LOOP;END; 25函數

19、的管理函數的管理u修改函數修改函數CREATE CREATE OR REPLACE OR REPLACE FUNCTION function_nameFUNCTION function_nameu重新編譯函數重新編譯函數ALTER FUNCTION function_name COMPILEALTER FUNCTION function_name COMPILE；u刪除存儲過程刪除存儲過程DROP FUNCTION function_name DROP FUNCTION function_name ；u查看過程源代碼查看過程源代碼select text from user_source whe

20、re name= function_namefunction_name;26局部子程序局部子程序u局部子程序局部子程序嵌套在嵌套在其他其他PL/SQL塊中的子程序塊中的子程序。只能在其定義的只能在其定義的塊內部被調用塊內部被調用，而不能在其，而不能在其父父塊外被調用塊外被調用。u使用局部子程序時需要注意：使用局部子程序時需要注意：局部子程序局部子程序只在當前語句塊內有效只在當前語句塊內有效；局部子程序局部子程序必須在必須在PL/SQL塊聲明塊聲明部分的最后部分的最后進行定義；進行定義；局部子程序局部子程序必須在使用之前聲明必須在使用之前聲明，如果是子程，如果是子程序間相互引用，則需要采用序間相

21、互引用，則需要采用預先聲明預先聲明；局部子程序局部子程序可以重載可以重載。27舉舉 例例u在一個塊內部在一個塊內部定義一個函數和一個過程定義一個函數和一個過程。u函數以函數以部門號部門號為參數返回該部門的為參數返回該部門的平均工平均工資資；u過程以過程以部門號部門號為參數，輸出該部門中工資為參數，輸出該部門中工資低于部門平均工資低于部門平均工資的員工的員工號、員工的員工的員工號、員工名。名。 28DECLARE v_deptno emp.deptno%TYPE; v_avgsal emp.sal%TYPE; FUNCTION return_avgsal(p_deptno emp.deptno%

22、TYPE) RETURN emp.sal%TYPE AS v_sal emp.sal%TYPE; BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; RETURN v_sal; END return_avgsal; PROCEDURE show_emp(p_deptno emp.deptno%TYPE) AS CURSOR c_emp IS SELECT * FROM emp WHERE salreturn_avgsal(p_deptno); BEGIN FOR v_emp IN c_emp LOOP DBMS_OU

23、TPUT.PUT_LINE(v_emp.empno| |v_emp.ename); END LOOP; END show_emp;BEGIN v_deptno:=&x; v_avgsal:=return_avgsal(v_deptno); show_emp(v_deptno);END;29存儲子程序與局部子程序區別存儲子程序與局部子程序區別u存儲子程序與局部子程序區別在于：存儲子程序與局部子程序區別在于：存儲子程序己經編譯好放在數據庫服務器端，存儲子程序己經編譯好放在數據庫服務器端，可以直接調用，而局部子程序存在于定義它的可以直接調用，而局部子程序存在于定義它的語句塊中，在語句塊中，在

24、運行時先進行編譯運行時先進行編譯；存儲子程序不能重載存儲子程序不能重載，而，而局部子程序可以進行局部子程序可以進行重載重載；存儲子程序存儲子程序可以被任意的可以被任意的PL/SQL塊調用，而塊調用，而局部子程序只能在定義它的塊中被調用局部子程序只能在定義它的塊中被調用。30舉舉 例例u在一個在一個PL/SQL塊中塊中重載兩個過程重載兩個過程:一個以員工號為參數，輸出該員工信息；一個以員工號為參數，輸出該員工信息；一個以員工名為參數，輸出員工信息。一個以員工名為參數，輸出員工信息。u利用這兩個過程分別查詢員工號為利用這兩個過程分別查詢員工號為7902，7934，以及員工名為，以及員工名為SMIT

25、H，FORD的員的員工信息工信息 。31DECLARE PROCEDURE show_empinfo(p_empno emp.empno%TYPE) AS v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE empno=p_empno; DBMS_OUTPUT.PUT_LINE(v_emp.ename| |v_emp.deptno); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There is not such an employee!); END show_

26、empinfo; PROCEDURE show_empinfo(p_ename emp.ename%TYPE) AS v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE ename=p_ename; DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.deptno); 舉舉 例例32EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There is not such an employee!); WHEN TOO_MANY_ROWS TH

27、EN DBMS_OUTPUT.PUT_LINE(There are more than on employee!);END show_empinfo;BEGIN show_empinfo(7902); show_empinfo(7934); show_empinfo(SMITH); show_empinfo(FORD);END ;舉舉 例例3312.3 包包u包概述包概述u包的創建包的創建u包的調用包的調用u包的重載包的重載u包的初始化包的初始化u包的管理包的管理34包概述包概述 u包是包含包是包含一個或多個子程序單元一個或多個子程序單元（過程、（過程、函數等）的容器函數等）的容器u包包是全局

28、的是全局的u包類型包類型數據庫內置包數據庫內置包 用戶創建的包用戶創建的包35u包由包由包規范和包體包規范和包體兩部分組成，在數據庫中兩部分組成，在數據庫中獨立存儲獨立存儲包規范包規范聲明了軟件包中所有內容，如聲明了軟件包中所有內容，如過程過程、函函數數、游標游標、類型類型、異常異常和和變量變量等，其中過程和等，其中過程和函數只包括原型信息，函數只包括原型信息，不包含任何子程序代碼不包含任何子程序代碼。 包體包體中包含了包規范中的中包含了包規范中的過程和函數的實現代過程和函數的實現代碼碼。包規范中還可以包括在規范中。包規范中還可以包括在規范中沒有聲明的沒有聲明的變量、游標、類型、異常、過程和函

29、數變量、游標、類型、異常、過程和函數，但是，但是它們是私有元素，只能由它們是私有元素，只能由同一包體中同一包體中其他過程其他過程和函數使用。和函數使用。包概述包概述 36u語法語法CREATE OR REPLACE PACKAGE package_name IS|AS type_definition|variable_declaration| exception_declaration| cursor_declaration| procedure_definition | function_definitionEND package_name; 創建包規范創建包規范37u注意：注意：v元素聲明

30、的順序可以是任意的，但必須元素聲明的順序可以是任意的，但必須先聲明先聲明后使用后使用；v所有元素是所有元素是可選的可選的；v過程和函數的聲明只包括原型，不過程和函數的聲明只包括原型，不包括具體實包括具體實現現。創建包規范創建包規范38u創建一個軟件包，包括創建一個軟件包，包括2個變量、個變量、2個過程和個過程和1個異常個異常CREATE OR REPLACE PACKAGE pkg_empAS minsal NUMBER; maxsal NUMBER; e_beyondbound EXCEPTION; PROCEDURE update_sal( p_empno NUMBER, p_sal NU

31、MBER); PROCEDURE add_employee( p_empno NUMBER,p_sal NUMBER);END pkg_emp; 包創建實例包創建實例39u語法語法CREATE OR REPLACE PACKAGE BODY package_name IS|AS PRAGMA SERIALLY_RESUABLE type_definition|variable_declaration| exception_declaration| cursor_declaration| procedure_definition | function_definitionEND package_

32、name; 創建包體創建包體40CREATE OR REPLACE PACKAGE BODY pkg_empAS PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER) AS BEGIN SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp; IF p_sal BETWEEN minsal AND maxsal THEN UPDATE emp SET sal=p_sal WHERE empno=p_empno; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERR

33、OR(-20000,The employee doesnt exist); END IF; ELSE RAISE e_beyondbound; END IF; EXCEPTION WHEN e_beyondbound THEN DBMS_OUTPUT.PUT_LINE(The salary is beyond bound!); END update_sal; 包創建實例包創建實例41 PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER) AS BEGIN SELECT min(sal), max(sal) INTO minsal,maxsal

34、FROM emp; IF p_sal BETWEEN minsal AND maxsal THEN INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal); ELSE RAISE e_beyondbound; END IF; EXCEPTION WHEN e_beyondbound THEN DBMS_OUTPUT.PUT_LINE(The salary is beyond bound!); END add_employee;END pkg_emp; 包創建實例包創建實例42包的調用包的調用u概念概念v指軟件包中指軟件包中特定的元素或結構的可視范圍特定

35、的元素或結構的可視范圍。u在軟件在軟件包頭部聲明的任何元素是公有的包頭部聲明的任何元素是公有的，在包外都是可見的在包外都是可見的 。v包外：通過包外：通過package.element形式調用；形式調用；v包內：直接通過包內：直接通過元素名進行調用元素名進行調用。 u在包體中定義而沒有在包頭中聲明的元素在包體中定義而沒有在包頭中聲明的元素是是私有的，私有的，只能在只能在包體中引用包體中引用。 43u調用軟件包調用軟件包pkg_emp中的過程中的過程update_sal，修改，修改7844員工工資為員工工資為3000。調用。調用add_employee添加一添加一個員工號為個員工號為1357，工

36、資為，工資為4000的員工。的員工。BEGIN pkg_emp.update_sal(7844,3000); pkg_emp.add_employee(1357,4000);END;包的調用包的調用44包的重載包的重載u重載子程序必須重載子程序必須同同，即，即名稱相同名稱相同，參數不同參數不同。u如果兩個子程序參數只是如果兩個子程序參數只是名稱和模式名稱和模式不同，則不同，則不能不能重載重載。PROCEDURE overloadme(parameter1 IN NUMBER);PROCEDURE overloadme(parameter2 OUT NUMBER); u不能根據兩個函數不能根據兩

37、個函數返回類型不同返回類型不同對它們進行重載。對它們進行重載。FUNCTION overloadme RETURN DATE;FUNCTION overloadme RETURN NUMBER;u重載子程序參數必須在重載子程序參數必須在類型系列方面有所不同類型系列方面有所不同。PROCEDURE overloadchar(parameter IN CHAR);PROCEDURE overloadchar(parameter IN VARCHAR2);45包包 舉例舉例u在一個包中在一個包中重載兩個過程重載兩個過程，分別以，分別以部門號部門號和部門名稱和部門名稱為為參數參數，查詢相應部門員工名，

38、查詢相應部門員工名、員工號信息。、員工號信息。 46舉例舉例CREATE OR REPLACE PACKAGE pkg_overloadAS PROCEDURE show_emp(p_deptno NUMBER); PROCEDURE show_emp(p_dname VARCHAR2);END pkg_overload; CREATE OR REPLACE PACKAGE BODY pkg_overload AS PROCEDURE show_emp(p_deptno NUMBER) AS BEGIN FOR v_emp IN (SELECT * FROM emp WHERE deptno=

39、p_deptno) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.ename); END LOOP; END show_emp;47 PROCEDURE show_emp(p_dname VARCHAR2) AS v_deptno NUMBER; BEGIN SELECT deptno INTO v_deptno FROM dept WHERE dname=p_dname; FOR v_emp IN (SELECT * FROM emp WHERE deptno=v_deptno) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.

40、empno| | v_emp.ename); END LOOP; END show_emp;END pkg_overload; 舉例舉例48包的初始化包的初始化u包在包在第一次被調用第一次被調用時從磁盤讀取到共享池時從磁盤讀取到共享池，并在整個會話的持續期間保持。在此過，并在整個會話的持續期間保持。在此過程中，可以程中，可以自動執行一個初始化過程自動執行一個初始化過程，對，對軟件包進行實例化軟件包進行實例化。49包的定義和調用包的定義和調用u在在pkg_emp包中，可以在包中，可以在包初始化時給包初始化時給minsal和和maxsal兩個變量賦值兩個變量賦值，而在子程序，而在子程序中直接引用中

41、直接引用這兩個變量這兩個變量。CREATE OR REPLACE PACKAGE pkg_empAS minsal NUMBER; maxsal NUMBER; e_beyondbound EXCEPTION; PROCEDURE update_sal( p_empno NUMBER, p_sal NUMBER); PROCEDURE add_employee( p_empno NUMBER,p_sal NUMBER);END pkg_emp; 50CREATE OR REPLACE PACKAGE BODY pkg_empAS PROCEDURE update_sal(p_empno NUM

42、BER, p_sal NUMBER) AS BEGIN IF p_sal BETWEEN minsal AND maxsal THEN UPDATE emp SET sal=p_sal WHERE empno=p_empno; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20000,The employee doesnt exist); END IF; ELSE RAISE e_beyondbound; END IF; EXCEPTION WHEN e_beyondbound THEN DBMS_OUTPUT.PUT_LINE(The salar

43、y is beyond bound!); END update_sal; 包的定義和調用包的定義和調用51 PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER) AS BEGIN IF p_sal BETWEEN minsal AND maxsal THEN INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal); ELSE RAISE e_beyondbound; END IF; EXCEPTION WHEN e_beyondbound THEN DBMS_OUTPUT.PUT_LINE(The sa

44、lary is beyond bound!); END add_employee;BEGIN SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;END pkg_emp;521. 可以在可以在PL/SQL程序塊中進行調用包的公有變量程序塊中進行調用包的公有變量BEGIN dbms_output.put_line(pkg_emp. minsal, pkg_emp. Maxsal);END; 2.可以在可以在PL/SQL程序塊中進行調用包的公有過程程序塊中進行調用包的公有過程BEGIN pkg_emp. add_employee(9000,9

45、00); END; 包的調用包的調用53包的管理包的管理u包的修改包的修改CREATE OR REPLACE PACKAGE CREATE OR REPLACE PACKAGE 包名包名 u重新編譯包重新編譯包ALTER PACKAGE package_name COMPILE;ALTER PACKAGE package_name COMPILE;ALTER PACKAGE package_name COMPILE BODY;ALTER PACKAGE package_name COMPILE BODY;u刪除包刪除包DROP PACKAGE package_name ;DROP PACKAG

46、E package_name ;DROP PACKAGE BODY package_name ; DROP PACKAGE BODY package_name ; 54u查看包源代碼查看包源代碼select text from user_source where name=EMP_PACKAGE and type=PACKAGE;包的管理包的管理5512.3 觸發器觸發器u 觸發器概述觸發器概述 u DML觸發器觸發器 u INSTEAD-OF觸發器觸發器 u 系統觸發器系統觸發器u 觸發器的管理觸發器的管理56觸發器概述觸發器概述 u觸發器的概念觸發器的概念觸發器是命名塊的一種。觸發器是命名

47、塊的一種。觸發器的執行是自動進行的，當相應事件發生觸發器的執行是自動進行的，當相應事件發生時就會激發觸發器的執行。時就會激發觸發器的執行。觸發器觸發器不接受任何參數不接受任何參數57u觸發器（觸發器（TRIGGERTRIGGER）作用）作用維護那些通過創建表時的維護那些通過創建表時的聲明約束不可能實現的復雜聲明約束不可能實現的復雜的完整性約束的完整性約束通過記錄已進行的改變及是誰進行了該項改變來通過記錄已進行的改變及是誰進行了該項改變來檢查檢查一個表中的信息一個表中的信息。當一個表發生改變時，自動當一個表發生改變時，自動向其他程序發送需要采取向其他程序發送需要采取行動的信號行動的信號觸發器（觸

48、發器（TRIGGER）作用）作用58觸發器類型觸發器類型u觸發器類型觸發器類型DML觸發器觸發器INSERT、DELETE、UPDATEINSTEAD-OF 觸發器觸發器 只可以定義為視圖的觸發器只可以定義為視圖的觸發器系統觸發器系統觸發器數據庫啟動或關閉之類的系統事件發生時觸發數據庫啟動或關閉之類的系統事件發生時觸發在執行諸如創建表之類的在執行諸如創建表之類的DDL操作時觸發操作時觸發59觸發器組成觸發器組成觸發器由觸發器由觸發器頭部觸發器頭部和和觸發器體觸發器體兩個部分組兩個部分組成，主要包括以下參數：成，主要包括以下參數：作用對象：表、視圖、數據庫、模式作用對象：表、視圖、數據庫、模式觸

49、發事件：觸發事件：DML、DDL、數據庫系統事件、數據庫系統事件觸發時間：觸發時間：BEFORE、AFTER觸發級別：語句級、行級觸發級別：語句級、行級觸發條件：觸發條件：WHEN條件條件觸發操作：觸發操作：SQL語句、語句、PL/SQL塊塊觸發器組成觸發器組成60DML觸發器觸發器uDML觸發器的種類以及執行順序觸發器的種類以及執行順序語句級前觸發器語句級前觸發器行級前觸發器行級前觸發器DML操作（觸發事件）操作（觸發事件）行級后觸發器行級后觸發器語句級后觸發器語句級后觸發器61創建創建DML觸發器觸發器CREATE OR REPLACE TRIGGER trigger_name BEFOR

50、E|AFTER triggering_event OF column_nameON table_name FOR EACH ROW WHEN trigger_conditionDECLARE /*Declarative section is here */BEGIN /*Exccutable section si here*/ EXCEPTION /*Exception section is here*/ END trigger_name;Trigger_body62u創建一個觸發器，創建一個觸發器，禁止在休息日改變雇員信息禁止在休息日改變雇員信息，create or replace trig

51、ger tr_sec_empbefore insert or update or delete on empbegin if to_char(sysdate,DY) in (星期六,星期日) then raise_application_error(-20001,不能在休息日修改員工信息); end if;end;觸發器創建實例觸發器創建實例63u判斷當前執行的觸發器到底是有那個判斷當前執行的觸發器到底是有那個DML操作激操作激發的。發的。謂詞行為INSERTING如果觸發語句是INSERT，則為TRUE；否則為FALSEUPDATING如果觸發語句是UPDATE，則為TRUE；否則為FALS

52、EDELETING如果觸發語句是DELETE，則為TRUE；否則為FALSE觸發器判斷觸發器判斷64u為為emp表創建一個觸發器，當表創建一個觸發器，當執行插入操執行插入操作時作時，統計操作后員工人數；當執行更新，統計操作后員工人數；當執行更新工資操作時，統計更新后員工平均工資；工資操作時，統計更新后員工平均工資；當執行刪除操作時，統計刪除后各個部門當執行刪除操作時，統計刪除后各個部門的人數。的人數。 觸發器創建實例觸發器創建實例65CREATE OR REPLACE TRIGGER trg_emp_dmlAFTER INSERT OR UPDATE OR DELETE ON empDECLA

53、RE v_count NUMBER; v_sal NUMBER(6,2);BEGIN IF INSERTING THEN SELECT count(*) INTO v_count FROM emp; DBMS_OUTPUT.PUT_LINE(v_count); ELSIF UPDATING THEN SELECT avg(sal) INTO v_sal FROM emp; DBMS_OUTPUT.PUT_LINE(v_sal); ELSE FOR v_dept IN (SELECT deptno,count(*) num FROM emp GROUP BY deptno) LOOP DBMS_

54、OUTPUT.PUT_LINE(v_dept.deptno| |v_dept.num); END LOOP; END IF;END trg_emp_dml; 觸發器創建實例觸發器創建實例66u是指執行是指執行DML操作時，每操作一記錄，操作時，每操作一記錄，觸發觸發器就執行一次器就執行一次，一個，一個DML操作操作涉及到多少個涉及到多少個記錄，觸發器就執行多少次記錄，觸發器就執行多少次。u在行級觸發器中可以使用在行級觸發器中可以使用WHEN條件條件，進一，進一步控制觸發器的執行。步控制觸發器的執行。u在在觸發器體中觸發器體中，可以，可以對當前操作的記錄對當前操作的記錄進行進行訪問和操作。訪問和

55、操作。 行級觸發器行級觸發器67u標識符標識符 :OLD 、:NEWu引用方式：引用方式： :old.field和和:new.field （執行部分）（執行部分） old.field 和和new.field (WHEN條件中條件中)u在不同操作中的意義在不同操作中的意義觸發語句：old：newINSERT未定義，所有字段都為NULL當語句完成時，將要被插入的值UPDATE更新前行的原始值當語句完成時，將要被更新的值DELETE行被刪除前的原始值未定義，所有字段都為NULL :OLD、:NEW偽記錄偽記錄68u注意事項注意事項：是是偽記錄偽記錄，不能作為整個記錄進行賦值或引用，不能作為整個記錄進

56、行賦值或引用 不能傳遞不能傳遞給帶給帶triggering_table%ROWTYPEtriggering_table%ROWTYPE參數的過參數的過程和函數程和函數 如果觸發器是建立在嵌套表上，：如果觸發器是建立在嵌套表上，：oldold和：和：newnew都執都執行行嵌套表的行嵌套表的行。 :OLD、:NEW偽記錄偽記錄69u為為empemp表創建一個觸發器，當表創建一個觸發器，當插入新員工時插入新員工時顯示新員工的員工號、員工名顯示新員工的員工號、員工名；當；當更新員工更新員工工資時，顯示修改前后員工工資工資時，顯示修改前后員工工資；當；當刪除員刪除員工時，顯示被刪除的員工號、員工名工時

57、，顯示被刪除的員工號、員工名。觸發器舉例觸發器舉例70CREATE OR REPLACE TRIGGER trg_emp_dml_row BEFORE INSERT OR UPDATE OR DELETE ON emp FOR EACH ROWBEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE(:new.empno| | :new.ename); ELSIF UPDATING THEN DBMS_OUTPUT.PUT_LINE(:old.sal| |:new.sal); ELSE DBMS_OUTPUT.PUT_LINE(:old.empno| | :ol

58、d.ename); END IF;END trg_emp_dml_row;觸發器舉例觸發器舉例71u在行級觸發器中，可以在行級觸發器中，可以使用使用WHEN子句進一步控制觸發器的子句進一步控制觸發器的執行執行。u修改員工工資時，保證修改員工工資時，保證修改后的工資高于修改前的工資修改后的工資高于修改前的工資。CREATE OR REPLACE TRIGGER trg_emp_update_row BEFORE UPDATE OF sal ON emp FOR EACH ROWWHEN(new.sal=old.sal)BEGIN RAISE_APPLICATION_ERROR( -20001,T

59、he salary is lower!);END trg_emp_update_row; 行級觸發器行級觸發器72u創建一個觸發器，在修改創建一個觸發器，在修改dept表的部門號時，同時表的部門號時，同時更新更新emp表中相應的員工的部門號。表中相應的員工的部門號。create or replace trigger tr_update_deptafter update of deptno on deptfor each rowBegin update emp set deptno=:new.deptno where deptno=:old.deptno;end;觸發器舉例觸發器舉例73INST

60、EAD OF觸發器觸發器u特點特點只能定義在只能定義在視圖視圖上上Instead-of觸發器是觸發器是行級觸發器行級觸發器Instead-of 觸發器觸發器由由DML操作激發操作激發，而，而DML操操作本身并不執行作本身并不執行u作用作用修改一個本來不可以修改一個本來不可以修改的視圖修改的視圖修改視圖中某修改視圖中某嵌套表列的列嵌套表列的列 74u如果視圖中包含下列任何一項，則如果視圖中包含下列任何一項，則該視圖該視圖不可修改不可修改集合操作符集合操作符聚集函數聚集函數GROUP BYGROUP BYDISTINCTDISTINCT連接（部分包含連接的視圖）連接（部分包含連接的視圖） INSTEAD