示例

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

gaussdb=# DROP TABLE IF EXISTS EXAMPLE1;gaussdb=# CREATE TABLE EXAMPLE1(COL1 INT);gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE()ASBEGIN    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;/gaussdb=# call TRANSACTION_EXAMPLE(); transaction_example ---------------------(1 row)

• 支持含有EXCEPTION的存储过程使用COMMIT/ROLLBACK。
• 支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK。
• 支持DDL在COMMIT/ROLLBACK后的提交/回滚。

gaussdb=# DROP TABLE IF EXISTS EXAMPLE1;gaussdb=# CREATE TABLE EXAMPLE1(COL1 INT);gaussdb=# CREATE OR REPLACE PROCEDURE TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK()ASBEGIN 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;/gaussdb=# call TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();NOTICE:  table "test_commit" does not exist, skippingCONTEXT:  SQL statement "DROP TABLE IF EXISTS TEST_COMMIT"PL/pgSQL function test_commit_insert_exception_rollback() line 3 at SQL statementNOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.CONTEXT:  SQL statement "CREATE TABLE TEST_COMMIT(A INT, B INT)"PL/pgSQL function test_commit_insert_exception_rollback() line 4 at SQL statementNOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.CONTEXT:  SQL statement "CREATE TABLE TEST_ROLLBACK(A INT, B INT)"PL/pgSQL function test_commit_insert_exception_rollback() line 7 at SQL statement test_commit_insert_exception_rollback ---------------------------------------(1 row)

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

gaussdb=# BEGIN;    -- TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK定义见示例2    CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.CONTEXT:  SQL statement "CREATE TABLE TEST_COMMIT(A INT, B INT)"PL/pgSQL function test_commit_insert_exception_rollback() line 4 at SQL statementNOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.CONTEXT:  SQL statement "CREATE TABLE TEST_ROLLBACK(A INT, B INT)"PL/pgSQL function test_commit_insert_exception_rollback() line 7 at SQL statement test_commit_insert_exception_rollback ---------------------------------------(1 row)gaussdb=# END;COMMIT

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

gaussdb=# CREATE OR REPLACE PROCEDURE TEST_COMMIT2()ISBEGIN    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;EXCEPTIONWHEN OTHERS THEN    INSERT INTO TEST_COMMIT SELECT 4;    COMMIT;END;/gaussdb=# call TEST_COMMIT2();NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.CONTEXT:  SQL statement "CREATE TABLE TEST_COMMIT(A INT)"PL/pgSQL function test_commit2() line 4 at SQL statement test_commit2 --------------(1 row)

支持存储过程内GUC参数的回滚提交。

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()ASBEGIN    SET enable_force_vector_engine = on;    COMMIT;    SET explain_perf_mode TO pretty;    ROLLBACK;END;/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;