-- ORACLE SPLIT QUERY -- 01. 문자 SELECT CODE_ARR FROM ( SELECT SUBSTRB( :AS_LIST , INSTRB(','||:AS_LIST, ',', 1, ROWNUM) , INSTRB(:AS_LIST||',', ',', LEVEL) - INSTRB(','||:AS_LIST, ',', 1, ROWNUM) ) AS CODE_ARR FROM DUAL WHERE 1=1 AND REPLACE(:AS_LIST,',','') IS NOT NULL AND INSTRB(:AS_LIST||',', ',', LEVEL) = LEVEL CONNECT BY LEVEL <= LENGTH(:AS_LIST) + LENGTH(',') ) WHERE 1=1 AND CODE_ARR IS NOT NULL -- 02. LOB DATA SELECT CODE_ARR FROM ( SELECT DBMS_LOB.SUBSTR( :AS_LIST , DBMS_LOB.INSTR(:AS_LIST || ',', ',', LEVEL) - DBMS_LOB.INSTR(',' || :AS_LIST, ',', 1, ROWNUM) , DBMS_LOB.INSTR(',' || :AS_LIST, ',', 1, ROWNUM) ) AS CODE_ARR FROM DUAL WHERE 1=1 AND DBMS_LOB.INSTR(:AS_LIST || ',', ',', LEVEL) = LEVEL CONNECT BY LEVEL <= LENGTH(:AS_LIST) + LENGTH(',') ) WHERE 1=1 AND CODE_ARR IS NOT NULL -- 03. 정규식 SELECT REGEXP_SUBSTR(CODE_ARR, '[^,]+', 1, LEVEL) AS CODE_ARR FROM ( SELECT :AS_LIST AS CODE_ARR FROM DUAL ) WHERE 1=1 CONNECT BY REGEXP_SUBSTR(CODE_ARR, '[^,]+', 1, LEVEL) IS NOT NULL
'IT > DB' 카테고리의 다른 글
[Oracle] ORDER BY에서 NULL 위치 (0) | 2014.10.17 |
---|---|
[Oracle] CLOB 타입 REPLACE 시키기 (0) | 2014.03.26 |
[Oracle] MODEL (0) | 2014.03.07 |
[ORACLE] 통계 방법 (0) | 2014.03.05 |