oracle事务活动日志,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;