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

程序包和觸發(fā)器2程序包程序包是對(duì)相關(guān)過程、函數(shù)、變量、游標(biāo)和異常等對(duì)象的封裝程序包由規(guī)范和主體兩部分組成聲明程序包中公共對(duì)象。包括類型、變量、常量、異常、游標(biāo)規(guī)范和子程序規(guī)范等聲明程序包私有對(duì)象和實(shí)現(xiàn)在包規(guī)范中聲明的子程序和游標(biāo)程序包規(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)點(diǎn)模塊化更輕松的應(yīng)用程序設(shè)計(jì)信息隱藏新增功能性能更佳5用于定義公用的信息createorreplacepackagemonis--定義公用的動(dòng)態(tài)游標(biāo)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程序包中的游標(biāo)游標(biāo)的定義分為游標(biāo)規(guī)范和游標(biāo)主體兩部分在包規(guī)范中聲明游標(biāo)規(guī)范時(shí)必須使用RETURN子句指定游標(biāo)的返回類型RETURN子句指定的數(shù)據(jù)類型可以是：用

%ROWTYPE屬性引用表定義的記錄類型程序員定義的記錄類型7程序包中的游標(biāo)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有關(guān)子程序和程序包的信息USER_OBJECTS視圖包含用戶創(chuàng)建的子程序和程序包的信息USER_SOURCE視圖存儲(chǔ)子程序和程序包的源代碼SELECTobject_name,object_typeFROMUSER_OBJECTSWHEREobject_typeIN('PROCEDURE','FUNCTION','PACKAGE','PACKAGEBODY');SELECTline,textFROMUSER_SOURCEWHERENAME='TEST';9內(nèi)置程序包8-1擴(kuò)展數(shù)據(jù)庫的功能為PL/SQL提供對(duì)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

包可用來生成隨機(jī)整數(shù)觸發(fā)器觸發(fā)器是當(dāng)特定事件出現(xiàn)時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過程特定事件可以是執(zhí)行更新的DML語句和DDL語句觸發(fā)器不能被顯式調(diào)用觸發(fā)器中不能使用事務(wù)觸發(fā)器的功能：自動(dòng)生成數(shù)據(jù)自定義復(fù)雜的安全權(quán)限提供審計(jì)和日志記錄啟用復(fù)雜的業(yè)務(wù)邏輯創(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語句和代碼，它們?cè)诎l(fā)出了觸發(fā)器語句且觸發(fā)限制的值為真時(shí)運(yùn)行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ù)庫級(jí)觸發(fā)器語句級(jí)(表級(jí))觸發(fā)器行級(jí)觸發(fā)器INSTEADOF觸發(fā)器觸發(fā)器類型DDL觸發(fā)器數(shù)據(jù)庫級(jí)觸發(fā)器DML觸發(fā)器語句級(jí)觸發(fā)器行級(jí)觸發(fā)器INSTEADOF觸發(fā)器在模式中執(zhí)行DDL語句時(shí)執(zhí)行在發(fā)生打開、關(guān)閉、登錄和退出數(shù)據(jù)庫等系統(tǒng)事件時(shí)執(zhí)行在對(duì)表或視圖執(zhí)行DML語句時(shí)執(zhí)行無論受影響的行數(shù)是多少，都只執(zhí)行一次對(duì)DML語句修改的每個(gè)行執(zhí)行一次用于用戶不能直接使用DML語句修改的視圖19系統(tǒng)事件觸發(fā)器系統(tǒng)事件：數(shù)據(jù)庫啟動(dòng)、關(guān)閉，服務(wù)器錯(cuò)誤createtriggerad_startup

afterstartup

ondatabase

begin

--dosomestuff

end;

/必須具有Sysdba權(quán)限的用戶才可以創(chuàng)建系統(tǒng)觸發(fā)器。20模式級(jí)觸發(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ā)器表級(jí)觸發(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;/語句級(jí)觸發(fā)器23表級(jí)觸發(fā)器表級(jí)觸發(fā)器可以驗(yàn)證用戶是否有對(duì)表操作的權(quán)限驗(yàn)證在一段時(shí)間內(nèi)無法對(duì)表進(jìn)行操作24行級(jí)觸發(fā)器new或者old只能用在行級(jí)triggerforeachrow表示行級(jí)觸發(fā)器行級(jí)觸發(fā)器只有Updating才有字段級(jí)的判斷行級(jí)觸發(fā)器的判斷操作行級(jí)觸發(fā)器的序列號(hào)行級(jí)觸發(fā)器行級(jí)觸發(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;