您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 實戰筆記（八）：觸發器類型全解析與內建套件實戰應用

編輯：Oracle 資料庫基礎

觸發器分類與語法要素

觸發時機： BEFORE / AFTER / INSTEAD OF
觸發事件： INSERT / UPDATE / DELETE（可指定欄位 UPDATE OF col）
觸發層級： ROW LEVEL（FOR EACH ROW） / STATEMENT LEVEL（預設）
條件謂詞： WHEN (condition)、INSERTING/UPDATING/DELETING
相關名稱： REFERENCING NEW AS new OLD AS old，體內使用 :NEW.col、:OLD.col

行級觸發器範例：條件修改薪資

CREATE OR REPLACE TRIGGER tri_employees2
  BEFORE INSERT OR UPDATE OF first_name ON employees2
  REFERENCING NEW AS newdata OLD AS olddata
  FOR EACH ROW
  WHEN (newdata.first_name = 'chen')
BEGIN
  :newdata.salary := 20000;
  DBMS_OUTPUT.PUT_LINE('new.salary:' || :newdata.salary);
  DBMS_OUTPUT.PUT_LINE('old.salary:' || :olddata.salary);
END;

測試：INSERT INTO employees2 VALUES (38,'SUP','WOR','chen','mp',50000); 或 UPDATE employees2 SET salary=90000, first_name='chen' WHERE employee_id=38;

行級觸發器實戰：自動填充序列值

CREATE SEQUENCE rowtablesequence;

CREATE OR REPLACE TRIGGER set_sequence
  BEFORE INSERT ON rowtable
  FOR EACH ROW
DECLARE
  rseq NUMBER;
BEGIN
  SELECT rowtablesequence.NEXTVAL INTO rseq FROM dual;
  :NEW.id := rseq;
END;

陳述式觸發器：稽核日誌

CREATE TABLE mylog (curr_user VARCHAR2(100), curr_date DATE, oper VARCHAR2(10));

CREATE OR REPLACE TRIGGER tri_mylog
  AFTER INSERT OR DELETE OR UPDATE ON employees2
BEGIN
  IF INSERTING THEN
    INSERT INTO mylog VALUES (USER, SYSDATE, 'INSERT');
  ELSIF DELETING THEN
    INSERT INTO mylog VALUES (USER, SYSDATE, 'DELETE');
  ELSE
    INSERT INTO mylog VALUES (USER, SYSDATE, 'UPDATE');
  END IF;
END;

INSTEAD OF 觸發器：視圖可更新化

CREATE VIEW employee_job AS
  SELECT e.job_id, e.employee_id, e.first_name, e.last_name, j.name
  FROM employees2 e, jobs j
  WHERE e.job_id = j.job_id;

CREATE OR REPLACE TRIGGER tri_view
  INSTEAD OF INSERT ON employee_job
  FOR EACH ROW
BEGIN
  INSERT INTO jobs VALUES (:NEW.job_id, :NEW.name);
  INSERT INTO employees2 (employee_id, first_name, last_name, job_id)
    VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.job_id);
END;

測試：INSERT INTO employee_job VALUES ('OTH',43,'abc','dd','OTHER');

Schema 級觸發器：DDL 稽核

CREATE TABLE dropped_obj (obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE);

CREATE OR REPLACE TRIGGER log_drop_obj
  AFTER DROP ON SCHEMA
BEGIN
  INSERT INTO dropped_obj VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;

測試：CREATE TABLE for_drop (x CHAR); DROP TABLE for_drop; → 查詢 dropped_obj 見紀錄。

Database 級觸發器：啟動記錄

CREATE TABLE database_log (startdate DATE, description VARCHAR2(20));

CREATE OR REPLACE TRIGGER t_database
  AFTER STARTUP ON DATABASE
BEGIN
  INSERT INTO database_log VALUES (SYSDATE, 'STARTUP DATABASE');
  COMMIT;
END;

觸發器管理指令

啟用/停用單一：ALTER TRIGGER trig_name ENABLE|DISABLE;
啟用/停用表上所有：ALTER TABLE tbl_name ENABLE|DISABLE ALL TRIGGERS;
刪除：DROP TRIGGER trig_name;
查詢：DESC USER_TRIGGERS; / SELECT * FROM USER_TRIGGERS;

常用內建套件實戰

DBMS_OUTPUT：PL/SQL 輸出

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('列印三角形');
  FOR i IN 1..9 LOOP
    FOR j IN 1..i LOOP DBMS_OUTPUT.PUT('*'); END LOOP;
    DBMS_OUTPUT.NEW_LINE;
  END LOOP;
END;

DBMS_SQL：動態 SQL 執行 DDL

CREATE PROCEDURE anyddl (sql_text VARCHAR2) AS
  c INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, sql_text, DBMS_SQL.V7);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

EXEC anyddl('CREATE TABLE mytable (id NUMBER(8), name VARCHAR2(20))');
EXEC anyddl('DROP TABLE mytable');

DBMS_RANDOM：隨機數生成

DECLARE
  l_num NUMBER; counter NUMBER := 1;
BEGIN
  WHILE counter <= 10 LOOP
    l_num := ABS(DBMS_RANDOM.RANDOM MOD 100) + 1;
    DBMS_OUTPUT.PUT_LINE(l_num);
    counter := counter + 1;
  END LOOP;
END;

UTL_FILE：作業系統檔案讀寫

前置設定： UTL_FILE_DIR = *（或具體目錄）於 init.ora 或修改參數。

CREATE OR REPLACE PROCEDURE write_txtfile (path VARCHAR2, name VARCHAR2, pstr VARCHAR2) AS
  f UTL_FILE.FILE_TYPE;
BEGIN
  f := UTL_FILE.FOPEN(path, name, 'A', 2000);  -- 追加模式，行長 2000
  UTL_FILE.PUT_LINE(f, pstr);
  UTL_FILE.FCLOSE(f);
END;

CREATE OR REPLACE PROCEDURE read_txtfile (path VARCHAR2, name VARCHAR2) AS
  f UTL_FILE.FILE_TYPE; line VARCHAR2(1000);
BEGIN
  f := UTL_FILE.FOPEN(path, name, 'R', 2000);
  LOOP
    UTL_FILE.GET_LINE(f, line);
    DBMS_OUTPUT.PUT_LINE(line);
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE(f);
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); UTL_FILE.FCLOSE(f);
END;

測試：EXEC write_txtfile('C:\DEVELOP','bfile.txt','測試內容'); EXEC read_txtfile('C:\DEVELOP','bfile.txt');

DBMS_ROWID：解析 ROWID 結構

CREATE OR REPLACE FUNCTION get_rowid (rid VARCHAR2) RETURN VARCHAR2 AS
  obj_num NUMBER; rel_fno NUMBER; blk_num NUMBER; row_num NUMBER; rtype NUMBER;
BEGIN
  DBMS_ROWID.ROWID_INFO(rid, rtype, obj_num, rel_fno, blk_num, row_num);
  RETURN 'OBJECT:'||obj_num||' FILE:'||rel_fno||' BLOCK:'||blk_num||' ROW:'||row_num;
END;

SELECT ROWID, employee_id FROM employees2 WHERE employee_id=2;
SELECT get_rowid('AAAIA/AAKAAAADyAAB') FROM dual;