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 > 命名条件