这是我的问题。我正在循环浏览一些值，其中一些值会引发异常。我想记录这些值，但程序流不应该中断。我的意思是，如果我遇到这样的值，我将简单地记录错误并跳到下一个值。
这是简化版本：
drop table test;
--destination Table
create table test
(
id varchar2(2)
);
-- 错误日志表
create table test_log
(
id varchar2(10)
);
DECLARE
l_num NUMBER;
BEGIN
FOR c IN 90..102
LOOP
INSERT INTO test
VALUES (c);
l_num:=c;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(l_num);
INSERT INTO test_log
VALUES (l_num);
COMMIT;
--raise;
END;
/
我的问题是，当它遇到错误时，它只是跳转到异常部分，而不是循环中后面的值。
You can catch the exception in an inner block:
DECLARE
l_num NUMBER;
BEGIN
FOR c IN 90..102
LOOP
l_num:=c;
BEGIN -- inner block
INSERT INTO test
VALUES (c);
EXCEPTION -- in inner block
WHEN OTHERS THEN
dbms_output.put_line(l_num);
INSERT INTO test_log
VALUES (l_num);
END; -- inner block
END LOOP;
END;
/
The loop won't be interrupted if an exception occurs; only that single insert is affected. Note that you don't really want to commit inside the exception handler as that will commit all the successful inserts so far, not just the error. (If you wanted to log the errors but later roll back you could use an autonomous procedure to do the logging, but that's a separate discussion). And you don't want to re-raise the exception as that would still break the loop and the whole outer anonymous block.
Catching 'others' is generally not a good idea; if you have known errors you could encounter - like a bad data or number format - it's better to catch those explicitly. If you have a wider problem like not being able to extend a data file then the insert inside the exception handler would presumably fail anyway though.
You don't really need any more as is still in-scope for the inner exception handler, so you could simplify slightly to:l_num``c
BEGIN
FOR c IN 90..102
LOOP
BEGIN -- inner block
INSERT INTO test
VALUES (c);
EXCEPTION -- in inner block
WHEN OTHERS THEN
dbms_output.put_line(c);
INSERT INTO test_log
VALUES (c);
END; -- inner block
END LOOP;
END;
/
