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

我在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过程,它将逗号分隔的名称列表转换为名称的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>

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!