Home>

Sometimes, you don't want the stored procedure to throw an error and abort the execution.Instead, i want to return an error code. mysql supports exception handling,Catch sqlwarning/not found/sqlexception by defining a handler for continue/exit exception handling (warning/no data/other exceptions). Among them, for can be changed to sqlwarning, not found, sqlexception to indicate that all exceptions are handled,Equivalent to others in oracle. For example, when exception handling is not performed,The following code will directly throw an error 1062 (23000) error:

create procedure test_proc_ins1 (
     in i_id int,     in i_name varchar (100)
)
begin
     insert into testproc values ​​(i_id, i_name);
     insert into testproc values ​​(i_id, i_name);
end;

After exception handling,Can avoid throwing errors,Instead, define a return parameter o_ret to assign a special value to indicate failure,This way, for example in java code, you can handle business logic by getting return values ​​instead of catching exceptions.For example, set the return value to -1:

create procedure test_proc_ins1 (
     in i_id int,     in i_name varchar (100),     out o_ret int)
begin
     declare exit handler for sqlstate "23000" set o_ret=-1;
-Can also be used like 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 specific SQL statements, you can also specify such as primary key conflicts,Just 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 () //
  • Previous MySQL triggers example tutorial for migrating and synchronizing data
  • Next MySQL LEFT JOIN table join advanced learning tutorial