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;