下面是示例代码,在发生异常的时候会将异常信息存入日志表中,并继续运行后面的语句. 如果您有更好的建议,望不吝赐教. 存储过程异常处理示例 复制代码 代码如下: -- -------------------
下面是示例代码,在发生异常的时候会将异常信息存入日志表中,并继续运行后面的语句. 如果您有更好的建议,望不吝赐教. 存储过程异常处理示例 复制代码 代码如下: -- -------------------------------------------------------------------------------- -- 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 FOR SQLEXCEPTION begin insert into t_runninglog values(default,default,'exception in MergeBrandProductKey',concat(@@error_count,' errors')); commit; end; DECLARE CONTINUE HANDLER FOR SQLWARNING begin 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 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(default,default,'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(default,default,'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(default,default,'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(default,default,'rule lenovo 1 in MergeBrandProductKey',''); commit; -- HP rule 1 -- HP Compaq 6535s --> HP Compaq 6535s 笔记本电脑 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=36 ) 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 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 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=36 ) 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 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 有关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类型: , EXIT: 发生错误时退出当前代码块（可能是子代码块或者main代码块） , 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 > 命名条件
