作用域:
1、包括begni..end;内的语句
DECLARE CONTINUE HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
BEGIN
INSERT INTO a VALUES (6,NULL);
END;
若a表第二字段定义为非空,则会触发1048错误
2、若错误处理在begin..end内定义,则在之外的语句不会触发错误发生
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR 1216 select
'Foreign key constraint violated';
END;
INSERT INTO departments (department_name,manager_id,location)
VALUES ('Elbonian HR','Catbert','Catbertia');
END;
3、能够捕获其它存储过程抛出的错误
DECLARE CONTINUE HANDLER FOR 1048 SELECT 'Attempt to insert a null value';
BEGIN
INSERT INTO a VALUES (6,NULL);
END;
若a表第二字段定义为非空,则会触发1048错误
2、若错误处理在begin..end内定义,则在之外的语句不会触发错误发生
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR 1216 select
'Foreign key constraint violated';
END;
INSERT INTO departments (department_name,manager_id,location)
VALUES ('Elbonian HR','Catbert','Catbertia');
END;
3、能够捕获其它存储过程抛出的错误
优先级:
在一个begin...end块中可以定义多个handler,来分别处理不同的异常。例如:
declare
exit
handler
for
sqlexception
begin
select ' error ' ;
end ;
DECLARE continue HANDLER FOR SQLSTATE ' 02000 ' SET done = 1 ;
begin
select ' error ' ;
end ;
DECLARE continue HANDLER FOR SQLSTATE ' 02000 ' SET done = 1 ;
处理的优先级是:
MySQL Error code > SQLSTATE code > 命名条件
MySQL error code,如1062
ANSI标准SQLSTATE code,如23000
命名条件,如NOT FOUND
ANSI标准SQLSTATE code,如23000
命名条件,如NOT FOUND