Oracle - 계층형 쿼리

생성

CREATE TABLE MENU(
ID				NUMBER PRIMARY KEY,
PARENT_ID			NUMBER , 
NAME				VARCHAR2(20),
ORD				NUMBER,
ICON				VARCHAR2(50)
)

 

 

 

 

부모 추가

-- 부모
INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD,
	ICON
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	0,
	'설정',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 0),
	'fas fa-fw fa-wrench'
)


INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD,
	ICON
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	0,
	'임직원',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 0),
	'fas fa-fw fa-cog'
)

INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD,
	ICON
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	0,
	'게시판',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 0),
	'fas fa-fw fa-clipboard'
)

 

 

 

 

자식

-- 자식
INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	1,
	'권한 및 메뉴',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 1)
)



INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	2,
	'직원 정보',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 2)
)


INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	2,
	'사내 일정',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 2)
)



INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	2,
	'결재 신청',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 2)
)


INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	2,
	'결재',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 2)
)



INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	3,
	'공지사항',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 3)
)


INSERT INTO MENU(
	ID,
	PARENT_ID,
	NAME,
	ORD
)VALUES(
	(SELECT NVL(MAX(ID), 0) +1 AS ID FROM MENU),
	3,
	'사내 게시판',
	(SELECT NVL(MAX(ORD), 0) +1 AS ORD FROM MENU WHERE PARENT_ID = 3)
)

 

 

 

계층형 쿼리

SELECT 
	ID,
	PARENT_ID,
	NAME,
	ORD,
	ICON
FROM MENU
START WITH PARENT_ID = 0		-- 시작할 부모 값
CONNECT BY PRIOR  ID = PARENT_ID	-- 본인의 ID와 부모의 값
ORDER SIBLINGS BY ORD;

 

 

 

TEST