背景
在Oracle中支持自治事务(Autonomous Transaction)允许用户创建一个“事务中的事务”,它能独立于其父事务提交或回滚。
利用自治事务,可以挂起当前执行的事务,开始一个新事务,完成一些工作,然后提交或回滚,所有这些都 不影响当前执行事务的状态。同样,当前事务的回退也对自治事务没有影响。
LightDB在2024RP1版本里面,将支持自治事务
支持范围不带游标参数的函数和存储过程
使用时在变量申明区域申明 pragma autonomous_transaction;
示例:
create table test001(id int primary key,ename varchar2);
create table test002(id int primary key,ename varchar2);
select dbms_output.serveroutput('t');
--无自治事务函数
create or replace function func_test000(a inout int,b inout varchar2) return integer as
begin
insert into test002(id,ename) values(a,b);
a := a + 1;
b := b || 'x';
return 0;
end;
/
declare
a int := 0;
b varchar2(10) := 'grace';
c int;
begin
insert into test001(id,ename) values(a,b);
c := func_test000(a,b);
dbms_output.put_line('a:' || a || ',b:' || b || ',c:' || c);
rollback;
end;
/
--打印结果
a:1,b:gracex,c:0
--预期test001,test002因ROLLBACK无数据
select * from test001;
id | ename
----+-------
(0 rows)
select * from test002;
id | ename
----+-------
(0 rows)
--返回常规类型函数(事务自治)
create or replace function func_test001(a inout int,b inout varchar2) return integer as
pragma autonomous_transaction;
begin
insert into test002(id,ename) values(a,b);
a := a + 1;
b := b || 'x';
return 0;
end;
/
declare
a int := 1;
b varchar2(10) := 'bob';
c int;
begin
insert into test001(id,ename) values(a,b);
c := func_test001(a,b);
dbms_output.put_line('a:' || a || ',b:' || b || ',c:' || c);
rollback;
end;
/
--打印结果
a:2,b:bobx,c:0
--预期test001因ROLLBACK无数据,test002因自治事务独立于父事务有数据
select * from test001;
id | ename
----+-------
(0 rows)
select * from test002;
id | ename
----+-------
1 | bob
(1 row)
--返回单行record函数(事务自治)
create or replace function func_test002(a inout int,b inout varchar2) return record as
pragma autonomous_transaction;
begin
insert into test002(id,ename) values(a,b);
a := a + 1;
b := b || 'x';
select a,b into a,b;
end;
/
declare
a int := 2;
b varchar2(10) := 'andy';
c record;
begin
insert into test001(id,ename) values(a,b);
c := func_test002(a,b);
dbms_output.put_line('a:' || a || ',b:' || b || ',c.a:' || c.a || ',c.b:' || c.b);
rollback;
end;
/
--打印结果
a:2,b:andy,c.a:3,c.b:andyx
--预期test001因ROLLBACK无数据,test002因自治事务独立于父事务有数据
select * from test001;
id | ename
----+-------
(0 rows)
select * from test002;
id | ename
----+-------
1 | bob
2 | andy
(2 rows)
--返回多行record函数(事务自治)
create or replace function func_test003(a int,b varchar2) return setof record as
pragma autonomous_transaction;
begin
insert into test002(id,ename) values(a,b);
return query (select id,ename from test002);
end;
/
declare
a int := 3;
b varchar2(10) := 'cindy';
c record;
begin
insert into test001(id,ename) values(a,b);
for c in select * from func_test003(a,b) as t(id int,ename varchar2)
loop
dbms_output.put_line('a:' || a || ',b:' || b || ',c.id:' || c.id || ',c.ename:' || c.ename);
end loop;
insert into test001(id,ename) values(a,b);
end;
/
--打印结果
a:3,b:cindy,c.id:1,c.ename:bob
a:3,b:cindy,c.id:2,c.ename:andy
a:3,b:cindy,c.id:3,c.ename:cindy
ERROR: duplicate key value violates unique constraint "test001_pkey"
DETAIL: Key (id)=(3) already exists.
CONTEXT: SQL statement "insert into test001(id,ename) values(a,b)"
PL/oraSQL function inline_code_block line 11 at SQL statement
--预期test001因主键冲突无数据,test002因自治事务独立于父事务有数据
select * from test001;
id | ename
----+-------
(0 rows)
select * from test002;
id | ename
----+-------
1 | bob
2 | andy
3 | cindy
(3 rows)
--带INOUT参数的存储过程(事务自治)
create or replace procedure proc_test001(a inout int,b inout varchar2) as
pragma autonomous_transaction;
begin
insert into test002(id,ename) values(a,b);
a := a + 1;
b := b || 'x';
end;
/
declare
a int := 4;
b varchar2(10) := 'eric';
begin
insert into test001(id,ename) values(a,b);
call proc_test001(a,b);
dbms_output.put_line('a:' || a || ',b:' || b);
rollback;
end;
/
--打印结果
a:5,b:ericx
--预期test001因ROLLBACK无数据,test002因自治事务独立于父事务有数据
select * from test001;
id | ename
----+-------
(0 rows)
select * from test002;
id | ename
----+-------
1 | bob
2 | andy
3 | cindy
4 | eric
(4 rows)
详情可参考LightDB官网查看: