事务语句
存储过程本身会自动处于一个事务中。调用最外围存储过程开始时会自动开启一个事务,同时在调用结束时自动提交或者中间异常时回滚。除了系统自动的事务控制外,也可以使用COMMIT/ROLLBACK来控制存储过程中的事务。在存储过程中调用COMMIT/ROLLBACK命令,将提交/回滚当前事务并自动开启一个新的事务,后续的所有操作都会运行在此新的事务中。
保存点SAVEPOINT是事务中的一个特殊记号,它允许将那些在它建立后执行的命令全部回滚,把事务的状态恢复到保存点所在的时刻。存储过程中允许使用保存点来进行事务管理,当前支持保存点的创建、回滚和释放操作。需要特别注意,存储过程中使用回滚保存点只是回退当前事务的修改,而不会改变存储过程的执行流程,也不会回退存储过程中的局部变量值等。
支持调用的上下文环境:
- 支持在PL/SQL的存储过程/函数内使用COMMIT/ROLLBACK/SAVEPOINT。
- 支持含有EXCEPTION的存储过程/函数使用COMMIT/ROLLBACK/SAVEPOINT。
- 支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK/SAVEPOINT。
- 支持在事务块里调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程,即通过/BEGIN/START/END等开启控制的外部事务。
- 支持在子事务中调用含SAVEPOINT的存储过程并使用外部定义的SAVEPOINT,回退事务状态到存储过程外定义的SAVEPOINT位置。
- 支持在存储过程外部可见存储过程内部定义的SAVEPOINT,即存储过程外可以将事务修改回滚到存储过程中定义SAVEPOINT的位置。
- 支持多数PL/SQL的上下文和语句内调用COMMIT/ROLLBACK/SAVEPOINT,包括常用的IF/FOR/CURSOR LOOP/WHILE。
支持提交/回滚的内容:
- 支持DDL在COMMIT/ROLLBACK后的提交/回滚。
- 支持DML的COMMIT/ROLLBACK后的提交。
- 支持存储过程内GUC参数的回滚提交。
语法
定义保存点 SAVEPOINT savepoint_name; 回滚保存点 ROLLBACK TO [SAVEPOINT] savepoint_name; 释放保存点 RELEASE [SAVEPOINT] savepoint_name;
示例
支持在PL/SQL的存储过程内使用COMMIT/ROLLBACK。
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; /
- 支持含有EXCEPTION的存储过程使用COMMIT/ROLLBACK。
- 支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK。
- 支持DDL在COMMIT/ROLLBACK后的提交/回滚。
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; /
支持在事务块里调用含有COMMIT/ROLLBACK的存储过程,即通过/BEGIN/START/END等开启控制的外部事务。
BEGIN; CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK(); END;
支持多数PL/SQL的上下文和语句内调用COMMIT/ROLLBACK,包括常用的IF/FOR/CURSOR LOOP/WHILE。
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; /
- 支持存储过程内GUC参数的回滚提交。
- enable_force_vector_engine 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;
支持在PL/SQL的存储过程内使用保存点回退事务部分修改。
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; /
支持在PL/SQL的存储过程中使用保存点回退到存储过程外部定义的保存点。
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;
支持在存储过程外部回退到在PL/SQL存储过程内部定义的保存点。
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;
支持函数(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; /
限制场景
- 不支持调用的上下文环境:
- 不支持除PL/SQL的其他存储过程中调用COMMIT/ROLLBACK/SAVEPOINT,例如PLPYTHON等。
- 不支持事务块中调用了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。
- 不支持存储过程返回值与表达式计算中调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程。
- 不支持存储过程中释放存储过程外部定义的保存点。
- 存储过程事务和其中的自治事务是两个独立的事务,不能互相使用对方事务中定义的保存点
- 不支持提交回滚的内容:
- 不支持存储过程内声明变量以及传入变量的提交/回滚。
- 不支持存储过程内必须重启生效的GUC参数的提交/回滚。
在存储过程使用commit/rollback有以下限制场景:
不允许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;
不支持带有IMMUABLE以及SHIPPABLE的存储过程调用commit/rolblack,或调用带有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; /
不支持存储过程中任何变量的提交,包括存储过程内声明的变量或者传入的参数。
CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE2(EXP_OUT OUT INT) AS EXP INT; BEGIN EXP_OUT := 0; COMMIT; DBE_OUTPUT.PRINT_LINE('EXP IS:'||EXP); EXP_OUT := 1; ROLLBACK; DBE_OUTPUT.PRINT_LINE('EXP IS:'||EXP); END; /
不支持出现在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; /
存储过程头带有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; /
游标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; /
不支持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; /
不支持存储过程返回值与表达式计算。
CREATE OR REPLACE PROCEDURE exec_func3(RET_NUM OUT INT) AS BEGIN RET_NUM := 1+1; COMMIT; END; / CREATE OR REPLACE PROCEDURE exec_func4(ADD_NUM IN INT) AS SUM_NUM INT; BEGIN SUM_NUM := ADD_NUM + exec_func3(); COMMIT; END; /
不支持存储过程中释放存储过程外部定义的保存点。
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;