手机站
扫一扫访问手机站
学习笔记,脚本工具,源码下载

口号
当前位置:首页 > 数据缓存 > Mysql > 正文

MySql存储过程异常处理示例代码分享

发布时间:2018-04-15 09:54:28   

下面是示例代码,在发生异常的时候会将异常信息存入日志表中,并继续运行后面的语句.

如果您有更好的建议,望不吝赐教.

存储过程异常处理示例
复制代码 代码如下:

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

mysql 常用设置 字符集编码、自动完成(自动提示)、监 mysql提示[Warning] Invalid (old?) table or database name问题的解决方法

相关文章

文章分类

本类排行榜

相关更新

悠悠之家集编程技术、手机软件与游戏、各种素材、字体下载, 还有大量的学习笔记,编程工具,源码下载,编程技术教学资源。

本站为非营利性网站,悠悠之家的资源均来自网络收集整理,个人纯属学习交流之用,如有侵犯您的版权请与我联系,我们会马上改正请在下载24小时内删除!

Copyright © 2021 悠悠下载(uoften.com) All Rights Reserved.