LightDB支持自治事务

背景
在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官网查看：

LightDB: 更快、更稳、更懂金融的分布式关系型数据库