Mysql Stored Procedure (4) -- Exception Handling

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 by defining

Continue/exit

Exception Handling

Handler

To capture

Sqlwarning/not found/sqlexception

(Warning

/

No data

/

Other exceptions ). Where,

For

You can change it

Sqlwarning,
Not found, sqlexception

To indicate that all exceptions are handled, which is equivalent

Oracle

In

Others

. For example, when no exception is processed, the following code directly throws

Error 1062 (23000)

Error:

SQL
Code 4-1:

Create procedure test_proc_ins1 (<br/> IN I _id INT, <br/> IN I _name VARCHAR (100) <br/>) <br/> BEGIN <br/> insert into testproc VALUES (I _id, I _name); <br/> insert into testproc VALUES (I _id, I _name); <br/> END;

 

After Exception Handling, you can avoid throwing errors, but define a return parameter.

O_ret

Assign a special value to indicate a failure. In this way

Java

In the code, the business logic can be handled by obtaining the return value rather than capturing exceptions. For example, set the return value

-1:

SQL
Code 4-2:

Create procedure test_proc_ins1 (<br/> IN I _id INT, <br/> IN I _name VARCHAR (100), <br/> OUT o_ret INT) <br/> BEGIN <br/> declare exit handler for sqlstate '000000' set o_ret =-1; <br/> -- this can also be used as follows: <br/> -- declare exit handler for sqlwarning, not found, SQLEXCEPTION set o_ret =-1; <br/> insert into testproc VALUES (I _id, I _name ); <br/> insert into testproc VALUES (I _id, I _name); <br/> set o_ret = 1; <br/> END;