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

程序包和觸發(fā)器2程序包程序包是對相關過程、函數、變量、游標和異常等對象的封裝程序包由規(guī)范和主體兩部分組成聲明程序包中公共對象。包括類型、變量、常量、異常、游標規(guī)范和子程序規(guī)范等聲明程序包私有對象和實現在包規(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;--定義公用的自定義數據類型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子句指定的數據類型可以是：用

%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內置程序包8-1擴展數據庫的功能為PL/SQL提供對SQL功能的訪問用戶SYS擁有所有程序包是公有同義詞可以由任何用戶訪問10內置程序包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

包可用來生成隨機整數觸發(fā)器觸發(fā)器是當特定事件出現時自動執(zhí)行的存儲過程特定事件可以是執(zhí)行更新的DML語句和DDL語句觸發(fā)器不能被顯式調用觸發(fā)器中不能使用事務觸發(fā)器的功能：自動生成數據自定義復雜的安全權限提供審計和日志記錄啟用復雜的業(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數據庫觸發(fā)器的組成部分Oracle數據庫更新表保存更新激活觸發(fā)器AFTER觸發(fā)器的工作原理創(chuàng)建觸發(fā)器CREATEORREPLACETRIGGERaiu_itemfileAFTERINSERTONitemfileFOREACHROWBEGINIF(:NEW.qty_hand=0)THENDBMS_OUTPUT.PUT_LINE('警告：已插入記錄，但數量為零');ELSEDBMS_OUTPUT.PUT_LINE(‘已插入記錄');ENDIF;END;/觸發(fā)器類型觸發(fā)器的類型有：觸發(fā)器類型模式(DDL)觸發(fā)器DML觸發(fā)器數據庫級觸發(fā)器語句級(表級)觸發(fā)器行級觸發(fā)器INSTEADOF觸發(fā)器觸發(fā)器類型DDL觸發(fā)器數據庫級觸發(fā)器DML觸發(fā)器語句級觸發(fā)器行級觸發(fā)器INSTEADOF觸發(fā)器在模式中執(zhí)行DDL語句時執(zhí)行在發(fā)生打開、關閉、登錄和退出數據庫等系統事件時執(zhí)行在對表或視圖執(zhí)行DML語句時執(zhí)行無論受影響的行數是多少，都只執(zhí)行一次對DML語句修改的每個行執(zhí)行一次用于用戶不能直接使用DML語句修改的視圖19系統事件觸發(fā)器系統事件：數據庫啟動、關閉，服務器錯誤createtriggerad_startup

afterstartup

ondatabase

begin

--dosomestuff

end;

/必須具有Sysdba權限的用戶才可以創(chuà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中的數據');ELSIFDELETINGTHENDBMS_OUTPUT.PUT_LINE(‘已刪除ORDER_MASTER中的數據');ELSIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(‘已在ORDER_MASTER中插入數據');ENDIF;END;/語句級觸發(fā)器23表級觸發(fā)器表級觸發(fā)器可以驗證用戶是否有對表操作的權限驗證在一段時間內無法對表進行操作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;