openGauss SQL参考—事务管理（4）

• 示例10：不支持带有IMMUABLE以及SHIPPABLE的存储过程调用commit/rollback，或调用带有commit/rollback语句的存储过程。

  CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE1()
  IMMUTABLE
  AS
  BEGIN
      FOR i IN 0..20 LOOP
          INSERT INTO EXAMPLE1 (col1) VALUES (i);
          IF i % 2 = 0 THEN
              COMMIT;
          ELSE
              ROLLBACK;
          END IF;
      END LOOP;
  END;
  /
  

• 示例11：不支持出现在SQL中的调用（除了Select Procedure）。

  CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE3()
  AS
  BEGIN
      FOR i IN 0..20 LOOP
          INSERT INTO EXAMPLE1 (col1) VALUES (i);
          IF i % 2 = 0 THEN
              EXECUTE IMMEDIATE 'COMMIT';
          ELSE
              EXECUTE IMMEDIATE 'ROLLBACK';
          END IF;
      END LOOP;
  END;
  /
  

• 示例12：存储过程头带有GUC参数设置的不允许调用commit/rollback语句。

  CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE4()
  SET ARRAY_NULLS TO "ON"
  AS
  BEGIN
      FOR i IN 0..20 LOOP
          INSERT INTO EXAMPLE1 (col1) VALUES (i);
          IF i % 2 = 0 THEN
              COMMIT;
          ELSE
              ROLLBACK;
          END IF;
      END LOOP;
  END;
  /
  

• 示例13：游标open的对象不允许为带有commit/rollback语句的存储过程。

  CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE5(INTIN IN INT, INTOUT OUT INT)
  AS
  BEGIN
  INTOUT := INTIN + 1;
  COMMIT;
  END;
  /
  
  CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE6()
  AS
  CURSOR CURSOR1(EXPIN INT)
  IS SELECT TRANSACTION_EXAMPLE5(EXPIN);
  INTEXP INT;
  BEGIN
      FOR i IN 0..20 LOOP
          OPEN CURSOR1(i);
          FETCH CURSOR1 INTO INTEXP;
          INSERT INTO EXAMPLE1(COL1) VALUES (INTEXP);
          IF i % 2 = 0 THEN
              COMMIT;
          ELSE
              ROLLBACK;
          END IF;
          CLOSE CURSOR1;
      END LOOP;
  END; 
  /
  

• 示例14：不支持CURSOR/EXECUTE语句，以及各类表达式内调用COMMIT/ROLLBACK。

  CREATE OR REPLACE PROCEDURE exec_func1()
  AS
  BEGIN
      CREATE TABLE TEST_exec(A INT);
  COMMIT;
  END;
  /
  CREATE OR REPLACE PROCEDURE exec_func2()
  AS
  BEGIN
  EXECUTE exec_func1();
  COMMIT;
  END;
  /
  

• 示例15：存储过程使用保存点回退事务部分修改。

  CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE1()
  AS
  BEGIN
      INSERT INTO EXAMPLE1 VALUES(1);
      SAVEPOINT s1;
      INSERT INTO EXAMPLE1 VALUES(2);
      ROLLBACK TO s1;  -- 回退插入记录2
      INSERT INTO EXAMPLE1 VALUES(3);
  END;
  /
  

• 示例16：存储过程中使用保存点回退到存储过程外部定义的保存点。

  CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE2()
  AS
  BEGIN
      INSERT INTO EXAMPLE1 VALUES(2);
      ROLLBACK TO s1;  -- 回退插入记录2
      INSERT INTO EXAMPLE1 VALUES(3);
  END;
  /
  
  BEGIN;
  INSERT INTO EXAMPLE1 VALUES(1);
  SAVEPOINT s1;
  CALL STP_SAVEPOINT_EXAMPLE2();
  SELECT * FROM EXAMPLE1;
  COMMIT;
  

• 示例17：存储过程外部回退到存储过程中定义的保存点。

  CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE3()
  AS
  BEGIN
      INSERT INTO EXAMPLE1 VALUES(1);
      SAVEPOINT s1;
      INSERT INTO EXAMPLE1 VALUES(2);
  END;
  /
  
  BEGIN;
  INSERT INTO EXAMPLE1 VALUES(3);
  CALL STP_SAVEPOINT_EXAMPLE3();
  ROLLBACK TO SAVEPOINT s1; --回退存储过程中插入记录2
  SELECT * FROM EXAMPLE1;
  COMMIT;
  

• 示例18：不支持存储过程中释放存储过程外部定义的保存点。

  CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE3() 
  AS 
  BEGIN 
      INSERT INTO EXAMPLE1 VALUES(2); 
      RELEASE SAVEPOINT s1; -- 释放存储过程外部定义的保存点 
      INSERT INTO EXAMPLE1 VALUES(3); 
  END;
  /
  
  BEGIN; 
  INSERT INTO EXAMPLE1 VALUES(1); 
  SAVEPOINT s1; 
  CALL STP_SAVEPOINT_EXAMPLE3(); 
  COMMIT;