设为首页 - 加入收藏 华夏网 ()- 云主机,资讯,互联网,人工智能,云计算,大赢家论坛,区块链,VR,站长网!
热搜: 平台 google 2019 用户
当前位置: 主页 > 水果奶奶论坛 > 正文

MySQL 存储过程的异常处理

发布时间:2021-01-14 09:22 所属栏目:[水果奶奶论坛] 来源:网络整理
导读:今天PHP站长网 52php.cn把收集自互联网的代码分享给大家,仅供参考。 mysqlmysql delimiter $$mysqlmysql CREATE PROCEDURE myProc - (p_first_name VARCHAR(30),- p_last_name VARCHAR(30),- p_city VARCHAR(30),- p_des

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家,仅供参考

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站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助,欢迎收藏、点赞、推荐、分享。

【免责声明】本站内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

网友评论
推荐文章