如何循环遍历 Oracle PLSQL 中的分隔列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19168819/
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
How to loop through a delimited list in Oracle PLSQL
提问by AAA
I am working on an Oracle procedure that calls another procedure within it. One of my parameters (parm1)can contain one or more values in a comma separated list. How can I loop through these values to pass them one at a time to another procedure?
我正在研究在其中调用另一个过程的 Oracle 过程。我的参数之一(parm1)可以在逗号分隔列表中包含一个或多个值。如何遍历这些值以将它们一次传递给另一个过程？
Here is an example of what I would like it to do:
这是我希望它做的一个例子：
When Parm1 = 123,312
callProcedure2(123)
callProcedure2(321)
-or-
-或者-
When Parm1 123
callProcedure2(123)
I think this can be accomplished using a loop but I can't figure out how to get it to use each value as a separated call within the loop.
我认为这可以使用循环来完成，但我无法弄清楚如何让它将每个值用作循环内的单独调用。
Any help would be appreciated!
任何帮助，将不胜感激！
Thanks!
谢谢！
回答by Vijay
CURSOR V_CUR IS
select regexp_substr(Parm1 ,'[^,]+', 1, level) As str from dual
connect by regexp_substr(Parm1, '[^,]+', 1, level) is not null;
This curor will give you result like this
这个 curor 会给你这样的结果
123
321
Now iterate the cursor and call the procedure in loop.
现在迭代游标并在循环中调用该过程。
For i IN V_CUR
LOOP
callProdcedure2(i.str);
END LOOP;
回答by ThinkJet
Just loop through substrings:
只需循环遍历子字符串：
declare
parm1 varchar2(1000) := '123,234,345,456,567,789,890';
vStartIdx binary_integer;
vEndIdx binary_integer;
vCurValue varchar2(1000);
begin
vStartIdx := 0;
vEndIdx := instr(parm1, ',');
while(vEndIdx > 0) loop
vCurValue := substr(parm1, vStartIdx+1, vEndIdx - vStartIdx - 1);
-- call proc here
dbms_output.put_line('->'||vCurValue||'<-');
vStartIdx := vEndIdx;
vEndIdx := instr(parm1, ',', vStartIdx + 1);
end loop;
-- Call proc here for last part (or in case of single element)
vCurValue := substr(parm1, vStartIdx+1);
dbms_output.put_line('->'||vCurValue||'<-');
end;
回答by Dudi Boy
There is a utility procedure COMMA_TO_TABLEand array type DBMS_UTILITY.UNCL_ARRAYdedicated for this task. Since Oracle 10g.
有一个专用于此任务的实用程序COMMA_TO_TABLE和数组类型DBMS_UTILITY.UNCL_ARRAY。从Oracle 10g 开始。
It is well document here.
这是很好的文件here。
Here is a sample solution:
这是一个示例解决方案：
SET SERVEROUTPUT ON
DECLARE
csvListElm VARCHAR2(4000) := 'elm1, elm2,elm3 ,elm4 , elm5';
csvListTable DBMS_UTILITY.UNCL_ARRAY;
csvListLen BINARY_INTEGER;
currTableName VARCHAR2(222);
BEGIN
DBMS_UTILITY.COMMA_TO_TABLE(csvListElm, csvListLen, csvListTable);
FOR csvElm IN 1..(csvListTable.COUNT - 1) LOOP
dbms_output.put_line('-- CSV element : <'||csvListTable(csvElm)||'>');
dbms_output.put_line('-- Trimmed CSV element: <'||trim(csvListTable(csvElm))||'>');
END LOOP;
END;
/
Sample output:
示例输出：
-- CSV element : <elm1>;
-- Trimmed CSV element: <elm1>;
-- CSV element : < elm2>;
-- Trimmed CSV element: <elm2>;
-- CSV element : <elm3 >;
-- Trimmed CSV element: <elm3>;
-- CSV element : <elm4 >;
-- Trimmed CSV element: <elm4>;
-- CSV element : < elm5>;
-- Trimmed CSV element: <elm5>;
回答by J. Chomel
It is possible to use a function that you can use in a forloop (without regexpfor ThinkJet):
可以使用一个可以在for循环中使用的函数（不regexp包括 ThinkJet）：
Create a type and function
创建类型和函数
CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
RETURN t_my_list
AS
l_string VARCHAR2(32767) := p_list || p_sep;
l_sep_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
l_tab t_my_list := t_my_list();
BEGIN
LOOP
l_sep_index := INSTR(l_string, p_sep, l_index);
EXIT
WHEN l_sep_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
l_index := l_sep_index + 1;
END LOOP;
RETURN l_tab;
END cto_table;
/
Then how to call it in the forloop:
那么如何在for循环中调用它：
DECLARE
parm1 varchar2(4000) := '123,234,345,456,567,789,890';
BEGIN
FOR x IN (select * from (table(cto_table(',', parm1)) ) )
LOOP
dbms_output.put_line('callProdcedure2 called with ' || x.COLUMN_VALUE);
callProdcedure2(x.COLUMN_VALUE);
END LOOP;
END;
/
Notice the default name COLUMN_VALUEgiven by Oracle, which is necessary for the use I want to make of the result.
请注意COLUMN_VALUEOracle 给出的默认名称，这是我想要使用的结果所必需的。
Result as expected:
结果如预期：
callProdcedure2 called with 123
callProdcedure2 called with 234
...
