Oracle第15章PLSQL程序設計ppt課件

1、1第15章 PL/SQL程序設計.2本章內容存儲過程函數部分子程序包觸發(fā)器.3本章要求掌握PL/SQL功能模塊的運用存儲過程、函數、包、觸發(fā)器的創(chuàng)建存儲過程、函數、包、觸發(fā)器的維護.415.1 存儲子程序存儲過程函數部分子程序.15.1.1存儲過程存儲過程的創(chuàng)建存儲過程的調用存儲過程的管理5.61存儲過程的創(chuàng)建根本語法CREATE OR REPLACE PROCEDURE procedure_name(parameter1_name mode datatype DEFAULT|:=value, parameter2_name mode datatype DEFAULT|:=value,)AS|

2、IS /*Declarative section is here */BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ ENDprocedure_name; .7參數闡明 參數的方式 IN默許參數方式表示當過程被調用時，實參值被傳送給形參；在過程內，形參起常量作用，只能讀該參數，而不能修正該參數；當子程序調用終了前往調用環(huán)境時，實參沒有被改動。IN方式參數可以是常量或表達式。OUT表示當過程被調用時，實參值被忽略；在過程內，形參起未初始化的PL/SQL變量的作用，初始值為NULL，可以進展讀

3、/寫操作；當子程序調用終了后前往調用環(huán)境時，形參值被賦給實參。OUT方式參數只能是變量，不能是常量或表達式。IN OUT表示當過程被調用時，實參值被傳送給形參；在過程內，形參起已初始化的PL/SQL變量的作用，可讀可寫；當子程序調用終了前往調用環(huán)境時，形參值被賦給實參。IN OUT方式參數只能是變量，不能是常量或表達式。 .8參數的限制在聲明形參時，不能定義形參的長度或精度、刻度，它們是作為參數傳送機制的一部分被傳送的，是由實參決議的。參數傳送方式當子程序被調用時，實參與形參之間值的傳送方式取決于參數的方式。IN參數為援用傳送，即實參的指針被傳送給形參；OUT，IN OUT參數為值傳送，即實參

4、的值被復制給形參。參數默許值可以為參數設置默許值，這樣存儲過程被調用時假設沒有給該參數傳送值，那么采用默許值。需求留意，有默許值的參數應該放在參數列表的最后。 .9創(chuàng)建一個存儲過程，以部門號為參數，查詢該部門的平均工資，并輸出該部門中比平均工資高的員工號、員工名。CREATE OR REPLACE PROCEDURE show_emp(p_deptno emp.deptno%TYPE)AS v_sal emp.sal%TYPE;BEGINSELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;DBMS_OUTPUT.PUT_LINE(

5、p_deptno| |average salary is:| v_sal);FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND salv_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.ename);END LOOP;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department doesnt exists!);END show_emp;.10通常，存儲過程不需求前往值，假設需求前往一個值可以經過函數

6、調用實現。但是，假設希望前往多個值，可以運用OUT或IN OUT方式參數來實現。.11創(chuàng)建一個存儲過程，以部門號為參數，前往該部門的人數和最高工資。CREATE OR REPLACE PROCEDURE return_deptinfo(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_DA

7、TA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department dont exists!);END return_deptinfo; .122存儲過程的調用在SQL*PLUS中調用EXEC procedure_name(parameter_list)EXECUTE show_emp(10)在PL/SQL塊中調用BEGIN procedure_name(parameter_list);END；留意在PL/SQL程序中，存儲過程可以作為一個獨立的表達式被調用。 .13DECLARE v_avgsal emp.sal%TYPE; v_count NUMBER;BE

8、GIN show_emp(20); return_deptinfo(10,v_avgsal,v_count); DBMS_OUTPUT.PUT_LINE(v_avgsal| | v_count);END; .143存儲過程的管理修正存儲過程 CREATE OR REPLACE PROCEDURE procedure_name查看存儲過程及其源代碼 查詢數據字典視圖USER_SOURCESELECT name,text FROM user_source WHERE type=PROCEDURE; 重新編譯存儲過程 ALTER PROCEDURECOMPILE ALTER PROCEDURE sh

9、ow_emp COMPILE; 刪除存儲過程 DROP PROCEDUREDROP PROCEDURE show_emp;.1515.1.2函數函數的創(chuàng)建函數的調用函數的管理.161函數的創(chuàng)建根本語法為 CREATE OR REPLACE FUNCTION function_name (parameter1_name mode datatype DEFAULT|:=value, parameter2_name mode datatype DEFAULT|:=value,)RETURN return_datatype AS|IS /*Declarative section is here */B

10、EGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END function_name; .17留意在函數定義的頭部，參數列表之后，必需包含一個RETURN語句來指明函數前往值的類型，但不能約束前往值的長度、精度、刻度等。假設運用%TYPE，那么可以隱含地包括長度、精度、刻度等約束信息；在函數體的定義中，必需至少包含一個RETURN 語句，來指明函數前往值。也可以有多個RETURN語句，但最終只需一個RETURN語句被執(zhí)行。.18創(chuàng)建一個以部門號為參數，前往該部門最高工資的函數。CREATE O

11、R 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 DBMS_OUTPUT.PUT_LINE(The deptno is invalid!);END return_maxsal;.19假設需求函數前往多個值，可以

12、運用OUT或IN OUT方式參數。.20創(chuàng)建一個函數，以部門號為參數，前往部門名、部門人數及部門平均工資。CREATE OR REPLACE FUNCTION ret_deptinfo(p_deptno dept.deptno%TYPE,p_num OUT NUMBER,p_avg OUT NUMBER)RETURN dept.dname%TYPEAS v_dname dept.dname%TYPE;BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno=p_deptno; SELECT count(*),avg(sal) INTO p_n

13、um,p_avg FROM emp WHERE deptno=p_deptno; RETURN v_dname;END ret_maxsal; .212函數的調用在SQL語句中調用函數在PL/SQL中調用函數留意函數只能作為表達式的一部分被調用。例如經過return_maxsal函數的調用，輸出各個部門的最高工資；經過ret_deptinfo函數調用，輸出各個部門名、部門人數及平均工資。.22DECLARE v_maxsal emp.sal%TYPE; v_avgsal emp.sal%TYPE; v_num NUMBER; v_dname dept.dname%TYPE;BEGIN FOR

14、v_dept IN (SELECT DISTINCT deptno FROM emp) 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子句；CONNECT BY，START WITH，ORDER BY，GROUP

15、BY子句；INSERT語句的VALUES子句中；UPDATE語句的SET子句中。 .24假設要在SQL中調用函數，那么函數必需符合以下限制和要求：在SELECT語句中的函數不能修正INSERT，UPDATE，DELETE調用函數的SQL語句中運用的表；函數在一個遠程或并行操作中運用時，不能讀/寫封裝變量；函數必需是一個存儲數據庫對象或存儲在包中；函數的參數只能運用IN方式；方式參數類型必需運用數據庫數據類型；前往的數據類型必需是數據庫數據類型；.253函數的管理函數的修正CREATE OR REPLACE FUNCTION function_name查看函數及其源代碼查詢數據字典視圖USER_

16、SOURCESELECT name,text FROM user_source WHERE type=FUNCTION;函數重編譯ALTER FUNCTIONCOMPILEALTER FUNCTION ret_maxsal COMPILE;刪除函數DROP FUNCTIONDROP FUNCTION ret_maxsal;.2615.1.3 部分子程序部分子程序嵌套在其他PL/SQL塊中的子程序。只能在其定義的塊內部被調用，而不能在其父塊外被調用。運用部分子程序時需求留意：部分子程序只在當前語句塊內有效；部分子程序必需在PL/SQL塊聲明部分的最后進展定義；部分子程序必需在運用之前聲明，假設是

17、子程序間相互援用，那么需求采用預先聲明；部分子程序可以重載。.27在一個塊內部定義一個函數和一個過程。函數以部門號為參數前往該部門的平均工資；過程以部門號為參數，輸出該部門中工資低于部門平均工資的員工的員工號、員工名。 .28DECLARE v_deptno emp.deptno%TYPE; v_avgsal emp.sal%TYPE; FUNCTION return_avgsal(p_deptno emp.deptno%TYPE) RETURN emp.sal%TYPE AS v_sal emp.sal%TYPE; BEGIN SELECT avg(sal) INTO v_sal FROM

18、emp WHERE deptno=p_deptno; RETURN v_sal; END return_avgsal; .29 PROCEDURE show_emp(p_deptno emp.deptno%TYPE) AS CURSOR c_emp IS SELECT * FROM emp 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

19、; END LOOP; END show_emp;BEGIN v_deptno:=&x; v_avgsal:=return_avgsal(v_deptno); show_emp(v_deptno);END;.30存儲子程序與部分子程序區(qū)別在于：存儲子程序己經編譯好放在數據庫效力器端，可以直接調用，而部分子程序存在于定義它的語句塊中，在運轉時先進展編譯；存儲子程序不能重載，而部分子程序可以進展重載；存儲子程序可以被恣意的PL/SQL塊調用，而部分子程序只能在定義它的塊中被調用。.31在一個PL/SQL塊中重載兩個過程，一個以員工號為參數，輸出該員工信息；另一個以員工名為參數，輸出員工信息。利用這

20、兩個過程分別查詢員工號為7902，7934，以及員工名為SMITH，FORD的員工信息 。.32DECLARE 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

21、such an employee!); END show_empinfo; .33 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); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There is not such an employ

22、ee!); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(There are more than one employee!); END show_empinfo;.34BEGIN show_empinfo(7902); show_empinfo(7934); show_empinfo(SMITH); show_empinfo(FORD);END ;.3515.2 包包概述包的創(chuàng)建包的調用包重載包的初始化包的管理.36包概述 包是包含一個或多個子程序單元過程、函數等的容器。包是一種全局構造 。包類型數據庫內置包 用戶創(chuàng)建的包包構成包規(guī)范包體兩.37包

23、規(guī)范聲明了軟件包中一切內容，如過程、函數、游標、類型、異常和變量等，其中過程和函數只包括原型信息，不包含任何子程序代碼。 包體中包含了在包頭中的過程和函數的實現代碼。包體中還可以包括在規(guī)范中沒有聲明的變量、游標、類型、異常、過程和函數，但是它們是私有元素，只能由同一包體中其他過程和函數運用。.3815.2.1 包的創(chuàng)建創(chuàng)建包規(guī)范 創(chuàng)建包體 .391創(chuàng)建包規(guī)范語法CREATE OR REPLACE PACKAGE package_name IS|ASPRAGMA SERIALLY_RESUABLE type_definition|variable_declaration| exception_d

24、eclaration|cursor_declaration| procedure_ declaration|function_ declarationEND package_name;.40留意：元素聲明的順序可以是恣意的，但必需先聲明后運用；一切元素是可選的；過程和函數的聲明只包括原型，不包括詳細實現。.41創(chuàng)建一個軟件包，包括2個變量、2個過程和1個異常。CREATE OR REPLACE PACKAGE pkg_empAS minsal NUMBER; maxsal NUMBER; e_beyondbound EXCEPTION; PROCEDURE update_sal( p_empn

25、o NUMBER, p_sal NUMBER); PROCEDURE add_employee( p_empno NUMBER,p_sal NUMBER);END pkg_emp;.42語法CREATE OR REPLACE PACKAGE BODY package_name IS|ASPRAGMA SERIALLY_RESUABLE type_definition|variable_declaration| exception_declaration| cursor_declaration| procedure_definition | function_definitionEND pack

26、age_name; 2創(chuàng)建包體 .43留意：包體中函數和過程的原型必需與包規(guī)范中的聲明完全一致；只需在包規(guī)范曾經創(chuàng)建的條件下，才可以創(chuàng)建包體；假設包規(guī)范中不包含任何函數或過程，那么可以不創(chuàng)建包體。 .44CREATE 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 UP

27、DATE 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 salary is beyond bound! ); END update_sal; .45PROCEDURE add_employee(p_empn

28、o NUMBER,p_sal NUMBER)AS BEGIN SELECT min(sal), max(sal) INTO minsal,maxsal 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

29、add_employee;END pkg_emp;.4615.2.2 包的調用在包規(guī)范聲明的任何元素是公有的，在包外都是可見的包外：經過package.element方式調用；包內：直接經過元素名進展調用。 在包體中定義而沒有在包頭中聲明的元素是私有的，只能在包體中援用 .47調用軟件包pkg_emp中的過程update_sal，修正7844員工工資為3000。調用add_employee添加一個員工號為7，工資為4000的員工。BEGIN pkg_emp.update_sal(7844,3000);pkg_emp.add_employee(7,4000);END;.4815.2.3 包重載重

30、載子程序必需同名不同參，即稱號一樣，參數不同。參數不同表達為參數的個數、順序、類型等不同。假設兩個子程序參數只是稱號和方式不同，那么不能重載。PROCEDURE overloadme(parameter1 IN NUMBER);PROCEDURE overloadme(parameter2 OUT NUMBER); 不能根據兩個函數前往類型不同而對它們進展重載。FUNCTION overloadme RETURN DATE;FUNCTION overloadme RETURN NUMBER;重載子程序參數必需在類型系列方面有所不同。PROCEDURE overloadchar(paramete

31、r IN CHAR);PROCEDURE overloadchar(parameter IN VARCHAR2);.49在一個包中重載兩個過程，分別以部門號和部門稱號為參數，查詢相應部門員工名、員工號信息。 .50CREATE OR REPLACE PACKAGE pkg_overloadAS PROCEDURE show_emp(p_deptno NUMBER); PROCEDURE show_emp(p_dname VARCHAR2);END pkg_overload;.51CREATE OR REPLACE PACKAGE BODY pkg_overloadAS PROCEDURE sh

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

33、p IN (SELECT * FROM emp WHERE deptno=v_deptno) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.ename); END LOOP; END show_emp;END pkg_overload; .5315.2.4包的初始化包在第一次被調用時從磁盤讀取到共享池，并在整個會話的繼續(xù)期間堅持。在此過程中，可以自動執(zhí)行一個初始化過程，對軟件包進展實例化。包的初始化過程只在包第一次被調用時執(zhí)行，因此也稱為一次性過程，它是一個匿名的PL/SQL塊，在包體構造的最后，以BEGIN開場。例如在pkg_emp包中，在包初

34、始化時給minsal和maxsal兩個變量賦值，在子程序中直接援用這兩個變量。.54CREATE 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; .55CREATE OR REPLACE PACKAGE BODY pkg_empAS PROC

35、EDURE update_sal(p_empno NUMBER, 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 DB

36、MS_OUTPUT.PUT_LINE(The salary is beyond bound!); END update_sal; .56PROCEDURE 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.P

37、UT_LINE(The salary is beyond bound!); END add_employee;BEGIN SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;END pkg_emp;.5715.2.5包的管理包的修正CREATE OR REPLACE PACKAGE package_name查看包及其源代碼查詢數據字典視圖USER_SOURCESELECT name,text FROM user_source WHERE type=PACKAGE;SELECT name,text FROM user_source WHER

38、E type=PACKAGE BODY;重新編譯包ALTER PACKAGECOMPILE包規(guī)范和包體ALTER PACKAGECOMPILE SPECIFICATION包規(guī)范ALTER PACKAGE COMPILE BODY包體 .58ALTER PACKAGE pkg_emp COMPILE;ALTERPACKAGE pkg_emp COMPILE SPECIFICATION;ALTER PACKAGE pkg_emp COMPILE BODY;刪除包DROP PACKAGE 包規(guī)范和包體DROP PACKAGE BODY 包體 DROP PACKAGE BODY pkg_emp;DRO

39、P PACKAGE pkg_emp;.5915.3 觸發(fā)器觸發(fā)器概述 DML觸發(fā)器 INSTEAD-OF觸發(fā)器 系統(tǒng)觸發(fā)器變異表觸發(fā)器 觸發(fā)器的管理.6015.3.1 觸發(fā)器概述觸發(fā)器的概念與作用觸發(fā)器的類型 觸發(fā)器組成 .611觸發(fā)器的概念與作用觸發(fā)器是一種特殊類型的存儲過程，編譯后存儲在數據庫效力器中。當特定事件發(fā)生時，由系統(tǒng)自動調用執(zhí)行，而不能由運用程序顯式地調用執(zhí)行。觸發(fā)器不接受任何參數。觸發(fā)器主要用于維護那些經過創(chuàng)建表時的聲明約束不能夠實現的復雜的完好性約束，并對數據庫中特定事件進展監(jiān)控和呼應。.622觸發(fā)器的類型 DML觸發(fā)器建立在根本表上的觸發(fā)器，呼應根本表的INSERT，UPD

40、ATE，DELETE操作。INSTEAD OF觸發(fā)器建立在視圖上的觸發(fā)器，呼應視圖上的INSERT，UPDATE，DELETE操作。系統(tǒng)觸發(fā)器建立在系統(tǒng)或方式上的觸發(fā)器，呼應系統(tǒng)事件和DDLCREATE，ALTER，DROP操作。 .633觸發(fā)器組成 觸發(fā)器由觸發(fā)器頭部和觸發(fā)器體兩個部分組成，主要包括：作用對象：觸發(fā)器作用的對象包括表、視圖、數據庫和方式。觸發(fā)事件：激發(fā)觸發(fā)器執(zhí)行的事件。如DML、DDL、數據庫系統(tǒng)事件等。觸發(fā)時間：用于指定觸發(fā)器在觸發(fā)事件完成之前還是之后執(zhí)行。假設指定為AFTER，那么表示先執(zhí)行觸發(fā)事件，然后再執(zhí)行觸發(fā)器；假設指定為BEFORE，那么表示先執(zhí)行觸發(fā)器，然后再執(zhí)

41、行觸發(fā)事件。.64觸發(fā)級別：觸發(fā)級別用于指定觸發(fā)器呼應觸發(fā)事件的方式。默以為語句級觸發(fā)器，即觸發(fā)事件發(fā)生后，觸發(fā)器只執(zhí)行一次。假設指定為FOR EACH ROW，即為行級觸發(fā)器，那么觸發(fā)事件每作用于一個記錄，觸發(fā)器就會執(zhí)行一次。觸發(fā)條件：由WHEN子句指定一個邏輯表達式，當觸發(fā)事件發(fā)生，而且WHEN條件為TRUE時，觸發(fā)器才會執(zhí)行。觸發(fā)操作：觸發(fā)器執(zhí)行時所進展的操作。.6515.3.2 DML觸發(fā)器DML觸發(fā)器的種類及執(zhí)行順序 創(chuàng)建DML觸發(fā)器 .661DML觸發(fā)器的種類及執(zhí)行順序DML觸發(fā)器的種類語句級前觸發(fā)器語句級后觸發(fā)器行級前觸發(fā)器行級后觸發(fā)器.67DML觸發(fā)器的執(zhí)行順序假設存在，那么執(zhí)

42、行語句級前觸發(fā)器。對于受觸發(fā)事件影響的每一個記錄：假設存在，那么執(zhí)行行級前觸發(fā)器；執(zhí)行當前記錄的DML操作觸發(fā)事件；假設存在，那么執(zhí)行行級后觸發(fā)器。假設存在，那么執(zhí)行語句級后觸發(fā)器。 .682創(chuàng)建DML觸發(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 secti

43、on si here*/ EXCEPTION /*Exception section is here*/ END trigger_name;Trigger_body.69語句級觸發(fā)器 在默許情況下創(chuàng)建的DML觸發(fā)器為語句級觸發(fā)器，即觸發(fā)事件發(fā)生后，觸發(fā)器只執(zhí)行一次。 .70創(chuàng)建一個觸發(fā)器，制止在休憩日改動雇員信息CREATE OR REPLACE TRIGGER trg_emp_weekendBEFORE INSERT OR UPDATE OR DELETE ON empBEGIN IF TO_CHAR(SYSDATE, DY, nls_date_language= american) IN(

44、SAT, SUN) THEN raise_application_error(-20000, Cant operate in weekend. ); END IF;END trg_emp_weekend; .71假設觸發(fā)器呼應多個DML事件，而且需求根據事件的不同進展不同的操作，那么可以在觸發(fā)器體中運用3個條件謂詞。謂詞行為INSERTING如果觸發(fā)語句是INSERT，則為TRUE；否則為FALSEUPDATING如果觸發(fā)語句是UPDATE，則為TRUE；否則為FALSEDELETING如果觸發(fā)語句是DELETE，則為TRUE；否則為FALSE.72為emp表創(chuàng)建一個觸發(fā)器，當執(zhí)行插入操作時，

45、統(tǒng)計操作后員工人數；當執(zhí)行更新工資操作時，統(tǒng)計更新后員工平均工資；當執(zhí)行刪除操作時，統(tǒng)計刪除后各個部門的人數。 .73CREATE OR REPLACE TRIGGER trg_emp_dmlAFTER INSERT OR UPDATE OR DELETE ON empDECLARE 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 av

46、g(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_OUTPUT.PUT_LINE(v_dept.deptno| |v_dept.num); END LOOP; END IF;END trg_emp_dml; .74行級觸發(fā)器 行級觸發(fā)器是指執(zhí)行DML操作時，每操作一個記錄，觸發(fā)器就執(zhí)行一次，一個DML操作涉及多少個記錄，觸發(fā)器就執(zhí)行多少次。在行級觸發(fā)器中可以運用WHE

47、N條件，進一步控制觸發(fā)器的執(zhí)行。在行級觸發(fā)器中引入了:old和:new 兩個標識符，來訪問和操作當前被處置記錄中的數據。 .75標識符:old和:new作為triggering_table%ROWTYPE類型的兩個變量在不同觸發(fā)事件中，:old和:new的意義不同觸發(fā)事件:old:newINSERT未定義，所有字段都為NULL當語句完成時，被插入的記錄UPDATE更新前原始記錄當語句完成時，更新后的記錄DELETE記錄被刪除前的原始值未定義，所有字段都為NULL.76援用方式： :old.field和:new.field 執(zhí)行部分 old.field 和new.field (WHEN條件中)本卷須知：是偽記錄，不能作為整個記錄進展賦值或援用 不能傳送給帶triggering_table%ROWTYPE參數的過程和函數 假設觸發(fā)器是建立在嵌套表上，;old和;new都執(zhí)行嵌套表的行，:parent指向父表中的當前行。 .77為emp表創(chuàng)建一個觸發(fā)器，當插入新員工時顯示新員工的員工號、員工名；當更新員工工資時，顯示修正前后員工工資；當刪除員工時，顯示被刪