mysql存储过程 try_MySql存储过程异常处理示例

------------------------------------------------------------------------------------Routine DDL--Note: comments before and after the routine body will not be stored by the server----------------------------------------------------------------------------------

DELIMITER $$CREATE DEFINER=`driveradmin`@`%` PROCEDURE`Merge_BrandProductKey`()BEGIN

DECLARE EXIT HANDLER FORSQLEXCEPTIONbegin

insert into t_runninglog values(default,default,'exception in MergeBrandProductKey',concat(@@error_count,'errors'));commit;end;DECLARE CONTINUE HANDLER FORSQLWARNINGbegin

insert into t_runninglog values(default,default,'warnings in MergeBrandProductKey',concat(@@warning_count,'warnings'));commit;end;insert into t_runninglog values(default,default,'start in MergeBrandProductKey','');commit;--任务执行主体 开始

--/*

--normal

update brandproductkey asbpk,

(selectbp.brandproductid, bp.brandproductenname, bp.brandidfrom brandproduct asbpinner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) asbpron bp.brandid=bpr.brandid and bp.brandproductid =bpr.brandproductid

)asbppset bpk.brandproductid=bpp.brandproductidwhere bpk.brandproductid = 0

--and bpk.computertype = 2 -- 0

and bpk.brandid =bpp.brandidand upper(bpk.brandproductkeyname) = upper(replace(bpp.brandproductenname,' ',''));commit;insert into t_runninglog values(default,default,'rule normal in MergeBrandProductKey','');commit;--sony rule 1

--VPCEA37EC --> (VPCEA37EC/B,VPCEA37EC/L,VPCEA37EC/P,VPCEA37EC/W)

update brandproductkey asbpk,

(selectbp.brandproductid, bp.brandproductenname, bp.brandidfrom brandproduct asbpinner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) asbpron bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=60)asbppset bpk.brandproductid=bpp.brandproductidwhere bpk.brandproductid = 0

--and bpk.computertype = 2 -- 0

and bpk.brandid =bpp.brandidand bpp.brandproductenname like concat(bpk.brandproductkeyname,'/%');commit;insert into t_runninglog values(default,default,'rule sony 1 in MergeBrandProductKey','');commit;--sony rule 2

--VGN-TZ37N_X --> VGN-TZ37N/X

update brandproductkey asbpk,

(selectbp.brandproductid, bp.brandproductenname, bp.brandidfrom brandproduct asbpinner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) asbpron bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=60)asbppset bpk.brandproductid=bpp.brandproductidwhere bpk.brandproductid = 0

--and bpk.computertype = 2 -- 0

and bpk.brandid =bpp.brandidand upper(bpk.brandproductkeyname) = upper(replace(bpp.brandproductenname,'/','_'));commit;insert into t_runninglog values(default,default,'rule sony 2 in MergeBrandProductKey','');commit;--lenovo rule 1

--ZHAOYANG E45 --> 昭阳E45

update brandproductkey asbpk,

(selectbp.brandproductid, bp.brandproductenname, bp.brandid,bpr.driveridfrom brandproduct asbpinner join (select brandid,brandproductid,max(driverinfoid) as driverid from brandproductdriverrelation group by brandid,brandproductid) asbpron bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=37)asbppset bpk.brandproductid=bpp.brandproductidwhere bpk.brandproductid = 0

--and bpk.computertype = 2 -- 0

and bpk.brandid =bpp.brandidand 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(default,default,'rule lenovo 1 in MergeBrandProductKey','');commit;--HP rule 1

--HP Compaq 6535s --> HP Compaq 6535s 笔记本电脑

update brandproductkey asbpk,

(selectbp.brandproductid, bp.brandproductenname, bp.brandidfrom brandproduct asbpinner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) asbpron bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=36)asbppset bpk.brandproductid =bpp.brandproductidwhere bpk.brandproductid = 0

--and bpk.computertype = 2 -- 0

and bpk.brandid =bpp.brandidand bpk.brandproductkeyname <> ''

and bpp.brandproductenname = concat(bpk.brandproductkeyname,'笔记本电脑');insert into t_runninglog values(default,default,'rule hp 1 in MergeBrandProductKey','');commit;--HP rule 2

--HP Compaq 6535s --> HP Compaq 6535s Notebook PC

update brandproductkey asbpk,

(selectbp.brandproductid, bp.brandproductenname, bp.brandidfrom brandproduct asbpinner join (select brandid,brandproductid from brandproductdriverrelation group by brandid,brandproductid) asbpron bp.brandid=bpr.brandid and bp.brandproductid = bpr.brandproductid and bp.brandid=36)asbppset bpk.brandproductid =bpp.brandproductidwhere bpk.brandproductid = 0

--and bpk.computertype = 2 -- 0

and bpk.brandid =bpp.brandidand bpk.brandproductkeyname <> ''

and upper(bpp.brandproductenname) = upper(concat(bpk.brandproductkeyname,'Notebook PC'));insert into t_runninglog values(default,default,'rule hp 2 in MergeBrandProductKey','');commit;--*/

--任务执行主体 结束

insert into t_runninglog values(default,default,'finish in MergeBrandProductKey','');commit;END