oracle 拆分字符串并迭代存储过程中的每个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29557207/
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
Split string and iterate for each value in a stored procedure
提问by Mandar Patil
I have a requirement in Oracle where I have to pass a comma separated string of country codes to an Oracle stored procedure.
我在 Oracle 中有一个要求，我必须将逗号分隔的国家/地区代码字符串传递给 Oracle 存储过程。
Inside a stored procedure I want to split the string on comma and iterate for each country code.
在存储过程中，我想用逗号分割字符串并迭代每个国家/地区代码。
I want a SP which is similar as follows:
我想要一个类似于以下的 SP：
PROCEDURE Get_Query
(
v_company IN VARCHAR2,
) IS
sqlstr VARCHAR2(32767);
BEGIN
/*
split the v_company on comma (e.g. IN,US,...etc)
iterate for each country code
*/
END;
回答by Lalit Kumar B
You can use DBMS_UTILITY.COMMA_TO_TABLE.
您可以使用DBMS_UTILITY.COMMA_TO_TABLE。
The DBMS_UTILITYpackage provides various utility subprograms. One such useful utility is COMMA_TO_TABLEprocedure, which converts a comma-delimited list of names into a PL/SQL table of names.
所述DBMS_UTILITY包提供各种实用程序的子程序。COMMA_TO_TABLE过程就是这样一种有用的实用程序，它将逗号分隔的名称列表转换为 PL/SQL 名称表。
For example,
例如，
SQL> set serveroutput on;
SQL> DECLARE
2 l_tablen BINARY_INTEGER;
3 l_tab DBMS_UTILITY.uncl_array;
4 CURSOR cur
5 IS
6 SELECT 'word1, word2, word3, word4, word5, word6' val FROM dual;
7 rec cur%rowtype;
8 BEGIN
9 OPEN cur;
10 LOOP
11 FETCH cur INTO rec;
12 EXIT
13 WHEN cur%notfound;
14 DBMS_UTILITY.comma_to_table (
15 list => rec.val, tablen => l_tablen, tab => l_tab);
16 FOR i IN 1 .. l_tablen
17 LOOP
18 DBMS_OUTPUT.put_line(i || ' : ' || trim(l_tab(i)));
19 END LOOP;
20 END LOOP;
21 CLOSE cur;
22 END;
23 /
1 : word1
2 : word2
3 : word3
4 : word4
5 : word5
6 : word6
PL/SQL procedure successfully completed.
SQL>
UPDATEAs @ruudvan points out, there are restrictions for using COMMA_TO_TABLElike it won't work if you have keywordsas delimited string like IS,ASetc.
更新正如@ruudvan 指出的那样，COMMA_TO_TABLE如果您将关键字作为分隔字符串IS,AS等，则使用类似的限制将不起作用。
To overcome the restrictions of COMMA_TO_TABLE, there are many other ways of doing it, see How to split comma delimited string into rows in Oracle
为了克服 的限制COMMA_TO_TABLE，还有很多其他方法可以做到，请参阅如何在 Oracle 中将逗号分隔的字符串拆分为行
For example, you could use REGULAR EXPRESSION as shown below:
例如，您可以使用 REGULAR EXPRESSION，如下所示：
Test case
测试用例
SQL> CREATE OR REPLACE
2 PROCEDURE Get_Query(
3 v_company IN VARCHAR2 )
4 IS
5 BEGIN
6
7 FOR i IN
8 (SELECT level,
9 trim(regexp_substr(v_company, '[^,]+', 1, LEVEL)) str
10 FROM dual
11 CONNECT BY regexp_substr(v_company , '[^,]+', 1, LEVEL) IS NOT NULL
12 )
13 LOOP
14 -- do something
15 dbms_output.put_line('Company code no.'||i.level||' = '||i.str);
16 END LOOP;
17 END;
18 /
Procedure created.
SQL> sho err
No errors.
Let's check:
让我们检查：
SQL> set serveroutput on
SQL> EXEC get_query('COMP1,COMP2,COMP3,COMP4');
Company code no.1 = COMP1
Company code no.2 = COMP2
Company code no.3 = COMP3
Company code no.4 = COMP4
PL/SQL procedure successfully completed.
SQL>
回答by APC
This use of regular expressions converts the input string into a stream of tokens which can be processed like the result set of any other SELECT statement.
正则表达式的这种使用将输入字符串转换为标记流，可以像处理任何其他 SELECT 语句的结果集一样进行处理。
PROCEDURE Get_Query
(
v_company IN VARCHAR2,
) IS
sqlstr VARCHAR2(32767);
BEGIN
for rec in (select distinct regexp_substr(v_company, '[^,]+', 1, level) as ctry
from dual
connect by level <= regexp_count (v_company, '[,]') +1
loop
do_something ( rec.ctry );
end loop;
END;
