mysql存儲過程也提供了對異常處理的功能：通過定義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類型:

1, EXIT: 發生錯誤時退出當前代碼塊(可能是子代碼塊或者main代碼塊)

2, 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 > 命名條件

使用SQLSTATE還是MySQL Error Code？

1，SALSTATE是標準，貌似會更portable，但是實際上MySQL、DB2、Oracle等等的存儲程序語法大相徑庭，所以portable的優勢不存在

2，MySQL error code與SQLSTATE并不是一一對應的，比如很多MySQL error code都映射到同一SQLSTATE code(HY000)

當MySQL客戶端碰到錯誤時，它會報告MySQL error code和相關的SQLSATE code:

mysql > CALL nosuch_sp();

ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist

具體的sqlsdate和mysql error code的對應可以在http://dev.mysql.com/doc/的MySQL reference manual的附錄B找到完整的最新的error codes

condition_name：命名條件

MySQL error code或者SQLSTATE code的可讀性太差，所以引入了命名條件:

語法：

Java代碼 ?

DECLARE?condition_name?CONDITION?FOR?condition_value

condition_value:

SQLSTATE?[VALUE]?sqlstate_value

|?mysql_error_code

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

使用:

Java代碼 ?

#?original

DECLARE?CONTINUE?HANDLER?FOR1216MySQL_statements;

#?changed

DECLARE?foreign_key_error?CONDITION?FOR1216;

DECLARE?CONTINUE?HANDLER?FOR?foreign_key_error?MySQL_statements;

# original

DECLARE CONTINUE HANDLER FOR 1216 MySQL_statements;

# changed

DECLARE foreign_key_error CONDITION FOR 1216;

DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;

用condition_name為錯誤代碼起了個別名。

示例1：Duplicate entry Handler

Sql代碼 ?

CREATEPROCEDUREsp_add_location

(in_locationVARCHAR(30),

in_address1VARCHAR(30),

in_address2VARCHAR(30),

zipcodeVARCHAR(10),

OUTout_statusVARCHAR(30))

BEGIN

DECLARECONTINUEHANDLER

FOR1062

SETout_status='Duplicate?Entry';

SETout_status='OK';

INSERTINTOlocations

(location,address1,address2,zipcode)

VALUES

(in_location,in_address1,in_address2,zipcode);

END;

CREATE PROCEDURE sp_add_location

(in_location VARCHAR(30),

in_address1 VARCHAR(30),

in_address2 VARCHAR(30),

zipcode VARCHAR(10),

OUT out_status VARCHAR(30))

BEGIN

DECLARE CONTINUE HANDLER

FOR 1062

SET out_status='Duplicate Entry';

SET out_status='OK';

INSERT INTO locations

(location,address1,address2,zipcode)

VALUES

(in_location,in_address1,in_address2,zipcode);

END;

示例2： Last Row Handler

Sql代碼 ?

CREATEPROCEDUREsp_not_found()

READS?SQL?DATA

BEGIN

DECLAREl_last_rowINTDEFAULT0;

DECLAREl_dept_idINT:

DECLAREc_deptCURSORFOR

SELECTdepartment_idFROMdepartments;

DECLARECONTINUEHANDLERFORNOTFOUNDSETl_last_row=1;

OPENc_dept;

dept_cursor:?LOOP

FETCHc_deptINTOl_dept_id;

IF?(l_last_row=1)THEN

LEAVE?dept_cursor;

ENDIF;

ENDLOOP?dept_cursor;

CLOSEc_dept;

END;

CREATE PROCEDURE sp_not_found()

READS SQL DATA

BEGIN

DECLARE l_last_row INT DEFAULT 0;

DECLARE l_dept_id INT:

DECLARE c_dept CURSOR FOR

SELECT department_id FROM departments;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;

OPEN c_dept;

dept_cursor: LOOP

FETCH c_dept INTO l_dept_id;

IF (l_last_row=1) THEN

LEAVE dept_cursor;

END IF;

END LOOP dept_cursor;

CLOSE c_dept;

END;

綜合示例：

Sql代碼 ?

CREATEPROCEDUREsp_add_department

(p_department_nameVARCHAR(30),

p_manager_surnameVARCHAR(30),

p_manager_firstnameVARCHAR(30),

p_locationVARCHAR(30),

OUTp_sqlcodeINT,

OUTp_status_messageVARCHAR(100))

BEGIN

/*?STARTDeclareConditions?*/

DECLAREduplicate_key?CONDITIONFOR1062;

DECLAREforeign_key_violated?CONDITIONFOR1216;

/*ENDDeclareCOnditions?*/

/*?STARTDeclarevariablesandcursors?*/

DECLAREl_manager_idINT;

DECLAREcsr_mgr_idCURSORFOR

SELECTemployee_idFROMemployees

WHEREsurname=UPPER(p_manager_surname)

ANDfirstname=UPPER(p_manager_firstname);

/*ENDDeclarevariablesandcursors?*/

/*?STARTDeclareException?Handlers?*/

DECLARECONTINUEHANDLERFORduplicate_key

BEGIN

SETp_sqlcode=1052;

SETp_status_message='Duplicate?key?error';

END;

DECLARECONTINUEHANDLERFORforeign_key_violated

BEGIN

SETp_sqlcode=1216;

SETp_status_message='Foreign?key?violated';

END;

DECLARECONTINUEHANDLERFORNOTFOUND

BEGIN

SETp_sqlcode=1329;

SETp_status_message='No?record?found';

END;

/*ENDDeclareException?Handlers?*/

/*?START?Execution?*/

SETp_sqlcode=0;

OPENcsr_mgr_id;

FETCHcsr_mgr_idINTOl_manager_id;

IF?p_sqlcode<>0THEN/*?Failedtoget?manager?id?*/

SETp_status_message=CONCAT(p_status_message,'?when?fetching?manager?id');

ELSE/*?Got?manager?id,?we?can?tryandinsert*/

INSERTINTOdepartments?(department_name,?manager_id,?location)

VALUES(UPPER(p_department_name),?l_manager_id,UPPER(p_location));

IF?p_sqlcode<>0THEN/*?Failedtoinsertnew?department?*/

SETp_status_message=CONCAT(p_status_message,'?when?inserting?new?department');

ENDIF;

ENDIF;

CLOSEcsr_mgr_id;

/*ENDExecution?*/

END

總結

以上是生活随笔為你收集整理的mysql存储过程捕获错误处理_mysql存储过程之异常处理篇的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。