欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySql存儲過程異常處理示例代碼分享

 更新時間:2012年06月09日 00:04:55   作者:  
在網(wǎng)上查了好多資料,發(fā)現(xiàn)關(guān)于mysql的異常處理資料都是一些錯誤號列表,對于平時運行中,我們可能更多的希望能夠記錄準(zhǔn)確的錯誤消息到日志中
下面是示例代碼,在發(fā)生異常的時候會將異常信息存入日志表中,并繼續(xù)運行后面的語句.

如果您有更好的建議,望不吝賜教.

存儲過程異常處理示例
復(fù)制代碼 代碼如下:

-- --------------------------------------------------------------------------------
-- 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;
-- 任務(wù)執(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;
-- */
-- 任務(wù)執(zhí)行主體 結(jié)束
insert into t_runninglog values(default,default,'finish in MergeBrandProductKey','');
commit;
END

有關(guān)HANDLER的語法結(jié)構(gòu)如下:
復(fù)制代碼 代碼如下:

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ā)生錯誤時退出當(dāng)前代碼塊(可能是子代碼塊或者main代碼塊)
, CONTINUE: 發(fā)送錯誤時繼續(xù)執(zhí)行后續(xù)代碼
condition_value:
condition_value支持標(biāo)準(zhǔn)的SQLSTATE定義;
SQLWARNING是對所有以01開頭的SQLSTATE代碼的速記
NOT FOUND是對所有以02開頭的SQLSTATE代碼的速記
SQLEXCEPTION是對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記
除了SQLSTATE值,MySQL錯誤代碼也被支持
但是對于mysql而言,優(yōu)先級如下:
MySQL Error code > SQLSTATE code > 命名條件

相關(guān)文章

  • mysql下載與安裝過程詳解

    mysql下載與安裝過程詳解

    這篇文章主要介紹了mysql下載與安裝過程,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-03-03
  • MySQL中對于索引的基本增刪查改操作總結(jié)

    MySQL中對于索引的基本增刪查改操作總結(jié)

    這篇文章主要介紹了MySQL中對于索引的基本增刪查改操作總結(jié),索引可以提高M(jìn)ySQL的檢索速度,需要的朋友可以參考下
    2016-01-01
  • MySQL數(shù)據(jù)庫事務(wù)transaction示例講解教程

    MySQL數(shù)據(jù)庫事務(wù)transaction示例講解教程

    這篇文章主要為大家介紹了MySQL數(shù)據(jù)庫事務(wù)transaction的示例講解教程,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步
    2021-10-10
  • mysql 5.7.14 下載安裝配置方法圖文教程

    mysql 5.7.14 下載安裝配置方法圖文教程

    這篇文章主要為大家分享了mysql 5.7.14 下載安裝配置方法圖文教程,感興趣的朋友可以參考一下
    2016-08-08
  • MySQL排序原理和案例詳析

    MySQL排序原理和案例詳析

    這篇文章主要給大家介紹了關(guān)于MySQL排序原理和案例詳析的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-12-12
  • mysql判斷當(dāng)前時間是否在開始與結(jié)束時間之間且開始與結(jié)束時間允許為空

    mysql判斷當(dāng)前時間是否在開始與結(jié)束時間之間且開始與結(jié)束時間允許為空

    這篇文章主要介紹了mysql判斷當(dāng)前時間是否在開始與結(jié)束時間之間且開始與結(jié)束時間允許為空,文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2021-09-09
  • jQuery 綁定事件的方式總結(jié)

    jQuery 綁定事件的方式總結(jié)

    這篇文章主要介紹了jQuery 綁定事件的方式總結(jié)的相關(guān)資料,這里整理了幾種方法,需要的朋友可以參考下
    2016-11-11
  • mysql 無法連接問題的定位和修復(fù)過程分享

    mysql 無法連接問題的定位和修復(fù)過程分享

    開發(fā)的一款網(wǎng)站防護(hù)產(chǎn)品中出現(xiàn)了一個客戶端上安裝后Mysql每隔一段時間就出現(xiàn)問題,這個問題是客戶反饋的,所以需要去復(fù)現(xiàn)和定位
    2013-03-03
  • MySql數(shù)據(jù)庫中Select用法小結(jié)

    MySql數(shù)據(jù)庫中Select用法小結(jié)

    在程序開發(fā)中數(shù)據(jù)庫是必要知識點,今天小編給大家介紹mysql數(shù)據(jù)庫中的select用法,包括條件篩選、指定篩選和分組顯示查詢語句的寫法,非常不錯,對mysql select用法相關(guān)知識感興趣的朋友一起看看吧
    2016-10-10
  • MySQL函數(shù)Locate的使用詳解

    MySQL函數(shù)Locate的使用詳解

    本文主要介紹了MySQL函數(shù)Locate的使用詳解,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2022-08-08

最新評論