oracle 계층형 쿼리

테이블생성

CREATE TABLE BOM (
    ITEM_ID        INTEGER NOT NULL,
    PARENT_ID    INTEGER,
    ITEM_NAME   VARCHAR2(20) NOT NULL,
    ITEM_QTY     INTEGER,
    PRIMARY KEY (ITEM_ID));

데이터 저장

INSERT INTO BOM VALUES (1001, NULL, '컴퓨터', 1);
INSERT INTO BOM VALUES (1002, 1001, '본체', 1);
INSERT INTO BOM VALUES (1003, 1001, '모니터', 1);
INSERT INTO BOM VALUES (1004, 1001, '프린터', 1);
INSERT INTO BOM VALUES (1005, 1002, 'MOTHER BOARD', 1);
INSERT INTO BOM VALUES (1006, 1002, '랜카드', 1);
INSERT INTO BOM VALUES (1007, 1002, 'POWER SUPPLY', 1);
INSERT INTO BOM VALUES (1008, 1005, 'RAN', 1);
INSERT INTO BOM VALUES (1009, 1005, 'CPU', 1);
INSERT INTO BOM VALUES (1010, 1005, '그래픽장치', 1);
INSERT INTO BOM VALUES (1011, 1005, '기타장치', 1);

계층형쿼리

SELECT  ITEM_ID
      , PARENT_ID
      , LEVEL
      , CONNECT_BY_ISLEAF AS LEAFS
      , LPAD(' ', 2*(LEVEL-1)) || ITEM_NAME AS ITEM_NAMES
      , SYS_CONNECT_BY_PATH(ITEM_NAME, '/') AS NAME_PATH
      , CONNECT_BY_ROOT  ITEM_ID
  FROM BOM
 START WITH PARENT_ID IS NULL
CONNECT BY NOCYCLE PRIOR  ITEM_ID = PARENT_ID;

관련링크

1. https://blog.naver.com/deimia0923/60118755129