oracle使用自治事務記錄系統日志

在我們對表記錄執行DML操作時，一方面，我們需要把錯誤記錄到資料庫的日志表中，另一方面，由于錯誤我們需要復原核心事務，此時我們可以在記錄日志的存儲過程中使用自治事務

1.　定義事務日志表

CREATE TABLE "SCOTT"."EXCEPTION_LOG" 
   (	"ID" NUMBER(18,0), 
	"ERROR_CODE" NUMBER(10,0), 
	"ERROR_MESSAGE" VARCHAR2(2000), 
	"CREATE_TIME" TIMESTAMP (6) DEFAULT Sysdate, //使用系統時間定義日志被建立時間
	"DESCRIPTION" VARCHAR2(500), 
	"EXCEPTION_LEVEL" VARCHAR2(30)
   )
           

2.　定義兩個表USERS表各USERS_TEST表，其中業務邏輯為把USERS_TEST表中資料複制到users表中，如果users_test表中username在users中的username列不存在，則把記錄插入users表中，如果存在，則把重複記錄的日志記錄到exception_log表中，如果有其它的異常，則復原插入記錄，但不復原日志記錄

CREATE TABLE "SCOTT"."USERS" 
   (	"USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"USERNAME" VARCHAR2(30) NOT NULL ENABLE, 
	"PASSWORD" VARCHAR2(50) NOT NULL ENABLE, 
	 CONSTRAINT "UNIQUE_NAME" UNIQUE ("USERNAME") //定義唯一限制在此列上
）
           

CREATE TABLE "SCOTT"."USERS_TEST" 
   (	"USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"USERNAME" VARCHAR2(30) NOT NULL ENABLE, 
	"PASSWORD" VARCHAR2(50) NOT NULL ENABLE
   )
           

3. 定義記錄日志包

CREATE OR REPLACE PACKAGE system_log IS

  PROCEDURE exception_log(ERROR_CODE      IN NUMBER,
                          error_message   IN VARCHAR2,
                          exception_level IN VARCHAR2,
                          description     IN VARCHAR2);

END system_log;
           

CREATE OR REPLACE PACKAGE BODY system_log IS

  PROCEDURE exception_log(ERROR_CODE      IN NUMBER,
                          error_message   IN VARCHAR2,
                          exception_level IN VARCHAR2,
                          description     IN VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION; //聲明自治事務
  BEGIN
    INSERT INTO exception_log
      (ERROR_CODE, error_message, exception_level, description)
    VALUES
      (ERROR_CODE, error_message, exception_level, description);
      COMMIT;　//送出事務，記得一定要送出，否則會報異常
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('can not insert log in exception_log table, error message is:' ||
                           dbms_utility.format_error_backtrace);
  END exception_log;

END system_log;
           

4. 定義轉儲users_test和users表中的資料

CREATE OR REPLACE PROCEDURE prc_unique_user(id NUMBER) IS
  CURSOR user_cursor IS
    SELECT * FROM users_test WHERE user_id > id;
BEGIN
  FOR uc IN user_cursor
  LOOP
    BEGIN
      INSERT INTO users
        (username, password)
      VALUES
        (uc.username, uc.password);
    EXCEPTION
      WHEN dup_val_on_index THEN
      
        DECLARE
          sql_code NUMBER := SQLCODE;
        BEGIN
          system_log.exception_log(sql_code,
                                   dbms_utility.format_error_stack ||
                                   ', error line:' ||
                                   dbms_utility.format_error_backtrace,
                                   'warn',
                                   '插入重複的username在users表中，username是：' ||
                                   uc.username || ',行号user_id是：' ||
                                   uc.user_id);
        END;
        dbms_output.put_line('插入重複的username在users表中，username是：' ||
                             uc.username || ',行号user_id是：' || uc.user_id);
        ROLLBACK;
    END;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    dbms_output.put_line('插入user表中出現了其它異常' ||
                         dbms_utility.format_error_backtrace);
END prc_unique_user;