MySQL存储过程的异常处理

无详细内容 异常处理 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

–>
• $velocityCount

–>

–>

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=&＃039;Duplicate key error&＃039;;    ->     END;    ->    ->   DECLARE CONTINUE HANDLER FOR foreign_key_violated    ->     BEGIN    ->       SET p_sqlcode=1216;    ->       SET p_status_message=&＃039;Foreign key violated&＃039;;    ->     END;    ->    ->   DECLARE CONTINUE HANDLER FOR not FOUND    ->     BEGIN    ->       SET p_sqlcode=1329;    ->       SET p_status_message=&＃039;No record found&＃039;;    ->     END;    ->    -> /* END Declare Exception Handlers */    ->    -> /* START Execution */    ->    ->   SET p_sqlcode=0;    ->   OPEN csr_mgr_id;    ->   FETCH csr_mgr_id INTO l_manager_id;    -
本文来源gao!daima.com搞$代!码#网#
>    ->   IF p_sqlcode0 THEN           /* Failed to get manager id*/    ->     SET p_status_message=CONCAT(p_status_message,&＃039; when fetching manager id&＃039;);    ->   ELSE    ->     INSERT INTO employee (first_name,id,city)    ->     VALUES(p_first_name,l_manager_id,p_city);    ->    ->     IF p_sqlcode0 THEN     /* Failed to insert new department */    ->       SET p_status_message=CONCAT(p_status_message,    ->                            &＃039; when inserting new department&＃039;);    ->     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(&＃039;Jason&＃039;,&＃039;Martin&＃039;,&＃039;New City&＃039;,&＃039;New Description&＃039;,@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)

---