MySQL存储过程的异常处理方法
本文实例讲述了MySQL存储过程的异常处理方法。分享给大家供大家参考。具体如下：
mysql>
mysql>delimiter$$
mysql>
mysql>CREATEPROCEDUREmyProc
->(p_first_nameVARCHAR(30),
->p_last_nameVARCHAR(30),
->p_cityVARCHAR(30),
->p_descriptionVARCHAR(30),
->OUTp_sqlcodeINT,
->OUTp_status_messageVARCHAR(100))
->BEGIN
->
->/*STARTDeclareConditions*/
->
->DECLAREduplicate_keyCONDITIONFOR1062;
->DECLAREforeign_key_violatedCONDITIONFOR1216;
->
->/*ENDDeclareConditions*/
->
->/*STARTDeclarevariablesandcursors*/
->
->DECLAREl_manager_idINT;
->
->DECLAREcsr_mgr_idCURSORFOR
->SELECTid
->FROMemployee
->WHEREfirst_name=p_first_name
->ANDlast_name=p_last_name;
->
->/*ENDDeclarevariablesandcursors*/
->
->/*STARTDeclareExceptionHandlers*/
->
->DECLARECONTINUEHANDLERFORduplicate_key
->BEGIN
->SETp_sqlcode=1052;
->SETp_status_message='Duplicatekeyerror';
->END;
->
->DECLARECONTINUEHANDLERFORforeign_key_violated
->BEGIN
->SETp_sqlcode=1216;
->SETp_status_message='Foreignkeyviolated';
->END;
->
->DECLARECONTINUEHANDLERFORnotFOUND
->BEGIN
->SETp_sqlcode=1329;
->SETp_status_message='Norecordfound';
->END;
->
->/*ENDDeclareExceptionHandlers*/
->
->/*STARTExecution*/
->
->SETp_sqlcode=0;
->OPENcsr_mgr_id;
->FETCHcsr_mgr_idINTOl_manager_id;
->
->IFp_sqlcode<>0THEN/*Failedtogetmanagerid*/
->SETp_status_message=CONCAT(p_status_message,'whenfetchingmanagerid');
->ELSE
->INSERTINTOemployee(first_name,id,city)
->VALUES(p_first_name,l_manager_id,p_city);
->
->IFp_sqlcode<>0THEN/*Failedtoinsertnewdepartment*/
->SETp_status_message=CONCAT(p_status_message,
->'wheninsertingnewdepartment');
->ENDIF;
->ENDIF;
->
->CLOSEcsr_mgr_id;
->
->/*ENDExecution*/
->
->END$$
QueryOK,0rowsaffected(0.02sec)
mysql>
mysql>delimiter;
mysql>set@myCode=0;
QueryOK,0rowsaffected(0.00sec)
mysql>set@myMessage=0;
QueryOK,0rowsaffected(0.00sec)
mysql>
mysql>callmyProc('Jason','Martin','NewCity','NewDescription',@myCode,@myMessage);
QueryOK,1rowaffected(0.00sec)
mysql>
mysql>select@myCode,@myMessage;
+---------+------------+
|@myCode|@myMessage|
+---------+------------+
|0|NULL|
+---------+------------+
1rowinset(0.00sec)
mysql>
mysql>dropproceduremyProc;
QueryOK,0rowsaffected(0.00sec)
希望本文所述对大家的MySQL数据库程序设计有所帮助。