Stored Procedure Supporting Autonomous Transaction

An autonomous transaction can be defined in a stored procedure. 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. For details, see CREATE PROCEDURE. The following is an example:

-- Create a table.
gaussdb=# 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
gaussdb=# INSERT INTO t2 VALUES(1,2);
INSERT 0 1
gaussdb=# SELECT * FROM t2;
 a | b 
---+---
 1 | 2
(1 row)

-- Create a stored procedure that contains an autonomous transaction.
gaussdb=# CREATE OR REPLACE 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); 
	DBE_OUTPUT.PRINT_LINE('JUST USE CALL.');
END;
/
CREATE PROCEDURE

-- Create a common stored procedure that calls an autonomous transaction stored procedure.
gaussdb=# CREATE OR REPLACE PROCEDURE autonomous_5(a INT, b INT)  AS 
DECLARE 
BEGIN
	DBE_OUTPUT.PRINT_LINE('JUST NO USE CALL.');
	INSERT INTO t2 VALUES(666, 666);
	autonomous_4(a,b);
	ROLLBACK;
END;
/
CREATE PROCEDURE

-- Call a common stored procedure.
gaussdb=# SELECT autonomous_5(11,22);
JUST NO USE CALL.
JUST USE CALL.
 autonomous_5 
--------------

(1 row)

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

gaussdb=# DROP TABLE t2;
DROP TABLE

In the preceding example, a stored procedure containing an autonomous transaction is finally executed in a transaction block to be rolled back, which directly illustrates a characteristic of the autonomous transaction, that is, rollback of the primary transaction does not affect content that has been committed by the autonomous transaction.