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;

 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 > 命名条件