본문 바로가기
개발/Oracle

Oracle - 계층형 쿼리

by 코딩하는 흰둥이 2024. 7. 31.
생성
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

 

'개발 > Oracle' 카테고리의 다른 글

Oracle - NVL , NVL2 함수 사용  (0) 2024.09.26
Oracle - INTERSECT  (0) 2023.03.23
[DBeaver] Oracle - LEFT , RIGHT Join  (4) 2023.03.19
[DBeaver] Oracle - Join  (2) 2023.03.19
[DBeaver] Oracle - Update 수정  (0) 2023.03.19

댓글