MySql存儲進程異常處置示例代碼分享。本站提示廣大學習愛好者：（MySql存儲進程異常處置示例代碼分享）文章只能為提供參考，不一定能成為您想要的結果。以下是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 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 > 定名前提
