自定义oracle拆分字符串函数splitstr

在oracle中将一个字符串按照某个分隔符进行拆分,然后以数据集合返回,这种需求还是比较常见的,然后oracle本身却没有提供相应的内置函数。

下面是在网上找到的一个自定义的splitstr函数,用来实现该功能:

Step 1:定义一个类型

CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);

Step 2:编写splitstr函数

CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN str_split
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END splitstr;

Step 3:创建同义词

CREATE OR REPLACE PUBLIC SYNONYM splitstr FOR YEDWARD.splitstr;

假设Step 2中的splitstr函数是在YEDWARD用户下编译,其owner是YEDWARD,为了方便被公共访问,可以建一个public同义词。

下面就可以测试了:

splitstr测试

图1:splitstr测试

上述代码已压缩成包:splitstr.zip

DECLARE
  TYPE t_char_array IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
  l_access_org_ids str_split;
BEGIN
  SELECT to_char(hou.organization_id)
    BULK COLLECT
    INTO l_access_org_ids
    FROM hr_operating_units hou;

  FOR rec IN (SELECT *
                FROM cux_settle_headers sha
               WHERE EXISTS (SELECT 1
                        FROM TABLE(l_access_org_ids) t
                       WHERE t.column_value = sha.org_id))
  LOOP
    dbms_output.put_line('header_id:' || sha.header_id);
  END LOOP;
END;

可以看出splistr的返回值其实是一个pl/sql table(oracle内存表),如果需要对pl/sql table类型的变量做查询,可以使用select * from table(l_plsql_type_var),如上代码所示。

本文标题:自定义oracle拆分字符串函数splitstr

本文链接:http://yedward.net/?id=366

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章