MySQL 存储过程的异常处理

[代码] SQL异常处理

view source

print ?

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 cursors */

031

->

032	-> /* START Declare Exception Handlers */

033

->

034	->   DECLARE CONTINUE HANDLER FOR duplicate_key

035	->     BEGIN

036	->       SET p_sqlcode=1052;

037	->       SET p_status_message='Duplicate key error';

038	->     END;

039

->

040	->   DECLARE CONTINUE HANDLER FOR foreign_key_violated

041	->     BEGIN

042	->       SET p_sqlcode=1216;

043	->       SET p_status_message='Foreign key violated';

044	->     END;

045

->

046	->   DECLARE CONTINUE HANDLER FOR not 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 Description',@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)