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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySql存储过程异常处理示例_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5558字，纯文字阅读大概需要8分钟。

内容图文

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,所以写了这个存储过程用来每周定时处理数据,哪位童鞋有更好的建议请评论提出,我会虚心接受,但是请勿人身攻击,谢谢.

内容总结

以上是互联网集市为您收集整理的MySql存储过程异常处理示例_MySQL全部内容，希望文章能够帮你解决MySql存储过程异常处理示例_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端