规格约束

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

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

    1. CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
    3. CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
    4. $$
    5. DECLARE
    6.     PRAGMA AUTONOMOUS_TRANSACTION;
    7. BEGIN
    8. INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
    9. RETURN NEW;
    10. END
    11. $$ LANGUAGE PLPGSQL;

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

    1. create table t1(a int ,b text);
    3. DECLARE 
    4.     --PRAGMA AUTONOMOUS_TRANSACTION;
    5. BEGIN
    6.     DECLARE 
    7.         PRAGMA AUTONOMOUS_TRANSACTION;
    8.     BEGIN
    9.         dbe_output.print_line('just use call.');
    10.         insert into t1 values(1,'can you rollback!');
    11.     END;
    12.     insert into t1 values(2,'I will rollback!');
    13.     rollback;
    14. END;
    15. /
    17. select * from t1;

  • 自治事务不支持ref_cursor参数传递。

    1. create table sections(section_ID int);
    2. insert into sections values(1);
    3. insert into sections values(1);
    4. insert into sections values(1);
    5. insert into sections values(1);
    7. CREATE OR REPLACE function proc_sys_ref()
    8. return SYS_REFCURSOR
    9. IS
    10. declare
    11.         PRAGMA AUTONOMOUS_TRANSACTION;
    12.         C1 SYS_REFCURSOR;
    13. BEGIN
    14.     OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
    15.     return C1;
    16. END;
    17. /
    19. CREATE OR REPLACE PROCEDURE proc_sys_call() AS 
    20. DECLARE
    21.      C1 SYS_REFCURSOR;
    22.      TEMP NUMBER(4);
    23. BEGIN
    24.      c1 = proc_sys_ref();
    25.      if c1%isopen then
    26.              raise notice '%','ok';
    27.     end if;
    29.      LOOP
    30.              FETCH C1 INTO TEMP;
    31.              raise notice '%',C1%ROWCOUNT;
    32.              EXIT WHEN C1%NOTFOUND;
    33.      END LOOP;
    34. END;
    35. /
    37. select proc_sys_call();
    39. CREATE OR REPLACE function proc_sys_ref(OUT C2 SYS_REFCURSOR, OUT a int)
    40. return SYS_REFCURSOR
    41. IS
    42. declare
    43.         PRAGMA AUTONOMOUS_TRANSACTION;
    44.         C1 SYS_REFCURSOR;
    45. BEGIN
    46.     OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
    47.     return C1;
    48. END;
    49. /
    51. CREATE OR REPLACE PROCEDURE proc_sys_call() AS 
    52. DECLARE
    53.      C1 SYS_REFCURSOR;
    54.      TEMP NUMBER(4);
    55.      a int;
    56. BEGIN
    57.      OPEN C1 FOR SELECT section_ID FROM sections ORDER BY section_ID;
    58.      c1 = proc_sys_ref(C1,a);
    59.      if c1%isopen then
    60.              raise notice '%','ok';
    61.     end if;
    63.      LOOP
    64.              FETCH C1 INTO TEMP;
    65.              raise notice '%',C1%ROWCOUNT;
    66.              EXIT WHEN C1%NOTFOUND;
    67.      END LOOP;
    68. END;
    69. /
    71. select proc_sys_call();

  • 自治事务函数不支持返回非out形式的record类型。

  • 不支持修改自治事务的隔离级别。
  • 不支持自治事务返回集合类型（setof）。