MySql存储过程异常处理示例_MySQL

bitsCN.com

MySql存储过程异常处理示例:

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

下面是示例代码,在发生异常的时候会将异常信息存入日志表中,并继续运行后面的语句.

如果您有更好的建议,望不吝赐教.

MySql存储过程异常处理示例

-- ---------------------------------------------------------------------------------- Routine DDL-- Note: comments before and after the routine body will not be stored by the server-- --------------------------------------------------------------------------------DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `Merge_BrandProductKey`()BEGIN    DECLARE CONTINUE HANDLER FOR SQLWARNING,SQLEXCEPTION    begin        insert into t_runninglog values(current_timestamp(),'error in MergeBrandProductKey',left(mysql_error(),255));        commit;    end;    insert into t_runninglog values(current_timestamp(),'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(current_timestamp(),'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(current_timestamp(),'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(current_timestamp(),'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(current_timestamp(),'rule lenovo 1 in MergeBrandProductKey','');    commit;        insert into t_runninglog values(current_timestamp(),'finish in MergeBrandProductKey','');    commit;END

这里的这段语句的作用是在后面的语句执行的时候,如果发生了错误,这段语句就会被触发,将错误消息存入到日志表后,存储过程会在刚才发生错误的地方继续向后执行.

    DECLARE CONTINUE HANDLER FOR SQLWARNING,SQLEXCEPTION    begin        insert into t_runninglog values(current_timestamp(),'error in MergeBrandProductKey',left(mysql_error(),255));        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 > 命名条件

mysql_error() 函数返回上一个 MySQL 操作产生的文本错误信息.

参考资料: mysql_error

BTW: 本人对mysql不熟悉,以前一直用的是oracle,最近需要用到mysql,所以写了这个存储过程用来每周定时处理数据,哪位童鞋有更好的建议请评论提出,我会虚心接受,但是请勿人身攻击,谢谢.

bitsCN.com