[PL/SQL] 테이블 함수

테이블 함수

여러 로우를 가진 컬렉션을 반환하는 함수.

컬렉션 타입을 반환하는데, 연관 배열은 반환하지 못하고, 중첩 테이블이나 VARRAY만 반환가능하다.

사용자정의 테이블 함수

일반 사용자정의 함수와 다를 것 없으나, 그 대상이 되는 컬렉션 타입이 먼저 만들어져 있어야한다.

아래는 NUMBER만 이용한 간단한 예시이다.

CREATE OR REPLACE TYPE CH14_NUM_AT IS TABLE OF NUMBER;

CREATE OR REPLACE FUNCTION FN_CH14_TABLE1 (P_N NUMBER)
    RETURN CH14_NUM_AT
IS
    -- 컬렉션 변수 선언
    VNT_RETURN  CH14_NUM_AT := CH14_NUM_AT();
BEGIN
    FOR IDX IN 1..P_N
    LOOP
        VNT_RETURN.EXTEND;
        VNT_RETURN(IDX) := IDX;
    END LOOP;
    
    RETURN VNT_RETURN;
END;

SELECT FN_CH14_TABLE1(10)
  FROM DUAL;

SELECT *
  FROM TABLE(FN_CH14_TABLE1(10));

실제 데이터를 가지고 다시 해보자.

매개변수로 커서를 받는 테이블 함수를 작성할 것이다.

먼저, 테이블 함수가 반환할 컬렉션 타입을 만든다.

CREATE OR REPLACE TYPE CH14_OBJ_TYPE1 AS OBJECT
(
      VARCHAR_COL1  VARCHAR2(100)
    , VARCHAR_COL2  VARCHAR2(100)
    , NUM_COL       NUMBER
    , DATE_COL      DATE
);

그리고 위에서 만든 CH14_OBJ_TYPE1 을 요소로 하는 중첩 테이블을 만든다.

CREATE OR REPLACE TYPE CH14_CMPLX_NT IS TABLE OF CH14_OBJ_TYPE1;

매개변수로 사용할 커서타입을 만들어야하는데 CREATE 로는 커서타입을 생성할 수가 없다.

그러므로, 패키지 하나를 선언하고 그 패키지 타입을 참조해 사용한다.

패키지를 만들자.

CREATE OR REPLACE PACKAGE CH14_EMPTY_PKG
IS
    TYPE EMP_REFC_T IS REF CURSOR
    RETURN EMPLOYEES%ROWTYPE;
END CH14_EMPTY_PKG;

마지막으로 함수를 작성한다.

CREATE OR REPLACE FUNCTION FN_CH14_TABLE2 (P_CUR CH14_EMPTY_PKG.EMP_REFC_T)
    RETURN CH14_CMPLX_NT
IS
    -- 입력 커서에 대한 변수
    V_CUR       P_CUR%ROWTYPE;
    
    VNT_RETURN  CH14_CMPLX_NT := CH14_CMPLX_NT();
BEGIN
    LOOP
        FETCH P_CUR INTO V_CUR;
        EXIT WHEN P_CUR%NOTFOUND;
        
        VNT_RETURN.EXTEND();
        VNT_RETURN(VNT_RETURN.LAST) := CH14_OBJ_TYPE1(NULL, NULL, NULL, NULL);
        
        VNT_RETURN(VNT_RETURN.LAST).VARCHAR_COL1 := V_CUR.EMP_NAME;
        VNT_RETURN(VNT_RETURN.LAST).VARCHAR_COL2 := V_CUR.PHONE_NUMBER;
        VNT_RETURN(VNT_RETURN.LAST).NUM_COL      := V_CUR.EMPLOYEE_ID;
        VNT_RETURN(VNT_RETURN.LAST).DATE_COL     := V_CUR.HIRE_DATE;
    END LOOP;
    
    RETURN VNT_RETURN;
END;

이제 작성한 함수를 사용해보자.

이 함수는 매개변수로 커서를 받으므로 CURSOR 표현식으로 SELECT문을 이용해 커서 타입으로 변환한 뒤 매개변수로 전달한다.

SELECT *
  FROM TABLE(
            FN_CH14_TABLE2(
                          CURSOR(
                                SELECT *
                                  FROM EMPLOYEES
                                 WHERE ROWNUM < 6
                                )
                          )
            );

실행결과와 같이, 여러개의 로우를 반환받은 것을 알 수 있다.