SQL plsql/cursors 处理异常并返回执行流程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8566339/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
plsql/cursors handle exception and return back to the execution flow
提问by codeObserver
I am trying to execute a cursor and want it to complete the loop even if there is some exception.
我正在尝试执行一个游标并希望它完成循环，即使有一些异常。
What I trying to do is "catch" all the exception and probaably log somthing or do nothing and then return back to the flow . Here is how the code looks like:
我试图做的是“捕获”所有异常并可能记录一些事情或什么都不做，然后返回到流程中。下面是代码的样子：
FOR line IN my_cursor
LOOP
begin
if<condition> then
GOTO pass;
else
<<do_something>>
exception
when others then
sys.dbms_output.put_line('say something');
end if;
<<pass>> null;
end
END LOOP;
The script doesnt compile. There is probably some syntactic error with the exception , but I am also not aware of semantics very well. Like I am not sure if you can return back to execution flow after handling an exception.
该脚本无法编译。异常可能存在一些语法错误，但我也不太了解语义。就像我不确定您是否可以在处理异常后返回执行流程。
p.s: The DB is 10g and there is not CONTINUE in it . Hence using GOTO.
ps：数据库是10g，里面没有CONTINUE。因此使用GOTO。
回答by Ian Carpenter
Put the code that you want to execute within the loop in it's own block and then you can use that blocks exception section to handle any problems during the loop iteration.
将要在循环中执行的代码放在它自己的块中，然后您可以使用该块异常部分来处理循环迭代期间的任何问题。
Once the exception for that iteration is handled, the next loop iteration will start
一旦处理了该迭代的异常，下一个循环迭代将开始
e.g.:
例如：
for line in my_cursor
loop
begin
<<do_something>>
exception
<<do_exception_processing>>
end;
end loop;
To illustrate this further, in the example below, I have declared a local variable of type exception. I am looping through numbers 1 to 10, during the second loop iteration, the if statement is true and processing passes to the exception handler. Once the exception is handled, the next iteration of the loop begins.
为了进一步说明这一点，在下面的示例中，我声明了一个异常类型的局部变量。我循环遍历数字 1 到 10，在第二次循环迭代期间，if 语句为真并且处理传递给异常处理程序。一旦异常被处理，循环的下一次迭代就开始了。
begin
for i in 1 .. 10
loop
declare
my_exception exception;
begin
if i = 2
then
-- if you need to do some processing then you would enter it
-- here and then when you want to enter the exception section
-- you would add the line below
raise my_exception;
end if;
exception
when my_exception then
dbms_output.put_line('in exception section');
end;
end loop;
end;
回答by Adam Musch
FOR line IN my_cursor
LOOP
if not some_condition then
begin
do_something;
exception
when others then log_my_error(); -- this should be something that uses
-- an autonomous transaction
end;
end if;
END LOOP;
回答by user3513976
BEGIN
FOR Line in My_Cursor LOOP
IF condition THEN
BEGIN
do something...
END;
ELSE
BEGIN
do something...
END;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('say something');
END;
