D第7章PL/SQL包與觸發器學習要點創建包調用和執行包組件建立DML觸發器建立INSTEADOF觸發器

本章主要內容

創建包調用和執行包組件觸發器介紹建立DML觸發器建立INSTEADOF觸發器觸發器的維護B7.1創建包包是由存儲在一起的相關對象組成的PL/SQL存儲程序。它可以把相關的過程、函數組織在一起，這樣便于管理，提高了代碼性能。

包由兩個分離的部件組成：包頭和包體。包頭也叫包說明或包規范，它包括函數頭、過程頭、和外部可視數據結構。在創建包時，一般先建立包頭，再建立包體。

B7.1.1包頭包頭用于定義包的公用組件，如：常量、類型、變量、游標、過程和函數等。創建包必須首先創建包規范，創建包規范的語法如下：CREATE[ORREPLACE]PACKAGEpackage_name{AS|IS}public_variable_declarations|public_type_declarations|public_exception_declarations|function_declarations|procedure_specificationsEND[package_name]其中：package_name：包名。public_variable_declarations：公共變量聲明。public_type_declarations：公共類型聲明public_exception_declarations：公共異常聲明。function_declarations：函數聲明。procedure_specifications：過程聲明。7.1.2包體包體用于實現包頭所定義的過程和函數。關于包的基本規則如下：（1）在包頭聲明過的是外部可以調用的過程、函數、變量。在包頭中定義的所有的常量、變量、過程和函數等都是公有的；就是說在包頭內定義的公有組件（常量、變量、過程和函數）既可以在包內使用也可以在包外部調用。（2）在包體內也可以定義過程、函數、變量，但這些定義只能在包體內可見。在包體內的各個過程和函數本身定義的處理和變量都是私有的。就是說在包體中定義的私有組件（常量、變量、過程和函數）都只能在包內使用，不能由其他子程序調用。（3）在包中聲明的時候，必須是前向聲明的，也就是對象需要引用的另一個對象必須在前面首先聲明。（4）包頭是必須存在的，包體是可選的。（5）包頭必須在包體前創建，否則創建不了包體。（6）只在包頭中包括公用變量、過程、函數的聲明

創建包體使用CREATEPACKAGEBODY語句：CREATE[ORREPLACE]PACKAGEBODYpackage_name{AS|IS}private_variable_declarations|private_type_declarations|private_exception_declarations|private_cursor_declarations|function_declarations|procedure_specificationsEND[package_name]7.2調用和執行包組件調用方法分為包內調用和包外調用。1．包內調用【例】在過程addemp中對validate過程進行包內調用。CREATEORREPLACEPACKAGEBODYmypackageISPROCEDUREaddemp(enoNUMBER,nameVARCHAR2,salNUMBER,dnoNUMBER)ISBEGINIFvalidate(dno)THENINSERTINTOemp(empno,ename,sal,deptno)VALUES(eno,name,sal,dno);ELSEraise_application_error(-200010,'不存在該部門');ENDIF;EXCEPTIONWHENDUP_VAL_ON_INDEXTHENraise_application_error(-200012,'該員工已經存在,重復插入');ENDmypackage;C2．調用包的公共變量包名：mypackage，公共變量pub_pSQL>EXECmypackage.pub_p:=20;3．調用包的公共過程包名：mypackage，公共過程：addemp(enoNUMBER,nameVARCHAR2,salNUMBER,dnoNUMBER)SQL>EXECmypackage.addemp(1111,'SCOTT',5600);SQL>EXECmypackage.addemp(1111,'SCOTT',5600,88);D4．調用包的公共函數包名：mypackage，公共函數：get_sal(enoNUMBER)功能：把員工號eno作為輸入參數，得到此員工的工資，返回一個NUMBER類型的數據。SQL>VARsalaryNUMBERSQL>EXEC:salary:=mypackage.get_sal(9012);SQL>PRINTsalary執行結果：SALARY---------5600C7.3觸觸發器介紹7.3.1觸觸發器的的作用與特點點觸發器的主要要作用是能夠夠實現由主建建和外鍵所不不能保證的參參照完整性和和數據的一致致性。除此之之外，觸發器器還有許多不不同的作用：：（1）強化約約束。觸發器器能夠實現比比CHECK語句更為為復雜的約束束。（2）跟蹤變變化。觸發器器可以偵測數數據庫內部的的操作，在沒沒有更新權限限的情況下不不允許對數據據庫進行更新新操作。（3）安全性性。可以基于于數據庫的值值使用戶具有有操作數據庫庫的某種權利利。例如：①①可以基于時時間限制用戶戶的操作，例例如，不允許許下班后和節節假日修改數數據庫數據；；②可以基于于數據庫中的的數據限制用用戶的操作，，例如，不允允許股票的價價格的升幅一一次超過10%；③審計計。可以跟蹤蹤用戶對數據據庫的操作，，審計用戶操操作數據庫的的語句，把用用戶對數據庫庫的更新寫入入審計表。（4）自動生生成導出的列列值。（5）防止無無效的事務。。（6）在分布布式數據庫中中實施跨越結結點的參照完完整性。（7）提供透透明事件日志志。A7.3.2觸觸發器的的類型和組成成1．觸發器的的類型對每一觸發語語句可有四種種類型觸發器器：（1）行觸發發器。對受觸觸發語句所影影響的每一行行進行觸發。。（2）語句觸觸發器。定義義語句觸發器器時要指定觸觸發時間，即即觸發器是在在觸發語句執執行之后觸發發還是在之前前觸發。（3）BEFORE觸發發器。該觸發發器執行觸發發器動作是在在觸發語句執執行之前。（4）AFTER觸發器器。該觸發器器執行觸發器器動作是在觸觸發語句執行行之后。C2．觸發器的的組成一個觸發器有有三個基本部部件：觸發事事件或語句、、觸發器條件件、觸發器動動作。觸發事件是指指引起觸發器器激發的事件件。觸發語句句指數據操縱縱（DML））語句，這些些語句是INSERT、、UPDATE或DELETE語句句。觸發器條件是是一個布爾表表達式。觸發器的動作作是一個PL/SQL塊塊（過程），，由SQL語語句和PL/SQL語句句組成。觸發發語句發出后后，當觸發器器的條件為TRUE時，，它被執行。。D7.4建建立DML觸觸發器DML事件觸觸發器可以是是語句或行級級觸發器。DML語句觸觸發器在觸發發語句之前或或之后觸發。。DML行級級觸發器在語語句影響的行行變化之前或或之后觸發。。用戶可以給給單一事件和和類型定義多多個觸發器，，但沒有任何何方法可以增增強多觸發器器觸發的命令令。7.4.1語語句觸發發器創建觸發器的的語法如下：：CREATE[ORREPLACE]TRIGGER觸發器器名觸發時時間觸發事事件ON表名[FOREACHROW]PL/SQL語句其中：（1）觸發器器名。觸發器器對象的名稱稱。由于觸發發器是數據庫庫自動執行的的，因此該名名稱只是一個個名稱，沒有有實質的用途途。觸發器的的名稱與過程程名稱不一樣樣，它有單獨獨的命名空間間，觸發器可可以與表名或或過程名同名名，但是在同同一個SCHEMA中的的觸發器名稱稱不能相同。。（2）觸發時時間。指明觸觸發器何時執執行。BEFORE表示在數據據庫動作之前前觸發器執行行。AFTER表表示在數據庫庫動作之后觸觸發器執行。。（3）觸發事事件。指明哪哪些數據庫動動作會觸發此此觸發器：INSERT：數據庫插插入會觸發此此觸發器。UPDATE：數據庫修修改會觸發此此觸發器。DELETE：數據庫刪刪除會觸發此此觸發器。（4）表名。。數據庫觸發發器所在的表表。FOREACHROW：對表的的每一行觸發發器執行一次次。如果沒有有這一選項，，則只對整個個表執行一次次。1．建立BEFORE語語句觸發器器【例】現在向向數據庫SCHOOL中中增加一個新新表DEL_TCH_PT，表的結結構同表TCH_PT一一樣，主要存存放從TCH_PT表中中刪除的記錄錄。因此需要要創建一個觸觸發器，當表表TCH_PT每刪除一一行，就把刪刪除的記錄寫寫到日志DEL_TCH_PT中。。觸發器創建如如下：CREATEORREPLACETRIGGERTCH_HISBEFOREDELETEONTCH_PTFROEACHROWBEGININSERTINTODEL_TCH_PT(XH,XM,XB)VALUES(:OLD.XH,:OLD.XM,:OLD.XB)ENDTCH_HIS;其中OLD修修飾訪問操作作前列的值。。2．使用條件件謂詞觸發器同時包包含多個事件件（插入、更更新、刪除）），為了區分分具體哪個事事件可以使用用相應的三個個條件謂詞：：INSERTING：當當觸發事件為為INSERT，該謂詞詞返回TRUE，否則為為FALSE。UPDATING：當觸觸發事件為UPDATE，該謂詞返返回TRUE，否則為FALSE。。DELETING：當觸觸發事件為DELETE，該謂詞返返回TRUE，否則為FALSE。。3．建立AFTER語語句觸發器【例】利用觸觸發器在數據據庫SCHOOL的TCH_PT中執行插插入、更新和和刪除3種操操作后給出相相應的提示。。CREATETRIGGERTISHI_TCHAFTERINSERTORUPDATEORDELETEONTCH_PTFOREACHROWDECLAREINFORCHAR(10);BEGINIFINSETINGTHENINFOR:="插入";ELSIFUPDATINGTHENINFOR:="更更新";ELSEINFOR:="刪除";ENDIF;INSERTINTOSQL_INFORVALUES(INFOR);ENDTISHI_TCH;7.4.2行行觸發器器行觸發器是執執行DML操操作時，每作作用一行就觸觸發一次的觸觸發器，主要要用于審計數數據變化。建立行觸發器器的語法如下下：CREATE[ORREPLACE]TRIGGERtrigger_nameTimingevent1[ORevent2ORevent3]ONtable_namePL/SQLblobk;其中：trigger_name是是觸發器名稱稱，Timing是觸觸發時機，event是是觸發事件，，table_name指DML操作的表表名。1．建立BEFORE行行觸發器【例】保障職職工的月薪不不低于5000元。觸發器創建如如下：CREATEORREPLACETRIGGERtrigger_up_salBEFOREUPDATEOFsalONEMPFOREACHROWBEGINIF:new.sal<5000THENRaise_application_error(-20010,'工資不能能低于5000');ENDIF;END;【例】把職工工進行修改的的記錄全部寫寫入審計表，，以便于審計計。審計表如下：：AUDIT(nameVARCHAR2(10),oldsalNUMBER,newsalNUMBER,timeDATE)。創建觸發器如如下：CREATEORREPLACETRIGGERtrigger_up_salAFTERUPDATEOFsalONEMPFOREACHROWDECLAREcountINT;BEGINSELECTcount(*)INTOcount//查詢審審計表的記錄錄個數存入變變量CountFROMauditWHEREname:old.ename;IFcount=0THEN//如果審計計表為空,插插入記錄INSERTINTOauditVALUES(:old.ename,:old.sal,:new.sal,SYSDATE)ELSE//如如果審計表不不為空,更新新記錄UPDATEauditSEToldsal=:old.sal,newsal=:new.sal,time=SYSDATEWHEREname=:old.enameENDIF;END2．建立AFTER行行觸發器7.5建建立INSTEADOF觸發器器INSTEADO