ORACLE에서 SPLIT 함수 구현

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

CREATE OR REPLACE FUNCTION 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 SPLIT;
/


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