Mysql系列（十一）異常處理

DROP?PROCEDURE?IF?EXISTS?`SP_MODEL`;  DELIMITER?;;  CREATE?PROCEDURE?`SP_MODEL`(IN?V_TYPE?INT)  BEGIN  /**********存儲(chǔ)過(guò)程模版，結(jié)合了·返回自定義錯(cuò)誤信息·錯(cuò)誤退出··事物回滾·的功能***********/  DECLARE?V_TEST?INT?DEFAULT?0;  DECLARE?V_ERR_NO?INT?DEFAULT?0;  DECLARE?V_ERR_MSG?VARCHAR(100)?DEFAULT?'執(zhí)行成功';  DECLARE?continue?HANDLER?FOR?SQLEXCEPTION,SQLWARNING,NOT?FOUND?SET?V_ERR_NO=1;  --?CREATE?TABLE?`test_error`?(  --?`a`?int(11)?NOT?NULL,  --?`b`?int(11)?DEFAULT?NULL,  --?PRIMARY?KEY?(`a`)  --?)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;  --?TRUNCATE?TABLE?TEST_ERROR;  --?CALL?SP_MODEL(1);  --?SELECT?*?FROM?TEST_ERROR;  START?TRANSACTION;  Label:--?執(zhí)行過(guò)程中出現(xiàn)錯(cuò)誤則跳出此Label  BEGIN  IF?V_TYPE=1?THEN  INSERT?INTO?TEST_ERROR(a,b)?VALUES(1,1);  INSERT?INTO?TEST_ERROR(a,b)?VALUES(1,1);  IF?V_ERR_NO=1?THEN  SET?V_ERR_MSG='您已經(jīng)插過(guò)啦，別再重復(fù)插~';  --?LEAVE?Label;  END?IF;  SET?V_TEST=1;  INSERT?INTO?TEST_ERROR(a,b)?VALUES(3,'a');  IF?V_ERR_NO=1?THEN  SET?V_ERR_MSG='尺寸不對(duì)，插不進(jìn)來(lái)~';  LEAVE?Label;  END?IF;  INSERT?INTO?TEST_ERROR(a,b)?VALUES(4,1);  SET?V_TEST=2;  ELSE  SET?V_ERR_MSG='傳入?yún)?shù)V_TYPE不正確';  SET?V_ERR_NO=1;?--?直接設(shè)置此變量值為1，主動(dòng)拋出異常  LEAVE?Label;  END?IF;  END?Label;  IF?V_ERR_NO=1?THEN  ROLLBACK;  ELSE  COMMIT;  END?IF;  SELECT?V_ERR_NO?AS?ERR_NO,V_ERR_MSG?AS?ERR_MSG,?V_TEST?AS?TEST;?--?返回執(zhí)行結(jié)果  END  ;;  DELIMITER?;