I insert a few records in the stored procedure sp1, and then call the stored procedure sp2
Then sp2 triggered is unusual, I stopped in the sp1 is unusual, call rollback after exit
Query data discovery, insert a few data in the sp1 didn't rollback success

 
Insert into t1 values (x, y, z); 
Insert into t2 values (x, y, z); 

The begin 
R:=0; 
Sp2 (p1, p2); 
R:=1; 
The exception 
The when others then null; 
-- tried to rollback here, there is no success 
end; 

If r!=1 then 
The rollback. -- not the rollback perform 
return; 
end if; 

CodePudding user response:

The when others then rollback.

CodePudding user response:

reference 1st floor riven2011 response:

the when others then rollback.

tried a useless

CodePudding user response:

In sp2 DDL statements, the error during execution of this statement

CodePudding user response:

Look at the sp2 abnormal how to deal with it

CodePudding user response:

The

reference 4 floor riven2011 reply:

see how sp2 exception handling

sp2 not handle directly out of the race

CodePudding user response:

Exit cannot directly on the outer catch exceptions, at the same time, the value of r 1

CodePudding user response:

Proposed to sp2 null or return to raise

CodePudding user response:

My outer can capture the mistake, now the focus is not the mistake, but why don't the rollback work

CodePudding user response:

refer to the eighth floor Sandrer response:

my outer can capture the error, now the focus is not the mistake, but why don't the rollback work

You directly the debug

CodePudding user response:

Just try it

 

Insert into t1 values (x, y, z); 
Insert into t2 values (x, y, z); 

Sp2 (p1, p2, r); 

If r!=1 then 
The rollback. -- not the rollback perform 
return; 
end if; 

CodePudding user response:

Understand the autonomous transaction PRAGMA AUTONOMOUS_TRANSACTION

CodePudding user response:

The

references to the tenth floor nayi_224 response:

literally try

 

Insert into t1 values (x, y, z); 
Insert into t2 values (x, y, z); 

Sp2 (p1, p2, r); 

If r!=1 then 
The rollback. -- not the rollback perform 
return; 
end if; 

Tried, but as long as the call after sp2, whatever where call rollback will not success
But if the rollback before calling sp2, can be successful

CodePudding user response:

Determine to rollback to the branch in the

CodePudding user response:

reference 13 floor AHUA1001 reply:

sure go into a branch of the rollback

words, according to the process, if the code did not enter the branch of the rollback, then represent no error occurs, the result is I can't find the rollback execute invalid
And I know the rollback execution is invalid, then certainly into the branches to the right...