Oracle數(shù)據(jù)庫開發(fā)實用教程第章

D第7章PL/SQL包與觸發(fā)器學習要點創(chuàng)建包調(diào)用和執(zhí)行包組件建立DML觸發(fā)器建立INSTEADOF觸發(fā)器

本章主要內(nèi)容

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

包由兩個分離的部件組成：包頭和包體。包頭也叫包說明或包規(guī)范，它包括函數(shù)頭、過程頭、和外部可視數(shù)據(jù)結(jié)構(gòu)。在創(chuàng)建包時，一般先建立包頭，再建立包體。

B7.1.1包頭包頭用于定義包的公用組件，如：常量、類型、變量、游標、過程和函數(shù)等。創(chuàng)建包必須首先創(chuàng)建包規(guī)范，創(chuàng)建包規(guī)范的語法如下：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：函數(shù)聲明。procedure_specifications：過程聲明。7.1.2包體包體用于實現(xiàn)包頭所定義的過程和函數(shù)。關(guān)于包的基本規(guī)則如下：（1）在包頭聲明過的是外部可以調(diào)用的過程、函數(shù)、變量。在包頭中定義的所有的常量、變量、過程和函數(shù)等都是公有的；就是說在包頭內(nèi)定義的公有組件（常量、變量、過程和函數(shù)）既可以在包內(nèi)使用也可以在包外部調(diào)用。（2）在包體內(nèi)也可以定義過程、函數(shù)、變量，但這些定義只能在包體內(nèi)可見。在包體內(nèi)的各個過程和函數(shù)本身定義的處理和變量都是私有的。就是說在包體中定義的私有組件（常量、變量、過程和函數(shù)）都只能在包內(nèi)使用，不能由其他子程序調(diào)用。（3）在包中聲明的時候，必須是前向聲明的，也就是對象需要引用的另一個對象必須在前面首先聲明。（4）包頭是必須存在的，包體是可選的。（5）包頭必須在包體前創(chuàng)建，否則創(chuàng)建不了包體。（6）只在包頭中包括公用變量、過程、函數(shù)的聲明

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