我在Oracle中有一个要求,我必须将逗号分隔的国家/地区代码字符串传递给Oracle存储过程.
在存储过程中,我想在逗号上拆分字符串并迭代每个国家/地区代码.
我想要一个类似如下的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;
解决方法
你可以使用
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等分隔字符串的关键字它将无法工作.
要克服COMMA_TO_TABLE的限制,还有许多其他方法,请参阅How to split comma delimited string into rows in Oracle
例如,您可以使用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,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>
