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

• 示例6：支持存储过程内GUC参数的回滚提交。

  SHOW explain_perf_mode;
  SHOW enable_force_vector_engine;
  
  CREATE OR REPLACE PROCEDURE GUC_ROLLBACK()
  AS
  BEGIN
      SET enable_force_vector_engine = on;
      COMMIT;
      SET explain_perf_mode TO pretty;
      ROLLBACK;
  END;
  /
  
  call GUC_ROLLBACK();
  SHOW explain_perf_mode;
  SHOW enable_force_vector_engine;
  SET enable_force_vector_engine = off;
  

• 示例7：函数（Function）中允许调用commit/rollback语句。

  CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE1() RETURN INT
  AS
  EXP INT;
  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;
      SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
      RETURN EXP;
  END;
  /
  

• 示例8：函数（Fucntion）中允许调用带有commit/rollback语句的存储过程。

  CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE2() RETURN INT
  AS
  EXP INT;
  BEGIN
      --transaction_example为存储过程，带有commit/rollback语句
      CALL transaction_example();
      SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
      RETURN EXP;
  END;
  /
  

• 示例9：不允许Trigger的存储过程包含commit/rollback语句，或调用带有commit/rollback语句的存储过程。

  CREATE OR REPLACE FUNCTION FUNCTION_TRI_EXAMPLE2() RETURN TRIGGER
  AS
  EXP INT;
  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;
      SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
  END;
  /
  
  CREATE TRIGGER TRIGGER_EXAMPLE AFTER DELETE ON EXAMPLE1 
  FOR EACH ROW EXECUTE PROCEDURE FUNCTION_TRI_EXAMPLE2();
  
  DELETE FROM EXAMPLE1;