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

1、ACCP V4.0第八章第八章觸發(fā)器和內(nèi)置程序包ACCP V4.02回顧q子程序是命名的 PL/SQL 塊，存儲在數(shù)據(jù)庫中，可帶參數(shù)并可在需要時隨時調(diào)用q有兩種類型的PL/SQL子程序，即過程和函數(shù)q過程用戶執(zhí)行特定的任務(wù)，函數(shù)用于執(zhí)行任務(wù)并返回值q程序包是對相關(guān)類型、變量、常量、游標、異常、過程和函數(shù)等對象的封裝q程序包由兩部分組成，即包規(guī)范和包主體q使用程序包的優(yōu)點是：模塊化、更輕松的程序設(shè)計、信息隱藏、新增功能以及性能更佳ACCP V4.03目標q理解和應(yīng)用觸發(fā)器q了解內(nèi)置程序包ACCP V4.04觸發(fā)器q觸發(fā)器是當(dāng)特定事件出現(xiàn)時自動執(zhí)行的存儲過程q特定事件可以是執(zhí)行更新的DML語句和D

2、DL語句q觸發(fā)器不能被顯式調(diào)用q觸發(fā)器的功能：q自動生成數(shù)據(jù)q自定義復(fù)雜的安全權(quán)限q提供審計和日志記錄q啟用復(fù)雜的業(yè)務(wù)邏輯ACCP V4.05創(chuàng)建觸發(fā)器的語法CREATE OR REPLACE TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OFINSERT OR UPDATE OF column_list OR DELETEON table_or_view_nameREFERENCING OLD AS old / NEW AS newFOR EACH ROWWHEN (condition)pl/sql_block;ACCP V4.06觸發(fā)器的組成部分

3、 3-1觸發(fā)器由三部分組成：q觸發(fā)器語句（事件）q定義激活觸發(fā)器的 DML 事件和 DDL 事件q觸發(fā)器限制q執(zhí)行觸發(fā)器的條件，該條件必須為真才能激活觸發(fā)器q觸發(fā)器操作（主體）q包含一些 SQL 語句和代碼，它們在發(fā)出了觸發(fā)器語句且觸發(fā)限制的值為真時運行ACCP V4.07觸發(fā)器的組成部分 3-2 SQL CREATE OR REPLACE TRIGGER trig_salAFTER UPDATE OF empsal ON salary_records觸發(fā)器語句為 salary_records 表創(chuàng)建 trig-sal 觸發(fā)器在更新 emp_sal 列之后激活觸發(fā)器觸發(fā)器限制SQL FOR E

4、ACH ROWWHEN (NEW.empsalOLD.empsal)DECLARESal_diff NUMBER;只有在WHEN子句中的條件得到滿足時，才激活trig_sal 觸發(fā)器觸發(fā)器操作SQL BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OUTPUT.PUT_LINE(工資差額：sal_diff);END;如果WHEN子句中的條件得到滿足，將執(zhí)行BEGIN 塊中的代碼ACCP V4.08觸發(fā)器的組成部分 3-3Oracle 數(shù)據(jù)庫更新表表保存更新激活觸發(fā)器觸發(fā)器AFTER 觸發(fā)器的工作原理BEFORE 觸發(fā)器的工作原理更新表表激活觸發(fā)器觸發(fā)

5、器保存更新Oracle 數(shù)據(jù)庫ACCP V4.09創(chuàng)建觸發(fā)器CREATE OR REPLACE TRIGGER aiu_itemfileAFTER INSERTON itemfileFOR EACH ROWBEGIN IF (:NEW.qty_hand = 0) THEN DBMS_OUTPUT.PUT_LINE(警告：已插入記錄，但數(shù)量為零); ELSE DBMS_OUTPUT.PUT_LINE(已插入記錄); END IF;END;/ACCP V4.010觸發(fā)器類型 6-1q觸發(fā)器的類型有：觸發(fā)器類型模式(DDL)觸發(fā)器DML觸發(fā)器數(shù)據(jù)庫級觸發(fā)器語句級觸發(fā)器行級觸發(fā)器INSTEAD OF觸

6、發(fā)器ACCP V4.011觸發(fā)器類型 6-2q DDL 觸發(fā)器q 數(shù)據(jù)庫級觸發(fā)器q DML 觸發(fā)器q 語句級觸發(fā)器q 行級觸發(fā)器q INSTEAD OF 觸發(fā)器在模式中執(zhí)行 DDL 語句時執(zhí)行在發(fā)生打開、關(guān)閉、登錄和退出數(shù)據(jù)庫等系統(tǒng)事件時執(zhí)行在對表或視圖執(zhí)行DML語句時執(zhí)行無論受影響的行數(shù)是多少，都只執(zhí)行一次對DML語句修改的每個行執(zhí)行一次用于用戶不能直接使用 DML 語句修改的視圖ACCP V4.012觸發(fā)器類型 6-3q行級觸發(fā)器SQL CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20);SQL CREATE SEQUENCE SEQ_T

7、EST;SQL CREATE OR REPLACE TRIGGER BI_TEST_TRGBEFORE INSERT OR UPDATE OF IDON TEST_TRGFOR EACH ROWBEGIN IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE RAISE_APPLICATION_ERROR(-20020, 不允許更新ID值！); END IF;END;/ACCP V4.013觸發(fā)器類型 6-4SQL CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT

8、 OR UPDATE OR DELETE ON order_masterBEGIN IF UPDATING THEN DBMS_OUTPUT.PUT_LINE(已更新 ORDER_MASTER 中的數(shù)據(jù)); ELSIF DELETING THEN DBMS_OUTPUT.PUT_LINE(已刪除 ORDER_MASTER 中的數(shù)據(jù)); ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(已在 ORDER_MASTER 中插入數(shù)據(jù)); END IF;END;/q語句級觸發(fā)器ACCP V4.014觸發(fā)器類型 6-5SQL CREATE OR REPLACE TRIG

9、GER upd_ord_view INSTEAD OF UPDATE ON ord_view FOR EACH ROW BEGIN UPDATE order_master SET vencode=:NEW.vencode WHERE orderno = :NEW.orderno;DBMS_OUTPUT.PUT_LINE(已激活觸發(fā)器); END; /qINSTEAD OF 觸發(fā)器ACCP V4.015觸發(fā)器類型 6-6SQL CREATE TABLE dropped_obj ( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DA

10、TE);SQL CREATE OR REPLACE TRIGGER log_drop_objAFTER DROP ON SCHEMABEGIN INSERT INTO dropped_obj VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;/q模式觸發(fā)器ACCP V4.016q啟用和禁用觸發(fā)器q刪除觸發(fā)器啟用、禁用和刪除觸發(fā)器SQL ALTER TRIGGER aiu_item;SQL ALTER TRIGGER aiu_item;SQL DROP TRIGGER aiu_itemfile;ACCP V4.017查看有關(guān)觸發(fā)

11、器的信息SQL SELECT TRIGGER_NAME FROM USER_TRIGGERSWHERE TABLE_NAME=EMP;SQL SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSEFROM USER_TRIGGERSWHERE TRIGGER_NAME = BIU_EMP_DEPTNO;qUSER_TRIGGERS 數(shù)據(jù)字典視圖包含有關(guān)觸發(fā)器的信息ACCP V4.018內(nèi)置程序包 8-1q擴展數(shù)據(jù)庫的功能q為 PL/SQL 提供對 SQL 功能的訪問q用戶 SYS 擁有所有程序包q是公有同義詞q可以由任何用戶訪問ACCP V4.01

12、9內(nèi)置程序包 8-2q一些內(nèi)置程序包：程序包名稱程序包名稱說明說明STANDARD和DBMS_STANDARD定義和擴展PL/SQL語言環(huán)境DBMS_LOB提供對 LOB數(shù)據(jù)類型進行操作的功能DBMS_OUTPUT處理PL/SQL塊和子程序輸出調(diào)試信息DBMS_RANDOM提供隨機數(shù)生成器DBMS_SQL允許用戶使用動態(tài) SQLDBMS_XMLDOM用DOM模型讀寫XML類型的數(shù)據(jù)DBMS_XMLPARSERXML解析，處理XML文檔內(nèi)容和結(jié)構(gòu)DBMS_XMLQUERY提供將數(shù)據(jù)轉(zhuǎn)換為 XML 類型的功能DBMS_XSLPROCESSOR提供XSLT功能，轉(zhuǎn)換XML文檔UTL_FILE用 PL

13、/SQL 程序來讀寫操作系統(tǒng)文本文件ACCP V4.020內(nèi)置程序包 8-3SQL SET SERVEROUTPUT ONSQL BEGIN DBMS_OUTPUT.PUT_LINE(打印三角形); FOR i IN 1.9 LOOP FOR j IN 1.i LOOP DBMS_OUTPUT.PUT(*); END LOOP for_j; DBMS_OUTPUT.NEW_LINE; END LOOP for_i;END;/qDBMS_OUTPUT包顯示 PL/SQL 塊和子程序的調(diào)試信息。ACCP V4.021內(nèi)置程序包 8-4qDBMS_LOB 包提供用于處理大型對象的過程和函數(shù)qDBMS

14、_XMLQUERY 包用于將查詢結(jié)果轉(zhuǎn)換為 XML 格式ACCP V4.022內(nèi)置程序包 8-5SQL DECLARE result CLOB; xmlstr VARCHAR2(32767); line VARCHAR2(2000); line_no INTEGER := 1;BEGIN result := DBMS_XMLQuery.getXml(SELECT empno, ename FROM employee); xmlstr := DBMS_LOB.SUBSTR(result,32767); LOOP EXIT WHEN xmlstr IS NULL; line := SUBSTR(x

15、mlstr,1,INSTR(xmlstr,CHR(10)-1); DBMS_OUTPUT.PUT_LINE(line_no | : | line); xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10)+1); line_no := line_no + 1; END LOOP;END;/ACCP V4.023內(nèi)置程序包 8-6SQL SET SERVEROUTPUT ONSQL DECLARE l_num NUMBER; counter NUMBER;BEGIN counter:=1; WHILE counter CREATE DIRECTORY TEST_

16、DIR AS C:DEVELOP;SQL GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;qUTL_FILE 包用于讀寫操作系統(tǒng)文本文件q操作文件的一般過程是打開、讀或?qū)?、關(guān)閉qUTL_FILE 包指定文件路徑依賴于 DIRECTORY 對象ACCP V4.025內(nèi)置程序包 8-8SQL SET SERVEROUTPUT ONSQL DECLARE input_file UTL_; input_buffer VARCHAR2(4000);BEGIN input_ UTL_( TEST_DIR, employees.xml, r); LOOP UTL_(input_); DBMS_OUTPUT.PUT_LINE(input_buffer); END LOOP; UTL_(input_file);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(-);E