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数据库程序设计有所帮助。