oracle 是否可以从异常继续循环？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5903027/
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
Is it possible to CONTINUE a loop from an exception?
提问by ProfessionalAmateur
I have a fetch being executed inside of a loop. If this fetch fails (no data) I would like to CONTINUEthe loop to the next record from within the EXCEPTION.
我有一个在循环内执行的提取。如果此提取失败（无数据），我希望CONTINUE从EXCEPTION.
Is this possible?
这可能吗？
I'm getting a ORA-06550& PLS-00201 identifer CONTINUE must be declared
我得到一个ORA-06550&PLS-00201 identifer CONTINUE must be declared
DECLARE
v_attr char(88);
CURSOR SELECT_USERS IS
SELECT id FROM USER_TABLE
WHERE USERTYPE = 'X';
BEGIN
FOR user_rec IN SELECT_USERS LOOP
BEGIN
SELECT attr INTO v_attr
FROM ATTRIBUTE_TABLE
WHERE user_id = user_rec.id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- user does not have attribute, continue loop to next record.
CONTINUE;
END;
END LOOP;
END;
采纳答案by angus
The CONTINUEstatement is a new feature in 11g.
该CONTINUE语句是 11g 中的一个新特性。
Here is a related question: 'CONTINUE' keyword in Oracle 10g PL/SQL
这是一个相关的问题：Oracle 10g PL/SQL 中的“CONTINUE”关键字
回答by DCookie
In the construct you have provided, you don't need a CONTINUE. Once the exception is handled, the statement after the END is performed, assuming your EXCEPTION block doesn't terminate the procedure. In other words, it will continue on to the next iteration of the user_rec loop.
在您提供的构造中，您不需要 CONTINUE。处理异常后，执行 END 之后的语句，假设您的 EXCEPTION 块不终止该过程。换句话说，它将继续进行 user_rec 循环的下一次迭代。
You also need to SELECT INTO a variable inside your BEGIN block:
您还需要在 BEGIN 块中 SELECT INTO 变量：
SELECT attr INTO v_attr FROM attribute_table...
Obviously you must declare v_attr as well...
显然你也必须声明 v_attr ......
回答by tbone
How about the ole goto statement (i know, i know, but it works just fine here ;)
ole goto 语句怎么样（我知道，我知道，但它在这里工作得很好；）
DECLARE
v_attr char(88);
CURSOR SELECT_USERS IS
SELECT id FROM USER_TABLE
WHERE USERTYPE = 'X';
BEGIN
FOR user_rec IN SELECT_USERS LOOP
BEGIN
SELECT attr INTO v_attr
FROM ATTRIBUTE_TABLE
WHERE user_id = user_rec.id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- user does not have attribute, continue loop to next record.
goto end_loop;
END;
<<end_loop>>
null;
END LOOP;
END;
Just put end_loop at very end of loop of course. The null can be substituted with a commit maybe or a counter increment maybe, up to you.
当然，只需将 end_loop 放在循环的最后。空值可以替换为提交或计数器增量，由您决定。
回答by bozon
For this example you really should just use an outer join.
对于这个例子，你真的应该只使用一个外连接。
declare
begin
FOR attr_rec IN (
select attr
from USER_TABLE u
left outer join attribute_table a
on ( u.USERTYPE = 'X' and a.user_id = u.id )
) LOOP
<process records>
<if primary key of attribute_table is null
then the attribute does not exist for this user.>
END LOOP;
END;
回答by Tagar
Notice you can use WHEN exception THEN NULLthe same way as you would use WHEN exception THEN continue. Example:
请注意，您可以使用WHEN exception THEN NULL与使用WHEN exception THEN continue. 例子：
DECLARE
extension_already_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(extension_already_exists, -20007);
l_hidden_col_name varchar2(32);
BEGIN
FOR t IN ( SELECT table_name, cast(extension as varchar2(200)) ext
FROM all_stat_extensions
WHERE owner='{{ prev_schema }}'
and droppable='YES'
ORDER BY 1
)
LOOP
BEGIN
l_hidden_col_name := dbms_stats.create_extended_stats('{{ schema }}', t.table_name, t.ext);
EXCEPTION
WHEN extension_already_exists THEN NULL; -- ignore exception and go to next loop iteration
END;
END LOOP;
END;
