首页 > 数据库 > mysql教程 > MySQL 存储过程的异常处理

MySQL 存储过程的异常处理

WBOY
发布: 2016-06-07 14:54:32
原创
1351 人浏览过

无详细内容 异常处理 MySQL mysqlmysql delimiter $$mysqlmysql 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 VARCH

异常处理 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)
登录后复制
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板