规格约束
注意:
- 自治事务执行时,将会在后台启动自治事务session,我们可以通过max_concurrent_autonomous_transactions设置自治事务执行的最大并行数量,该参数取值范围为0~1024,默认值为10。
- 当max_concurrent_autonomous_transactions参数设置为0时,自治事务将无法执行。
- 自治事务新启session后,将使用默认session参数,不共享主session下对象(包括session级别变量,本地临时变量,全局临时表的数据等)。
触发器函数不支持自治事务。
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;
自治事务不支持非顶层匿名块调用(仅支持顶层自治事务,包括存储过程、函数、匿名块)。
自治事务不支持ref_cursor参数传递。
create table sections(section_ID int); insert into sections values(1); insert into sections values(1); insert into sections values(1); insert into sections values(1); CREATE OR REPLACE function proc_sys_ref() return SYS_REFCURSOR IS declare PRAGMA AUTONOMOUS_TRANSACTION; C1 SYS_REFCURSOR; BEGIN OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID; return C1; END; / CREATE OR REPLACE PROCEDURE proc_sys_call() AS DECLARE C1 SYS_REFCURSOR; TEMP NUMBER(4); BEGIN c1 = proc_sys_ref(); if c1%isopen then raise notice '%','ok'; end if; LOOP FETCH C1 INTO TEMP; raise notice '%',C1%ROWCOUNT; EXIT WHEN C1%NOTFOUND; END LOOP; END; / select proc_sys_call(); CREATE OR REPLACE function proc_sys_ref(OUT C2 SYS_REFCURSOR, OUT a int) return SYS_REFCURSOR IS declare PRAGMA AUTONOMOUS_TRANSACTION; C1 SYS_REFCURSOR; BEGIN OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID; return C1; END; / CREATE OR REPLACE PROCEDURE proc_sys_call() AS DECLARE C1 SYS_REFCURSOR; TEMP NUMBER(4); a int; BEGIN OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID; c1 = proc_sys_ref(C1,a); if c1%isopen then raise notice '%','ok'; end if; LOOP FETCH C1 INTO TEMP; raise notice '%',C1%ROWCOUNT; EXIT WHEN C1%NOTFOUND; END LOOP; END; / select proc_sys_call();
自治事务函数不支持返回非out形式的record类型。
不支持修改自治事务的隔离级别。
不支持自治事务返回集合类型(setof)。
create table test_in (id int,a date); create table test_main (id int,a date); insert into test_main values (1111,'2021-01-01'),(2222,'2021-02-02'); truncate test_in,test_main; CREATE OR REPLACE FUNCTION autonomous_f_022(num1 int) RETURNS SETOF test_in LANGUAGE plpgsql AS $$ DECLARE count int :=3; test_row test_in%ROWTYPE; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN while true loop if count=3 then null; else if count=2 then insert into test_main values (count,'2021-03-03'); goto pos1; end if; end if; count=count-1; end loop; insert into test_main values (1000,'2021-04-04'); <<pos1>> for test_row in select * from test_main loop return next test_row; end loop; return; END; $$ ;
意见反馈