SPLIT 함수

CREATE OR REPLACE TYPE TB_SPLIT AS TABLE OF VARCHAR2(32767);

CREATE OR REPLACE FUNCTION UFN_SPLIT
(
    P_LIST VARCHAR2,
    P_DEL VARCHAR2
)

    RETURN TB_SPLIT PIPELINED
IS
    L_IDX PLS_INTEGER;
    L_LIST VARCHAR2(32767) := P_LIST;
    L_VALUE VARCHAR2(32767);
BEGIN
    LOOP
        L_IDX := INSTR(L_LIST,P_DEL);
        IF L_IDX > 0 THEN
            PIPE ROW(SUBSTR(L_LIST,1,L_IDX-1));
            L_LIST := SUBSTR(L_LIST,L_IDX+LENGTH(P_DEL));
        ELSE
            PIPE ROW(L_LIST);
            EXIT;
        END IF;
    END LOOP;
   
    RETURN;
END UFN_SPLIT;
/


SELECT ROWNUM, COLUMN_VALUE FROM TABLE(UFN_SPLIT('9,8,1,2,3,4,5,6,7',','));