Menu

文档

规格约束

注意: 自治事务执行时,将会在后台启动自治事务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
            dbe_output.print_line('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)。

本文档遵循知识共享许可协议CC 4.0 (http://creativecommons.org/Licenses/by/4.0/)。