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

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

本章主要內(nèi)容

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

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

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

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