在我们对表记录执行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;