Atitit mysql 存储过程捕获所有异常，以及日志记录异常信息
1.1. 异常的处理模式exit continue undo模式 1
1.2. 捕获所有异常使用 DECLARE continue HANDLER FOR sqlexception 1
1.3. 捕获特定异常使用HANDLER FOR errorcode 2
1.4. 记录异常到日志表，获取异常代码和异常信息 2
1.5. 抛出自定义异常 3
2. 程序语言中捕获sql自定义抛出的异常 3
2.1. 代码 3
3. 集合的循环loop while repeat模式 4
3.1. 对集合的循环 使用loop模式最简单，while和repeat都麻烦店。loop模式会自动处理集合结束。 4
3.2. While模式循环集合 。。需要定义一个 CONTINUE HANDLER FOR NOT FOUND 4
3.3. 参考资料 5
1.1. 异常的处理模式exit continue undo模式
默认情况下，mysql异常机制是exit模式，出错直接退出。。
当当我们对一个集合循环做处理的时候，需要捕获异常，记录日志，继续执行。
1.2. 捕获所有异常使用 DECLARE continue HANDLER FOR sqlexception
相比js这一类语言的异常捕获，sql的异常捕获比较粗糙，只能捕获一个sp内的异常，不能精确到某几个代码行。。。
BEGIN
#Routine body goes here...
DECLARE a varchar(102) ; DECLARE code varchar(102) ; DECLARE msg varchar(102) ;
DECLARE b varchar(100) ; DECLARE n int ;
##catch
DECLARE continue HANDLER FOR sqlexception
cat1:BEGIN
GET DIAGNOSTICS CONDITION 1
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
SELECT code,msg;
#exit ALL
#leave cat1;
END;
set n=1;
# while n<5 do
lable:LOOP
call exThrow();
set n=n+1;
select 'in loop';
if n>3 THEN
select ' n>3 ';
leave lable;
end if;
#end while;
end loop;
END
1.3. 捕获特定异常使用HANDLER FOR errorcode
1.4. 记录异常到日志表，获取异常代码和异常信息
GET DIAGNOSTICS CONDITION 1
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
SELECT code,msg;
1.5. 抛出自定义异常
#Routine body goes here...
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = "extag_1";
2. 程序语言中捕获sql自定义抛出的异常
2.1. 代码
BEGIN
#Routine body goes here...
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = "extag_1";
END
{
"@type":"java.lang.RuntimeException",
"cause":{
"@type":"java.sql.SQLException",
"errorCode":1644,
"localizedMessage":"extag_1 Query: call exThrow Parameters: []",
"message":"extag_1 Query: call exThrow Parameters: []",
"nextException":{
"errorCode":1644,
"localizedMessage":"extag_1",
"message":"extag_1",
"sQLState":"HY000",
"sQLState":"HY000",
"stackTrace":[{
"localizedMessage":"java.sql.SQLException: extag_1 Query: call exThrow Parameters: []",
"message":"java.sql.SQLException: extag_1 Query: call exThrow Parameters: []",
"stackTrace":[{
3. 集合的循环loop while repeat模式
3.1. 对集合的循环 使用loop模式最简单，while和repeat都麻烦店。loop模式会自动处理集合结束。
BEGIN
#Routine body goes here...
DECLARE a varchar(102) ;
DECLARE b varchar(100) ; DECLARE n int ; DECLARE rs_finished int ;
DECLARE cursor_name CURSOR FOR select id,identity from system_passport order by id desc limit 3;
OPEN cursor_name;
lable:LOOP
fetch cursor_name into a,b;
select a,b;
end LOOP;
END
3.2. While模式循环集合 。。需要定义一个 CONTINUE HANDLER FOR NOT FOUND
BEGIN
#Routine body goes here...
DECLARE a varchar(102) ;
DECLARE b varchar(100) ; DECLARE n int ; DECLARE rs_finished int ;
DECLARE cursor_name CURSOR FOR select id,identity from system_passport order by id desc limit 5;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET rs_finished=1;
#set rs_finished=0;
set n=1;select 'open cursor bef';
OPEN cursor_name;
select 'open cursor after';
fetch cursor_name into a,b;
select a,b;
select rs_finished; # is null
while rs_finished is null do
select a,b;
fetch cursor_name into a,b;
end while;
END
3.3. 参考资料
GET DIAGNOSTIC 语句 - 千里之行始于足下
Atitit mysql数据库自定义异常在java里面的捕获与处理推荐标准与规范
