MySql存储过程异常处理示例代码分享

在网上查了好多资料,发现关于mysql的异常处理资料都是一些错误号列表,对于平时运行中,我们可能更多的希望能够记录准确的错误消息到日志中

-- -------------------------------------------------------------------------------- 
-- 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; 
-- 任务执行主体 开始 
-- /* 
-- 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 --> 昭阳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 笔记本电脑 
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,' 笔记本电脑'); 
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; 
-- */ 
-- 任务执行主体 结束 
insert into t_runninglog values(default,default,'finish in MergeBrandProductKey',''); 
commit; 
END 
 
有关HANDLER的语法结构如下: 
 代码如下:

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类型: 
, EXIT: 发生错误时退出当前代码块（可能是子代码块或者main代码块） 
, CONTINUE: 发送错误时继续执行后续代码 
condition_value： 
condition_value支持标准的SQLSTATE定义； 
SQLWARNING是对所有以01开头的SQLSTATE代码的速记 
NOT FOUND是对所有以02开头的SQLSTATE代码的速记 
SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记 
除了SQLSTATE值，MySQL错误代码也被支持 
但是对于mysql而言，优先级如下： 
MySQL Error code > SQLSTATE code > 命名条件