欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-26 12:09:58
本文实例讲述了mysql存储过程的异常处理方法。分享给大家供大家参考。具体如下: mysql> mysql> delimiter $$ 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数据库程序设计有所帮助。