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. The following is an example.

-- Create a table.
drop table t2;
create table t2(a int, b int);
insert into t2 values(1,2);
select * from t2;

-- Create a stored procedure or function in a package that contains autonomous transactions.
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 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 a common stored procedure that calls a stored procedure or function from a package that contains autonomous transactions.
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;
/
-- Call a common stored procedure.
select autonomous_5(11,22);
-- View the table result.
select * from t2 order by a;

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