示例

• 示例1：支持在PL/SQL的存储过程内使用COMMIT/ROLLBACK，后续示例依赖此用例。

  gaussdb=# DROP TABLE IF EXISTS EXAMPLE1; 
  NOTICE:  table "example1" does not exist, skipping
  DROP TABLE
  gaussdb=# CREATE TABLE EXAMPLE1(COL1 INT);
  CREATE TABLE
  gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE()
  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;
  /
  CREATE PROCEDURE
  gaussdb=# CALL TRANSACTION_EXAMPLE();
   transaction_example 
  ---------------------
  
  (1 row)

• 示例2：

  支持含有EXCEPTION的存储过程使用COMMIT/ROLLBACK。

  支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK。

  支持DDL在COMMIT/ROLLBACK后的提交/回滚。

  gaussdb=# CREATE OR REPLACE PROCEDURE TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK()
  AS
  BEGIN
   DROP TABLE IF EXISTS TEST_COMMIT; 
   CREATE TABLE TEST_COMMIT(A INT, B INT);
   INSERT INTO TEST_COMMIT SELECT 1, 1;
   COMMIT;
          CREATE TABLE TEST_ROLLBACK(A INT, B INT);
   RAISE EXCEPTION 'RAISE EXCEPTION AFTER COMMIT';
  EXCEPTION
      WHEN OTHERS THEN
   INSERT INTO TEST_COMMIT SELECT 2, 2;
   ROLLBACK;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();
  NOTICE:  table "test_commit" does not exist, skipping
  CONTEXT:  SQL statement "DROP TABLE IF EXISTS TEST_COMMIT"
  PL/pgSQL function test_commit_insert_exception_rollback() line 3 at SQL statement
   test_commit_insert_exception_rollback 
  ---------------------------------------
  
  (1 row)

• 示例3：支持在事务块里调用含有COMMIT/ROLLBACK的存储过程，即通过BEGIN/START/END等开启控制的外部事务。

  gaussdb=# BEGIN;
      -- TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK定义见示例2
      CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();
  END;
   test_commit_insert_exception_rollback 
  ---------------------------------------
  
  (1 row)
  COMMIT

• 示例4：支持多数PL/SQL的上下文和语句内调用COMMIT/ROLLBACK，包括常用的IF/FOR/CURSOR LOOP/WHILE。

  gaussdb=# CREATE OR REPLACE PROCEDURE TEST_COMMIT2()
  IS
  BEGIN
      DROP TABLE IF EXISTS TEST_COMMIT;
      CREATE TABLE TEST_COMMIT(A INT);
      FOR I IN REVERSE 3..0 LOOP
   INSERT INTO TEST_COMMIT SELECT I;
   COMMIT;
      END LOOP;
      FOR I IN REVERSE 2..4 LOOP
   UPDATE TEST_COMMIT SET A=I;
   COMMIT;
      END LOOP;
  EXCEPTION
  WHEN OTHERS THEN   
   INSERT INTO TEST_COMMIT SELECT 4;
      COMMIT;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CALL TEST_COMMIT2();
   test_commit2 
  --------------
  
  (1 row)

• 示例5：支持存储过程返回值与简单表达式计算。

  gaussdb=# CREATE OR REPLACE PROCEDURE exec_func3(RET_NUM OUT INT)
  AS
  BEGIN
      RET_NUM := 1+1;
  COMMIT;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CALL exec_func3('');
   ret_num 
  ---------
         2
  (1 row)
  gaussdb=# CREATE OR REPLACE PROCEDURE exec_func4(ADD_NUM IN INT)
  AS
  SUM_NUM INT;
  BEGIN
  SUM_NUM := ADD_NUM + exec_func3();
  COMMIT;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CALL exec_func4(1);
   exec_func4 
  ------------
  
  (1 row)

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

  gaussdb=# SET explain_perf_mode='normal';
  SET
  gaussdb=# SHOW explain_perf_mode;
   explain_perf_mode 
  -------------------
   normal
  (1 row)
  gaussdb=# SHOW enable_force_vector_engine;
   enable_force_vector_engine 
  ----------------------------
   off
  (1 row)
  gaussdb=# CREATE OR REPLACE PROCEDURE GUC_ROLLBACK()
  AS
  BEGIN
      SET enable_force_vector_engine = on;
      COMMIT;
      SET explain_perf_mode TO pretty;
      ROLLBACK;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CALL GUC_ROLLBACK();
   guc_rollback 
  --------------
  
  (1 row)
  gaussdb=# SHOW explain_perf_mode;
   explain_perf_mode 
  -------------------
   normal
  (1 row)
  gaussdb=# SHOW enable_force_vector_engine;
   enable_force_vector_engine 
  ----------------------------
   on
  (1 row)
  gaussdb=# SET enable_force_vector_engine = off;
  SET

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

  gaussdb=# 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 FUNCTION
  gaussdb=# CREATE TRIGGER TRIGGER_EXAMPLE AFTER DELETE ON EXAMPLE1 
  FOR EACH ROW EXECUTE PROCEDURE FUNCTION_TRI_EXAMPLE2();
  CREATE TRIGGER
  gaussdb=# DELETE FROM EXAMPLE1;
  ERROR:  Can not commit/rollback if it's atomic is true: can not use commit rollback in Complex SQL
  CONTEXT:  PL/pgSQL function function_tri_example2() line 7 at COMMIT

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

  gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE1()
  IMMUTABLE
  AS
  EXP INT;
  BEGIN
      FOR i IN 0..20 LOOP
          SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
          IF i % 2 = 0 THEN
              COMMIT;
          ELSE
              ROLLBACK;
          END IF;
      END LOOP;
  END;
  /
  CREATE PROCEDURE
  gaussdb=#CALL TRANSACTION_EXAMPLE1();
  ERROR:  Can not commit/rollback if it's atomic is true: commit/rollback/savepoint is not allowed in a non-volatile function
  CONTEXT:  PL/pgSQL function transaction_example1() line 7 at COMMIT

• 示例9：不支持存储过程中任何变量的提交，包括存储过程内声明的变量或者传入的参数。

  gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE2(EXP_OUT OUT INT)
  AS
  EXP INT:=-1;
  BEGIN
      EXP_OUT := 0;
      EXP := 0;
      COMMIT;
      DBE_OUTPUT.PRINT_LINE('EXP IS:'||EXP);
      DBE_OUTPUT.PRINT_LINE('EXP_OUT IS:'||EXP_OUT);
      EXP := 1;
      EXP_OUT := 1;
      ROLLBACK;
      DBE_OUTPUT.PRINT_LINE('EXP IS:'||EXP);
      DBE_OUTPUT.PRINT_LINE('EXP_OUT IS:'||EXP_OUT);
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CALL TRANSACTION_EXAMPLE2(1);
  EXP IS:0
  EXP_OUT IS:0
  EXP IS:1
  EXP_OUT IS:1
   exp_out 
  ---------
         1
  (1 row)

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

  gaussdb=# CREATE OR REPLACE FUNCTION TRANSACTION_EXAMPLE3()
  RETURN INT
  IS
  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;
      RETURN 1;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# SELECT * FROM example1 WHERE col1=TRANSACTION_EXAMPLE3();
  ERROR:  cannot call transaction statements in EXECUTE IMMEDIATE statement.
  CONTEXT:  PL/pgSQL function transaction_example3() line 6 at EXECUTE statement

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

  gaussdb=# 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;
  /
  CREATE PROCEDURE
  gaussdb=# CALL TRANSACTION_EXAMPLE4();
  ERROR:  Can not commit/rollback if it's atomic is true: transaction statement in store procedure with GUC setting in option clause is not supported
  CONTEXT:  PL/pgSQL function transaction_example4() line 6 at COMMIT

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

  gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE5(INTIN IN INT, INTOUT OUT INT)
  AS
  BEGIN
  INTOUT := INTIN + 1;
  COMMIT;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# 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; 
  /
  CREATE PROCEDURE
  gaussdb=# CALL TRANSACTION_EXAMPLE6();
  ERROR:  Can not commit/rollback if it's atomic is true: transaction statement in store procedure used as cursor is not supported
  CONTEXT:  PL/pgSQL function transaction_example5(integer) line 4 at COMMIT
  referenced column: transaction_example5
  PL/pgSQL function transaction_example6() line 8 at FETCH

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

  gaussdb=# CREATE OR REPLACE PROCEDURE exec_func1()
  AS
  BEGIN
      CREATE TABLE TEST_exec(A INT);
  COMMIT;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CREATE OR REPLACE PROCEDURE exec_func2()
  AS
  BEGIN
  EXECUTE exec_func1();
  COMMIT;
  END;
  /
  CREATE PROCEDURE
  gaussdb=# CALL exec_func2();
  ERROR:  Can not commit/rollback if it's atomic is true: transaction statement in store procedure used as a expression is not supported
  CONTEXT:  PL/pgSQL function exec_func1() line 4 at COMMIT
  PL/pgSQL function exec_func2() line 3 at EXECUTE statement