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

1、程序包和觸發(fā)器,2,程序包,程序包是對相關過程、函數(shù)、變量、游標和異常等對象的封裝 程序包由規(guī)范和主體兩部分組成,3,創(chuàng)建程序包,程序包規(guī)范 CREATE OR REPLACE PACKAGE package_name IS|AS Public item declarations Subprogram specification END package_name;,程序包主體 CREATE OR REPLACE PACKAGE BODY package_name IS|AS Private item declarations Subprogram bodies BEGIN Initializa

2、tion END package_name;,4,程序包的優(yōu)點,模塊化 更輕松的應用程序設計 信息隱藏 新增功能 性能更佳,5,用于定義公用的信息,create or replace package Pack_Common is -定義公用的動態(tài)游標 type System_RefCursor is ref cursor; -定義公用的自定義數(shù)據(jù)類型 type Common_Record is Record( month varchar2(10), area varchar2(10), OpenCount number, DeleCount number ); global_save_mone

3、y constant number := 3; global_get_money constant number := 4; global_change_money constant number := 5; end Pack_Common;,6,程序包中的游標,游標的定義分為游標規(guī)范和游標主體兩部分 在包規(guī)范中聲明游標規(guī)范時必須使用 RETURN 子句指定游標的返回類型 RETURN子句指定的數(shù)據(jù)類型可以是： 用 %ROWTYPE 屬性引用表定義的記錄類型 程序員定義的記錄類型,7,程序包中的游標 2-2,8,有關子程序和程序包的信息,USER_OBJECTS 視圖包含用戶創(chuàng)建的子程序和程序

4、包的信息 USER_SOURCE 視圖存儲子程序和程序包的源代碼,SELECT object_name, object_type FROM USER_OBJECTS WHERE object_type IN (PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY);,SELECT line, text FROM USER_SOURCE WHERE NAME=TEST;,9,內(nèi)置程序包 8-1,擴展數(shù)據(jù)庫的功能 為 PL/SQL 提供對 SQL 功能的訪問 用戶 SYS 擁有所有程序包 是公有同義詞 可以由任何用戶訪問,10,內(nèi)置程序包,DBMS_RANDOM 包

5、可用來生成隨機整數(shù),觸發(fā)器,觸發(fā)器是當特定事件出現(xiàn)時自動執(zhí)行的存儲過程 特定事件可以是執(zhí)行更新的DML語句和DDL語句 觸發(fā)器不能被顯式調(diào)用 觸發(fā)器中不能使用事務 觸發(fā)器的功能： 自動生成數(shù)據(jù) 自定義復雜的安全權限 提供審計和日志記錄 啟用復雜的業(yè)務邏輯,創(chuàng)建觸發(fā)器的語法,CREATE OR REPLACE TRIGGER trigger_name AFTER | BEFORE | INSTEAD OF INSERT OR UPDATE OF column_list OR DELETE ON table_or_view_name REFERENCING OLD AS old / NEW AS

6、new FOR EACH ROW WHEN (condition) pl/sql_block;,觸發(fā)器的組成部分 3-1,觸發(fā)器由三部分組成： 觸發(fā)器語句（事件） 定義激活觸發(fā)器的 DML 事件和 DDL 事件 觸發(fā)器限制 執(zhí)行觸發(fā)器的條件，該條件必須為真才能激活觸發(fā)器 觸發(fā)器操作（主體） 包含一些 SQL 語句和代碼，它們在發(fā)出了觸發(fā)器語句且觸發(fā)限制的值為真時運行,14,觸發(fā)器的組成部分,觸發(fā)器的組成部分,創(chuàng)建觸發(fā)器,CREATE OR REPLACE TRIGGER aiu_itemfile AFTER INSERT ON itemfile FOR EACH ROW BEGIN IF (:

7、NEW.qty_hand = 0) THEN DBMS_OUTPUT.PUT_LINE(警告：已插入記錄，但數(shù)量為零); ELSE DBMS_OUTPUT.PUT_LINE(已插入記錄); END IF; END; /,觸發(fā)器類型,觸發(fā)器的類型有：,觸發(fā)器類型,模式(DDL) 觸發(fā)器,DML 觸發(fā)器,數(shù)據(jù)庫級 觸發(fā)器,語句級(表級)觸發(fā)器,行級觸發(fā)器,INSTEAD OF觸發(fā)器,觸發(fā)器類型,19,系統(tǒng)事件觸發(fā)器,系統(tǒng)事件：數(shù)據(jù)庫啟動、關閉，服務器錯誤 create trigger ad_startupafter startupon databasebegin- do some stuffend

8、;/ 必須具有Sysdba權限的用戶才可以創(chuàng)建系統(tǒng)觸發(fā)器。,20,模式級觸發(fā)器,用戶事件：用戶登陸、注銷，create / alter / drop / grant / revoke / rename / truncate / logoff,模式觸發(fā)器,模式觸發(fā)器,表級觸發(fā)器,SQL CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT OR UPDATE OR DELETE ON order_master BEGIN IF UPDATING THEN DBMS_OUTPUT.PUT_LINE(已更新 ORDER_MASTER 中的數(shù)據(jù)); ELSIF

9、DELETING THEN DBMS_OUTPUT.PUT_LINE(已刪除 ORDER_MASTER 中的數(shù)據(jù)); ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(已在 ORDER_MASTER 中插入數(shù)據(jù)); END IF; END; /,語句級觸發(fā)器,23,表級觸發(fā)器,表級觸發(fā)器可以驗證用戶是否有對表操作的權限 驗證在一段時間內(nèi)無法對表進行操作,24,行級觸發(fā)器,new或者old只能用在行級trigger for each row表示行級觸發(fā)器 行級觸發(fā)器只有Updating才有字段級的判斷 行級觸發(fā)器的判斷操作 行級觸發(fā)器的序列號,行級觸發(fā)器,行級觸

10、發(fā)器,SQL CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20); SQL CREATE SEQUENCE SEQ_TEST; SQL CREATE OR REPLACE TRIGGER BI_TEST_TRG BEFORE INSERT OR UPDATE OF ID ON TEST_TRG FOR EACH ROW BEGIN IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE RAISE_APPLICATION_ERROR(-20020, 不允許更新ID值！); END IF; END; /,啟用和禁用觸發(fā)器 刪除觸發(fā)器,啟用、禁用和刪除觸發(fā)器,SQL ALTER TRIGGER aiu_itemfile DISABLE;,SQL ALTER TRIGGER aiu_itemfile ENABLE;,SQL DROP TRIGGER aiu_itemfile;,查看有關觸發(fā)器的信息,SQL SELECT TRIGGER_NAME FROM USER_TRIGG