Question

如何在SQL存储过程中处理错误

Answer 1

1. 建立一个过程，第一个语句 DECLARE EXIT HANDLER是用来处理异常的，意思是如果错误 1216发生，这个程序将会在错误记录表中插入一行，
EXIT的意思是 当动作成功提交后推出这个复合语句。
create procedure p22(parameter int)
begin
declare exit Handler for 1452
insert into error_log values(concat('Time: ',current_date,'.Foreign key reference failure for value=',parameter));
insert into t3 values(parameter);
end;//
2. 申明异常处理的语法 DECLARE HANDLER syntax：
DECLARE {EXIT|CONTINUE} HANDLER FOR {error_number|{SQLSTATE error-string}|condition} SQL Statement
上面就是错误处理的用法，也就是一段当程序出错后自动触发的代码，MYSQL允许两种处理器，一种是exit处理，另外一种是 continue处理，与exit
不同的是在于他执行后，原主程序仍然继续运行，那么该复合语句就没有出口了。
----continue处理的例子：
create table t4(s1 int primary key);//
create procedure p23()
begin
declare continue handler for SQLSTATE '23000' set @x2=1;
set @x=1;
insert into t4 values (1);
set @x=2;
insert into t4 values(1);
set @x=3;
select @x, @x2;
end;//
call p23();//
---- rollback（回滚事务）,定义自己的错误处理名字 declare '错误处理名' condition for SQLSTATE'23000';
create procedure p24()
begin
declare ViolationSelf condition for SQLSTATE'23000';
DECLARE EXIT HANDLER for ViolationSelf rollback;
start transaction;
insert into t2 values(1);
insert into t2 values(1);
commit;
end;//

/******************************************** Cursor游标 **********************************************************/
游标实现功能的摘要： 声明游标， 打开游标，从游标里读取，关闭游标
DECLARE cursor-name CURSOR FOR SELECT ······
OPEN cursor-name;
FETCH cursor-name INTO variable;
CLOSE cursor-name;
1. create procedure p25(out return_val int)
begin
DECLARE a,b,c int;
DECLARE cur_1 CURSOR for select s1 from t;
DECLARE continue handler for not found set b=1;
open cur_1;
set c=0;
repeat
fetch cur_1 into a;
until b=1
end repeat;
close cur_1;
set return_val=a;
end;//
2. create procedure p25_1(out return_val int)
begin
DECLARE a,b,c int;
DECLARE cur_1 CURSOR for select s1 from t;
DECLARE continue handler for not found set b=1;
open cur_1;
set c=0;
lable_1:loop
fetch cur_1 into a;
if b=1 then
leave lable_1;
end if;
set c=c+1;
end loop;
close cur_1;
set return_val=c;
end;//
create procedure p34(in va int)
begin
delete from t where s1=va;
end;//

Answer 2

BEGIN TRAN --事务
BEGIN TRY --捕获异常

INSERT INTO score VALUES (101,90)
INSERT INTO score VALUES (102,78)
INSERT INTO score VALUES (107, 76) /* 外键错误 */
INSERT INTO score VALUES (103,81)
INSERT INTO score VALUES (104,65)
COMMIT TRAN
PRINT '事务提交'
END TRY
BEGIN CATCH --有异常，处理
ROLLBACK
PRINT '事务回滚'
END CATCH