oracle自治事务

       最近同事在编写测试脚本的时候有一个问题无法实现。他们通过一个测试案例的数据进行理赔测试，但是在执行完理赔操作之后，计划进行rollback，好用这个测试数据继续测试其他场景，但是同时希望写入的结果数据可以保留，不会回归。帮忙研究了一下，发现自治事务可以解决这个问题。

       他们通过一个测试案例的数据

       自治事务是指的存储过程和函数可以自己处理内部事务不受外部事务的影响，用pragma autonomous_transaction来声明，要创建一个自治事务，您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中，使用PL/SQL中的PRAGMA AUTONOMOUS_TRANSACTION语句。在这样的模块或过程中执行的SQL语句都是自治的。

       常用场景：

触发器抛错导致事务回滚，日志的记录需要用自治事务进行记录；

代码抛错，导致数据库链接的整个事务回滚，需要在回滚之前入库的数据，比如系统间交互的日志报文，也许要用自治事务的方式，将数据记录到数据库里，方便问题的定位。

 

       实验代码如下：

--oracle自治事务使用案例脚本

-- 创建测试用临时表
create table lts_auto_object_table
(
  user_name                    VARCHAR2(40),
  user_id                      NUMBER(8)
);
--创建测试用日志表
create table lts_auto_object_log
(
  user_name                    VARCHAR2(40),
  user_id                      NUMBER(8),
  OPERATE_TYPE                 VARCHAR2(40),
  created_time                 date
);

--创建自治事务存储过程，过程方法是往日志表写入数据。
-- 自治事务（pragma autonomous_transaction）
create or replace procedure lts_insert_auto_object_pkg(i_user_name        in varchar2,
                                                                                 i_user_id          in number) is
  pragma autonomous_transaction;
begin
  INSERT INTO lts_auto_object_log
    (user_name,
     user_id,
     OPERATE_TYPE,
     created_time)
  VALUES
    (i_user_name,
     i_user_id,
     'update',
     sysdate);
  commit;

end;

-- 创建触发器，当表lts_auto_object_table做更新操作时触发，插入日志到lts_auto_object_log表，抛出异常(不允许对lts_auto_object_table做更新)
create or replace trigger trg_auto_object_modify after update on lts_auto_object_table for each row 
declare 
  v_username VARCHAR2(40):= 'lts';  
  v_userid   NUMBER(8)   := 1;      
begin
  lts_insert_auto_object_pkg(v_username, seq_test.nextval); --自治事务，调用这个过程的时候它就会独立于调用它的父事务进行操作 
  RAISE_APPLICATION_ERROR(-20100, '抛出自定义异常,回滚事务');
end;

--清除创建的所有对象
drop trigger trg_auto_object_modify;
drop procedure lts_insert_auto_object_pkg;
drop table lts_auto_object_table;
drop table lts_auto_object_log;

--往表中写入记录
insert into lts_auto_object_table(user_name,user_id) values('lts007',7);
select * from lts_auto_object_table ;

--进行修改，由于定义的异常，进行回滚
update lts_auto_object_table set user_id = 9 where user_name = 'lts007';

--检查执行结果，update没有生效，但是自治事务的日志写入成功。
select * from lts_auto_object_table ;
select * from lts_auto_object_log ;