일반적으로 트리구조를 프로그래밍 언어에서 구현을 할때 링크드리스트의 배열을
재귀적인 함수호출로 구현했던것을 응용하여
오라클 패키지에서 재귀함수를 이용해서 디렉토리 구조를 row 로 리턴하는것을
목표로 만들어봤습니다.
-- 재귀호출로 계층구조 표현.
-- by NALCODER
-- http://valenny.tistory.com
-- valenny@naver.com
-- valenny@nate.com
/* 테이블생성 */
create table T_DEPT_MASTER
(
DEPT_IDX NUMBER
,DEPT_NAME VARCHAR2(100)
,PARENT_DEPT_IDX NUMBER
);
/* 유니크 인덱스 생성 */
create UNIQUE INDEX PK_T_DEPT_MASTER
on T_DEPT_MASTER
( DEPT_IDX )
LOGGING TABLESPACE PHPSCHOOL_IDX;
/* 인덱스 생성 */
create INDEX IDX_T_DEPT_MASTER_PARENT
on T_DEPT_MASTER
(PARENT_DEPT_IDX)
LOGGING TABLESPACE PHPSCHOOL_IDX;
/* PRIMARY KEY 생성 */
alter table T_DEPT_MASTER
ADD PRIMARY KEY (DEPT_IDX)
using index;
insert into T_DEPT_MASTER values (1,'부서1', '');
insert into T_DEPT_MASTER values (2,'부서2', 1);
insert into T_DEPT_MASTER values (3,'부서3', 1);
insert into T_DEPT_MASTER values (4,'부서4', 1);
insert into T_DEPT_MASTER values (5,'부서5', 2);
insert into T_DEPT_MASTER values (6,'부서6', 5);
insert into T_DEPT_MASTER values (7,'부서7', 5);
insert into T_DEPT_MASTER values (8,'부서8', 6);
아주 단순한 형태의 계층구조를 담는 테이블을 생성했습니다.
계층구조 질의문 CONNECT BY PRIOR 를 이용해서 다음처럼 계층구조로
표현할 수 있습니다.
-- [SQL] -----------------------------------------------
SELECT a.*
,SYS_CONNECT_BY_PATH (dept_idx, '/') AS PATH_name
,LEVEL AS lev
FROM t_dept_master a
START WITH dept_idx = 1
CONNECT BY PRIOR dept_idx = parent_dept_idx
ORDER SIBLINGS BY dept_idx;
-- [결과] -----------------------------------------------
DEPT_IDX,DEPT_NAME,PARENT_DEPT_IDX,PATH_NAME,LEV
1,부서1,,/1,1
2,부서2,1,/1/2,2
5,부서5,2,/1/2/5,3
6,부서6,5,/1/2/5/6,4
8,부서8,6,/1/2/5/6/8,5
7,부서7,5,/1/2/5/7,4
3,부서3,1,/1/3,2
4,부서4,1,/1/4,2
---------------------------------------------------------
이것을 패키지로 구성해서 같은 결과를 내는 펑션을 만들어보고자 합니다.
-- 패키지 사양 ---------------------------------------------
CREATE OR REPLACE PACKAGE NALCO.PCK_DEPT AS
TYPE REF_CURSOR IS REF CURSOR;
/* 출력 row 타입 */
TYPE R_DEPT_INFO IS RECORD
(
DEPT_IDX T_DEPT_MASTER.DEPT_IDX%TYPE
,DEPT_NAME T_DEPT_MASTER.DEPT_NAME%TYPE
,PARENT_DEPT_IDX T_DEPT_MASTER.PARENT_DEPT_IDX%TYPE
,PATH_NAME VARCHAR2 (500)
,LEV NUMBER
);
TYPE T_DEPT_INFO IS TABLE OF R_DEPT_INFO;
/* 재귀함수 */
FUNCTION recur_dept_info (P_CUR_DEPT_IDX IN T_DEPT_MASTER.DEPT_IDX%TYPE
,P_CUR_LEV IN NUMBER
,P_CUR_PATH IN VARCHAR2
,P_DELEMETER IN VARCHAR2)
RETURN T_DEPT_INFO
PIPELINED;
FUNCTION GET_DEPT_INFO (P_START_DEPT_IDX IN T_DEPT_MASTER.DEPT_IDX%TYPE)
RETURN T_DEPT_INFO
PIPELINED;
END;
/
---------------------------------------------------------------
-- 패키지 바디 ------------------------------------------------
CREATE OR REPLACE PACKAGE BODY NALCO.PCK_DEPT AS
FUNCTION recur_dept_info (P_CUR_DEPT_IDX IN T_DEPT_MASTER.DEPT_IDX%TYPE
,P_CUR_LEV IN NUMBER
,P_CUR_PATH IN VARCHAR2
,P_DELEMETER IN VARCHAR2)
RETURN T_DEPT_INFO
PIPELINED IS
CURSOR cur_list IS
SELECT *
FROM t_dept_master
WHERE parent_dept_idx = p_cur_dept_idx
ORDER BY dept_idx;
retDeptInfo R_DEPT_INFO;
CURSOR cur_recur (p_dept_idx IN T_DEPT_MASTER.DEPT_IDX%TYPE) IS
SELECT *
FROM TABLE (RECUR_DEPT_INFO (P_DEPT_IDX
,P_CUR_LEV + 1
,P_CUR_PATH || P_DELEMETER || P_DEPT_IDX
,P_DELEMETER));
BEGIN
FOR cl IN cur_list LOOP
retDeptInfo.dept_idx := cl.dept_idx;
retDeptInfo.dept_name := cl.dept_name;
retDeptInfo.parent_dept_idx := cl.parent_dept_idx;
retDeptInfo.PATH_NAME := P_CUR_PATH || P_DELEMETER || cl.dept_idx;
retDeptInfo.lev := P_CUR_LEV + 1;
PIPE ROW (retDeptInfo);
FOR cr IN cur_recur (retDeptInfo.dept_idx) LOOP
retDeptInfo.dept_idx := cr.dept_idx;
retDeptInfo.dept_name := cr.dept_name;
retDeptInfo.parent_dept_idx := cr.parent_dept_idx;
retDeptInfo.PATH_NAME := cr.PATH_NAME;
retDeptInfo.lev := cr.lev;
PIPE ROW (retDeptInfo);
END LOOP;
END LOOP;
END;
FUNCTION GET_DEPT_INFO (P_START_DEPT_IDX IN T_DEPT_MASTER.DEPT_IDX%TYPE)
RETURN T_DEPT_INFO
PIPELINED IS
retDeptInfo R_DEPT_INFO;
CUR_LIST REF_CURSOR; -- REF CURSOR
BEGIN
SELECT dept_idx
,dept_name
,parent_dept_idx
,'/' || dept_idx
,1
INTO retDeptInfo.dept_idx
,retDeptInfo.dept_name
,retDeptInfo.parent_dept_idx
,retDeptInfo.path_name
,retDeptInfo.lev
FROM t_dept_master
WHERE dept_idx = p_start_dept_idx;
PIPE ROW (retDeptInfo);
OPEN CUR_LIST FOR
SELECT *
FROM TABLE (recur_dept_info (P_START_DEPT_IDX
,retDeptInfo.lev
,retDeptInfo.path_name
,'/'));
LOOP
FETCH CUR_LIST
INTO retDeptInfo;
EXIT WHEN CUR_LIST%NOTFOUND;
PIPE ROW (retDeptInfo);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
---------------------------------------------------------------
-- [SQL] -----------------------------------------------
SELECT *
FROM TABLE (pck_dept.GET_DEPT_INFO (1)); -- 1:시작 idx
-- [결과] -----------------------------------------------
DEPT_IDX,DEPT_NAME,PARENT_DEPT_IDX,PATH_NAME,LEV
1,부서1,,/1,1
2,부서2,1,/1/2,2
5,부서5,2,/1/2/5,3
6,부서6,5,/1/2/5/6,4
8,부서8,6,/1/2/5/6/8,5
7,부서7,5,/1/2/5/7,4
3,부서3,1,/1/3,2
4,부서4,1,/1/4,2
---------------------------------------------------------
맺음말 :
오라클에서 재귀적 호출로 connect by prior 라는 계층구조를 표현하는
구문과 같은 패키지를 생성해봤습니다.
