使用Oracle PL/SQL的for循环来迭代逗号分隔字符串

Question

我正在编写一段代码，需要迭代字符串内容，每个值都用,分隔。

例如，我有我的elements。

v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';

我怎样才能将它转换成数组/游标以便在循环中进行迭代？

for x in (elements)
loop
   -- do my stuff
end loop;

我希望能够找到一种非常简单的方法，如果可能的话，避免声明关联数组。

是否有可能创建一个函数，该函数返回可用作for循环输入的某些内容（相对于使用类似于https://dev59.com/_XfZa4cB1Zd3GeqPTZMd#19184203中的while循环）?

提前致谢。

- J. Chomel

---

可能是如何在Oracle中遍历一个分隔列表的重复问题。 - Sathyajith Bhat

但是为什么要用PL/SQL，而不是使用普通的SQL呢？请参见将逗号分隔的字符串拆分为行。 - Lalit Kumar B

谢谢，使用instr在while中是我的第二选择。是否可以使用一个函数，在for循环中返回可用的内容？ - J. Chomel

谢谢@Lalit，我认为这会对我很有帮助。 - J. Chomel

Answer 1

在纯 SQL 中可以轻松地完成它。有多种实现方式，请参见Oracle中将逗号分隔的字符串拆分成行。

然而，如果您真的想在 PL / SQL 中完成它，那么可以这样做：

SQL> set serveroutput on
SQL> DECLARE
  2    str VARCHAR2(100) := 'PEBO,PTGC,PTTL,PTOP,PTA';
  3  BEGIN
  4    FOR i IN
  5    (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) l
  6    FROM dual
  7      CONNECT BY LEVEL <= regexp_count(str, ',')+1
  8    )
  9    LOOP
 10      dbms_output.put_line(i.l);
 11    END LOOP;
 12  END;
 13  /
PEBO
PTGC
PTTL
PTOP
PTA

PL/SQL procedure successfully completed.

SQL>

- Lalit Kumar B

---

这对我有用。我将提出另一个更准确的关于我正在寻找的“函数”的问题。 - J. Chomel

@J.Chomel 你可以创建一个用户定义的函数。或者使用Oracle内置的包DBMS_UTILITY。如果你发布了不同的问题，请告诉我，我可以帮你回答。 - Lalit Kumar B

Answer 2

感谢Lalit的详细指导，我现在能够创建一个函数并从我的for循环中调用它：

创建类型和函数

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
       FUNCTION comma_to_table(p_list IN VARCHAR2)
         RETURN t_my_list
       AS
         l_string VARCHAR2(32767) := p_list || ',';
         l_comma_index PLS_INTEGER;
         l_index PLS_INTEGER := 1;
         l_tab t_my_list     := t_my_list();
       BEGIN
         LOOP
           l_comma_index := INSTR(l_string, ',', l_index);
           EXIT
         WHEN l_comma_index = 0;
           l_tab.EXTEND;
           l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_comma_index - l_index));
           l_index            := l_comma_index + 1;
         END LOOP;
         RETURN l_tab;
       END comma_to_table;
/

那么如何在我的for循环中调用它呢？

declare
  v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';
begin
    FOR x IN (select * from (table(comma_to_table(v_list_pak_like)) ) )
    loop
        dbms_output.put_line(x.COLUMN_VALUE);
    end loop;
end;
/

请注意，Oracle默认给出的名称COLUMN_VALUE对于我想要使用的结果是必要的。

如预期的结果：

PEBO
PTGC
PTTL
PTOP
PTA

- J. Chomel

---

干得好，@J. Chomel。这正是我想要的，将结果放入一个可以循环的变量中。谢谢。 - W. Elbashier

Answer 3

declare
    type array_type is table of VARCHAR2(255) NOT NULL;
    my_array array_type := array_type('aaa','bbb','ccc');
begin
    for i in my_array.first..my_array.last loop
        dbms_output.put_line( my_array(i) );
    end loop;
end;

第一行定义了一个你想要的任何类型的表格。

然后声明一个该类型的变量，并使用构造函数给它赋值。

然后循环遍历从第一个索引到最后一个索引的结果。

编辑 - 一个处理真实字符串的解决方案。

declare
    str      VARCHAR2(1024) := 'aaa,bbb,ccc';
    type     ARRAY_TYPE is table of VARCHAR2(255) NOT NULL;
    my_array ARRAY_TYPE;
begin
    SELECT REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) BULK COLLECT INTO my_array
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) IS NOT NULL;

    for i in my_array.first..my_array.last loop
        dbms_output.put_line( my_array(I));
    end loop;
end;

- Izik

---

这个答案看起来不错，但是如果加上一些解释，它会成为一个更好的答案;-) - schlebe

这很容易理解 :) 但是好吧，我添加了一些解释。 - Izik

迄今为止最佳答案 - Johan

似乎无法迭代逗号分隔的字符串，例如'PEBO,PTGC,PTTL,PTOP,PTA'。 - dougd_in_nc

您是对的，我没有看到那个。添加了另一个步骤来将其处理为字符串。 - Izik