Anonymous Block Supporting Autonomous Transaction

An autonomous transaction can be defined in an anonymous block. The identifier of an autonomous transaction is PRAGMA AUTONOMOUS_TRANSACTION. The syntax of an autonomous transaction is the same as that of creating an anonymous block. The following is an example.

gaussdb=# create table t1(a int ,b text);
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=# START TRANSACTION;
START TRANSACTION
gaussdb=# DECLARE 
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	dbe_output.print_line('just use call.');
	insert into t1 values(1,'you are so cute,will commit!');
END;
/
just use call.
ANONYMOUS BLOCK EXECUTE
gaussdb=# insert into t1 values(1,'you will rollback!');
INSERT 0 1
gaussdb=# rollback;
ROLLBACK

gaussdb=# select * from t1;
 a |              b
---+------------------------------
 1 | you are so cute,will commit!
(1 row)

In the preceding example, an anonymous block containing an autonomous transaction is finally executed before 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.