Some basic exception handling tutorials in Mysql stored procedures _mysql

Sometimes, you do not want the stored procedure to throw an error abort execution, but rather to return an error code. Mysql supports exception handling by defining Continue/exit exception handling HANDLER to capture Sqlwarning/not found/sqlexception (warning/No data/other exceptions). Where, for later, you can change to sqlwarning, not FOUND, SQLEXCEPTION to indicate that all exceptions are handled, equivalent to others in Oracle. For example, when exception handling is not done, the following code throws an error 1062 (23000) fault directly:

CREATE PROCEDURE test_proc_ins1 (
     in i_id INT, in
     i_name VARCHAR ()
)
BEGIN inserts into
     testproc VALUES (i_id,i_name);
     INSERT into TestProc VALUES (i_id,i_name);
End;

After exception handling, you can avoid throwing errors, but define a return parameter O_ret give a special value to represent a failure, such as in Java code, you can handle the business logic by getting the return value instead of catching the exception. For example, set the return value to-1:

CREATE PROCEDURE test_proc_ins1 (
     in i_id INT, in
     i_name VARCHAR (M), out
     o_ret INT)
BEGIN
     DECLARE EXIT HANDLER for SQLSTATE ' 23000 ' Set o_ret =-1;
--You can also use this:
--DECLARE EXIT HANDLER for Sqlwarning,not found,sqlexception set o_ret=-1;
     INSERT into TestProc VALUES (i_id,i_name);
     INSERT into TestProc VALUES (i_id,i_name);
     Set o_ret = 1;
End;

Of course, for a specific SQL statement, you can also specify such as the primary key conflict, rollback;

DECLARE exit HANDLER for SQLSTATE ' 23000 '

delimiter//

CREATE PROCEDURE TEST ()
BEGIN
DECLARE exit HANDLER for Sqlexception,sqlwarning,not FOUND
begin
rollback;
Insert into BB values (' Error ');
End;
START TRANSACTION;
INSERT into AA VALUES (1);
INSERT into AA VALUES (2);
COMMIT;
End;
Call

Test ()//