15.계층형쿼리
/*
<계층형 쿼리>
노드 : 계층형쿼리를 이루고 있는 항목
루트노트 : 계층형 트리구조의 최상위 노드
리프노드 : 하위노드가 없는 항목
부모노드 : 트리구조의 상위에 있는 노드
자식노드 : 트리구조의 하위에 있는 노드
레벨 : 루트노드의 레벨을 1로 시작하여 자식레벨로 넘어갈수록 1씩 증가한다
- 상위코드와 하위코드를 이용하여 TREE형태로 조회 ex) 회사 조직도, 메뉴
0. LEVEL : 계층형 구조의 DEPTH(깊이)를 반환
1. 계층형 쿼리의 정렬
ODER SIBLINGS BY 컬럼명 - 계층형 쿼리가 깨지지 않고 정렬
2. 최상위 노드 반환
CONNECT_BY_ROOT 컬럼명
3. 최하위 노드이면 1, 아니면 0
CONNECT_BY_ISLEAF
4. 계층형 쿼리에서 루트노드로 시작해 자신의 행까지 연결된 경로를 반환
SYS_CONNECT_BY_PATH(컬럼명, '구분자')
- 회사 조직도 -
MANAGER_ID : 상관의 ID (null을 루트노드로 보기로함)
루트노드 : 최상위 노트
LEVEL : 계층구조 쿼리의 수행결과 DEPTH를 표현하는 의사컬럼(ROOT=1)
의사컬럼 : 테이블의 컬럼은 아니지만 컬럼처럼 동작
*/
SELECT emp_id, emp_name, LEVEL, LPAD(' ',(LEVEL-1)*3)||emp_name 계층구조
, CONNECT_BY_ROOT emp_name AS LOOTNAME-- 최상위 노드의 컬럼을 반환
, CONNECT_BY_ISLEAF AS ISLEAF-- 최하위노드이면 1, 아니면 0
, SYS_CONNECT_BY_PATH(emp_name, '|')
, SYS_CONNECT_BY_PATH(job_name, '|')-- 계층형 쿼리에서 루트노드~자신까지 연결된 형태로 반환
FROM Emp
JOIN job USING(job_code)
-- 루트노드의 조건 또는 값
START WITH manager_id IS NULL -- 조직도의 시작이 누구인지 지정해줌
CONNECT BY PRIOR emp_id = manager_id -- CONNECT BY (상위노드의) emp_id = (자식노드의)manager_id 계층관계를 명시
ORDER SIBLINGS BY emp_name; -- 값 레벨별로 정렬
SELECT emp_id, emp_name, LPAD(' ', (LEVEL-1)*3)||job_name AS 계층구조, LPAD(' ', (LEVEL-1)*3)||dept_title AS 계층구조1, SYS_CONNECT_BY_PATH(job_name, '|')-- 계층형 쿼리에서 루트노드~자신까지 연결된 형태로 반환
FROM Emp
LEFT JOIN job USING(job_code)
LEFT JOIN dept ON(dept_code = dept_id)
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;
/*
emp_id : 사원번호
manager_id : 사원의 매니저 번호
*/
SELECT manager_id AS 매니저번호, emp_id AS 사원번호, emp_name AS 사원명, LPAD(' ', (LEVEL-1)*3)||emp_name, LPAD(' ', (LEVEL-1)*3)||dept_title,
CONNECT_BY_ROOT dept_title, SYS_CONNECT_BY_PATH(emp_name, '>') AS INFO, CONNECT_BY_ISLEAF AS 리프
FROM emp, dept
-- 1. 루트노트의 조건 또는 값을 지정
WHERE dept_code = dept_id
START WITH manager_id IS NULL
-- 2. 계층관계를 명시
-- CONNECT BY PRIOR (상위노드의)emp_id = (자식노드의)manager_id
CONNECT BY PRIOR emp_id = manager_id;
-- 메뉴테이블 생성
CREATE TABLE MENU (
menu_id VARCHAR2(50) PRIMARY KEY,
up_menu_id VARCHAR2(50),
title VARCHAR2(50),
url VARCHAR2(50),
sort NUMBER(2,0),
visible CHAR(1)
);
-- id up title url sort visible
INSERT INTO menu VALUES('m01', '', '대메뉴1','url1','0', '1');
INSERT INTO menu VALUES('m01_01', 'm01', '중메뉴1','url1-1', '0', '1');
INSERT INTO menu VALUES('m01_01_01', 'm01_01', '소메뉴1','url1-1-1', '0', '1');
INSERT INTO menu VALUES('m02', '', '대메뉴2', 'url2', '0', '1');
INSERT INTO menu VALUES('m02_01', 'm02', '2중메뉴1','url2-1' , '0', '1');
INSERT INTO menu VALUES('m02_02', 'm02', '2중메뉴2','url2-2' ,'0', '1');
INSERT INTO menu VALUES('m03', '', '대메뉴3','url3', '0', '1');
INSERT INTO menu VALUES('m03_01', 'm03', '3중메뉴1','url3-1', '0', '1');
INSERT INTO menu VALUES('m03_01_01', 'm03_01', '3소메뉴1','url3-1-1', '0', '1');
INSERT INTO menu VALUES('m03_01_01_01', 'm03_01_01', '3메뉴1','url3-1-1-1', '0', '1');
commit;
SELECT menu_id,SYS_CONNECT_BY_PATH(title,'>')
FROM menu
START WITH up_menu_id IS NULL
CONNECT BY PRIOR menu_id = up_menu_id ;