Sunday, December 2, 2012

0 AUTONOMOUS_TRANSACTION

When store procedure is defined as AUTONOMOUS_TRANSACTION,even outer transaction has been roll back , it would not impact the transaction has been commit inside the sp.

Here is the example:

Lets create the simple test table:

create table employee (
emp_id int,
name varchar(30)
);
insert into employee values( 1,'Bob');
insert into employee values( 2,'Tom');
commit;

image

Lets create the AUTONOMOUS_TRANSACTION sp.

create or replace procedure insert_emp  
    (    in_id in number,
        in_name in employee.name%TYPE) 
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        insert into employee 
        values ( in_id, in_name);
        commit;
END;
/

Begin the transaction. The insert john has been roll back, so the data would not reflect in to the table.

Ted was updated inside the store procedure, therefore it is been commit.

begin
insert into employee values ( 5, 'John');
 
insert_emp ( 100,'Ted');
rollback;
end;
/

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates