时间:2021-07-01 10:21:17 帮助过:11人阅读
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;
经过异常处理后,可以避免抛出错误,而是定义一个返回参数 o_ret 赋予特殊值来表示失败,这样,比如在 java 代码中,可以通过获取返回值而不是捕获异常的方式来处理业务逻辑。例如将返回值设置为 -1:
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;
当然,对于特定的SQL语句,也可以指定比如主键冲突,就rollback;
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()//