oracle的自治事务语序只提交自己块里的语句,不提交前面执行的sql语句。
通过pragma autonomous_transaction 申明过程为自治过程。当然也可以在函数 触发器等sql块中申明使用。
例子:
create table logs(name varchar2(20),errors varchar2(20));
create or replace procedure p_deail_log(pname in varchar2,errors in varchar2)
as
pragma autonomous_transaction;
begin
insert into logs values(pname,errors);
commit;
end;
as
pragma autonomous_transaction;
begin
insert into logs values(pname,errors);
commit;
end;
测试案例1
begin
insert into logs values('main','test');--第一条sql
p_deail_log('default','test');
dbms_lock.sleep(10);
rollback;
insert into logs values('main1','test1');
commit;
end;
begin
insert into logs values('main','test');--第一条sql
p_deail_log('default','test');
dbms_lock.sleep(10);
rollback;
insert into logs values('main1','test1');
commit;
end;
结果
在第一条sql语句并不会被过程p_deail_log提交,会被混滚。过程p_deail_log里的sql正常提交。
再建普通函数
create or replace procedure p_deail_log1(pname in varchar2,errors in varchar2)
as
begin
insert into logs values(pname,errors);
commit;
end;
as
begin
insert into logs values(pname,errors);
commit;
end;
案例2:
begin
insert into logs values('main','test');--第一条sql
p_deail_log1('default','test');
dbms_lock.sleep(10);
rollback;
insert into logs values('main1','test1');
commit;
end;
结果
第一条sql会被过程p_deail_log1提交。