云数据库 GaussDB-PACKAGE支持自治事务

时间：2025-02-12 15:08:12

云数据库 GaussDB

PACKAGE支持自治事务

自治事务可以在package中的存储过程或者函数中定义，标识符为PRAGMA AUTONOMOUS_TRANSACTION，其余语法与创建package中存储过程或函数语法相同，请参见CREATE PACKAGE。

gaussdb=# create database test DBCOMPATIBILITY = 'ORA';CREATE DATABASEtest=# \c test--建表test=# CREATE TABLE t2(a INT, b INT);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.CREATE TABLEtest=# INSERT INTO t2 VALUES(1,2);INSERT 0 1test=# SELECT * FROM t2; a | b ---+--- 1 | 2(1 row)--创建包含自治事务的PACKAGE中的存储过程和函数test=# CREATE OR REPLACE PACKAGE autonomous_pkg AS  PROCEDURE autonomous_4(a INT, b INT);  FUNCTION autonomous_32(a INT ,b INT) RETURN INT;END autonomous_pkg;/CREATE PACKAGEtest=# CREATE OR REPLACE PACKAGE BODY autonomous_pkg ASPROCEDURE autonomous_4(a INT, b INT)  AS DECLARE  num3 INT := a; num4 INT := b; PRAGMA AUTONOMOUS_TRANSACTION;BEGININSERT INTO t2 VALUES(num3, num4); END;FUNCTION autonomous_32(a INT ,b INT) RETURN INT AS DECLARE PRAGMA AUTONOMOUS_TRANSACTION;BEGININSERT INTO t2 VALUES(a, b);RETURN 1;END;END autonomous_pkg;/CREATE PACKAGE BODY--创建调用PACKAGE自治事务存储过程和函数的普通存储过程test=# CREATE OR REPLACE PROCEDURE autonomous_5(a INT, b INT)  AS DECLAREva INT;BEGININSERT INTO t2 VALUES(666, 666);autonomous_pkg.autonomous_4(a,b);        va := autonomous_pkg.autonomous_32(a + 1, b + 1);ROLLBACK;END;/CREATE PROCEDURE--调用普通存储过程test=# SELECT autonomous_5(11,22); autonomous_5 --------------(1 row)--查看表结果test=# SELECT * FROM t2 ORDER BY a; a  | b  ----+----  1 |  2 11 | 22 12 | 23(3 rows)test=# DROP TABLE t2;DROP TABLE

上述例子，最后在回滚的事务块中执行包含PACKAGE自治事务的存储过程和函数，直接说明了自治事务的特性，即主事务的回滚不会影响自治事务已经提交的内容。

父主题： 自治事务

上一篇：云数据库 GaussDB-PG_APP_WORKLOADGROUP_MAPPING

下一篇：云数据库 GaussDB-PG_APP_WORKLOADGROUP_MAPPING