bitsCN.com下面是示例代码,在发生异常的时候会将异常信息存入日志表中,并继续运行后面的语句.
如果您有更好的建议,望不吝赐教.
存储过程异常处理示例-- ---------------------------------------------------------------------------------- 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 > 命名条件bitsCN.com