首页 / 数据库 / MySQL / MySQL存储过程的异常处理方法

mysql>mysql> delimiter $$mysql>mysql> CREATE PROCEDURE myProc-> （p_first_name VARCHAR（30）,->p_last_nameVARCHAR（30）,->p_cityVARCHAR（30）,->p_description VARCHAR（30）,->OUT p_sqlcode INT,->OUT p_status_message VARCHAR（100））-> BEGIN->-> /* START Declare Conditions */->->DECLARE duplicate_key CONDITION FOR 1062;->DECLARE foreign_key_violated CONDITION FOR 1216;->-> /* END Declare Conditions */->-> /* START Declare variables and cursors */->-> DECLARE l_manager_idINT;->-> DECLARE csr_mgr_id CURSOR FOR->SELECT id-> FROM employee->WHERE first_name=p_first_name-> AND last_name=p_last_name;->-> /* END Declare variables and cursors */->-> /* START Declare Exception Handlers */->->DECLARE CONTINUE HANDLER FOR duplicate_key-> BEGIN->SET p_sqlcode=1052;->SET p_status_message="Duplicate key error";-> END;->->DECLARE CONTINUE HANDLER FOR foreign_key_violated-> BEGIN->SET p_sqlcode=1216;->SET p_status_message="Foreign key violated";-> END;->->DECLARE CONTINUE HANDLER FOR not FOUND-> BEGIN->SET p_sqlcode=1329;->SET p_status_message="No record found";-> END;->-> /* END Declare Exception Handlers */->-> /* START Execution */->->SET p_sqlcode=0;->OPEN csr_mgr_id;->FETCH csr_mgr_id INTO l_manager_id;->->IF p_sqlcode<>0 THEN/* Failed to get manager id*/-> SET p_status_message=CONCAT（p_status_message," when fetching manager id"）;->ELSE-> INSERT INTO employee （first_name,id,city）-> VALUES（p_first_name,l_manager_id,p_city）;->-> IF p_sqlcode<>0 THEN /* Failed to insert new department */->SET p_status_message=CONCAT（p_status_message,->" when inserting new department"）;-> END IF;->END IF;->->CLOSE csr_mgr_id;->-> /* END Execution */->-> END$$Query OK, 0 rows affected （0.02 sec）mysql>mysql> delimiter ;mysql> set @myCode = 0;Query OK, 0 rows affected （0.00 sec）mysql> set @myMessage = 0;Query OK, 0 rows affected （0.00 sec）mysql>mysql> call myProc（"Jason","Martin","New City","New Description",@myCode,@myMessage）;Query OK, 1 row affected （0.00 sec）mysql>mysql> select @myCode, @myMessage;+---------+------------+| @myCode | @myMessage |+---------+------------+| 0| NULL|+---------+------------+1 row in set （0.00 sec）mysql>mysql> drop procedure myProc;Query OK, 0 rows affected （0.00 sec）