MySQL存儲(chǔ)過(guò)程中一些基本的異常處理教程_MySQL

CREATE PROCEDURE test_proc_ins1(
 IN i_id INT,
 IN i_name VARCHAR(100)
)
BEGIN
 INSERT INTO testproc VALUES (i_id,i_name);
 INSERT INTO testproc VALUES (i_id,i_name);
END;

CREATE PROCEDURE test_proc_ins1(
 IN i_id INT,
 IN i_name VARCHAR(100),
 OUT o_ret INT)
BEGIN
 DECLARE EXIT HANDLER FOR SQLSTATE '23000' set o_ret = -1;
-- 也可以這樣使用：
-- DECLARE EXIT HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION set o_ret=-1;
 INSERT INTO testproc VALUES (i_id,i_name);
 INSERT INTO testproc VALUES (i_id,i_name);
 set o_ret = 1;
END;

DECLARE exit HANDLER FOR SQLSTATE '23000'

delimiter //

CREATE PROCEDURE TEST()
BEGIN
DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
begin
rollback;
insert into bb values('error');
end;
START TRANSACTION;
INSERT INTO aa VALUES (1);
INSERT INTO aa VALUES (2);
COMMIT;
END;
//

CALL test()//