oracle 分割字符串，转多行

由于varchar2的问题，长度限制为4000

首先创建Type  strsplit_type

CREATE OR REPLACE TYPE strsplit_type IS TABLE OF VARCHAR2 (4000)

然后创建函数strsplit

CREATE OR REPLACE FUNCTION STRSPLIT(P_VALUE VARCHAR2,
                                    P_SPLIT VARCHAR2 := ',')
--usage: select * from table(strsplit('1,2,3,4,5'))
 RETURN STRSPLIT_TYPE
  PIPELINED IS
  V_IDX       INTEGER;
  V_STR       VARCHAR2(500);
  V_STRS_LAST VARCHAR2(4000) := P_VALUE;

BEGIN
  LOOP
    V_IDX := INSTR(V_STRS_LAST, P_SPLIT);
    EXIT WHEN V_IDX IS NULL OR V_IDX <= 0;
    V_STR       := SUBSTR(V_STRS_LAST, 1, V_IDX - 1);
    V_STRS_LAST := SUBSTR(V_STRS_LAST, V_IDX + 1);
    PIPE ROW(V_STR);
  END LOOP;
  PIPE ROW(V_STRS_LAST);
  RETURN;

END STRSPLIT;

测试

实际应用

SELECT A.*,
       (SELECT WM_CONCAT(FILEPATH)
          FROM TABLENAME_B
         WHERE AID IN (SELECT * FROM TABLE(STRSPLIT(A.ABNORMAL_AID, ',')))) ATTA
  FROM TABLENAME_A A
 WHERE A.ABNORMAL_AID IS NOT NULL