ORACLE中发生异常DUP_瓦尔_ON_INDEX时如何在多个for循环中继续循环

piztneat 于 2023-04-29 发布在 Oracle

关注(0)|答案(1)|浏览(153)

我一直在搜索所有的论坛，我没有找到任何好的解释如何继续循环，在多个“for循环”中发生异常。
我有下面的'for循环'，在很多情况下，唯一的约束错误发生。我想记录异常并继续循环。我知道如何在一个“for循环”中完成，但我不知道如何在多个循环中完成，因为代码在“LOOP 2”中停止，并且“CONTINUE”似乎不起作用。我错过了什么？

--- MAIN LOOP --

DECLARE

BEGIN
     
   FOR v_client_id IN cur_client_id LOOP -- 1 LOOP
     
     FOR dept IN v_dept_type.first..v_dept_type.last LOOP -- 2 LOOP
        
         v_SQL_INSERT:='INSERT INTO clients VALUES( some values )';
         EXECUTE IMMEDIATE v_SQL_INSERT;
            
        FOR v_ip_client_dept IN ASCII('A') .. ASCII('Z') LOOP -- 3 LOOP

            v_SQL_INSERT:='INSERT INTO clients VALUES( some values ';
            EXECUTE IMMEDIATE v_SQL_INSERT;

            v_SQL_INSERT:='INSERT INTO clients VALUES( some values ';
            EXECUTE IMMEDIATE v_SQL_INSERT;
            
        END LOOP; -- 3 LOOP
            
     END LOOP; -- 2 LOOP
   
   END LOOP; -- 1 LOOP
   
END;`

--- BELOW IS WHAT I TRIED SO FAR

1.

DECLARE

BEGIN
     
   FOR v_client_id IN cur_client_id LOOP -- 1 LOOP
     
     FOR dept IN v_dept_type.first..v_dept_type.last LOOP -- 2 LOOP
        BEGIN
         v_SQL_INSERT:='INSERT INTO clients VALUES( some values )';
         EXECUTE IMMEDIATE v_SQL_INSERT;
         
          EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
                CONTINUE;
         END;
            
        FOR v_ip_client_dept IN ASCII('A') .. ASCII('Z') LOOP -- 3 LOOP
            
            BEGIN
            v_SQL_INSERT:='INSERT INTO clients VALUES( some values ';
            EXECUTE IMMEDIATE v_SQL_INSERT;
            
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
                CONTINUE;
            END;
            
            BEGIN
            v_SQL_INSERT:='INSERT INTO clients VALUES( some values ';
            EXECUTE IMMEDIATE v_SQL_INSERT;
            
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
                CONTINUE;
            END;
            
        END LOOP; -- 3 LOOP
            
     END LOOP; -- 2 LOOP
   
   END LOOP; -- 1 LOOP
   
END;

2. 

DECLARE

BEGIN
     
   FOR v_client_id IN cur_client_id LOOP -- 1 LOOP
     
     FOR dept IN v_dept_type.first..v_dept_type.last LOOP -- 2 LOOP
        BEGIN
         v_SQL_INSERT:='INSERT INTO clients VALUES( some values )';
         EXECUTE IMMEDIATE v_SQL_INSERT;
            
        FOR v_ip_client_dept IN ASCII('A') .. ASCII('Z') LOOP -- 3 LOOP
            
            BEGIN
            v_SQL_INSERT:='INSERT INTO clients VALUES( some values ';
            EXECUTE IMMEDIATE v_SQL_INSERT;
            
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
                CONTINUE;
            END;
            
            BEGIN
            v_SQL_INSERT:='INSERT INTO clients VALUES( some values ';
            EXECUTE IMMEDIATE v_SQL_INSERT;
            
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
                CONTINUE;
            END;
            
        END LOOP; -- 3 LOOP
            
     END LOOP; -- 2 LOOP
     EXCEPTION
           WHEN DUP_VAL_ON_INDEX THEN
               DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
               CONTINUE;
        END;
   
   END LOOP; -- 1 LOOP
   
END;

oracle

来源：https://stackoverflow.com/questions/76098831/how-to-continue-looping-in-multiple-for-loops-when-exception-dup-val-on-index-oc

1条答案

按热度按时间

a9wyjsp71#

您不需要CONTINUE关键字。正如官方的documentation声明：* CONTINUE语句退出循环的当前迭代 *。
异常被捕获在EXCEPTION块中。如果没有重新引发异常，则将执行下一个块。
下面是一个类似的例子：

declare
  l_nr NUMBER;
begin
  for i in 1 .. 3 loop
    if i = 2 then
      begin
        l_nr := 'x';
      exception when value_error then
        dbms_output.put_line('iteration '||i ||': value error'); 
        CONTINUE;
      end;
    else
      dbms_output.put_line('iteration '||i ||': all ok');
    end if;
    dbms_output.put_line('iteration '||i ||': last log');
  end loop;
end;
/

iteration 1: all ok
iteration 1: last log
iteration 2: value error
iteration 3: all ok
iteration 3: last log

注意迭代2没有“最后一个日志”行。这是因为CONTINUE关键字退出当前迭代并移到迭代3。如果注解掉CONTINUE关键字，它将返回

iteration 1: all ok
iteration 1: last log
iteration 2: value error
iteration 2: last log
iteration 3: all ok
iteration 3: last log

所以对于你的代码，尝试：

DECLARE
BEGIN
     
   FOR v_client_id IN cur_client_id LOOP -- 1 LOOP
     
     FOR dept IN v_dept_type.first..v_dept_type.last LOOP -- 2 LOOP
        BEGIN
         v_SQL_INSERT:='INSERT INTO clients VALUES( some values )';
         EXECUTE IMMEDIATE v_SQL_INSERT;
         
          EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
                --CONTINUE;
         END;
            
        FOR v_ip_client_dept IN ASCII('A') .. ASCII('Z') LOOP -- 3 LOOP
            
            BEGIN
            v_SQL_INSERT:='INSERT INTO clients VALUES( some values ';
            EXECUTE IMMEDIATE v_SQL_INSERT;
            
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
                --CONTINUE;
            END;
            
            BEGIN
            v_SQL_INSERT:='INSERT INTO clients VALUES( some values ';
            EXECUTE IMMEDIATE v_SQL_INSERT;
            
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!');
                --CONTINUE;
            END;
            
        END LOOP; -- 3 LOOP
            
     END LOOP; -- 2 LOOP
   
   END LOOP; -- 1 LOOP
   
END;

赞(0）回复(0）举报 2023-04-29

我来回答

ORACLE中发生异常DUP_瓦尔_ON_INDEX时如何在多个for循环中继续循环

1条答案

相关问题

热门标签

最新问答