# 事务控制语句
存储过程本身就处于一个事务中，开始调用最外围存储过程时会自动开启一个事务，在调用结束时自动提交或者发生异常时回滚。除了系统自动的事务控制外，也可以使用COMMIT/ROLLBACK来控制存储过程中的事务。在存储过程中调用COMMIT/ROLLBACK命令，将提交/回滚当前事务并自动开启一个新的事务，后续的所有操作都会在此新事务中运行。
**本功能仅9.1.1.100及以上集群版本支持。**
#### 使用场景
- 存储过程支持包含DML语句的COMMIT/ROLLBACK。
- 存储过程支持包含DDL语句的COMMIT/ROLLBACK。
- 嵌套存储过程的调用支持使用COMMIT/ROLLBACK。
- 包含EXCEPTION的存储过程中支持使用COMMIT/ROLLBACK。
- 匿名块内支持COMMIT/ROLLBACK。
- 事务块中支持存储过程带COMMIT/ROLLBACK。
 
#### 约束限制
- 函数（function）中不允许调用COMMIT/ROLLBACK语句。
- 函数（function）中不允许调用带有COMMIT/ROLLBACK语句的存储过程。
- 写cursor loop过程中不允许COMMIT/ROLLBACK。
- 不支持存储过程中设置参数后COMMIT/ROLLBACK。
- cursor loop中不支持调用COMMIT/ROLLBACK语句：提交回滚时游标 (cursor) 被关闭。
- 不支持出现在SQL中的调用（SELECT PROCEDURE除外）。
- 不支持出现在显式子事务savepoint后的存储过程中进行COMMIT、ROLLBACK。
 
#### 示例
- 示例1：存储过程支持包含DML语句的COMMIT/ROLLBACK。
  ```
  CREATE TABLE IF NOT EXISTS test1(a int, b int);
   
  CREATE OR REPLACE PROCEDURE transaction_test1()
  AS
  BEGIN
      FOR i IN 0..9 LOOP
          INSERT INTO test1 (a) VALUES (i);
          IF i % 2 = 0 THEN
              COMMIT;
          ELSE
              ROLLBACK;
          END IF;
      END LOOP;
  END;
  /
   
  CALL transaction_test1();
  ```
  
- 示例2：存储过程支持包含DDL语句的COMMIT/ROLLBACK。
  ```
  DROP TABLE IF EXISTS test2;
   
  CREATE OR REPLACE PROCEDURE transaction_test2()
  AS
  BEGIN
      CREATE TABLE test2(a int);
      COMMIT;
  DROP TABLE test2;
  END;
  /
   
  CALL transaction_test2();
  ```
  
- 示例3：嵌套存储过程的调用（带有COMMIT/ROLLBACK）。
  ```
  CREATE OR REPLACE PROCEDURE transaction_test3()
  AS
  BEGIN
      PERFORM transaction_test1();
      ROLLBACK;
  PERFORM transaction_test2();
  ROLLBACK;
  END;
  /
   
  CALL transaction_test3();
  ```
  
- 示例4：包含EXCEPTION的存储过程（COMMIT/ROLLBACK）。
  ```
  CREATE OR REPLACE PROCEDURE TEST_COMMIT_EXCEPTION()
  AS
  BEGIN
      INSERT INTO test1 (a) VALUES (1);
      INSERT INTO test1 (a) VALUES (2);
      COMMIT;
      INSERT INTO test1 (a) VALUES (3);
      RAISE EXCEPTION 'RAISE EXCEPTION-A';
  EXCEPTION
      WHEN OTHERS THEN
      INSERT INTO test1 (a) VALUES (4);
  END;
  /
   
  CALL TEST_COMMIT_EXCEPTION();
  ```
  
- 示例5：匿名块内支持COMMIT/ROLLBACK。
  ```
  DO LANGUAGE plpgsql $$
  BEGIN
      BEGIN
          INSERT INTO test1 (a) VALUES (1);
          COMMIT;
          INSERT INTO test1 (a) VALUES (1/0);
          COMMIT;
      EXCEPTION
          WHEN division_by_zero THEN
              RAISE NOTICE 'caught division_by_zero';
      END;
  END;
  $$;
  ```
  
- 示例6：事务块中支持存储过程带COMMIT/ROLLBACK。
  ```
  CREATE TABLE IF NOT EXISTS test1(a int, b int);
   
  CREATE OR REPLACE PROCEDURE transaction_test1()
  AS
  BEGIN
      FOR i IN 0..9 LOOP
          INSERT INTO test1 (a) VALUES (i);
          IF i % 2 = 0 THEN
              COMMIT;
          ELSE
              ROLLBACK;
          END IF;
      END LOOP;
  END;
  /
   
  START TRANSACTION;
  CALL transaction_test1();
  COMMIT;
  ```
  
- 示例7：函数（function）中不允许调用COMMIT/ROLLBACK语句。
  ```
  CREATE FUNCTION transaction_test2() RETURNS int
  LANGUAGE plpgsql
  AS $$
  BEGIN
      FOR i IN 0..9 LOOP
          INSERT INTO test1 (a) VALUES (i);
          IF i % 2 = 0 THEN
              COMMIT;
          ELSE
              ROLLBACK;
          END IF;
      END LOOP;
      RETURN 1;
  END
  $$;
   
  SELECT transaction_test2();
  ```
  
- 示例8：函数（function）中不允许调用带有COMMIT/ROLLBACK语句的存储过程。
  ```
  CREATE OR REPLACE PROCEDURE transaction_test1()
  AS
  BEGIN
      INSERT INTO test1 (a) VALUES (1);
      ROLLBACK;
      INSERT INTO test1 (a) VALUES (2);
  END;
  /
  CREATE FUNCTION transaction_test3() RETURNS int
  LANGUAGE plpgsql
  AS $$
  BEGIN
      CALL transaction_test1();
      RETURN 1;
  END;
  $$;
   
  SELECT transaction_test3();
  ```
  
- 示例9：写cursor loop过程中不允许COMMIT/ROLLBACK。
  ```
  DO LANGUAGE plpgsql $$
  DECLARE
      r RECORD;
  BEGIN
      FOR r IN UPDATE test2 SET x = x * 2 RETURNING x LOOP
          INSERT INTO test1 (a) VALUES (r.x);
          ROLLBACK;
      END LOOP;
  END;
  $$;
  ```
  
- 示例10：不支持存储过程中设置参数后COMMIT/ROLLBACK。
  ```
  CREATE OR REPLACE PROCEDURE transaction_test5()
  SET work_mem = 555
  AS
  BEGIN
      insert into test1 select setting from pg_settings where name like 'work_mem';
      COMMIT;
  END;
  /
   
  CALL transaction_test5();
  ```
  
- 示例11：cursor loop中不支持调用COMMIT/ROLLBACK语句，提交回滚时游标 (cursor) 被关闭。
  ```
  DROP TABLE IF EXISTS cursor_back_tab2;
  CREATE TABLE cursor_back_tab2(i_int bigint,i_char varchar ,i_numeric numeric(18,3),i_data date);
  CREATE OR REPLACE PROCEDURE cursor_back_pro_2()
  AS
  declare 
  o_int bigint;
  o_char varchar;
  o_numeric numeric;
  o_data date;
  cursor cursor1 for select * from cursor_back_tab1 order by 1,2,3,4;
  begin 
      open cursor1;
      fetch  next from cursor1 into o_int,o_char,o_numeric,o_data;
      LOOP
             fetch  next from cursor1 into o_int,o_char,o_numeric,o_data;
  exit when cursor1%NOTFOUND;
  insert into cursor_back_tab2 values(o_int,o_char,o_numeric,o_data);
      END LOOP;
  COMMIT;
  ROLLBACK;
  CLOSE cursor1;
  END;
  /
  CALL cursor_back_pro_2();
  ```
  
- 示例12：不支持出现在SQL中的调用（SELECT PROCEDURE除外）。
  ```
  CREATE TABLE IF NOT EXISTS test1(a int, b int);
   
  CREATE OR REPLACE PROCEDURE transaction_test1()
  AS
  BEGIN
      FOR i IN 0..9 LOOP
          INSERT INTO test1 (a) VALUES (i);
          IF i % 2 = 0 THEN
              COMMIT;
          ELSE
              ROLLBACK;
          END IF;
      END LOOP;
  END;
  /
   
  SELECT transaction_test1() from test1;
  ```
  
- 示例13：不支持出现在显式子事务savepoint后的存储过程中进行COMMIT、ROLLBACK。
  ```
  CREATE OR REPLACE PROCEDURE sp_txn_test1_1()
  AS
  BEGIN
  INSERT INTO test1 (a) VALUES (1);
  COMMIT;
  INSERT INTO test1 (a) VALUES (2);
  END;
  /
   
  BEGIN;
  SAVEPOINT s1;
  CALL sp_txn_test1_1();
  ROLLBACK;
  ```