MySql存储过程异常处理示例_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySql存储过程异常处理示例_MySQL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5558字,纯文字阅读大概需要8分钟。
内容图文
![MySql存储过程异常处理示例_MySQL](/upload/InfoBanner/zyjiaocheng/573/ca873dcd16e74e7d9c6c6e173ba55cf9.jpg)
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内容总结
以上是互联网集市为您收集整理的MySql存储过程异常处理示例_MySQL全部内容,希望文章能够帮你解决MySql存储过程异常处理示例_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。