Home > database >  In a stored procedure call a stored procedure, after the rollback doesn't work
In a stored procedure call a stored procedure, after the rollback doesn't work

Time:09-15

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 the 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 the 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 the 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...
  • Related