文档首页/ 数据仓库服务 DWS/ 开发指南/ 开发指南(9.1.1.x)/ DWS存储过程/ DWS存储过程控制语句/ 事务控制语句
更新时间：2025-08-22 GMT+08:00
查看PDF
分享

事务控制语句

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

使用场景

  • 存储过程支持包含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。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    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。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    		 
    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）。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    CREATE OR REPLACE PROCEDURE transaction_test3()
AS
BEGIN
    PERFORM transaction_test1();
    ROLLBACK;
PERFORM transaction_test2();
ROLLBACK;
END;
/
 
CALL transaction_test3();
  • 示例4：包含EXCEPTION的存储过程（COMMIT/ROLLBACK）。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    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。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    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。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
    		 
    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语句。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    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语句的存储过程。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
    		 
    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。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    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。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    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) 被关闭。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
    		 
    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除外）。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    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。 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    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;
父主题： DWS存储过程控制语句

相关文档