oracle存储过程拆分字符串函数,oracle – 拆分字符串并迭代存储过程中的每个值...

你可以使用

DBMS_UTILITY.COMMA_TO_TABLE.

DBMS_UTILITY包提供各种实用程序子程序.一个这样有用的实用程序是COMMA_TO_TABLE过程,它将逗号分隔的名称列表转换为名称的PL / SQL表.

例如,

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>

更新正如@ruudvan指出的那样,使用COMMA_TO_TABLE有一些限制,如果你有像IS,AS等分隔字符串的关键字它将无法工作.

例如,您可以使用REGULAR EXPRESSION,如下所示：

测试用例

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.

让我们检查：

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>