생성
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 |
댓글