[PL/SQL] 파이프라인 테이블함수

파이프라인 테이블함수(PIPELINED TABLE FUNCTION)

CREATE OR REPLACE FUNCTION 함수명 (매개변수)
    RETURN 컬렉션타입
    PIPELINED
IS
    ...
BEGIN
    ...
    LOOP
    
        PIPE ROW(반환데이터);
    END LOOP;
    RETURN;
END;

예제

CREATE OR REPLACE FUNCTION FN_CH14_PIPE_TABLE (P_N NUMBER)
    RETURN CH14_NUM_AT
    PIPELINED
IS
    VNT_RETURN  CH14_NUM_AT := CH14_NUM_AT();
BEGIN
    FOR IDX IN 1..P_N
    LOOP
        VNT_RETURN.EXTEND;
        VNT_RETURN(IDX) := IDX;
        
        PIPE ROW(VNT_RETURN(IDX));
    END LOOP;
    RETURN;
END;

SELECT *
  FROM TABLE(FN_CH14_PIPE_TABLE(10));

다른 테이블 함수와 다를 것이 없어보인다. 하지만, 데이터의 양이 많아지면 속도차이를 실감할 수 있다.

아래는 사용자정의 테이블함수를 실행하는 쿼리문과 실행결과다.

SELECT *
  FROM TABLE(FN_CH14_TABLE1(4000000));

아래는 파이프라인 테이블함수 쿼리문과 실행결과다.

SELECT *
  FROM TABLE(FN_CH14_PIPE_TABLE(4000000));

확실히 파이프라인의 성능이 월등하며, 같은 조건과 목적이라면 파이프라인 테이블함수를 사용하는 것이 성능상 우위가 있다.

또, 파이프라인 테이블함수는 루프 중간에 PIPE ROW문을 사용해 컬렉션 타입을 반환하므로 루프 안에서 여러번 사용하면 데이터 역시 여러번 반환된다.

CREATE OR REPLACE FUNCTION FN_CH14_PIPE_TABLE2 (P_CUR CH14_EMPTY_PKG.EMP_REFC_T)
    RETURN CH14_CMPLX_NT
    PIPELINED
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;
        PIPE ROW(VNT_RETURN(VNT_RETURN.LAST));
        
        VNT_RETURN(VNT_RETURN.LAST).VARCHAR_COL1 := V_CUR.JOB_ID;
        VNT_RETURN(VNT_RETURN.LAST).VARCHAR_COL2 := V_CUR.EMAIL;
        PIPE ROW(VNT_RETURN(VNT_RETURN.LAST));
    END LOOP;
    RETURN;
END;

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

실행결과를 보게되면, 같은 NUM_COL, DATE_COL 값을 가진 행이 하나씩 더 반환된 것을 알 수있다.