事务管理

事务管理

存储过程本身就处于一个事务中，开始调用最外围存储过程时会自动开启一个事务，在调用结束时自动提交或者发生异常时回滚。除了系统自动的事务控制外，也可以使用COMMIT/ROLLBACK来控制存储过程中的事务。在存储过程中调用COMMIT/ROLLBACK命令，将提交/回滚当前事务并自动开启一个新的事务，后续的所有操作都会在此新事务中运行。

保存点SAVEPOINT是事务中的一个特殊记号，它允许将那些在它建立后执行的命令全部回滚，把事务的状态恢复到保存点所在的时刻。存储过程中允许使用保存点来进行事务管理，当前支持保存点的创建、回滚和释放操作。存储过程中使用回滚保存点只是回退当前事务的修改，而不会改变存储过程的执行流程，也不会回退存储过程中的局部变量值等。

语法格式

1. 定义保存点
2. SAVEPOINT savepoint_name;
3. 回滚保存点
4. ROLLBACK TO [SAVEPOINT] savepoint_name;
5. 释放保存点
6. RELEASE [SAVEPOINT] savepoint_name;

使用场景

支持调用的上下文环境：

支持在PLSQL的存储过程内使用COMMIT/ROLLBACK/SAVEPOINT。
支持含有EXCEPTION的存储过程使用COMMIT/ROLLBACK/SAVEPOINT。
支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK/SAVEPOINT。
支持在事务块里调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程，即通过/BEGIN/START/END等开启控制的外部事务。
支持在子事务中调用含有SAVEPOINT的存储过程，即存储过程中使用外部定义的SAVEPOINT，回退事务状态到存储过程外定义的SAVEPOINT位置。
支持存储过程外部对存储过程内定义的SAVEPOINT可见，即存储过程外可以将事务修改回滚到存储过程中定义SAVEPOINT的位置。
支持多数PLSQL的上下文和语句内调用COMMIT/ROLLBACK/SAVEPOINT，包括常用的IF/FOR/CURSOR LOOP/WHILE。
支持存储过程返回值与简单表达式计算中调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程或者函数。

支持提交/回滚的内容：

支持DDL在COMMIT/ROLLBACK后的提交/回滚。
支持DML的COMMIT/ROLLBACK后的提交。
支持存储过程内GUC参数的回滚提交。

使用限制

不支持调用的上下文环境：

不支持除PLSQL的其他存储过程中调用COMMIT/ROLLBACK/SAVEPOINT，例如PLJAVA、PLPYTHON等。
不支持函数中调用COMMIT/ROLLBACK/SAVEPOINT，包括函数调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程。
不支持事务块中调用了SAVEPOINT后，调用含有COMMIT/ROLLBACK的存储过程。
不支持TRIGGER中调用含有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程。
不支持EXECUTE语句中调用COMMIT/ROLLBACK/SAVEPOINT语句。
不支持在CURSOR语句中打开一个含有COMMIT/ROLLBACK/SAVEPOINT的存储过程。
不支持带有IMMUTABLE以及SHIPPABLE的存储过程调用COMMIT/ROLLBACK/SAVEPOINT，或调用带有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程。
不支持SQL中调用含有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程，除了SELECT PROC以及CALL PROC。
存储过程头带有GUC参数设置的不允许调用COMMIT/ROLLBACK/SAVEPOINT语句。
不支持CURSOR/EXECUTE语句，以及各类表达式内调用COMMIT/ROLLBACK/SAVEPOINT。
自治事务和存储过程事务是两个独立的事务，不能互相使用对方事务中定义的保存点。

不支持提交回滚的内容：

不支持存储过程内声明变量以及传入变量的提交/回滚。
不支持存储过程内必须重启生效的GUC参数的提交/回滚。

示例

示例1：支持在PLSQL的存储过程内使用COMMIT/ROLLBACK。

``` 1. CREATE TABLE EXAMPLE1(COL1 INT);

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;
/ ```
示例2：

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

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

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

1. CREATE OR REPLACE PROCEDURE TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK() 2. AS 3. BEGIN 4. DROP TABLE IF EXISTS TEST_COMMIT; 5. CREATE TABLE TEST_COMMIT(A INT, B INT); 6. INSERT INTO TEST_COMMIT SELECT 1, 1; 7. COMMIT; 8. CREATE TABLE TEST_ROLLBACK(A INT, B INT); 9. RAISE EXCEPTION 'RAISE EXCEPTION AFTER COMMIT'; 10. EXCEPTION 11. WHEN OTHERS THEN 12. INSERT INTO TEST_COMMIT SELECT 2, 2; 13. ROLLBACK; 14. END; 15. / - 示例3：支持在事务块里调用含有COMMIT/ROLLBACK的存储过程，即通过/BEGIN/START/END等开启控制的外部事务。

1. BEGIN; 2. CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK(); 3. END; - 示例4：支持多数PLSQL的上下文和语句内调用COMMIT/ROLLBACK，包括常用的IF/FOR/CURSOR LOOP/WHILE。

1. CREATE OR REPLACE PROCEDURE TEST_COMMIT2() 2. IS 3. BEGIN 4. DROP TABLE IF EXISTS TEST_COMMIT; 5. CREATE TABLE TEST_COMMIT(A INT); 6. FOR I IN REVERSE 3..0 LOOP 7. INSERT INTO TEST_COMMIT SELECT I; 8. COMMIT; 9. END LOOP; 10. FOR I IN REVERSE 2..4 LOOP 11. UPDATE TEST_COMMIT SET A=I; 12. COMMIT; 13. END LOOP; 14. EXCEPTION 15. WHEN OTHERS THEN 16. INSERT INTO TEST_COMMIT SELECT 4; 17. COMMIT; 18. END; 19. / - 示例5：支持存储过程返回值与简单表达式计算。

2. CREATE OR REPLACE PROCEDURE exec_func3(RET_NUM OUT INT) 3. AS 4. BEGIN 5. RET_NUM := 1+1; 6. COMMIT; 7. END; 8. / 9. CREATE OR REPLACE PROCEDURE exec_func4(ADD_NUM IN INT) 10. AS 11. SUM_NUM INT; 12. BEGIN 13. SUM_NUM := ADD_NUM + exec_func3(); 14. COMMIT; 15. END; 16. / - 示例6：支持存储过程内GUC参数的回滚提交。

``` 1. SHOW explain_perf_mode; 2. 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语句，同时不允许函数调用含有commit/rollback的存储过程。

1. CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE1() RETURN INT 2. AS 3. EXP INT; 4. BEGIN 5. FOR i IN 0..20 LOOP 6. INSERT INTO EXAMPLE1(col1) VALUES (i); 7. IF i % 2 = 0 THEN 8. COMMIT; 9. ELSE 10. ROLLBACK; 11. END IF; 12. END LOOP; 13. SELECT COUNT(*) FROM EXAMPLE1 INTO EXP; 14. RETURN EXP; 15. END; 16. / - 示例8：函数（Fucntion）中不允许调用带有commit/rollback语句的存储过程。

1. CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE2() RETURN INT 2. AS 3. EXP INT; 4. BEGIN 5. --transaction_example为存储过程，带有commit/rollback语句 6. CALL transaction_example(); 7. SELECT COUNT(*) FROM EXAMPLE1 INTO EXP; 8. RETURN EXP; 9. END; 10. / - 示例9：不允许Trigger的存储过程包含commit/rollback语句，或调用带有commit/rollback语句的存储过程。

``` 1. CREATE OR REPLACE FUNCTION FUNCTION_TRI_EXAMPLE2() RETURN TRIGGER 2. AS 3. EXP INT; 4. BEGIN 5. FOR i IN 0..20 LOOP 6. INSERT INTO EXAMPLE1(col1) VALUES (i); 7. IF i % 2 = 0 THEN 8. COMMIT; 9. ELSE 10. ROLLBACK; 11. END IF; 12. END LOOP; 13. SELECT COUNT(*) FROM EXAMPLE1 INTO EXP; 14. END; 15. /

CREATE TRIGGER TRIGGER_EXAMPLE AFTER DELETE ON EXAMPLE1
FOR EACH ROW EXECUTE PROCEDURE FUNCTION_TRI_EXAMPLE2();
DELETE FROM EXAMPLE1; ```
示例10：不支持带有IMMUABLE以及SHIPPABLE的存储过程调用commit/rollback，或调用带有commit/rollback语句的存储过程。

1. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE1() 2. IMMUTABLE 3. AS 4. BEGIN 5. FOR i IN 0..20 LOOP 6. INSERT INTO EXAMPLE1 (col1) VALUES (i); 7. IF i % 2 = 0 THEN 8. COMMIT; 9. ELSE 10. ROLLBACK; 11. END IF; 12. END LOOP; 13. END; 14. / - 示例11：不支持出现在SQL中的调用（除了Select Procedure）。

1. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE3() 2. AS 3. BEGIN 4. FOR i IN 0..20 LOOP 5. INSERT INTO EXAMPLE1 (col1) VALUES (i); 6. IF i % 2 = 0 THEN 7. EXECUTE IMMEDIATE 'COMMIT'; 8. ELSE 9. EXECUTE IMMEDIATE 'ROLLBACK'; 10. END IF; 11. END LOOP; 12. END; 13. / - 示例12：存储过程头带有GUC参数设置的不允许调用commit/rollback语句。

1. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE4() 2. SET ARRAY_NULLS TO "ON" 3. AS 4. BEGIN 5. FOR i IN 0..20 LOOP 6. INSERT INTO EXAMPLE1 (col1) VALUES (i); 7. IF i % 2 = 0 THEN 8. COMMIT; 9. ELSE 10. ROLLBACK; 11. END IF; 12. END LOOP; 13. END; 14. / - 示例13：游标open的对象不允许为带有commit/rollback语句的存储过程。

``` 1. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE5(INTIN IN INT, INTOUT OUT INT) 2. AS 3. BEGIN 4. INTOUT := INTIN + 1; 5. COMMIT; 6. END; 7. /

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。

1. CREATE OR REPLACE PROCEDURE exec_func1() 2. AS 3. BEGIN 4. CREATE TABLE TEST_exec(A INT); 5. COMMIT; 6. END; 7. / 8. CREATE OR REPLACE PROCEDURE exec_func2() 9. AS 10. BEGIN 11. EXECUTE exec_func1(); 12. COMMIT; 13. END; 14. / - 示例15：存储过程使用保存点回退事务部分修改。

1. CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE1() 2. AS 3. BEGIN 4. INSERT INTO EXAMPLE1 VALUES(1); 5. SAVEPOINT s1; 6. INSERT INTO EXAMPLE1 VALUES(2); 7. ROLLBACK TO s1; -- 回退插入记录2 8. INSERT INTO EXAMPLE1 VALUES(3); 9. END; 10. / - 示例16：存储过程中使用保存点回退到存储过程外部定义的保存点。

``` 1. CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE2() 2. AS 3. BEGIN 4. INSERT INTO EXAMPLE1 VALUES(2); 5. ROLLBACK TO s1; -- 回退插入记录2 6. INSERT INTO EXAMPLE1 VALUES(3); 7. END; 8. /

BEGIN;
INSERT INTO EXAMPLE1 VALUES(1);
SAVEPOINT s1;
CALL STP_SAVEPOINT_EXAMPLE2();
SELECT * FROM EXAMPLE1;
COMMIT; ```
示例17：存储过程外部回退到存储过程中定义的保存点。

``` 1. CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE3() 2. AS 3. BEGIN 4. INSERT INTO EXAMPLE1 VALUES(1); 5. SAVEPOINT s1; 6. INSERT INTO EXAMPLE1 VALUES(2); 7. END; 8. /

BEGIN;
INSERT INTO EXAMPLE1 VALUES(3);
CALL STP_SAVEPOINT_EXAMPLE3();
ROLLBACK TO SAVEPOINT s1; --回退存储过程中插入记录2
SELECT * FROM EXAMPLE1;
COMMIT; ```