oracle 字符串分割 转多行,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