MSSQL,ORACLE을 이용하여 트리구조로 출력시켜주는 재귀쿼리를 만들어보자

DBMS별 DEPTH별로 트리목록을 출력하기위한 

재귀쿼리를 만들어보도록 하겠습니다.

재귀쿼리는 트리형 메뉴 혹은 부서 또는 직급별로 

각 레벨이 구분되어있고 단계별로 한방에 출력하고자 할때 

사용이 되는 방법(?)입니다.

이번 포스팅은 MS-SQL과 Oracle에 대하여 포스팅 하도록 하겠습니다.

MySQL에서는 재귀쿼리에 사용되는 문법이 제공되지 않아

 FUNCTION을 이용하여 만들어 사용하므로

 MySQL 재귀호출에 대해서는 제외하도록 하겠습니다.

우선적으로 MSSQL과 오라클에 재귀호출을 하기위해서 임의의 테이블 + 데이터가 필요하겠습니다.

DBMS 공통적으로 적용할수 있는 테이블을 생성 해보도록 하겠습니다.

테이블 생성 및 데이터 INSERT(ORACLE / MSSQL 동일)

create table parent_child(
  child int,
  parent int,
  name varchar(50)
);
insert into parent_child
values(1,0,'홍길동할아버지');
insert into parent_child
values(2,0,'이순신할아버지');
insert into parent_child
values(3,0,'개발로짜할아버지');
insert into parent_child
values(4,1,'홍길동아버지1');
insert into parent_child
values(5,1,'홍길동아버지2');
insert into parent_child
values(6,3,'개발로짜아버지');
insert into parent_child
values(7,4,'홍길동아버지1의아들');
insert into parent_child
values(8,4,'홍길동아버지1의딸');
insert into parent_child
values(9,6,'개발로짜아들');

위처럼 가족관계에 대한 샘플 데이터를 생성하였습니다.

제일먼저 다루어볼 재귀쿼리는 MSSQL과 오라클 공통적으로 사용이 가능한 문법입니다.

문법은 같으나 살짝 다른 부분이 있어서 두개쿼리 나눠서 작성을 해보았습니다.

MSSQL 재귀호출 쿼리

WITH recursive_query(child,parent,name,sort,dept_name) AS (
  SELECT  
         child
       , parent
       , name
       , convert(varchar(255), child) sort  
  	   , convert(varchar(255), name) dept_name
    FROM parent_child
    WHERE parent = 0
    UNION ALL 
    SELECT
          b.child
        , b.parent
        , b.name
        , convert(varchar(255), convert(nvarchar,c.sort) + ' > ' +  convert(varchar(255), b.child)) sort
        , convert(varchar(255), convert(nvarchar,c.dept_name) + ' > ' +  convert(varchar(255), b.name)) dept_name
    FROM  parent_child b, recursive_query c
    WHERE b.parent = c.child
) 
SELECT name, dept_name FROM recursive_query order by sort

ORACLE 재귀호출 쿼리(1)

WITH recursive_query(child,parent,name,sort,dept_name) AS (
  SELECT  
         child
       , parent
       , name
       , ''||child sort  
  	   , ''||name dept_name
    FROM parent_child
    WHERE parent = 0
    UNION ALL 
    SELECT
          b.child
        , b.parent
        , b.name
        , c.sort || ' > ' ||  b.child sort
        , c.dept_name || ' > ' ||  b.name dept_name
    FROM  parent_child b, recursive_query c
    WHERE b.parent = c.child
) 
SELECT name, dept_name FROM recursive_query order by sort

위처럼 MSSQL과 Oracle의 문법은 동일하지만 

문자열 표시의 합치는 부분의 차이로 인해서 쿼리를 두개로 나누었습니다.

또다른 차이점이라고 하면 차이일수 있겠지만

 WITH recrusive_query다음에 괄호부분의 컬럼지정하는 문법은

 Oracle에서는 필수지만 MSSQL에서는 제외하셔도 무방합니다.

오라클에는 추가로 재귀쿼리를 지원합니다 

ORACLE 재귀호출쿼리(2)

-- name 컬럼만 출력해도 되지만 연결점을 확인하기 위한 LTRIM , SYS_CONNECT_BY_PATH를 사용해보았음
SELECT name, LTRIM (SYS_CONNECT_BY_PATH (name, ' > '), ' > ') AS dept_name
FROM parent_child 
-- 시작지점
START WITH parent = 0
-- 트리구조로 연결할 부모와 자식간의 아이디값
CONNECT BY PRIOR child=parent

오라클의 경우 호출쿼리(2) 쿼리가 간단하니 

START WITH CONNECT BY문을 사용하시는게 나으실듯..

재귀쿼리 실행결과

3가지 재귀쿼리코드를 작성했었는데

결과는 모두 동일할것입니다 ^^

by 개발로짜