MySql stored procedure exception handling sample code sharing

Here is the sample code that stores the exception information in the log table when an exception occurs and continues to run the following statements.

If you have a better suggestion, please let me know.

Example stored procedure exception handling

 
-- -------------------------------------------------------------------------------- 
-- Routine DDL 
-- Note: comments before and after the routine body will not be stored by the server 
-- -------------------------------------------------------------------------------- 
DELIMITER $$ 
CREATE DEFINER=`driveradmin`@`%` PROCEDURE `Merge_BrandProductKey`() 
BEGIN 
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
begin 
insert into t_runninglog values(default,default,'exception in MergeBrandProductKey',concat(@@error_count,' errors')); 
commit; 
end; 
DECLARE CONTINUE HANDLER FOR SQLWARNING 
begin 
insert into t_runninglog values(default,default,'warnings in MergeBrandProductKey',concat(@@warning_count,' warnings')); 
commit; 
end; 
insert into t_runninglog values(default,default,'start in MergeBrandProductKey',''); 
commit; 
--  Task executor   start  
-- /* 
-- normal 
update brandproductkey as bpk, 
(select bp.brandproductid, bp.brandproductenname, bp.brandid 
from brandproduct as bp 
inner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) as bpr 
on bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid 
) as bpp 
set bpk.brandproductid=bpp.brandproductid 
where bpk.brandproductid = 0 
-- and bpk.computertype = 2 -- 0 
and bpk.brandid = bpp.brandid 
and upper(bpk.brandproductkeyname) = upper(replace(bpp.brandproductenname,' ','')); 
commit; 
insert into t_runninglog values(default,default,'rule normal in MergeBrandProductKey',''); 
commit; 
-- sony rule 1 
-- VPCEA37EC --> (VPCEA37EC/B,VPCEA37EC/L,VPCEA37EC/P,VPCEA37EC/W) 
update brandproductkey as bpk, 
(select bp.brandproductid, bp.brandproductenname, bp.brandid 
from brandproduct as bp 
inner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) as bpr 
on bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=60 
) as bpp 
set bpk.brandproductid=bpp.brandproductid 
where bpk.brandproductid = 0 
-- and bpk.computertype = 2 -- 0 
and bpk.brandid = bpp.brandid 
and bpp.brandproductenname like concat(bpk.brandproductkeyname,'/%'); 
commit; 
insert into t_runninglog values(default,default,'rule sony 1 in MergeBrandProductKey',''); 
commit; 
-- sony rule 2 
-- VGN-TZ37N_X --> VGN-TZ37N/X 
update brandproductkey as bpk, 
(select bp.brandproductid, bp.brandproductenname, bp.brandid 
from brandproduct as bp 
inner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) as bpr 
on bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=60 
) as bpp 
set bpk.brandproductid=bpp.brandproductid 
where bpk.brandproductid = 0 
-- and bpk.computertype = 2 -- 0 
and bpk.brandid = bpp.brandid 
and upper(bpk.brandproductkeyname) = upper(replace(bpp.brandproductenname,'/','_')); 
commit; 
insert into t_runninglog values(default,default,'rule sony 2 in MergeBrandProductKey',''); 
commit; 
-- lenovo rule 1 
-- ZHAOYANG E45 -->  zhaoyang E45 
update brandproductkey as bpk, 
(select bp.brandproductid, bp.brandproductenname, bp.brandid,bpr.driverid 
from brandproduct as bp 
inner join (select brandid,brandproductid,max(driverinfoid) as driverid from brandproductdriverrelation group by brandid,brandproductid) as bpr 
on bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=37 
) as bpp 
set bpk.brandproductid=bpp.brandproductid 
where bpk.brandproductid = 0 
-- and bpk.computertype = 2 -- 0 
and bpk.brandid = bpp.brandid 
and bpk.brandproductkeyname <> '' 
and instr(bpp.brandproductenname,SUBSTRING_INDEX(bpk.brandproductkeyname,' ',-1))>0 
and bpp.brandproductenname regexp concat('^[^\x00-\xff]+', SUBSTRING_INDEX(bpk.brandproductkeyname,' ',-1),'$'); 
commit; 
insert into t_runninglog values(default,default,'rule lenovo 1 in MergeBrandProductKey',''); 
commit; 
-- HP rule 1 
-- HP Compaq 6535s --> HP Compaq 6535s  Laptop computer  
update brandproductkey as bpk, 
(select bp.brandproductid, bp.brandproductenname, bp.brandid 
from brandproduct as bp 
inner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) as bpr 
on bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=36 
) as bpp 
set bpk.brandproductid = bpp.brandproductid 
where bpk.brandproductid = 0 
-- and bpk.computertype = 2 -- 0 
and bpk.brandid = bpp.brandid 
and bpk.brandproductkeyname <> '' 
and bpp.brandproductenname = concat(bpk.brandproductkeyname,'  Laptop computer '); 
insert into t_runninglog values(default,default,'rule hp 1 in MergeBrandProductKey',''); 
commit; 
-- HP rule 2 
-- HP Compaq 6535s --> HP Compaq 6535s Notebook PC 
update brandproductkey as bpk, 
(select bp.brandproductid, bp.brandproductenname, bp.brandid 
from brandproduct as bp 
inner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) as bpr 
on bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=36 
) as bpp 
set bpk.brandproductid = bpp.brandproductid 
where bpk.brandproductid = 0 
-- and bpk.computertype = 2 -- 0 
and bpk.brandid = bpp.brandid 
and bpk.brandproductkeyname <> '' 
and upper(bpp.brandproductenname) = upper(concat(bpk.brandproductkeyname,' Notebook PC')); 
insert into t_runninglog values(default,default,'rule hp 2 in MergeBrandProductKey',''); 
commit; 
-- */ 
--  Task executor   The end of the  
insert into t_runninglog values(default,default,'finish in MergeBrandProductKey',''); 
commit; 
END 

The syntax structure of HANDLER is as follows:

 
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement 
handler_type: CONTINUE | EXIT 
condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code 
Handlers type : 
, EXIT:  Exits the current block of code (either a subblock or a subblock) when an error occurs main The code block)  
, CONTINUE:  The following code continues to execute when the error is sent  
condition_value :  
condition_value standards-supporting SQLSTATE Definition;  
SQLWARNING It's for all 01 At the beginning of SQLSTATE Code shorthand  
NOT FOUND It's for all 02 At the beginning of SQLSTATE Code shorthand  
SQLEXCEPTION Is to all have not been SQLWARNING or NOT FOUND The captured SQLSTATE Code shorthand  
 In addition to SQLSTATE Value, MySQL Error codes are also supported  
 But for mysql The priorities are as follows:  
MySQL Error code > SQLSTATE code >  Named conditions