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