TABLE FUNCTION 사용하기

TABLE FUNCTION 사용하기

[출처] [Oracle]TABLE FUNCTION 사용하기|작성자 영현아범

 

아... 안녕하세요.

그동안 사용해온 SQL Server를 뒤로하고 오라클로 갈아타게된 유일환입니다.

오라클이 좋아서나, SQL Server가 싫어서가 아닌, 밥 벌이 한다는게 원래 그런거 아니겠습니까?

뜻하지 않게 원하지 않던 일을 하게 되거나, 뜻하지 않게 좋은일이 생긴다거나... 모 그런거죠..

오라클로 갈아타게 되면서, 좋은일 나쁜일들이 동시에 발생되네요.

새로운 DB를 다루어 본다는 기쁨과 좋은일

기존에 잘 되던 것들이 문제가 생겨서 밥을 새워야 한다는 피곤함.

결과적으로 자기 발전에는 다 좋게 작용될 것이라 생각이 되네요.

오라클을 다루게 되었으니, 이제는 오라클 관련 글들을 쓰게 될거 같네요.

SQL Server가 SQL Server만의 편안함과 좋은 기능들을 많이 가지고 있었다면

오라클 역시 오라클만의 훌륭함과 좋은 기능들이 많이 있네요.

오늘은 그 중에 하나인 TABLE FUNCTION에 대해 적어 볼까 합니다.

아직은 오라클 경력이 짧아, 틀린 부분이 있을지도 모르겠습니다.

그러한 부분들은 전문가 분들께서 답글로 고쳐주시길 기대하면서, 한 번 적어보겠습니다.

 

TABLE FUNCTION은 말 그대로 함수를 사용해서 TABLE처럼 결과를 내보내는 기능입니다.

TABLE FUNCTION은 SELECT의 FROM절에 위치해서 TABLE처럼 사용이 가능합니다.

물론, SQL Server에도 있는 기능입니다.

오라클은 이 TABLE FUNCTION은 PIPELINE이라는 기능을 사용합니다.

함수에서 만들어진 결과를 FROM절에 PIPELINE을 통해서 차례대로 던져준다는 의미인거 같습니다.

먼저 이러한 TABLE FUNCTION을 어떤 경우 사용하게 되는지 정의해 보도록 하겠습니다.

저희 시스템 같은경우 분석 리포트가 많이 있습니다. 그리고, 대부분의 SQL들이 정적 프로시저로 구현되어 있습니다.

정적 프로시저로 구현되어 있다는 것은, 사용자가 요청한 다양한 조건을 동적으로 구현하지 못한 다는 것입니다.

예를 들어, 사용자가 화면에사 여러가지 제품을 멀티로 선택해서 조회할 경우, SQL 구현이 매우 어렵습니다.

사용자가 화면에서 'PROD1,PROD2,PROD3' 이렇게 3가지를 선택하면 3가지 제품만 조회하는 SQL이 만들어져야 하는 거지요.

또는 2개의 제품을 선택하거나, 많게는 10개 이상의 제품을 화면에서 선택할 수도 있을 것입니다.

이런 경우, 매개변수를 사용할 수 있는 제품수만큼 만들어서 IN조건으로 처리할 수도 있겠지만,

제품리스트란 변수 하나에 'PROD1,PROD2,PROD3' 이런식으로 매개변수를 받을 수도 있습니다.

구현 방법은 여러가지가 있겠죠. 후자에 설명한 하나의 변수에 제품리스트가 콤마(,)로 분리되어 조회된다고 가정할 때,

WHERE절의 SQL을 어떻게 구현해야 할까요?

방법은 여러가지가 있을 수 있는데, 그 중의 하나가 TABLE FUNCTION을 사용해서, 'PROD1,PROD2,PROD3'매개변수를

PROD1

PROD2

PROD3

과 같이 테이블 형태로 만드는 것입니다.

즉, 이렇게 되겠죠.

 

WHERE PROD_CD IN (SELECT RESULT FROM TABLE(UFN_STR_TO_TAB(:PROD_LST, ','))

 

위에서 UFN_STR_TO_TAB은 테이블 펑션 이름이고, :PROD_LST는 매개변수입니다.

UFN_STR_TO_TAB은 외부에서 받은 콤마로 구분된 제품리스트를 테이블형태로 변환하는 함수입니다.

자, 이러한 SQL을 처리하기 위해 UFN_STR_TO_TAB이 필요한 것입니다.

 

아래는 구현하는 과정인데, 설명은 줄이고 스크립트 위주로 보도록 하겠습니다.

1.자료형 구현

- TABLE FUNCTION을 구현하기 위해서는 자료형을 먼저 선언해야 하더군요.

다음과 같이 자료형을 만들어 줍니다.

 

CREATE OR REPLACE TYPE PIPE_FUN_RET AS OBJECT(RESULT varchar2(50)); CREATE OR REPLACE TYPE PIPE_FUN_TAB AS TABLE OF PIPE_FUN_RET;

 

 

2.함수 구형

실제 수행되는 함수를 구현합니다.

 

CREATE OR REPLACE FUNCTION UFN_STR_TO_TAB ( v_LIST_T IN NVARCHAR2, --리스트 매개변수 v_DIV IN NVARCHAR2 --데이터를 구분할 분리자 ) RETURN PIPE_FUN_TAB PIPELINED IS v_LIST VARCHAR2(1000) := v_LIST_T; v_CUR SYS_REFCURSOR; v_RESULT VARCHAR2(50); BEGIN v_LIST := REPLACE(v_LIST || v_DIV, v_DIV||v_DIV, v_DIV); OPEN v_CUR FOR SELECT SUBSTR(T2.TXT, T1.START_POS, T1.LENG) RESULT --, T1.START_POS, T1.LENG, T2.TXT FROM ( SELECT NVL(T1.FR_RNO,0) + 1 START_POS ,T1.TO_RNO - (NVL(T1.FR_RNO,0) + 1) LENG FROM ( SELECT T1.CH ,T1.RNO TO_RNO ,LAG(T1.RNO) OVER(ORDER BY RNO) FR_RNO FROM ( SELECT SUBSTR(v_LIST,RNO, 1) CH, RNO FROM ( SELECT v_LIST, ROWNUM RNO FROM DUAL CONNECT BY LEVEL <= LENGTH(v_LIST) ) T1 WHERE SUBSTR(v_LIST, RNO, 1) = v_DIV ) T1 ) T1 ) T1 CROSS JOIN ( SELECT v_LIST TXT FROM DUAL ) T2; LOOP FETCH v_CUR INTO v_RESULT; EXIT WHEN v_CUR%NOTFOUND; PIPE ROW (PIPE_FUN_RET(v_RESULT)); END LOOP; END; /

 

 

자!! 끝입니다.^^

위 함수를 사용해 봅니다.

 

 

SELECT RESULT FROM TABLE(UFN_STR_TO_TAB('A,B,C,D', ',')

 

A,B,C,D가 4개의 로우로 분리된 것을 볼 수 있습니다.

UFN_STR_TO_TAB를 만드는 SQL을 보면, 길지는 않은데, 약간 복잡한 것을 알 수 있습니다.

간단하게 SQL의 요점만 설명드리면

매개변수로 받은 A,B,C,D를 FROM DUAL의 SELECT에 넣고, CONNECT BY를 이용해

입력된 문자수 만큼 로우로 만들어 버립니다. 이렇게 하면, A,B,C,D 란 데이터가 문자 길이수만큼 나타나는데,

이것을 ROWNUM을 사용해서

A

,

B

,

D

,

E

,

와 같은 결과를 만들어 버립니다. 그 다음에, 콤마(,)가 위치한 ROWNUM만 뽑아낸다음.

LAG 분석함수를 사용해서, 현재 콤마 바로 위의 콤마를 읽어오도록 합니다. 다음과 같겠죠.

ROWNUM LAG(RNO)

2 NULL

4 2

6 4

8 6

이와 같이 데이터를 만들어서,

이 4건과 입력된 값 'A,B,C,D'를 다시 CROSS JOIN해서 4건의 A,B,C,D를 만든다음

위에서 구한 ROWNUM과 LAG(RNO)를 가공해서, 각 로우에 A B C D만 남도록 만들어 버리는 것입니다.

그 다음에 PIPE를 이용해 결과를 리턴시키는 것이죠..

이렇게 하고 나니, SQL을 한문장으로 해보고자 하는 욕심에 복잡한데, 꼭 이렇게 안하셔도 됩니다.

그냥, 간단히 LOOP돌면서 짤라내서, 테이블어레이에 담아서 넘겨도 됩니다.

단!! 조심하세요 무한루프를..

원래.. 저 함수가 저희쪽에 LOOP돌면서 짤라내는 거였는데, NULL값이 들어가면 무한 루프가 돌도록 구현이 되어있었더라고요.

엄청난 UNDOTBS를 차지하면서 뻗을뻔 했습니다.ㅡ,.ㅡ;

 

TABLE FUNCTION에 대하 설명은 여기까지 입니다.

그런데 사실 외부에서 매개변수를 'PROD1,PROD2,PROD3'으로 받아도 위와 같이 TABLE FUNCTION이 아닌

일반 구문으로 처리가 가능합니다.

 

 

FROM TAB A WHERE :V_PROD_LST LIKE '%' A.PROD_CD || '%';

 

 

이렇게 하면, TABLE FUNCTION을 사용하지 않고도 구현이 가능합니다.

(이 SQL은 제 머리에서 나온 것은 아니고 조시형님의 오라클 성능 고도화 I 책을 뚫어지게 보다 발견한 방법입니다.

아.. 이렇게 쓰는 방법이 있겠구나라고 한참 감동했었다는.....)

단, PROD_CD에 꼭 사용될 인덱스가 있다면 사용은 불가능하겠죠.

 

이상입니다. 길고 지루한 글 읽어주셔서 감사합니다.^^

[출처] [Oracle]TABLE FUNCTION 사용하기|작성자 영현아범

 

 

http://blog.naver.com/PostView.nhn?blogId=ryu1hwan&logNo=140102563250