다형성 테이블 함수

18.1 버전에 다형성 테이블 함수(Polymorphic Table Function, PTF) 기능이 추가되었다.^[각주:1] PTF는 새로운 유형의 테이블 함수로 입력된 인수에 의해 반환 형식이 결정된다. 간단한 예제를 살펴보자.

테스트를 위해 아래와 같이 패키지를 생성하자. fnc_flat 함수는 테이블과 구분자를 입력받아 플랫 파일 형식의 결과를 반환한다.

-- 1-1
CREATE OR REPLACE PACKAGE pkg_flat
IS
    FUNCTION fnc_flat (
        i_tab IN TABLE
      , i_del IN VARCHAR2 DEFAULT ','
    )
        RETURN TABLE PIPELINED ROW POLYMORPHIC USING pkg_flat;

    FUNCTION describe (
        i_tab IN OUT DBMS_TF.TABLE_T
      , i_del IN     VARCHAR2 DEFAULT ','
    )
        RETURN DBMS_TF.DESCRIBE_T;

    PROCEDURE fetch_rows (
        i_del IN VARCHAR2 DEFAULT ','
    );
END pkg_flat;
/

-- 1-2
CREATE OR REPLACE PACKAGE BODY pkg_flat
IS
    FUNCTION describe (
        i_tab IN OUT DBMS_TF.TABLE_T
      , i_del IN     VARCHAR2 DEFAULT ','
    )
        RETURN DBMS_TF.DESCRIBE_T
    IS
        l_col_new DBMS_TF.COLUMNS_NEW_T;
    BEGIN
        FOR i IN 1 .. i_tab.COLUMN.COUNT LOOP
            CONTINUE WHEN NOT DBMS_TF.SUPPORTED_TYPE (i_tab.COLUMN(i).DESCRIPTION.TYPE);
            i_tab.COLUMN(i).PASS_THROUGH := FALSE;
            i_tab.COLUMN(i).FOR_READ := TRUE;
        END LOOP;

        l_col_new(1) := DBMS_TF.COLUMN_METADATA_T (name => 'COLUNM_VALUE', type => DBMS_TF.TYPE_VARCHAR2);

        RETURN DBMS_TF.DESCRIBE_T (new_columns => l_col_new);
    END describe;

    PROCEDURE fetch_rows (
        i_del IN VARCHAR2 DEFAULT ','
    )
    IS
        l_row_set DBMS_TF.ROW_SET_T;
        l_row_cnt PLS_INTEGER;
        l_col_cnt PLS_INTEGER;
        l_tab_col DBMS_TF.TAB_VARCHAR2_T;
    BEGIN
        DBMS_TF.GET_ROW_SET (l_row_set, l_row_cnt, l_col_cnt);

        FOR r IN 1 .. l_row_cnt LOOP
            l_tab_col(r) := NULL;

            FOR c IN 1 .. l_col_cnt LOOP
                l_tab_col(r) := l_tab_col(r) || i_del || TRIM ('"' FROM DBMS_TF.COL_TO_CHAR (l_row_set(c), r));
            END LOOP;

            l_tab_col(r) := LTRIM (l_tab_col(r), i_del);
        END LOOP;

        DBMS_TF.PUT_COL (1, l_tab_col);
    END fetch_rows;
END pkg_flat;
/

아래 쿼리는 PTF에 dept 테이블을 인수로 입력했다. 

-- 2
SELECT * FROM pkg_flat.fnc_flat (dept);

COLUNM_VALUE
----------------------
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

4 행이 선택되었습니다.

----------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      0 |      0 |       0 |
|   1 |  POLYMORPHIC TABLE FUNCTION | FNC_FLAT |      1 |      4 |      85 | -- !
|   2 |   TABLE ACCESS FULL         | DEPT     |      1 |      4 |       6 |
----------------------------------------------------------------------------

WITH 절을 사용하면 PTF에 서브 쿼리를 입력할 수 있다. 실행 계획 1번에서 144개의 블록 I/O가 발생한 것을 확인할 수 있다. 리컬시브 콜에 의한 블록 I/O로 좀 더 테스트가 필요할 것 같다.

-- 3
WITH w1 AS (SELECT empno, ename, hiredate, sal FROM emp WHERE deptno = 10)
SELECT * FROM pkg_flat.fnc_flat (w1);

COLUNM_VALUE
------------------------------------
7782,CLARK,1981-06-09 00:00:00,2450
7839,KING,1981-11-17 00:00:00,5000
7934,MILLER,1982-01-23 00:00:00,1300

3 행이 선택되었습니다.

-------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      0 |      0 |       0 |
|   1 |  POLYMORPHIC TABLE FUNCTION          | FNC_FLAT |      1 |      3 |     146 | -- !
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |      1 |      3 |       2 |
|   3 |    INDEX RANGE SCAN                  | EMP_X1   |      1 |      3 |       1 |
-------------------------------------------------------------------------------------

1. ANSI SQL 2016에 포함된 기능이다. [본문으로]