觸發(fā)器和內(nèi)置程序包

程序包和觸發(fā)器2程序包程序包是對相關過程、函數(shù)、變量、游標和異常等對象的封裝程序包由規(guī)范和主體兩部分組成聲明程序包中公共對象。包括類型、變量、常量、異常、游標規(guī)范和子程序規(guī)范等聲明程序包私有對象和實現(xiàn)在包規(guī)范中聲明的子程序和游標程序包規(guī)范主體3創(chuàng)建程序包程序包規(guī)范CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];程序包主體CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];4程序包的優(yōu)點模塊化更輕松的應用程序設計信息隱藏新增功能性能更佳5用于定義公用的信息createorreplacepackagemonis--定義公用的動態(tài)游標typeSystem_RefCursorisrefcursor;--定義公用的自定義數(shù)據(jù)類型typeCommon_RecordisRecord(monthvarchar2(10),areavarchar2(10),OpenCountnumber,DeleCountnumber);global_save_moneyconstantnumber:=3;global_get_moneyconstantnumber:=4;global_change_moneyconstantnumber:=5;endmon;6程序包中的游標游標的定義分為游標規(guī)范和游標主體兩部分在包規(guī)范中聲明游標規(guī)范時必須使用RETURN子句指定游標的返回類型RETURN子句指定的數(shù)據(jù)類型可以是：用

%ROWTYPE屬性引用表定義的記錄類型程序員定義的記錄類型7程序包中的游標2-2SQL>CREATEORREPLACEPACKAGEcur_packISCURSORord_cur(vcodeVARCHAR2)RETURNorder_master%ROWTYPE;PROCEDUREord_pro(vcodeVARCHAR2);ENDcur_pack;/SQL>CREATEORREPLACEPACKAGEBODYcur_packASCURSORord_cur(vcodeVARCHAR2)RETURNorder_master%ROWTYPEISSELECT*FROMorder_masterWHEREVENCODE=vcode;PROCEDUREord_pro(vcodeVARCHAR2)ISor_recorder_master%ROWTYPE;BEGINOPENord_cur(vcode);LOOPFETCHord_curINTOor_rec;EXITWHENord_cur%NOTFOUND;DBMS_OUTPUT.PUT_LIne(’返回的值為'||or_rec.orderno);ENDLOOP;ENDord_pro;ENDcur_pack;/8有關子程序和程序包的信息USER_OBJECTS視圖包含用戶創(chuàng)建的子程序和程序包的信息USER_SOURCE視圖存儲子程序和程序包的源代碼SELECTobject_name,object_typeFROMUSER_OBJECTSWHEREobject_typeIN('PROCEDURE','FUNCTION','PACKAGE','PACKAGEBODY');SELECTline,textFROMUSER_SOURCEWHERENAME='TEST';9內(nèi)置程序包8-1擴展數(shù)據(jù)庫的功能為PL/SQL提供對SQL功能的訪問用戶SYS擁有所有程序包是公有同義詞可以由任何用戶訪問10內(nèi)置程序包SQL>SETSERVEROUTPUTONSQL>DECLAREl_numNUMBER;counterNUMBER;BEGINcounter:=1;WHILEcounter<=10LOOPl_num:=DBMS_RANDOM.RANDOM;DBMS_OUTPUT.PUT_LINE(l_num);counter:=counter+1;ENDLOOP;END;/DBMS_RANDOM

包可用來生成隨機整數(shù)觸發(fā)器觸發(fā)器是當特定事件出現(xiàn)時自動執(zhí)行的存儲過程特定事件可以是執(zhí)行更新的DML語句和DDL語句觸發(fā)器不能被顯式調(diào)用觸發(fā)器中不能使用事務觸發(fā)器的功能：自動生成數(shù)據(jù)自定義復雜的安全權限提供審計和日志記錄啟用復雜的業(yè)務邏輯創(chuàng)建觸發(fā)器的語法CREATE[ORREPLACE]TRIGGERtrigger_nameAFTER|BEFORE|INSTEADOF[INSERT][[OR]UPDATE[OFcolumn_list]][[OR]DELETE]ONtable_or_view_name[REFERENCING{OLD[AS]old/NEW[AS]new}][FOREACHROW][WHEN(condition)]pl/sql_block;觸發(fā)器的組成部分3-1觸發(fā)器由三部分組成：觸發(fā)器語句（事件）定義激活觸發(fā)器的DML事件和DDL事件觸發(fā)器限制執(zhí)行觸發(fā)器的條件，該條件必須為真才能激活觸發(fā)器觸發(fā)器操作（主體）包含一些SQL語句和代碼，它們在發(fā)出了觸發(fā)器語句且觸發(fā)限制的值為真時運行14觸發(fā)器的組成部分更新表BEFORE觸發(fā)器的工作原理激活觸發(fā)器保存更新Oracle數(shù)據(jù)庫觸發(fā)器的組成部分Oracle數(shù)據(jù)庫更新表保存更新激活觸發(fā)器AFTER觸發(fā)器的工作原理創(chuàng)建觸發(fā)器CREATEORREPLACETRIGGERaiu_itemfileAFTERINSERTONitemfileFOREACHROWBEGINIF(:NEW.qty_hand=0)THENDBMS_OUTPUT.PUT_LINE('警告：已插入記錄，但數(shù)量為零');ELSEDBMS_OUTPUT.PUT_LINE(‘已插入記錄');ENDIF;END;/觸發(fā)器類型觸發(fā)器的類型有：觸發(fā)器類型模式(DDL)觸發(fā)器DML觸發(fā)器數(shù)據(jù)庫級觸發(fā)器語句級(表級)觸發(fā)器行級觸發(fā)器INSTEADOF觸發(fā)器觸發(fā)器類型DDL觸發(fā)器數(shù)據(jù)庫級觸發(fā)器DML觸發(fā)器語句級觸發(fā)器行級觸發(fā)器INSTEADOF觸發(fā)器在模式中執(zhí)行DDL語句時執(zhí)行在發(fā)生打開、關閉、登錄和退出數(shù)據(jù)庫等系統(tǒng)事件時執(zhí)行在對表或視圖執(zhí)行DML語句時執(zhí)行無論受影響的行數(shù)是多少，都只執(zhí)行一次對DML語句修改的每個行執(zhí)行一次用于用戶不能直接使用DML語句修改的視圖19系統(tǒng)事件觸發(fā)器系統(tǒng)事件：數(shù)據(jù)庫啟動、關閉，服務器錯誤createtriggerad_startup

afterstartup

ondatabase

begin

--dosomestuff

end;

/必須具有Sysdba權限的用戶才可以創(chuàng)建系統(tǒng)觸發(fā)器。20模式級觸發(fā)器用戶事件：用戶登陸、注銷，create/alter/drop/grant/revoke/rename/truncate/logoff模式觸發(fā)器SQL>CREATETABLEdropped_obj(obj_nameVARCHAR2(30),obj_typeVARCHAR2(20),drop_dateDATE);SQL>CREATEORREPLACETRIGGERlog_drop_objAFTERDROPONSCHEMABEGININSERTINTOdropped_objVALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);END;/模式觸發(fā)器表級觸發(fā)器SQL>CREATEORREPLACETRIGGERtrgdemoAFTERINSERTORUPDATEORDELETEONorder_masterBEGINIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(‘已更新ORDER_MASTER中的數(shù)據(jù)');ELSIFDELETINGTHENDBMS_OUTPUT.PUT_LINE(‘已刪除ORDER_MASTER中的數(shù)據(jù)');ELSIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(‘已在ORDER_MASTER中插入數(shù)據(jù)');ENDIF;END;/語句級觸發(fā)器23表級觸發(fā)器表級觸發(fā)器可以驗證用戶是否有對表操作的權限驗證在一段時間內(nèi)無法對表進行操作24行級觸發(fā)器new或者old只能用在行級triggerforeachrow表示行級觸發(fā)器行級觸發(fā)器只有Updating才有字段級的判斷行級觸發(fā)器的判斷操作行級觸發(fā)器的序列號行級觸發(fā)器行級觸發(fā)器SQL>CREATETABLETEST_TRG(IDNUMBER,NAMEVARCHAR2(20));SQL>CREATESEQUENCESEQ_TEST;SQL>CREATEORREPLACETRIGGERBI_TEST_TRGBEFOREINSERTORUPDATEOFIDONTEST_TRGFOREACHROWBEGINIFINSERTINGTHENSELECTSEQ_TEST.NEXTVALINTO:NEW.IDFROMDUAL;ELSERAISE_APPLICATION_ERROR(-20020,'不允許更新ID值！');ENDIF;END;/啟用和禁用觸發(fā)器刪除觸發(fā)器啟用、禁用和刪除觸發(fā)器SQL>ALTERTRIGGERaiu_itemfileDISABLE;SQL>ALTERTRIGGERaiu_itemfileENABLE;