Oracle实现split函数

来源：互联网发布：网络舆情特点编辑：程序博客网时间：2024/05/20 13:18

--创建pack spec
create or replace package yhef_test is
  --构建索引表
  TYPE t_Array IS TABLE OF VARCHAR2(4000) index by binary_integer;
  -- Purpose 实现类似java的split函数
  -- parameter pi_str需要操作的字符串
  -- parameter pi_separator 分隔符
  function fn_split(pi_str In Varchar2, pi_separator In Varchar2)
    RETURN t_Array;

end yhef_test;

--创建pack body
create or replace package body yhef_test is
  -- Purpose 实现类似java的split函数
  -- parameter pi_str需要操作的字符串
  -- parameter pi_separator 分隔符
  FUNCTION fn_split(pi_str In Varchar2, pi_separator In Varchar2)
    RETURN t_Array IS

    idx          Number; --分隔符在字符串中的位置
    currentindex Number := 1; --数组的索引
    strs         t_Array; --返回值
    v_str        varchar2(4000) := pi_str; --临时变量
  BEGIN

    IF pi_str IS NOT NULL AND pi_separator IS NOT NULL THEN
      --存在分隔符
      IF INSTR(v_str, pi_separator) <> 0 THEN
        LOOP
          EXIT WHEN v_str IS NULL;
          EXIT WHEN INSTR(v_str, pi_separator) = 0;
          idx := INSTR(v_str, pi_separator);
          --截取字符串赋值给对应的数组
          strs(currentindex) := SUBSTR(v_str, 0, idx - 1);
          currentindex := currentindex + 1;
          --更新字符串
          v_str := SUBSTR(v_str, idx + 1);
        END LOOP;
      END IF;
    END IF;
    --如果分隔符为空，则直接返回字符串
    IF v_str IS NOT NULL THEN
      strs(currentindex) := v_str;
    END IF;
    Return strs;
  END fn_split;

end yhef_test;

--test
declare
  ret yhef_test.t_array;
begin
  -- Call the function
  ret := yhef_test.fn_split('a,b,c,d,e', ',');
  for i in 1 .. ret.count loop
    dbms_output.put_line(i || '=' || ret(i));
  end loop;
end;

整体语句：

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

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN ty_str_split
IS
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (4000);
    str_split ty_str_split := ty_str_split ();
BEGIN
    len := LENGTH (p_str);
    len1 := LENGTH (p_delimiter);

    WHILE j < len
    LOOP
        j := INSTR (p_str, p_delimiter, i);

        IF j = 0
        THEN
            j := len;
             str := SUBSTR (p_str, i);
             str_split.EXTEND;
             str_split (str_split.COUNT) := str;

            IF i >= len
            THEN
                EXIT;
            END IF;
        ELSE
            str := SUBSTR (p_str, i, j - i);
            i := j + len1;
            str_split.EXTEND;
            str_split (str_split.COUNT) := str;
        END IF;
    END LOOP;

RETURN str_split;
END fn_split;
/

测试：

DECLARE
    CURSOR c
    IS
        SELECT *
          FROM TABLE (CAST (fn_split ('1;;12;;123;;1234;;12345', ';;') AS ty_str_split
                           )
                     );

r c%ROWTYPE;
BEGIN
OPEN c;

LOOP
FETCH c INTO r;

        EXIT WHEN c%NOTFOUND;
        DBMS_OUTPUT.put_line (r.column_value);
    END LOOP;

CLOSE c;
END;
/
　

结果：
1
12
123
1234
12345

0 0