时间:2021-05-24
本文实例讲述了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 -> -> -> -> DECLARE duplicate_key CONDITION FOR 1062; -> DECLARE foreign_key_violated CONDITION FOR 1216; -> -> -> -> -> -> 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; -> -> -> -> -> -> 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; -> -> -> -> -> -> SET p_sqlcode=0; -> OPEN csr_mgr_id; -> FETCH csr_mgr_id INTO l_manager_id; -> -> IF p_sqlcode<>0 THEN -> 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 -> SET p_status_message=CONCAT(p_status_message, -> ' when inserting new department'); -> END IF; -> END IF; -> -> CLOSE csr_mgr_id; -> -> -> -> 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数据库程序设计有所帮助。
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
有时候,不希望存储过程抛出错误中止执行,而是希望返回一个错误码。Mysql支持异常处理,通过定义CONTINUE/EXIT异常处理的HANDLER来捕获SQLW
本文实例分析了php调用mysql存储过程的方法。分享给大家供大家参考。具体分析如下:Mysql存储过程创建语法,代码如下:CREATEPROCEDURE和CR
MySQL存储过程14.1.1创建存储过程MySQL中,创建存储过程的基本形式如下:CREATEPROCEDUREsp_name([proc_parameter
本文实例讲述了php中调用执行mysql存储过程然后返回由存储过程返回的值,分享给大家供大家参考。具体分析如下:调用存储过程的方法.a。如果存储过程有IN/IN
MySQL存储过程参数有三种类型:in、out、inout。它们各有什么作用和特点呢?一、MySQL存储过程参数(in)MySQL存储过程“in”参数:跟C语言