數(shù)據(jù)庫課件：第10章PLSQL程序開發(fā)

1、1第第10章章 PL/SQL命名對象命名對象2本章內(nèi)容本章內(nèi)容l存儲過程l函數(shù)l包l觸發(fā)器310.1 存儲過程存儲過程l創(chuàng)建存儲過程l調(diào)用存儲過程l案例數(shù)據(jù)庫中存儲過程的創(chuàng)建410.1.1 創(chuàng)建存儲過程創(chuàng)建存儲過程l基本語法CREATE OR REPLACE PROCEDURE procedure_name(parameter1_name mode datatype DEFAULT|:=value, parameter2_name mode datatype DEFAULT|:=value,)AS|IS /*Declarative section is here */BEGIN /*Execu

2、table section is here*/ EXCEPTION /*Exception section is here*/ ENDprocedure_name; 5l參數(shù)說明 參數(shù)的模式參數(shù)的模式 pIN（默認參數(shù)模式）表示當過程被調(diào)用時，實參值被傳遞給形參；在過程內(nèi)，形參起常量作用，只能讀該參數(shù)，而不能修改該參數(shù)；當子程序調(diào)用結束返回調(diào)用環(huán)境時，實參沒有被改變。IN模式參數(shù)可以是常量或表達式。pOUT表示當過程被調(diào)用時，實參值被忽略；在過程內(nèi)，形參起未初始化的PL/SQL變量的作用，初始值為NULL，可以進行讀/寫操作；當子程序調(diào)用結束后返回調(diào)用環(huán)境時，形參值被賦給實參。OUT模式參數(shù)只

3、能是變量，不能是常量或表達式。pIN OUT表示當過程被調(diào)用時，實參值被傳遞給形參；在過程內(nèi)，形參起已初始化的PL/SQL變量的作用，可讀可寫；當子程序調(diào)用結束返回調(diào)用環(huán)境時，形參值被賦給實參。IN OUT模式參數(shù)只能是變量，不能是常量或表達式。 6參數(shù)的限制參數(shù)的限制p在聲明形參時，不能定義形參的長度或精度、刻度，它們是作為參數(shù)傳遞機制的一部分被傳遞的，是由實參決定的。參數(shù)傳遞方式參數(shù)傳遞方式p當子程序被調(diào)用時，實參與形參之間值的傳遞方式取決于參數(shù)的模式。IN參數(shù)為引用傳遞，即實參的指針被傳遞給形參；OUT，IN OUT參數(shù)為值傳遞，即實參的值被復制給形參。參數(shù)默認值參數(shù)默認值p可以為參數(shù)設

4、置默認值，這樣存儲過程被調(diào)用時如果沒有給該參數(shù)傳遞值，則采用默認值。需要注意，有默認值的參數(shù)應該放在參數(shù)列表的最后。 7l創(chuàng)建一個存儲過程，以部門號為參數(shù)，查詢該部門的平均工資，并輸出該部門中比平均工資高的員工號、員工名。lCREATE OR REPLACE PROCEDURE proc_show_emp(l p_deptno employees.department_id%TYPE)lASl v_sal employees.salary%TYPE;lBEGINl SELECT avg(salary) INTO v_sal FROM employees l WHERE department_i

5、d=p_deptno;l DBMS_OUTPUT.PUT_LINE(p_deptno| |l average salary is: |v_sal);l FOR v_emp IN (SELECT * FROM employees l WHERE department_id=p_deptno AND salaryv_sal)LOOPl DBMS_OUTPUT.PUT_LINE(v_emp.employee_id| |l v_emp.first_name| |v_emp.last_name);l END LOOP;lEXCEPTIONl WHEN NO_DATA_FOUND THENl DBMS_O

6、UTPUT.PUT_LINE(The department doesnt exists! );lEND proc_show_emp;8l通常，存儲過程不需要返回值，如果需要返回一個值可以通過函數(shù)調(diào)用實現(xiàn)。但是，如果希望返回多個值，可以使用OUT或IN OUT模式參數(shù)來實現(xiàn)。9l創(chuàng)建一個存儲過程，以部門號為參數(shù)，以部門編號為參數(shù)返回該部門的人數(shù)和平均工資。l lCREATE OR REPLACE PROCEDURE proc_return_deptinfo(l p_deptno employees.department_id%TYPE,l p_avgsal OUT employees.salar

7、y%TYPE,l p_count OUT NUMBER)lASlBEGINl SELECT avg(salary),count(*) INTO p_avgsal,p_count FROM employeesl WHERE department_id=p_deptno;lEXCEPTIONl WHEN NO_DATA_FOUND THENl DBMS_OUTPUT.PUT_LINE(The department dont exists! );lEND proc_return_deptinfo;1010.1.2 調(diào)用存儲過程調(diào)用存儲過程l在SQL*PLUS中調(diào)用EXEC procedure_nam

8、e(parameter_list)EXECUTE proc_show_emp(10)l在PL/SQL塊中調(diào)用BEGIN procedure_name(parameter_list);END；l注意在在PL/SQL程序中，存儲過程可以作為一個獨立的程序中，存儲過程可以作為一個獨立的表達式被調(diào)用。表達式被調(diào)用。 11lDECLAREl v_avgsal emp.sal%TYPE;l v_count NUMBER;lBEGINl proc_show_emp(20);l proc_return_deptinfo(10,v_avgsal,v_count);l DBMS_OUTPUT.PUT_LINE(v

9、_avgsal| | l v_count);lEND; 1210.1.3 案例數(shù)據(jù)庫中存儲過程的創(chuàng)建案例數(shù)據(jù)庫中存儲過程的創(chuàng)建l創(chuàng)建名為“PROC_SECURE_DML”的存儲過程，檢查當前用戶操作時間是否為工作時間，即非周六、周日，時間為08：00-18：00。lCREATE OR REPLACE PROCEDURE proc_secure_dmllISlBEGINl IF TO_CHAR (SYSDATE, HH24:MI) NOT BETWEEN 08:00 l AND 18:00 OR TO_CHAR (SYSDATE, DY, l NLS_DATE_LANGUAGE=AMERICAN

10、) IN (SAT, SUN) l THENl RAISE_APPLICATION_ERROR (-20205,只能在正常的工作時l 間內(nèi)進行改變。);l END IF;lEND proc_secure_dml; 13l創(chuàng)建名為“PROC_JOB_CHANGE”的存儲過程，實現(xiàn)員工職位的調(diào)動。lCREATE OR REPLACE PROCEDURE proc_job_change(l p_employee_id employees.employee_id%type,l p_new_job_title jobs.job_title%type)lASl v_old_job_id jobs.job_

11、id%type;l v_old_job_title jobs.job_title%type;l v_new_job_id jobs.job_id%type;lBEGINl SELECT job_id INTO v_old_job_id FROM employees WHERE employee_id=p_employee_id;l SELECT job_title INTO v_old_job_title FROM jobs WHERE job_id=v_old_job_id;l IF v_old_job_title=p_new_job_title THENl RAISE_APPLICATIO

12、N_ERROR(-20001,the new job title is as same as before!);l END IF;l SELECT job_id INTO v_new_job_id FROM jobs WHERE job_title=p_new_job_title;l UPDATE employees SET job_id=v_new_job_id WHERE employee_id=p_employee_id;l COMMIT;lEXCEPTIONl WHEN NO_DATA_FOUND THENl RAISE_APPLICATION_ERROR(-20002,The job

13、 title does not exists!);lEND proc_job_change; 14l創(chuàng)建名為“PROC_DEPARTMENT_CHANGE”的存儲過程，實現(xiàn)員工部門的調(diào)動。lCREATE OR REPLACE PROCEDURE proc_department_change(l p_employee_id employees.employee_id%type,l p_new_department_name departments.department_name%type)l ASl v_old_department_id departments.department_id%ty

14、pe;l v_old_department_name departments.department_name%type;l v_new_department_id departments.department_id%type;l BEGINl SELECT department_id INTO v_old_department_id FROM employees l WHERE employee_id=p_employee_id;l SELECT department_name INTO v_old_department_name FROM departments l WHERE depart

15、ment_id=v_old_department_name;l IF v_old_department_name=p_new_department_name THENl RAISE_APPLICATION_ERROR(-20001,the new department name is as same as before!);l END IF;l SELECT department_id INTO v_new_department_id FROM departments l WHERE department_name=p_new_department_name;l UPDATE employee

16、s SET department_id=v_new_department_id l WHERE employee_id=p_employee_id;l COMMIT;l EXCEPTIONl WHEN NO_DATA_FOUND THENl RAISE_APPLICATION_ERROR(-20002,The department name does not exists!);l END proc_department_change; 1510. 2函數(shù)函數(shù)l創(chuàng)建函數(shù)l調(diào)用l案例數(shù)據(jù)庫中函數(shù)的創(chuàng)建1610.2.1 創(chuàng)建函數(shù)創(chuàng)建函數(shù)l基本語法為 CREATE OR REPLACE FUNCTIO

17、N function_name (parameter1_name mode datatype DEFAULT|:=value, parameter2_name mode datatype DEFAULT|:=value,)RETURN return_datatype AS|IS /*Declarative section is here */BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END function_name; 17l注意在函數(shù)定義的頭部，參數(shù)列表之后，必須包含一個在函數(shù)定義

18、的頭部，參數(shù)列表之后，必須包含一個RETURN語句來指明函數(shù)返回值的類型，但不能約束語句來指明函數(shù)返回值的類型，但不能約束返回值的長度、精度、刻度等。如果使用返回值的長度、精度、刻度等。如果使用%TYPE，則可以隱含地包括長度、精度、刻度等約束信息；則可以隱含地包括長度、精度、刻度等約束信息；在函數(shù)體的定義中，必須至少包含一個在函數(shù)體的定義中，必須至少包含一個RETURN 語語句，來指明函數(shù)返回值。也可以有多個句，來指明函數(shù)返回值。也可以有多個RETURN語句，語句，但最終只有一個但最終只有一個RETURN語句被執(zhí)行。語句被執(zhí)行。18l創(chuàng)建名為“FUNC_DEPT_MAXSAL”的函數(shù)，以部門

19、編號為參數(shù)，返回部門最高工資。l lCREATE OR REPLACE FUNCTION func_dept_maxsal(l p_deptno employees.department_id%TYPE)l RETURN employees.salary%TYPElASl v_maxsal employees.salary%TYPE;lBEGINl SELECT max(salary) INTO v_maxsal FROM employees l WHERE department_id=p_deptno;l RETURN v_maxsal;lEXCEPTION l WHEN NO_DATA_F

20、OUND THENl DBMS_OUTPUT.PUT_LINE(The deptno is invalid! );lEND func_dept_maxsal;19l如果需要函數(shù)返回多個值，可以使用OUT或IN OUT模式參數(shù)。20l創(chuàng)建一個名為“FUNC_DEPT_INFO”的函數(shù)，以部門號為參數(shù)，返回部門名、部門人數(shù)及部門平均工資。 lCREATE OR REPLACE FUNCTION func_dept_info(l p_deptno departments.department_id%TYPE,lp_num OUT NUMBER,lp_avg OUT NUMBER)l RETURN d

21、epartments.department_name%TYPElASl v_dname departments.department_name%TYPE;lBEGINl SELECT department_name INTO v_dname FROM departments lWHERE department_id=p_deptno;l SELECT count(*),avg(salary) INTO p_num,p_avg FROM employees lWHERE department_id=p_deptno;l RETURN v_dname;lEND func_dept_info; 21

22、10.2.2 調(diào)用函數(shù)調(diào)用函數(shù)l在SQL語句中調(diào)用函數(shù)l在PL/SQL中調(diào)用函數(shù)l注意函數(shù)只能作為表達式的一部分被調(diào)用。函數(shù)只能作為表達式的一部分被調(diào)用。l示例通過通過func_dept_maxsal函數(shù)的調(diào)用，輸出各個部函數(shù)的調(diào)用，輸出各個部門的最高工資；門的最高工資；通過通過func_dept_info函數(shù)調(diào)用，輸出各個部門名、函數(shù)調(diào)用，輸出各個部門名、部門人數(shù)及平均工資。部門人數(shù)及平均工資。 22lDECLAREl v_maxsal employees.salary%TYPE;l v_avgsal employees.salary%TYPE;l v_num NUMBER;l v_dnam

23、e departments.department_name%TYPE;lBEGINl FOR v_dept IN (SELECT DISTINCT department_id FROM l employees WHERE department_id IS NOT NULL) LOOPl v_maxsal:=func_dept_maxsal(v_dept.department_id); l v_dname:=func_dept_info(v_dept.department_id,l v_num, v_avgsal);l DBMS_OUTPUT.PUT_LINE(v_dname| |v_maxsa

24、l| |l v_avgsal| |v_num);l END LOOP;lEND;23l函數(shù)可以在SQL語句的以下部分調(diào)用：SELECT語句的目標列；語句的目標列；WHERE和和HAVING子句；子句；CONNECT BY，START WITH，ORDER BY，GROUP BY子句；子句；INSERT語句的語句的VALUES子句中；子句中；UPDATE語句的語句的SET子句中。子句中。 2410.2.3 案例數(shù)據(jù)庫中函數(shù)的創(chuàng)建案例數(shù)據(jù)庫中函數(shù)的創(chuàng)建l創(chuàng)建名為“FUNC_EMP_SALARY”的函數(shù)，以員工編號為參數(shù)，返回員工的工資。lCREATE OR REPLACE FUNCTION fun

25、c_emp_salary(l p_empno employees.employee_id%type)lRETURN employees.salary%typelASl v_sal employees.salary%type;lBEGINl SELECT salary INTO v_sal FROM employees l WHERE employee_id=p_empno;l RETURN v_sal;lEXCEPTIONl WHEN NO_DATA_FOUND THENl RAISE_APPLICATION_ERROR(-20000,There is not l such an employ

26、ee!);lEND func_emp_salary;25l創(chuàng)建名為“FUNC_EMP_DEPT_AVGSAL”的函數(shù)，以員工編號為參數(shù)，返回該員工所在部門的平均工資。lCREATE OR REPLACE FUNCTION func_emp_dept_avgsal(lp_empno employees.employee_id%type)lRETURN employees.salary%typelASl v_deptno employees.department_id%type;l v_avgsal employees.salary%type;lBEGINl SELECT department_i

27、d INTO v_deptno FROM employees l WHERE employee_id=p_empno;l SELECT avg(salary) INTO v_avgsal FROM employees l WHERE department_id=v_deptno;l RETURN v_avgsal;lEXCEPTIONl WHEN NO_DATA_FOUND THENl RAISE_APPLICATION_ERROR(-20000,There is not such l an employee!);lEND func_emp_dept_avgsal;2610.3 包包l創(chuàng)建包l

28、調(diào)用包27l包是包含一個或多個子程序單元（過程、函數(shù)等）的容器。l包構成包規(guī)范：聲明了軟件包中所有內(nèi)容，如過程、函數(shù)、游包規(guī)范：聲明了軟件包中所有內(nèi)容，如過程、函數(shù)、游標、類型、異常和變量等，其中過程和函數(shù)只包括原型標、類型、異常和變量等，其中過程和函數(shù)只包括原型信息，不包含任何子程序代碼。信息，不包含任何子程序代碼。 包體：包含了在包頭中的過程和函數(shù)的實現(xiàn)代碼。包體包體：包含了在包頭中的過程和函數(shù)的實現(xiàn)代碼。包體中還可以包括在規(guī)范中沒有聲明的變量、游標、類型、中還可以包括在規(guī)范中沒有聲明的變量、游標、類型、異常、過程和函數(shù)，但是它們是私有元素，只能由同一異常、過程和函數(shù)，但是它們是私有元素，

29、只能由同一包體中其他過程和函數(shù)使用。包體中其他過程和函數(shù)使用。2810.3.1 創(chuàng)建包創(chuàng)建包l創(chuàng)建包規(guī)范 l創(chuàng)建包體 29（1）創(chuàng)建包規(guī)范）創(chuàng)建包規(guī)范l語法CREATE OR REPLACE PACKAGE package_name IS|ASPRAGMA SERIALLY_RESUABLE type_definition|variable_declaration| exception_declaration|cursor_declaration| procedure_ declaration|function_ declarationEND package_name;30l注意：元素聲明的順

30、序可以是任意的，但必須先聲明元素聲明的順序可以是任意的，但必須先聲明后使用；后使用；所有元素是可選的；所有元素是可選的；過程和函數(shù)的聲明只包括原型，不包括具體實過程和函數(shù)的聲明只包括原型，不包括具體實現(xiàn)。現(xiàn)。31l創(chuàng)建一個軟件包，包括2個變量、2個過程和1個異常。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(

31、p_empno NUMBER,p_sal NUMBER);END pkg_emp; 32l語法CREATE OR REPLACE PACKAGE BODY package_name IS|ASPRAGMA SERIALLY_RESUABLE type_definition|variable_declaration| exception_declaration| cursor_declaration| procedure_definition | function_definitionEND package_name; （2）創(chuàng)建包體）創(chuàng)建包體 33l注意：包體中函數(shù)和過程的原型必須與包規(guī)范中的

32、聲包體中函數(shù)和過程的原型必須與包規(guī)范中的聲明完全一致；明完全一致；只有在包規(guī)范已經(jīng)創(chuàng)建的條件下，才可以創(chuàng)建只有在包規(guī)范已經(jīng)創(chuàng)建的條件下，才可以創(chuàng)建包體；包體；如果包規(guī)范中不包含任何函數(shù)或過程，則可以如果包規(guī)范中不包含任何函數(shù)或過程，則可以不創(chuàng)建包體。不創(chuàng)建包體。 34lCREATE OR REPLACE PACKAGE BODY pkg_emplASl PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER)l ASl BEGINl SELECT min(salary), max(salary) INTO minsal,maxsal l FROM e

33、mployees;l IF p_sal BETWEEN minsal AND maxsal THENl UPDATE employees SET salary=p_sal l WHERE employee_id=p_empno;l IF SQL%NOTFOUND THENl RAISE_APPLICATION_ERROR(-20000,l The employee doesnt exist);l END IF;l ELSEl RAISE e_beyondbound;l END IF;l EXCEPTIONl WHEN e_beyondbound THENl DBMS_OUTPUT.PUT_LI

34、NE(The salary is beyond bound! );l END update_sal; 35lPROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)l ASl BEGINl SELECT min(salary), max(salary) INTO minsal,maxsal l FROM employees;l IF p_sal BETWEEN minsal AND maxsal THENl INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,sal

35、ary)lVALUES(p_empno,Smith,sysdate, ST_MAN,p_sal);l ELSEl RAISE e_beyondbound;l END IF;l EXCEPTIONl WHEN e_beyondbound THENl DBMS_OUTPUT.PUT_LINE(The salary is beyond bound! );l END add_employee; lEND pkg_emp;3610.3.2調(diào)用包調(diào)用包l在包規(guī)范聲明的任何元素是公有的，在包外都是可見的包外：通過包外：通過package.element形式調(diào)用；形式調(diào)用；包內(nèi)：直接通過元素名進行調(diào)用。包內(nèi)：

36、直接通過元素名進行調(diào)用。 l在包體中定義而沒有在包頭中聲明的元素是私有的，只能在包體中引用 37l調(diào)用包pkg_emp中的過程update_sal，修改150號員工工資為8000。l調(diào)用add_employee添加一個員工號為2011，工資為9000的員工 BEGIN pkg_emp.update_sal(150,8000); pkg_emp.add_employee(2011,9000);END;3810.4 觸觸 發(fā)發(fā) 器器l觸發(fā)器概述 lDML觸發(fā)器概述l創(chuàng)建DML觸發(fā)器l變異表觸發(fā)器 l案例數(shù)據(jù)庫觸發(fā)器的創(chuàng)建3910.4.1 DML觸發(fā)器概述觸發(fā)器概述l觸發(fā)器是一種特殊類型的存儲過程，

37、編譯后存儲在數(shù)據(jù)庫服務器中。l當特定事件發(fā)生時，由系統(tǒng)自動調(diào)用執(zhí)行，而不能由應用程序顯式地調(diào)用執(zhí)行。l觸發(fā)器主要用于維護那些通過創(chuàng)建表時的聲明約束不可能實現(xiàn)的復雜的完整性約束，并對數(shù)據(jù)庫中特定事件進行監(jiān)控和響應。40觸發(fā)器的類型觸發(fā)器的類型 lDML觸發(fā)器建立在基本表上的觸發(fā)器建立在基本表上的觸發(fā)器響應基本表的響應基本表的INSERT，UPDATE，DELETE操作。操作。lINSTEAD OF觸發(fā)器建立在視圖上的觸發(fā)器建立在視圖上的觸發(fā)器響應視圖上的響應視圖上的INSERT，UPDATE，DELETE操作。操作。l系統(tǒng)觸發(fā)器建立在數(shù)據(jù)庫系統(tǒng)或模式上的觸發(fā)器建立在數(shù)據(jù)庫系統(tǒng)或模式上的觸發(fā)器響應

38、系統(tǒng)事件響應系統(tǒng)事件(STARTUP，SHUTDOWN，SERVERERROR，LOGON，LOGOFF)和和DDL（CREATE，ALTER，DROP）操作。）操作。 41觸發(fā)器組成觸發(fā)器組成 l觸發(fā)器由觸發(fā)器頭部和觸發(fā)器體兩個部分組成l觸發(fā)器頭部（觸發(fā)器何時被調(diào)用）作用對象：觸發(fā)器作用的對象包括表、視圖、數(shù)據(jù)庫作用對象：觸發(fā)器作用的對象包括表、視圖、數(shù)據(jù)庫和模式。和模式。觸發(fā)事件：激發(fā)觸發(fā)器執(zhí)行的事件。如觸發(fā)事件：激發(fā)觸發(fā)器執(zhí)行的事件。如DML、DDL、數(shù)據(jù)庫系統(tǒng)事件等。數(shù)據(jù)庫系統(tǒng)事件等。觸發(fā)時間：用于指定觸發(fā)器在觸發(fā)事件完成之前還是觸發(fā)時間：用于指定觸發(fā)器在觸發(fā)事件完成之前還是之后執(zhí)行。

39、如果指定為之后執(zhí)行。如果指定為AFTER，則表示先執(zhí)行觸發(fā)事，則表示先執(zhí)行觸發(fā)事件，然后再執(zhí)行觸發(fā)器；如果指定為件，然后再執(zhí)行觸發(fā)器；如果指定為BEFORE，則表，則表示先執(zhí)行觸發(fā)器，然后再執(zhí)行觸發(fā)事件。示先執(zhí)行觸發(fā)器，然后再執(zhí)行觸發(fā)事件。10.4.2 DML觸發(fā)器概述觸發(fā)器概述lDML觸發(fā)器包括語句級前觸發(fā)器、語句級后觸發(fā)器、行級前觸發(fā)器、行級后觸發(fā)器4大類，其執(zhí)行的順序如下。（1）如果存在，則執(zhí)行語句級前觸發(fā)器。）如果存在，則執(zhí)行語句級前觸發(fā)器。（2）對于受觸發(fā)事件影響的每一個記錄：）對于受觸發(fā)事件影響的每一個記錄：p如果存在，則執(zhí)行行級前觸發(fā)器；p執(zhí)行當前記錄的DML操作（觸發(fā)事件）；p

40、如果存在，則執(zhí)行行級后觸發(fā)器。（3）如果存在，則執(zhí)行語句級后觸發(fā)器。）如果存在，則執(zhí)行語句級后觸發(fā)器。4310.4.3 創(chuàng)建創(chuàng)建DML觸發(fā)器觸發(fā)器lDML觸發(fā)器語法 CREATE OR REPLACE TRIGGER trigger_nameBEFORE|AFTER triggering_event OF column_nameON table_nameFOR EACH ROWWHEN trigger_conditionDECLARE /*Declarative section is here */BEGIN /*Exccutable section si here*/ EXCEPTION /

41、*Exception section is here*/ END trigger_name;Trigger_body44l創(chuàng)建觸發(fā)器，保證非工作時間禁止對EMPLOYEES表進行DML操作。CREATE OR REPLACE TRIGGER trg_secure_empBEFORE INSERT OR UPDATE OR DELETEON employeesBEGINIF TO_CHAR (SYSDATE, HH24:MI) NOT BETWEEN 08:00 AND 18:00 OR TO_CHAR (SYSDATE, DY, NLS_DATE_LANGUAGE=AMERICAN) IN (

42、SAT, SUN) THENRAISE_APPLICATION_ERROR (-20005,只能在正常的工只能在正常的工作時間內(nèi)進行改變。作時間內(nèi)進行改變。);END IF;END trg_secure_emp; （1）創(chuàng)建語句級創(chuàng)建語句級DML觸發(fā)器觸發(fā)器45p如果觸發(fā)器響應多個DML事件，而且需要根據(jù)事件的不同進行不同的操作，則可以在觸發(fā)器體中使用3個條件謂詞。謂詞行為INSERTING 如果觸發(fā)語句是INSERT，則為TRUE；否則為FALSEUPDATING如果觸發(fā)語句是UPDATE，則為TRUE；否則為FALSEDELETING如果觸發(fā)語句是DELETE，則為TRUE；否則為FALS

43、E46l為employees表創(chuàng)建一個觸發(fā)器，當執(zhí)行插入操作時，統(tǒng)計操作后員工人數(shù)；當執(zhí)行插入操作時，統(tǒng)計操作后員工人數(shù)；當執(zhí)行更新員工工資操作時，統(tǒng)計更新后員工平均工當執(zhí)行更新員工工資操作時，統(tǒng)計更新后員工平均工資；資；當執(zhí)行刪除員工操作時，統(tǒng)計刪除后各個部門的員工當執(zhí)行刪除員工操作時，統(tǒng)計刪除后各個部門的員工人數(shù)。人數(shù)。 47lCREATE OR REPLACE TRIGGER trg_emp_dmllAFTER INSERT OR UPDATE OR DELETE ON employeeslDECLAREl v_count NUMBER;l v_sal NUMBER(6,2);lBEGI

44、Nl IF INSERTING THEN l SELECT count(*) INTO v_count FROM employees;l DBMS_OUTPUT.PUT_LINE(v_count);l ELSIF UPDATING THENl SELECT avg(salary) INTO v_sal FROM employees;l DBMS_OUTPUT.PUT_LINE(v_sal);l ELSEl FOR v_dept IN (SELECT department_id,count(*) num l FROM employees GROUP BY department_id) LOOPl

45、 DBMS_OUTPUT.PUT_LINE(v_dept.department_id| |l v_dept.num);l END LOOP;l END IF;lEND trg_emp_dml; 48（2）創(chuàng)建行級創(chuàng)建行級DML觸發(fā)器觸發(fā)器l行級觸發(fā)器是指執(zhí)行DML操作時，每操作一個記錄，觸發(fā)器就執(zhí)行一次，一個DML操作涉及多少個記錄，觸發(fā)器就執(zhí)行多少次。l在行級觸發(fā)器中可以使用WHEN條件，進一步控制觸發(fā)器的執(zhí)行。l在行級觸發(fā)器中引入了:old和:new 兩個標識符，來訪問和操作當前被處理記錄中的數(shù)據(jù)。 49p:old和:new標識符n:old和:new作為triggering_table%R

46、OWTYPE類型的兩個變量n在不同觸發(fā)事件中，:old和:new的意義不同觸發(fā)事件:old:newINSERT未定義，所有字段都為NULL當語句完成時，被插入的記錄UPDATE更新前原始記錄當語句完成時，更新后的記錄DELETE記錄被刪除前的原始值未定義，所有字段都為NULL50l引用方式： :old.field和:new.field （執(zhí)行部分） old.field 和new.field (WHEN條件中)l注意事項：是偽記錄，不能作為整個記錄進行賦值或引用是偽記錄，不能作為整個記錄進行賦值或引用 不能傳遞給帶不能傳遞給帶triggering_table%ROWTYPE參數(shù)參數(shù)的過程和函數(shù)的過程和函數(shù) 如果觸發(fā)器是建立在嵌套表上，如果觸發(fā)器是建立在嵌套表上，;old和和;new都執(zhí)行嵌都執(zhí)行嵌套表的行，套表的行，: :parent指向父表中的當前行。指向父表中的當前行。 51l為employees表創(chuàng)建一個觸發(fā)器，當插入新員工時顯示新員工的員工號、員工名；當更新員工工資時，顯示修改前后員工工資；當刪除員工時，顯示被刪除的員工號、員