mysql 存储过程 回滚_如何在MySQL存储过程中检测回滚？

I'm trying to figure out a way to detect an occurrence of rollback in a MySQL stored procedure so I could handle the situation accordingly from a PHP script, but so far I can not find any solution.

My stored procedure looks like this:

delimiter |

create procedure multi_inserts(

IN var1 int(11),

.

.

.

IN string1 text

)

BEGIN

declare exit handler for sqlexception rollback;

declare exit handler for sqlwarning rollback;

START TRANSACTION;

insert into table1(a,b,c,d) values(var1,var2,var3,var4);

insert into table2(e,f,g) values(var5,var6,string1);

COMMIT;

END

delimiter ;

I did a rollback test on this procedure and it did rollback but I got no false.

I want my stored procedure to throw some kind of error message if the transaction failed, so I could handle it like this:

$result = mysql_query($procedure);

if(!$result)

{

//rollback occured do something

}

Is there a way to detect rollback in MySQL?

Am I missing something?

Any reply will be appreciated.

Thanks for reading.

Thanks to your advices I fixed this problem. Here's what I did:

Stored Procedure

delimiter |

create procedure multi_inserts(

IN var1 int(11),

.

.

.

IN string1 text

)

BEGIN

declare exit handler for sqlexception sqlwarning

BEGIN

rollback;

select -1;

END;

START TRANSACTION;

insert into table1(a,b,c,d) values(var1,var2,var3,var4);

insert into table2(e,f,g) values(var5,var6,string1);

COMMIT;

END

delimiter ;

If I use out variable instead of select -1, it gives me this error:

OUT or INOUT argument is not a

variable or NEW pseudo-variable in

BEFORE trigger

I don't know what did I wrong, but I couldn't fix this problem.

PHP script

$result=mysqli_query($con,$procedure);

if(is_object($result))

{

//rollback happened do something!

}

If the SP is successful it throws true.

解决方案

You can add an output param and then set it to the value you want in your exit handlers.

Here's an example using your proc:

delimiter $$

create procedure multi_inserts(

IN var1 int(11),

.

.

.

IN string1 text,

OUT p_return_code tinyint unsigned

)

BEGIN

DECLARE exit handler for sqlexception

BEGIN

-- ERROR

set p_return_code = 1;

rollback;

END;

DECLARE exit handler for sqlwarning

BEGIN

-- WARNING

set p_return_code = 2;

rollback;

END;

START TRANSACTION;

insert into table1(a,b,c,d) values(var1,var2,var3,var4);

insert into table2(e,f,g) values(var5,var6,string1);

COMMIT;

-- SUCCESS

set p_return_code = 0;

END $$

delimiter ;