오라클 테이블 반환 (PIPELINED TABLE) 함수 작성법

오라클 에서 함수(Function) 를 작성하여 반환 값으로 보통 하나의  문자나 숫자 형태로 

반환할 경우가 있지만

 

테이블 형태로도 반환 할 수 있다. 그 테이블 형태로 반환 하는 방법을 

정리해 놓았다.

 

1. 레코드 타입 선언

- 함수에서 반환하는 레코드의 스키마를 정의  

- FUNC_MULTIVALUES 함수명 정의

--레코드 타입 선언 CREATE OR REPLACE TYPE FUNC_MULTIVALUES AS OBJECT (       AUTH_NO VARCHAR2(10) );

 

2. 테이블 타입 선언

- 함수에서 반환하는 레코드의 집합(테이블) 정의

- “T_” + 함수명 (명칭 정의 임의로 해도 상관은 없다.)

--테이블 타입 선언 CREATE OR REPLACE TYPE T_FUNC_MULTIVALUES  AS TABLE OF FUNC_MULTIVALUES ;

 

3. 함수 작성

- Pipelined Table 함수 작성

- 반환 유형: 2단계에서 작성한 테이블 타입 반환

--테이블 반환 함수 (※ 1번의 FUNC_MULTIVALUES  명칭과 다르게 정의 해야 한다.)   - 아래 함수 사용시 인자값 (변수1, 변수2) 정의 CREATE OR REPLACE FUNCTION GET_FUNC_MULTIVALUES  (                   --변수1           USER_ID VARCHAR2            --변수2          , DEPT_NO VARCHAR2 ) RETURN T_FUNC_MULTIVALUES  PIPELINED AS     V_RESULT_ROW        T_FUNC_MULTIVALUES  ; BEGIN /******************************************************************     * Function Name : GET_FUNC_MULTIVALUES      * Return Type   : Table     * Description   : 사용자 정보로 해당 하는 권한 검색     * CUR_T검색하여 테이블로 리턴한다. ******************************************************************/     FOR CUR_T IN (       SELECT B.AUTH_NO        FROM EMP A JOIN B EMP_AUTH       ON A.USER_ID = B.USER_ID AND A.DEPT_NO = B.DEPT_NO       WHERE A.CODE =1       ) LOOP         --반환할 행 생성         V_RESULT_ROW := FUNC_MULTIVALUES (A.CODE, A.VALUE1, A.VALUE2);                 PIPE ROW(V_RESULT_ROW);         END LOOP;       RETURN; END;

 

4. 조회

- "TABLE" 키워드를 사용하여 조회

SELECT A.AUTH_NO  FROM TABLE(GETMULTIVALUES('FSL', 'AA01')) A ;

 

위 예시는 각 메뉴마다 사용자의 권한에 따라 버튼(기능)들을 보이게 할때 사용한 예를 들었다.

장점으론 4번의 위 한줄로 SQL 문 의 가독성을 높일수가 있다.

그외에도 필요한 부분이 있으면 활용 하면 될것 같다.