[ORACLE] 문자열을 구분자 기준으로 ROW로 변환하는 테이블 함수

CREATE OR REPLACE TYPE EUS3_TYP_TO_ROW AS OBJECT
(
IDX NUMBER
, VAL VARCHAR2(4000)
);




CREATE OR REPLACE TYPE EUS3_TYP_TO_ROW_TBL AS TABLE OF EUS3_TYP_TO_ROW
;




CREATE OR REPLACE FUNCTION EUS3_FUNC_TO_ROW(IN_STR IN CLOB, IN_DELI IN VARCHAR2 DEFAULT ',')
RETURN EUS3_TYP_TO_ROW_TBL PIPELINED AS

V_DELI VARCHAR2(10) := IN_DELI;
V_DELI_LEN INT := LENGTH(V_DELI);
V_STR CLOB := IN_STR||V_DELI;
V_NUM NUMBER;
V_IDX NUMBER := 0;
V_TYPE EUS3_TYP_TO_ROW;

BEGIN

LOOP

EXIT WHEN IN_STR IS NULL;

V_NUM := INSTR(V_STR, V_DELI);

EXIT WHEN (NVL(V_NUM,0) = 0);

V_IDX := V_IDX + 1;
V_TYPE := EUS3_TYP_TO_ROW( V_IDX, SUBSTR(V_STR, 1, V_NUM-1) );

PIPE ROW (V_TYPE);

V_STR := SUBSTR(V_STR, V_NUM+V_DELI_LEN);

END LOOP;

RETURN;

END EUS3_FUNC_TO_ROW;



SELECT *
FROM TABLE(EUS3_FUNC_TO_ROW(
TO_CLOB('AA001,AA002')
))
;