自治事务控制

-- 创建测试表
create table at_tb2(id int, val varchar(64));
-- 创建声明为自治事务的存储过程
create or replace procedure at_test3(i int)
AS
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  insert into at_tb2 values(1, 'before s1');
  insert into at_tb2 values(2, 'after s1');
  if i > 10 then
  rollback;
  else
  commit;
  end if;
end;
/
commit; -- 如果数据库未开启默认自动提交，则需要手动提交

-- 调用存储过程
call at_test3(6);
 at_test3
----------

(1 row)

select * from at_tb2;
 id |    val
----+-----------
  1 | before s1
  2 | after s1
(2 rows)

-- 支持plpgsql块内的sql语句使用函数/存储过程参数和定义的变量
create or replace procedure at_proc(i int)
as
declare
strval varchar;
pragma autonomous_transaction;
begin
strval:= 'begin:insert by autonomous transaction procedure<at_proc>';
insert into at_tb2 values(i,strval);
commit;
end;
/

commit; -- 如果数据库未开启默认自动提交，则需要手动提交

-- 调用存储过程
call at_proc(1);
at_proc
----------

(1 row)

select * from at_tb2;
id |    val
----+-----------
  1 | before s1
  2 | after s1
  3 | begin:insert by autonomous transaction procedure<at_proc>
(3 rows)


-- 支持在触发器函数内声明自治事务
-- 创建源表及触发表
create table test_trigger_src_tbl(id1 int, id2 int, id3 int);
create table test_trigger_des_tbl(id1 int, id2 int, id3 int);

-- 创建触发器函数
create or replace function tri_insert_func() returns trigger as
$$
declare
pragma autonomous_transaction;
begin
insert into test_trigger_des_tbl values(new.id1, new.id2, new.id3);
return new;
end
$$ language plpgsql;

commit; -- 如果数据库未开启默认自动提交，则需要手动提交

-- 创建insert触发器
create trigger insert_trigger
before insert on test_trigger_src_tbl
for each row
execute procedure tri_insert_func();

-- 执行insert触发事件并检查触发结果
insert into test_trigger_src_tbl values(100,200,300);
select * from test_trigger_src_tbl;
 id1 | id2 | id3
-----------------
 100 | 200 | 300
 (1 row)

select * from test_trigger_des_tbl;  -- 查看触发操作是否生效。
id1 | id2 | id3
-----------------
 100 | 200 | 300
 (1 row)

-- 支持在匿名块内声明自治事务
truncate at_tb2;
-- 匿名块声明自治事务
declare
pragma autonomous_transaction;
strval varchar;
begin strval:='insert by autonomous transaction by anonymous block';
insert into at_tb2 values(1,strval);
commit;
end;
/

select * from at_tb2;
id |                             val
----------------------------------------------------------
1 | insert by autonomous transaction by anonymous block
(1 row)

-- 支持自治事务嵌套使用
-- 创建嵌套自治事务的存储过程
create or replace procedure at_proc_inner(i int)
as
declare
pragma autonomous_transaction;
begin
insert into at_tb2 values(1,'insert by autonomous transaction procedure<at_proc_inner>');
if i>5 then
rollback;
else
commit;
end if;
end;
/

create or replace procedure at_proc_outer(i int)
as
declare
pragma autonomous_transaction;
begin
insert into at_tb2 values(1,'insert by autonomous transaction procedure<at_proc_outer>');
at_proc_inner(i);
if i>10 then rollback;
else
commit;
end if;
end;
/

-- 调用存储过程
-- (1)
truncate at_tb2;
call at_proc_outer(11);
at_proc_outer
-----------------

(1 row)

select * from at_tb2;
 id | v
---------
(0 rows)

-- (2)
truncate at_tb2;
call at_proc_outer(8);
at_proc_outer
-----------------

(1 row)

select * from at_tb2;
id |                                 val
---------------------------------------------------------------
  1 | insert by autonomous transaction procedure<at_proc_outer>
(1 row)
-- (3)
truncate at_tb2;
call at_proc_outer(2);
at_proc_outer
-----------------

(1 row)

select * from at_tb2;
id |                                 val
---------------------------------------------------------------
  1 | insert by autonomous transaction procedure<at_proc_outer>
  1 | insert by autonomous transaction procedure<at_proc_inner>
(2 rows)
VastbaseG100

自治事务控制

弹框头部
