自治事务pragma autonomous_transaction的使用场景

场景：包体里的函数调用嵌套了两层的存过，存过中有DML，DDL语句，报错ORA-14552,ORA-06512等

 如果存过中只是DML语句报错：

第一层：ORA-14552: 在查询或 DML 中无法执行 DDL, 提交或回退
ORA-14551: 无法在查询中执行 DML 操作

场景还原：

包体：

create or replace package body pkg_temp is

FUNCTION fun_temp(var_code varchar2) return varchar2 is

 ov_tag VARCHAR2(200);
 query_sql varchar2(200);
pragma  AUTONOMOUS_TRANSACTION;

begin

dbms_output.put_line('函数开始');

pro_temp(var_code,ov_tag);
commit;
return ov_tag;

end fun_temp;


end pkg_temp;

第一个存过：

create or replace procedure pro_temp(order_code  VARCHAR2,
                                     resultInfo  out VARCHAR2) is
    ii_sql_text  VARCHAR2(100);
    io_resp_code VARCHAR2(4);
    io_resp_data VARCHAR2(4000);

  begin  
    
    pro_temp_2(order_code,io_resp_data);
    dbms_output.put_line('第一层：' ||io_resp_data );
    resultInfo:='第一层：' ||io_resp_data || '---';
    
    
    insert into temp_log values(resultInfo,sysdate);
    commit;

  EXCEPTION
    WHEN OTHERS THEN
      resultInfo  := '第一层：' || resultInfo|| SQLERRM;
      dbms_output.put_line('第一层执行失败：' || SQLERRM);

    ROLLBACK;
      RETURN;
  end pro_temp;

第二个存过：

create or replace procedure pro_temp_2(order_code  VARCHAR2,
                                     resultInfo  out VARCHAR2) is
    ii_sql_text  VARCHAR2(100);
    io_resp_code VARCHAR2(4);
    io_resp_data VARCHAR2(4000);

  begin
    dbms_output.put_line('第二层输入：' ||order_code );
    resultInfo:='第二层' || order_code;
    insert into temp_log values(resultInfo,sysdate);
    commit;

  EXCEPTION
    WHEN OTHERS THEN
      resultInfo  := '第二层：' || SQLERRM;
      dbms_output.put_line('第二层：' || SQLERRM);

      ROLLBACK;
      RETURN;
  end pro_temp_2;

执行语句：

select pkg_temp.fun_temp('whz') from dual;

如果函数中注释掉：pragma  AUTONOMOUS_TRANSACTION;

就会报错

ORA-14551: 无法在查询中执行 DML 操作

ORA-14552: 在查询或 DML 中无法执行 DDL, 提交或回退

总结：

做了自治事务的声明后，不但函数中可以用DML语句，甚至可以使用DDL语句，比如CREATE TABLE ,CREATE INDEX等。因为自治函数内部所做的任何事务都独立于外部语句所运行的事务。

原理就是自治事务是在某个会话中独立开启一个事务，在其中处理的操作不会影响到同一会话中其他事务未提交的内容。