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