MySQL 存储过程的异常处理

ysitbook

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)
    
78. 
    
79. mysql>
    
80. mysql> delimiter ;
    
81. mysql> set @myCode = 0;
    
82. Query OK, 0 rows affected (0.00 sec)
    
83. 
    
84. mysql> set @myMessage = 0;
    
85. Query OK, 0 rows affected (0.00 sec)
    
86. 
    
87. mysql>
    
88. mysql> call myProc('Jason','Martin','New City','New Description',@myCode,@myMessage);
    
89. Query OK, 1 row affected (0.00 sec)
    
90. 
    
91. mysql>
    
92. mysql> select @myCode, @myMessage;
    
93. +---------+------------+
    
94. | @myCode | @myMessage |
    
95. +---------+------------+
    
96. | 0       | NULL       |
    
97. +---------+------------+
    
98. 1 row in set (0.00 sec)
    
99. 
    
100. mysql>
     
101. mysql> drop procedure myProc;
     
102. Query OK, 0 rows affected (0.00 sec)

复制代码