Package Supporting Autonomous Transaction

An autonomous transaction can be defined in a stored procedure or function in a package. The identifier of an autonomous transaction is PRAGMA AUTONOMOUS_TRANSACTION. The syntax of an autonomous transaction is the same as that of creating a stored procedure or function in a package. For details, see CREATE PACKAGE.

gaussdb=# create database test DBCOMPATIBILITY = 'ORA';
CREATE DATABASE
test=# \c test
-- Create a table.
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 TABLE
test=# INSERT INTO t2 VALUES(1,2);
INSERT 0 1
test=# SELECT * FROM t2;
 a | b 
---+---
 1 | 2
(1 row)

-- Create a stored procedure or function in a package that contains autonomous transactions.
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 PACKAGE
test=# CREATE OR REPLACE PACKAGE BODY autonomous_pkg AS
PROCEDURE autonomous_4(a INT, b INT)  AS 
DECLARE 
 num3 INT := a;
 num4 INT := b;
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	INSERT INTO t2 VALUES(num3, num4); 
END;
FUNCTION autonomous_32(a INT ,b INT) RETURN INT AS 
DECLARE 
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	INSERT INTO t2 VALUES(a, b);
	RETURN 1;
END;
END autonomous_pkg;
/
CREATE PACKAGE BODY
-- Create a common stored procedure that calls a stored procedure or function from a package that contains autonomous transactions.
test=# CREATE OR REPLACE PROCEDURE autonomous_5(a INT, b INT)  AS 
DECLARE
va INT;
BEGIN
	INSERT INTO t2 VALUES(666, 666);
	autonomous_pkg.autonomous_4(a,b);
        va := autonomous_pkg.autonomous_32(a + 1, b + 1);
	ROLLBACK;
END;
/
CREATE PROCEDURE
-- Call a common stored procedure.
test=# SELECT autonomous_5(11,22);
 autonomous_5 
--------------

(1 row)

-- View the table result.
test=# SELECT * FROM t2 ORDER BY a;
 a  | b  
----+----
  1 |  2
 11 | 22
 12 | 23
(3 rows)

test=# DROP TABLE t2;
DROP TABLE