MySql存储过程异常处理示例

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类型:
 
1, EXIT: 发生错误时退出当前代码块（可能是子代码块或者main代码块）
2, 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 > 命名条件