[代码] SQL异常处理
001 |
mysql> |
002 |
mysql> delimiter $$ |
003 |
mysql> |
004 |
mysql> CREATE PROCEDURE myProc |
005 |
-> (p_first_name VARCHAR (30), |
006 |
-> p_last_name VARCHAR (30), |
007 |
-> p_city VARCHAR (30), |
008 |
-> p_description VARCHAR (30), |
009 |
-> OUT p_sqlcode INT , |
010 |
-> OUT p_status_message VARCHAR (100)) |
011 |
-> BEGIN |
012 |
-> |
013 |
-> /* START Declare Conditions */ |
014 |
-> |
015 |
-> DECLARE duplicate_key CONDITION FOR 1062; |
016 |
-> DECLARE foreign_key_violated CONDITION FOR 1216; |
017 |
-> |
018 |
-> /* END Declare Conditions */ |
019 |
-> |
020 |
-> /* START Declare variables and cursors */ |
021 |
-> |
022 |
-> DECLARE l_manager_id INT ; |
023 |
-> |
024 |
-> DECLARE csr_mgr_id CURSOR FOR |
025 |
-> SELECT id |
026 |
-> FROM employee |
027 |
-> WHERE first_name=p_first_name |
028 |
-> AND last_name=p_last_name; |
029 |
-> |
030 |
-> /* END Declare variables and
|
031 |
-> |
032 |
-> /* START Declare Exception Handlers */ |
033 |
-> |
034 |
-> DECLARE CONTINUE HANDLER FOR
|
035 |
-> BEGIN |
036 |
-> SET p_sqlcode=1052; |
037 |
-> SET p_status_message= 'Duplicate key error' ; |
038 |
-> END ; |
039 |
-> |
040 |
-> DECLARE CONTINUE HANDLER FOR
|
041 |
-> BEGIN |
042 |
-> SET p_sqlcode=1216; |
043 |
-> SET p_status_message= 'Foreign key violated' ; |
044 |
-> END ; |
045 |
-> |
046 |
-> DECLARE CONTINUE HANDLER FOR FOUND |
047 |
-> BEGIN |
048 |
-> SET p_sqlcode=1329; |
049 |
-> SET p_status_message= 'No record found' ; |
050 |
-> END ; |
051 |
-> |
052 |
-> /* END Declare Exception Handlers */ |
053 |
-> |
054 |
-> /* START Execution */ |
055 |
-> |
056 |
-> SET p_sqlcode=0; |
057 |
-> OPEN csr_mgr_id; |
058 |
-> FETCH csr_mgr_id INTO l_manager_id; |
059 |
-> |
060 |
-> IF p_sqlcode<>0 THEN /* Failed to get manager id*/ |
061 |
-> SET p_status_message=CONCAT(p_status_message, ' when fetching manager id' ); |
062 |
-> ELSE |
063 |
-> INSERT INTO employee (first_name,id,city) |
064 |
-> VALUES (p_first_name,l_manager_id,p_city); |
065 |
-> |
066 |
-> IF p_sqlcode<>0 THEN /* Failed to insert new department */ |
067 |
-> SET p_status_message=CONCAT(p_status_message, |
068 |
-> ' when inserting new department' ); |
069 |
-> END IF; |
070 |
-> END IF; |
071 |
-> |
072 |
-> CLOSE csr_mgr_id; |
073 |
-> |
074 |
-> /* END Execution */ |
075 |
-> |
076 |
-> END $$ |
077 |
Query OK, 0 rows affected (0.02 sec) |
078 |
079 |
mysql> |
080 |
mysql> delimiter ; |
081 |
mysql> set @myCode = 0; |
082 |
Query OK, 0 rows affected (0.00 sec) |
083 |
084 |
mysql> set @myMessage = 0; |
085 |
Query OK, 0 rows affected (0.00 sec) |
086 |
087 |
mysql> |
088 |
mysql> call myProc( 'Jason' , 'Martin' , 'New City' , 'New ,@myCode,@myMessage); |
089 |
Query OK, 1 row affected (0.00 sec) |
090 |
091 |
mysql> |
092 |
mysql> select @myCode, @myMessage; |
093 |
+ ---------+------------+ |
094 |
| @myCode | @myMessage | |
095 |
+ ---------+------------+ |
096 |
| 0 | NULL | |
097 |
+ ---------+------------+ |
098 |
1 row in set (0.00 sec) |
099 |
100 |
mysql> |
101 |
mysql> drop procedure myProc; |
102 |
Query OK, 0 rows affected (0.00 sec) |