plsql只提交存储过程里的事务,如何在Oracle PLSQL中提交单个事务

I need to write a PL/SQL procedure, within this procedure I need to call another procedure within its own transaction bounds, and commit it regardless of failure or commit of main transaction. In other words I need something like REQUIRES NEW transaction propagation.

Something like:

procedure mainProcedure(arugements) is

begin

// some statements

nestedProcedure(someArguments);

// some other statements

end;

procedure nestedProcedure(arguments) is

begin

// start a new transaction

// some statements, lock some objects!

// commit the new transaction and release locked objects

end;

How can I achieve this?

解决方案

You may use pragma autonomous_transaction. It does the same what you need. But don't forget that in the sub transaction you will not see any updates from above transactions.

procedure mainProcedure(arugements) is

begin

// some statements

nestedProcedure(someArguments);

// some other statements

end;

procedure nestedProcedure(arguments) is

pragma autonomous_transaction;

begin

// start a new transaction

// some statements, lock some objects!

// commit the new transaction and release locked objects

commit;

end;