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