Unpivot Pipelined Function

APEX 개발에 사용하기 위해 Ref 커서를 입력받아 Unpivot한 결과를 반환하는 Pipelined Function을 작성했습니다.

 

아래와 같이 패키지를 생성하겠습니다. DBMS_SQL.TO_CURSOR_NUMBER 프로시저로 Ref 커서를 DBMS_SQL 커서로 변경하는 부분을 제외하면 Tom Kyte가 개발한 print_table 프로시저와 동작 방식이 동일합니다.

-- 1-1
CREATE OR REPLACE PACKAGE pkg_unpivot
IS
    TYPE trd IS RECORD (
        name  VARCHAR2(4000)
      , value VARCHAR2(4000)
    );

    TYPE tnt IS TABLE OF trd;

    FUNCTION fnc_get_value (
        i_cursor IN SYS_REFCURSOR
    )
        RETURN tnt PIPELINED;
END;
/

-- 1-2
CREATE OR REPLACE PACKAGE BODY pkg_unpivot
IS
    FUNCTION fnc_get_value (
        i_cursor IN SYS_REFCURSOR
    )
        RETURN tnt PIPELINED
    IS
        v_rc      SYS_REFCURSOR := i_cursor;
        v_c       NUMBER;
        v_col_cnt NUMBER;
        v_desc_t  DBMS_SQL.DESC_TAB;
        v_column  VARCHAR2(4000);
    BEGIN
        v_c := DBMS_SQL.TO_CURSOR_NUMBER (v_rc);
        DBMS_SQL.DESCRIBE_COLUMNS (v_c, v_col_cnt, v_desc_t);

        FOR i IN 1 .. v_col_cnt LOOP
            DBMS_SQL.DEFINE_COLUMN (v_c, i, v_column, 4000);
        END LOOP;

        WHILE DBMS_SQL.FETCH_ROWS (v_c) > 0 LOOP
            FOR i IN 1 .. v_col_cnt LOOP
                DBMS_SQL.COLUMN_VALUE (v_c, i, v_column);
                PIPE ROW (trd (v_desc_t(i).col_name, v_column));
            END LOOP;
        END LOOP;
    END;
END;
/

 

아래는 함수를 사용한 결과입니다.

-- 2
VAR B_DEPTNO NUMBER = 10

SELECT *
  FROM TABLE (pkg_unpivot.fnc_get_value (CURSOR (SELECT empno, ename, comm FROM emp WHERE deptno = :b_deptno)));

NAME  VALUE
----- ------
EMPNO 7782
ENAME CLARK
COMM
EMPNO 7839
ENAME KING
COMM
EMPNO 7934
ENAME MILLER
COMM

9 행이 선택되었습니다.

 

아래 패키지는 2개의 값을 Unpivot합니다. 로직을 응용하면 다수의 값을 Unpivot할 수 있습니다.

-- 3-1
CREATE OR REPLACE PACKAGE pkg_unpivot2
IS
    TYPE trd IS RECORD (
        name   VARCHAR2(4000)
      , value1 VARCHAR2(4000)
      , value2 VARCHAR2(4000)
    );

    TYPE tnt IS TABLE OF trd;

    FUNCTION fnc_get_value (
        i_cursor IN SYS_REFCURSOR
    )
        RETURN tnt PIPELINED;
END;
/

-- 3-2
CREATE OR REPLACE PACKAGE BODY pkg_unpivot2
IS
    FUNCTION fnc_get_value (
        i_cursor IN SYS_REFCURSOR
    )
        RETURN tnt PIPELINED
    IS
        v_rc      SYS_REFCURSOR := i_cursor;
        v_c       NUMBER;
        v_col_cnt NUMBER;
        v_desc_t  DBMS_SQL.DESC_TAB;
        v_column  VARCHAR2(4000);
        v_row_cnt NUMBER := 0;
        v_value   DBMS_SQL.VARCHAR2_TABLE;
    BEGIN
        v_c := DBMS_SQL.TO_CURSOR_NUMBER (v_rc);
        DBMS_SQL.DESCRIBE_COLUMNS (v_c, v_col_cnt, v_desc_t);

        FOR i IN 1 .. v_col_cnt LOOP
            DBMS_SQL.DEFINE_COLUMN (v_c, i, v_column, 4000);
        END LOOP;

        WHILE DBMS_SQL.FETCH_ROWS (v_c) > 0 LOOP
            v_row_cnt := v_row_cnt + 1;

            FOR i IN 1 .. v_col_cnt LOOP
                IF MOD (v_row_cnt, 2) = 1 THEN
                    DBMS_SQL.COLUMN_VALUE (v_c, i, v_column);
                    v_value(i) := v_column;
                ELSE
                    DBMS_SQL.COLUMN_VALUE (v_c, i, v_column);
                    PIPE ROW (trd (v_desc_t(i).col_name, v_value(i), v_column));
                END IF;
            END LOOP;
        END LOOP;

        IF MOD (v_row_cnt, 2) = 1 THEN
            FOR i IN 1 .. v_col_cnt LOOP
                PIPE ROW (trd (v_desc_t(i).col_name, v_value(i), NULL));
            END LOOP;
        END IF;
    END;
END;
/

 

아래는 함수를 사용한 결과입니다.

-- 4
SELECT *
  FROM TABLE (pkg_unpivot2.fnc_get_value (CURSOR (SELECT empno, ename, comm FROM emp WHERE deptno = :b_deptno)));

NAME  VALUE1 VALUE2
----- ------ ------
EMPNO 7782   7839
ENAME CLARK  KING
COMM
EMPNO 7934
ENAME MILLER
COMM

6 행이 선택되었습니다.

 

아래 패키지는 Ref 커서를 XMLSEQUENCE 함수로 처리합니다.

-- 5-1
CREATE OR REPLACE PACKAGE pkg_unpivot_xml
IS
    TYPE trd IS RECORD (
        name  VARCHAR2(4000)
      , value VARCHAR2(4000)
    );

    TYPE tnt IS TABLE OF trd;

    FUNCTION fnc_get_value (
        i_cursor IN SYS_REFCURSOR
    )
        RETURN tnt PIPELINED;
END;
/

-- 5-2
CREATE OR REPLACE PACKAGE BODY pkg_unpivot_xml
IS
    FUNCTION fnc_get_value (
        i_cursor IN SYS_REFCURSOR
    )
        RETURN tnt PIPELINED
    IS
    BEGIN
        FOR v IN (SELECT b.column_value.GETROOTELEMENT () AS name
                       , EXTRACTVALUE (b.column_value, 'node()') AS value
                    FROM TABLE (XMLSEQUENCE (i_cursor)) a
                       , TABLE (XMLSEQUENCE (EXTRACT (a.column_value, '/ROW/node()'))) b)
        LOOP
            PIPE ROW (trd (v.name, v.value));
        END LOOP;
    END;
END;
/

 

아래는 함수를 사용한 결과입니다. 공백이 _x0020_로 표시되고, 값이 널인 로우가 출력되지 않는 문제가 있습니다.

-- 6
SELECT *
  FROM TABLE (pkg_unpivot_xml.fnc_get_value (CURSOR (SELECT empno AS "emp no", ename, comm FROM emp WHERE deptno = :b_deptno)));

NAME         VALUE
------------ ------
emp_x0020_no 7782
ENAME        CLARK
emp_x0020_no 7839
ENAME        KING
emp_x0020_no 7934
ENAME        MILLER

6 행이 선택되었습니다.