使用 Oracle PL/SQL For 循环遍历逗号分隔的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36325831/
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
Use Oracle PL/SQL For Loop to iterate through comma delimited string
提问by J. Chomel
I am writing a piece of code that would need to iterate on the content of a string, each values being separated with a ,.
我正在编写一段需要迭代字符串内容的代码，每个值都用,.
e.g. I have my elements
例如我有我的 elements
v_list_pak_like varchar2(4000) := 'PEBO,PTGC,PTTL,PTOP,PTA';
How can I get it into an Array / Cursor to iterate on it in my loop?
如何将其放入数组/光标以在循环中对其进行迭代？
for x in (elements)
loop
-- do my stuff
end loop;
I am looking for the very simple way, if possible avoiding to declare associative arrays.
我正在寻找一种非常简单的方法，如果可能的话，避免声明关联数组。
Would it be possible to create a functionthat would return something usable as an input for a forloop (opposite to the while that could be used like in https://stackoverflow.com/a/19184203/6019417)?
是否有可能创建一个函数来返回可用作for循环输入的东西（与可以在https://stackoverflow.com/a/19184203/6019417 中使用的 while 相反）？
Many thanks in advance.
提前谢谢了。
回答by Lalit Kumar B
You could do it easily in pure SQL. there are multiple ways of doing it, see Split comma delimited string into rows in Oracle
您可以在纯SQL 中轻松完成。有多种方法可以做到这一点，请参阅将逗号分隔的字符串拆分为 Oracle 中的行
However, if you really want to do it in PL/SQL, then you could do it as:
但是，如果您真的想在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>
回答by J. Chomel
Thanks to Lalit great instructions, I am able to create a function that I can call from my forloop:
感谢 Lalit 的指导，我能够创建一个可以从for循环中调用的函数：
Create a type and function
创建类型和函数
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;
/
Then how to call it in my forloop:
然后如何在我的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;
/
Notice the default name COLUMN_VALUEgiven by Oracle that is necessary for the use I want to make of the result.
请注意COLUMN_VALUEOracle 给出的默认名称，这是我想要使用的结果所必需的。
Result as expected:
结果如预期：
PEBO
PTGC
PTTL
PTOP
PTA
