异常处理 mysql mysql>mysql> delimiter $$mysql>mysql> create procedure myproc -> (p_first_name varchar(30), -> p_last_name varchar(30), -> p_city varchar(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_id int; -> -> 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_sqlcode0 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_sqlcode0 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)