规格约束

注意： 自治事务执行时，将会在后台启动自治事务session，我们可以通过max_concurrent_autonomous_transactions设置自治事务执行的最大并行数量，该参数取值范围为0~1024，默认值为10。当max_concurrent_autonomous_transactions参数设置为0时，自治事务将无法执行。

触发器函数不支持自治事务。

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;

自治事务不支持非顶层匿名块调用（仅支持顶层自治事务,包括存储过程、函数、匿名块）。

create table t1(a int ,b text);

DECLARE
    --PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    DECLARE
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        raise notice 'just use call.';
        insert into t1 values(1,'can you rollback!');
    END;
    insert into t1 values(2,'I will rollback!');
    rollback;
END;
/

select * from t1;

自治事务不支持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）。

反馈