MySQL 存储过程的异常处理

Question

1. mysql>
2. mysql> delimiter $$
3. mysql>
4. mysql> CREATE PROCEDURE myProc
5.     ->     (p_first_name          VARCHAR(30),
6.     ->       p_last_name           VARCHAR(30),
7.     ->       p_city                VARCHAR(30),
8.     ->       p_description         VARCHAR(30),
9.     ->       OUT p_sqlcode         INT,
10.     ->       OUT p_status_message  VARCHAR(100))
11.     -> BEGIN
12.     ->
13.     -> /* START Declare Conditions */
14.     ->
15.     ->   DECLARE duplicate_key CONDITION FOR 1062;
16.     ->   DECLARE foreign_key_violated CONDITION FOR 1216;
17.     ->
18.     -> /* END Declare Conditions */
19.     ->
20.     -> /* START Declare variables and cursors */
21.     ->
22.     ->      DECLARE l_manager_id       INT;
23.     ->
24.     ->      DECLARE csr_mgr_id CURSOR FOR
25.     ->       SELECT id
26.     ->         FROM employee
27.     ->        WHERE first_name=p_first_name
28.     ->              AND last_name=p_last_name;
29.     ->
30.     -> /* END Declare variables and cursors */
31.     ->
32.     -> /* START Declare Exception Handlers */
33.     ->
34.     ->   DECLARE CONTINUE HANDLER FOR duplicate_key
35.     ->     BEGIN
36.     ->       SET p_sqlcode=1052;
37.     ->       SET p_status_message='Duplicate key error';
38.     ->     END;
39.     ->
40.     ->   DECLARE CONTINUE HANDLER FOR foreign_key_violated
41.     ->     BEGIN
42.     ->       SET p_sqlcode=1216;
43.     ->       SET p_status_message='Foreign key violated';
44.     ->     END;
45.     ->
46.     ->   DECLARE CONTINUE HANDLER FOR not FOUND
47.     ->     BEGIN
48.     ->       SET p_sqlcode=1329;
49.     ->       SET p_status_message='No record found';
50.     ->     END;
51.     ->
52.     -> /* END Declare Exception Handlers */
53.     ->
54.     -> /* START Execution */
55.     ->
56.     ->   SET p_sqlcode=0;
57.     ->   OPEN csr_mgr_id;
58.     ->   FETCH csr_mgr_id INTO l_manager_id;
59.     ->
60.     ->   IF p_sqlcode<>0 THEN           /* Failed to get manager id*/
61.     ->     SET p_status_message=CONCAT(p_status_message,' when fetching manager id');
62.     ->   ELSE
63.     ->     INSERT INTO employee (first_name,id,city)
64.     ->     VALUES(p_first_name,l_manager_id,p_city);
65.     ->
66.     ->     IF p_sqlcode<>0 THEN     /* Failed to insert new department */
67.     ->       SET p_status_message=CONCAT(p_status_message,
68.     ->                            ' when inserting new department');
69.     ->     END IF;
70.     ->   END IF;
71.     ->
72.     ->   CLOSE csr_mgr_id;
73.     ->
74.     -> /* END Execution */
75.     ->
76.     -> END$$
77. Query OK, 0 rows affected (0.02 sec)
79. mysql>
80. mysql> delimiter ;
81. mysql> set @myCode = 0;
82. Query OK, 0 rows affected (0.00 sec)
84. mysql> set @myMessage = 0;
85. Query OK, 0 rows affected (0.00 sec)
87. mysql>
88. mysql> call myProc('Jason','Martin','New City','New Description',@myCode,@myMessage);
89. Query OK, 1 row affected (0.00 sec)
91. mysql>
92. mysql> select @myCode, @myMessage;
93. +---------+------------+
94. | @myCode | @myMessage |
95. +---------+------------+
96. | 0       | NULL       |
97. +---------+------------+
98. 1 row in set (0.00 sec)
100. mysql>
101. mysql> drop procedure myProc;
102. Query OK, 0 rows affected (0.00 sec)

复制代码