[ORACLE] 구분자로 분리하여 행으로 반환하는 TABLE FUNCTION

1. 우선 RETURN 받을 ROW의 COLUMN TYPE OBJECT를 생성

컬럼이 더 필요하다면 추가정의 하면 된다.

CREATE OR REPLACE TYPE TY_SPLIT_TO_ROW/*1.*/ AS OBJECT
(
    TEXT VARCHAR2(4000)
)

 

2. 이어서 COLLECTION TYPE 생성

CREATE OR REPLACE TYPE TB_SPLIT_TO_ROW/*2.*/ AS TABLE OF TY_SPLIT_TO_ROW/*1.*/;

 

3. 드디어 TABLE FUNCTION 생성

데이터 구분자가 ','로 고정 되어 있지만 필요에 따라 파라미터로 받아서 처리 할 수도 있을 것이다.

CREATE OR REPLACE FUNCTION TF_SPLIT_TO_ROW/*3.*/
(
    P_PARAM VARCHAR2
)
RETURN TB_SPLIT_TO_ROW/*2.*/ PIPELINED
IS
    V_RESULT TY_SPLIT_TO_ROW/*1.*/;
BEGIN
    FOR CUR IN ( 
            SELECT TRIM(REGEXP_SUBSTR(P_PARAM, '[^' || ','/*데이터구분자*/ || ']+', 1, LEVEL)) AS TEXT
              FROM DUAL
        CONNECT BY INSTR(P_PARAM, ','/*데이터구분자*/, 1, LEVEL - 1 ) > 0
    ) 
    LOOP
        V_RESULT/*2.*/ := TY_SPLIT_TO_ROW/*1.*/(CUR.TEXT);
        PIPE ROW(V_RESULT/*2.*/);
    END LOOP;
END;

 

 

활용예제1

',' 구분자로 되어 있는 데이터를 행으로 반환한다.

SELECT A.TEXT
  FROM TABLE(TF_SPLIT_TO_ROW('1교시,2교시,3교시')) A

 

 

 

 

 

활용예제2

① 테이블에 아래와 같은 데이터가 있을때

SELECT A.SBJ AS SBJ
     , A.TM  AS TM
  FROM TB A

 

 

 

 

② 테이블의 데이터를 TABLE FUNCTION에 던져 반환한다.

SELECT A.SBJ  AS SBJ
     , A.TM   AS TM
     , B.TEXT AS TEXT
  FROM TB A
    LEFT OUTER JOIN TABLE(TF_SPLIT_TO_ROW(A.TM)) B
      ON 1 = 1

 

 

 

 

 

 

 

활용예제3

① IN절에서 활용

SELECT *
  FROM TB
 WHERE YYYY IN (
                   SELECT TEXT 
                     FROM TF_SPLIT_TO_ROW('1989,1995,2012')
               )

 

 

 

참고

http://www.gurubee.net/lecture/2238

Table Function & Pipelined Table

Blog Theme - Details (oracle.com)

How to split comma separated value strings into rows in Oracle Database

 

 

 

 

※ 작은 팁

삭제나 수정때는 위의 과정을 역순으로 진행하여 DROP 시킨 이후에,

일련과정을 다시 순서대로 진행해야 하는 귀찮음이 존재한다.

 

그래서 Function 내부 주석에 삭제와 생성에 대한 DDL을 아래와 같이 작성해 놓는다면 귀찮음이 조금은 수월해진다.

CREATE OR REPLACE FUNCTION TF_SPLIT_TO_ROW

-- [삭제]
-- 1.
-- DROP FUNCTION TF_SPLIT_TO_ROW;
-- 2.
-- DROP TYPE TB_SPLIT_TO_ROW;
-- 3.
-- DROP TYPE TY_SPLIT_TO_ROW;

-- [생성]
-- 1.
-- CREATE OR REPLACE TYPE TY_SPLIT_TO_ROW AS OBJECT
-- (
--     TEXT VARCHAR2(4000)
-- )
-- 2.
-- CREATE OR REPLACE TYPE TB_SPLIT_TO_ROW AS TABLE OF TY_SPLIT_TO_ROW;

(
    P_PARAM VARCHAR2
)
RETURN TB_SPLIT_TO_ROW PIPELINED
IS
    V_RESULT TY_SPLIT_TO_ROW;
BEGIN
    FOR CUR IN ( 
            SELECT TRIM(REGEXP_SUBSTR(P_PARAM, '[^' || ','|| ']+', 1, LEVEL)) AS TEXT
              FROM DUAL
        CONNECT BY INSTR(P_PARAM, ',', 1, LEVEL - 1 ) > 0
    ) 
    LOOP
        V_RESULT := TY_SPLIT_TO_ROW(CUR.TEXT);
        PIPE ROW(V_RESULT);
    END LOOP;
END;