Some basic Exception Handling tutorials in the MySQL stored procedure,

Some basic Exception Handling tutorials in the MySQL stored procedure,

Sometimes, you do not want the stored procedure to throw an error and stop the execution. Instead, you want to return an error code. Mysql supports Exception Handling. It captures SQLWARNING/not found/SQLEXCEPTION by defining HANDLER for handling the CONTINUE/EXIT exception (warning/no data/other exceptions ). Here, FOR can be followed by SQLWARNING, not found, SQLEXCEPTION to indicate that all exceptions are handled, which is equivalent to others in oracle. For example, if no exception is processed, the following code directly throws an ERROR 1062 (23000:

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;

After Exception Handling, you can avoid throwing an error. Instead, you can define a special value to indicate the failure. For example, in java code, the business logic can be handled by obtaining the return value rather than capturing exceptions. For example, set the return value to-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 '000000' set o_ret =-1; -- this can also be used: -- 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;

Of course, for specific SQL statements, you can also specify rollback for primary key conflicts;

DECLARE exit HANDLER FOR SQLSTATE '23000'delimiter //CREATE PROCEDURE TEST()BEGINDECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUNDbeginrollback;insert into bb values('error');end;START TRANSACTION;INSERT INTO aa VALUES (1);INSERT INTO aa VALUES (2);COMMIT;END;//CALL test()//

Articles you may be interested in:

• Troubleshooting of MySQL Stored Procedure exceptions
• Sample MySql Stored Procedure exception handling code sharing