在網(wǎng)上查了好多資料,發(fā)現(xiàn)關(guān)于mysql的異常處理資料都是一些錯誤號列表,對于平時運行中,我們可能更多的希望能夠記錄準確的錯誤消息到日志中
下面是示例代碼,在發(fā)生異常的時候會將異常信息存入日志表中,并繼續(xù)運行后面的語句.
如果您有更好的建議,望不吝賜教.
存儲過程異常處理示例
代碼如下:
— ——————————————————————————–
— 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;
— 任務執(zhí)行主體 開始
— /*
— 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;
— */
— 任務執(zhí)行主體 結(jié)束
insert into t_runninglog values(default,default,’finish in MergeBrandProductKey’,”);
commit;
END
有關(guān)HANDLER的語法結(jié)構(gòu)如下:
代碼如下:
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: 發(fā)生錯誤時退出當前代碼塊（可能是子代碼塊或者main代碼塊）
, CONTINUE: 發(fā)送錯誤時繼續(xù)執(zhí)行后續(xù)代碼
condition_value：
condition_value支持標準的SQLSTATE定義；
SQLWARNING是對所有以01開頭的SQLSTATE代碼的速記
NOT FOUND是對所有以02開頭的SQLSTATE代碼的速記
SQLEXCEPTION是對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記
除了SQLSTATE值，MySQL錯誤代碼也被支持
但是對于mysql而言，優(yōu)先級如下：
MySQL Error code > SQLSTATE code > 命名條件
