Example Analysis of MySQL exception handling
This article shares the content of an example analysis of MySQL exception handling. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.
For errors or warning messages that may occur in stored procedures, triggers or internal statements of functions, relevant exceptions or exceptions need to be captured and then handled accordingly. The exception handling methods are as follows:
DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code| MySQL error code| condition_name} handler_actions
Description:
· Handler type (CONTINUE, EXIT): type of processing, continue or exit
Handler condition (SQLSTATE, MySQL error code, named condition)
· Hander actions
Note:a, exit only exit the current block.
If handler_action is defined, it will be executed before continue or exit.
Error conditions include:
· mysql error code
· ANSI-standard SQLSTATE code. For details, please see dev.mysql.com/doc/refman/5.6/en/error-messages-server.html
·Naming conditions can be customized using the built-in SQLEXCEPTION, SQLWARNING, and NOT FOUND.
When error handling is defined using both MySQl error codes, standard SQLSTATE error codes, and naming conditions (SQLEXCEPTION), the catch order is (only one error can be caught):
MYSQL Code->SQLSTATE-> Naming Conditions
Scope:
begni.. end, which fruit error handling is defined in begin... End, then begin... Errors other than end are not caught.
It can catch errors in other storage processes.
For scope, consider the following procedure
drop table nums;
drop procedure pCreateNums;
CREATE TABLE nums (
id INT NOT NULL PRIMARY KEY,
b INT NOT NULL
);
delimiter $$
create procedure pCreateNums(cnt int)
begin
begin
declare continue handler for sqlexception rollback;
start transaction;
insert into nums(id,b) values(cnt+1,1);
insert into nums(id,b) values(cnt,null);
insert into nums(id,b) values(cnt+2,1);
commit;
end;
insert into nums values(100,100);
select * from nums;
end $$
delimiter ;
call pCreateNums(10);
Nums table b field is not empty.
The internal begin end is defined by an exception handler of type continue
Then, when executing sql below,
insert into nums(id,b) values(cnt,null);
Trigger exception handling, review previous SQL
But the following SQL continues to execute.
The outer begin end block is also executed.
The results are as follows:
mysql> call pCreateNums(10);
+-----+-----+
| id | b |
+-----+-----+
| 12 | 1 |
| 100 | 100 |
+-----+-----+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
If the exception handling is Exit type, the inner begin end block rolls back, and the rest of the SQL in the inner layer is no longer executed.
But the outer SQL is unaffected. Because exception handling is defined internally.
This is the scope of exception handling.
The results are as follows:
mysql> call pCreateNums(10);
+-----+-----+
| id | b |
+-----+-----+
| 100 | 100 |
+-----+-----+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Of course, if exception handling is defined in the outer layer. His scope is the whole process.
Thank you for reading! About "MySQL exception handling example analysis" This article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!
