Stored Procedure 5-condition and Exception Handling

Exception Handling can be used for general process control in subprograms. If you want to handle errors during SQL Execution, you can use exception handling. For example, for errors or warnings that may occur in Stored Procedures, triggers, or internal functions, capture related exceptions or exceptions, and then handle them accordingly.

I. Conditions and handling procedures

1. Declare Conditions

Syntax:

Declare condition name condition for condition Value

The condition values are as follows:

• Sqlstate [value] sqlstate_value
• Mysql_error_code

Both sqlstate_value and mysql_error_code can indicate MySQL errors. For example, in error 1146 (42s02), sqlstate_value is 42s02, and mysql_error_code is 1146. This statement specifies the conditions that require special processing. It associates a name with a specified error condition. This name can then be used in the declare handler statement.

Code:

-- 1: Use sqlstate_value declare can_not_find condition for sqlstate '42s02 '; -- 2: Use mysql_error_code declare can_not_find condition for 1146;

2. Declare Handler

Syntax:
Declare processing type handler for Parameter Error Type [,...] sp_statement

The processing type has the following values:

• Continue: the error is not processed. Continue to run down.
• Exit: Exit immediately after an error occurs.
• Undo: if an error occurs, the operation is recalled. This method is not supported in MySQL.

Parameter error types include the following values:

• Sqlstate [value] qlstate_value: This format is specifically for ansi SQL, ODBC, and other standards. Not all MySQL error codes are mapped to sqlstate.
• Condition_name: Condition name of the declare Condition
• Sqlwarning: shorthand for all sqlstate codes starting with 01.
• Not found: a stenographer for all sqlstate codes starting with 02. Of course, a cursor can also be reached at the end of the dataset.
• Sqlexception: A shorthand for all sqlstate codes that are not captured by sqlwarning or not found.
• Mysql_error_code: Commonly Used mysql_error_code list http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

Code:

-- 1: capture sqlstate_value declare continue handler for sqlstate '42s02' set @ info = 'can not find '; -- 2: capture mysql_error_code declare continue handler for 1146 set @ info = 'can not find '; -- 3: first define the condition and then call declare can_not_find condition for sqlstate '42s02 '; -- declare can_not_find condition for 1146; declare continue handler for can_not_find set @ info = 'can not find '; -- 4: Use sqlwarning declare exit handler for sqlwarning set @ info = 'error '; -- Method 5: Use not found declare exit handler for not found set @ info = 'can not find '; -- 6: use sqlexception declare exit handler for sqlexception set @ info = 'error ';

Ii. Instances

Insert a duplicate record to the table. If the ID is the same, an exception occurs. In this case, we can handle the exception.

1 -- -------------------------- 2 -- table structure for course 3 -- ---------------------------- 4 drop table if exists 'Course'; 5 create table 'Course' (6 'id' int (11) not null, 7 'name' varchar (255) not null, 8 'score 'int (11) not null, 9 primary key ('id') 10) engine = InnoDB default charset = utf8; 1112 13 drop procedure if exists proc_test_exce; 14 create procedure proc_test_exce (15 in uid int (11), 16 in uname varchar (255), 17 in uscore int (11 ), 18 out result int (11) 19) 20 begin 21 -- declare exit handler for sqlstate '000000' set result =-1; 22 declare exit handler for sqlwarning, not found, sqlexception set result =-1; 23 Start transaction; 24 insert into course (ID, name, score) values (UID, uname, uscore); 25 set result = 1; 26 commit; 27 end; 28 29 -- returns-130 call proc_test_exce (3, 'Chinese', 34, @ result); 31 select @ result;

Call proc_test_exce (3, 'Chinese', 34, @ result) is executed consecutively. Twice, the first reuslt returns 1, and the second returns-1. Because of the ID conflict, if you run the command in 24 rows, the system will not exit and continue to execute, but 22 rows will be executed. Therefore, the second response is-1.