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 행이 선택되었습니다.