ORACLE 按逗号分割字符串

--定义TYPE
CREATE OR REPLACE TYPE "RESOLVE_STR" IS TABLE OF VARCHAR2 (4000);
--定义函数
CREATE OR REPLACE FUNCTION F_SPLIT
    (
       P_STR       IN   VARCHAR2,
       P_DELIMITER IN   varchar2  := ','
     ) RETURN RESOLVE_STR PIPELINED
AS
    j     INT := 0;
    i     INT := 1;
    len   INT := 0;
    len1  INT := 0;
    --tmp   VARCHAR2 (4000);
    v_str VARCHAR2 (4000);
BEGIN
    v_str := TRIM(BOTH P_DELIMITER FROM P_STR);--去掉前后的分隔符
    len := LENGTH (v_str);
    len1 := LENGTH(P_DELIMITER);

    WHILE j < len
      LOOP
          j := INSTR (v_str, P_DELIMITER, i);--寻分隔符位置

          IF j = 0 THEN--没有了
              --tmp := SUBSTR (v_str, i);
              PIPE ROW (SUBSTR (v_str, i));
              EXIT;--结束
          ELSE
              --tmp := SUBSTR (v_str, i, j - i);
              PIPE ROW (SUBSTR (v_str, i, j - i));
              i := j + len1;

          END IF;
      END LOOP;

    RETURN;
END;
--测试代码
SELECT F_SPLIT(',123,456,789,123,') FROM DUAL;

测试结果: