MySQL存储过程的异常处理方法

本文实例讲述了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_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)

希望本文所述对大家的mysql数据库程序设计有所帮助。