oracle 是否有其他方法可以在 pl/sql for 循环中说“next”？

Are there alternative methods for saying 'next' in a pl/sql for loop?

提问by Billy Gray

So I've got a for loop that processes a list of IDs and has some fairly complex things to do. Without going into all the ugly details, basically this:

所以我有一个 for 循环来处理 ID 列表并有一些相当复杂的事情要做。不涉及所有丑陋的细节，基本上是这样的：

    DECLARE
      l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;

      ...snip...
    BEGIN

      -- get the list ids
      l_selected := APEX_UTIL.STRING_TO_TABLE(:P4_SELECT_LIST);
      -- process each in a nice loop
      FOR i IN 1..l_selected.count 
      LOOP
        -- do some data checking stuff...

        -- here we will look for duplicate entries, so we can noop if duplicate is found
        BEGIN
          SELECT county_id INTO v_dup_check FROM org_county_accountable
          WHERE organization_id = :P4_ID AND county_id = v_county_id;
          -- NEXT;! NOOP;! but there is no next!
        EXCEPTION WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('no dups found, proceeding');
        END;
        -- here we have code we only want to execute if there are no dupes already
        IF v_dup_check IS NULL THEN
          -- if not a duplicate record, proceed...

        ELSE
          -- reset duplicate check variable
          v_dup_check := NULL;
        END;
      END LOOP;
    END;

How I normally handle this is by selecting into a value, and then wrap the following code in an IF statement checking to make sure that duplicate check variable is NULL. But it's annoying. I just want to be able to say NEXT; or NOOP; or something. Especially since I already have to catch the NO_DATA_FOUND exception. I suppose I could write a letter to Oracle, but I'm curious how others handle this.

我通常的处理方法是选择一个值，然后将以下代码包装在 IF 语句检查中，以确保重复检查变量为 NULL。但这很烦人。我只想说 NEXT；或 NOOP；或者其他的东西。特别是因为我已经必须捕获 NO_DATA_FOUND 异常。我想我可以给 Oracle 写一封信，但我很好奇其他人是如何处理的。

I could also wrap this in a function, too, but I was looking for something a little cleaner/simpler.

我也可以将它包装在一个函数中，但我正在寻找更清洁/更简单的东西。

采纳答案by tuinstoel

To count the number of rows is also possible (see Pourquoi Litytestdata) but you can also do what you want to do in the when_no_data_found exceptionblock.

也可以计算行数（请参阅 Pourquoi Litytestdata），但您也可以在when_no_data_found exception块中执行您想要执行的操作。

declare 
  l_selected    apex_application_global.vc_arr2;
  l_county_id   org_county_accountable.count_id%type;
begin
  l_selected := apex_util.string_to_table(:p4_select_lst);
  for i in l_selected.first..l_selected.last loop
    begin
      select count_id
      into   l_county_id
      from   org_county_accountable
      where  organization_id = :p4_id
      and    county_id       = v_county_id;
    exception
      when no_data_found then 
        -- here we have code we only want to execute if there are no dupes already
        -- if not a duplicate record, proceed...
    end;
  end loop;
end;

回答by jimmyorr

Oracle 11g adds a C-style "continue" loop construct to PL/SQL, which syntactically sounds like what you're looking for.

Oracle 11g 向 PL/SQL 添加了一个 C 风格的“continue”循环构造，这在语法上听起来很像您正在寻找的。

For your purposes, why not just eliminate the duplicates prior to entering the loop? This could be done by querying l_selected using a table function, and then filtering out records you don't want instead of iterating over everyvalue. Something like...

出于您的目的，为什么不在进入循环之前消除重复项？这可以通过使用表函数查询 l_selected 来完成，然后过滤掉不需要的记录，而不是迭代每个值。就像是...

declare

l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;

cursor no_dups_cur (p_selected APEX_APPLICATION_GLOBAL.VC_ARR2) is 
  select * from (
  select selected.*, 
         count(*) over (partition by county_id) cnt -- analytic to find counts grouped by county_id
    from table(p_selected) selected -- use table function to treat VC_ARR2 like a table 
    ) where cnt = 1 -- remove records that have duplicate county_ids
    ;

begin

l_selected := APEX_UTIL.STRING_TO_TABLE(:P4_SELECT_LIST);

for i in no_dups_cur(l_selected) loop

  null; -- do whatever to non-duplicates 

end loop;

end;

Just substitute the logic for determining a "duplicate" with your own (didn't have enough info from your example to really answer that part)

只需用您自己的逻辑替换确定“重复”的逻辑（您的示例中没有足够的信息来真正回答该部分）

回答by Luke Woodward

Instead of catching NO_DATA_FOUND, how about SELECTing the number of matching entries into a variable, say l_count, and proceeding if this count works out to be zero? Something like the following:

不是NO_DATA_FOUNDcatch ，而是SELECTing 匹配条目的数量到一个变量中，比如l_count，如果这个计数为零，则继续？类似于以下内容：

    DECLARE
      l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
      l_count    INTEGER;

      ...snip...
    BEGIN

      -- get the list ids
      l_selected := APEX_UTIL.STRING_TO_TABLE(:P4_SELECT_LIST);
      -- process each in a nice loop
      FOR i IN 1..l_selected.count 
      LOOP
        -- do some data checking stuff...

        -- here we will count duplicate entries, so we can noop if duplicate is found
        SELECT COUNT(*) INTO l_count FROM org_county_accountable
         WHERE organization_id = :P4_ID AND county_id = v_county_id;
        IF l_count = 0 THEN
          -- here we have code we only want to execute if there are no dupes already
          -- if not a duplicate record, proceed...

        END IF;
      END LOOP;
    END;

回答by pete

<xmp>
<<next_loop>>
loop
...
...
if ....
then
   goto next_loop;

</xmp>

回答by Tony Andrews

Another way - turn the check into a local function:

另一种方式 - 将支票转换为本地函数：

DECLARE
  l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;

  ...snip...
  FUNCTION dup_exists 
     ( p_org_id org_county_accountable.organization_id%TYPE
     , p_county_id org_county_accountable.county_id%TYPE
     ) RETURN BOOLEAN 
  IS
    v_dup_check org_county_accountable.county_id%TYPE;
  BEGIN
    SELECT county_id INTO v_dup_check FROM org_county_accountable
    WHERE organization_id = p_org_id AND county_id = p_county_id;
    RETURN TRUE;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
  END;
BEGIN

  -- get the list ids
  l_selected := APEX_UTIL.STRING_TO_TABLE(:P4_SELECT_LIST);
  -- process each in a nice loop
  FOR i IN 1..l_selected.count
  LOOP
    -- do some data checking stuff...

    -- here we have code we only want to execute if there are no dupes already
    IF NOT dup_exists (:P4_ID, v_county_id) THEN
      -- if not a duplicate record, proceed...

    END;
  END LOOP;
END;

Of course, the local function could be re-written to use the count method if you prefer:

当然，如果您愿意，可以重写本地函数以使用 count 方法：

  FUNCTION dup_exists 
     ( p_org_id org_county_accountable.organization_id%TYPE
     , p_county_id org_county_accountable.county_id%TYPE
     ) RETURN BOOLEAN 
  IS
    l_count INTEGER;
  BEGIN
     SELECT COUNT(*) INTO l_count 
       FROM org_county_accountable
      WHERE organization_id = p_org_id AND county_id = p_county_id;
     RETURN (l_count > 0);
  END;

回答by Tony Andrews

Another method is to raise and handle a user-defined exception:

另一种方法是引发和处理用户定义的异常：

DECLARE
  l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
  duplicate_org_county EXCEPTION;

  ...snip...
BEGIN

  -- get the list ids
  l_selected := APEX_UTIL.STRING_TO_TABLE(:P4_SELECT_LIST);
  -- process each in a nice loop
  FOR i IN 1..l_selected.count 
  LOOP
    BEGIN
      -- do some data checking stuff...

      -- here we will look for duplicate entries, so we can noop if duplicate is found
      BEGIN
        SELECT county_id INTO v_dup_check FROM org_county_accountable
        WHERE organization_id = :P4_ID AND county_id = v_county_id;
        RAISE duplicate_org_county;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('no dups found, proceeding');
      END;
      -- here we have code we only want to execute if there are no dupes already

    EXCEPTION
      WHEN duplicate_org_county THEN NULL;
    END;
  END LOOP;
END;

I wouldn't normally do this, but if there were half a dozen reasons to jump to the next record, this might be preferable to multiple nested IFs.

我通常不会这样做，但如果有六个理由跳转到下一条记录，这可能比多个嵌套的 IF 更可取。

回答by Typo

I know this is an oldie but I couldn't help notice that none of the answers above take into account the cursor attributes:

我知道这是一个老问题，但我不禁注意到上面的答案都没有考虑到光标属性：

There are four attributes associated with cursors: ISOPEN, FOUND, NOTFOUND, and ROWCOUNT. These attributes can be accessed with the % delimiter to obtain information about the state of the cursor.

有四个与游标相关的属性：ISOPEN、FOUND、NOTFOUND 和 ROWCOUNT。可以使用 % 分隔符访问这些属性以获取有关游标状态的信息。

The syntax for a cursor attribute is:

游标属性的语法是：

cursor_name%attribute

where cursor_name is the name of the explicit cursor.

其中 cursor_name 是显式游标的名称。

So in this case you could use ROWCOUNT (which indicates the number of rows fetched so far) for your purposes, like this:

因此，在这种情况下，您可以出于自己的目的使用 ROWCOUNT（表示到目前为止获取的行数），如下所示：

declare 
   aux number(10) := 0;
   CURSOR cursor_name is select * from table where something;
begin
     select count(*) into aux from table where something;
     FOR row IN cursor_name LOOP
        IF(aux > cursor_name%ROWCOUNT) THEN 'do something is not over';
        ELSE 'do something else';
        END IF;
     END LOOP;
end;

回答by Adam Hawkes

This is a case where a GOTO statement mightbe useful. See the Oracle Documentationin the control structures to see how to do this. Also, you may want to search around here to find out how to query for the existence of a record. Running a query and waiting for an exception isn't optimal.

这是 GOTO 语句可能有用的情况。请参阅控制结构中的Oracle 文档以了解如何执行此操作。此外，您可能想在此处搜索以了解如何查询记录是否存在。运行查询并等待异常不是最佳选择。