SQL BOM 계층형쿼리

BOM(BILL OF MATERIAL : 계층형쿼리)

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, '메인보드', 1);
INSERT INTO BOM VALUES ( 1006, 1002, '랜카드', 1);
INSERT INTO BOM VALUES ( 1007, 1002, '파워서플라이', 1);
INSERT INTO BOM VALUES ( 1008, 1005, 'CPU', 1);
INSERT INTO BOM VALUES ( 1009, 1005, 'RAM', 1);
INSERT INTO BOM VALUES ( 1010, 1005, '그래픽카드', 1);
INSERT INTO BOM VALUES ( 1011, 1005, '기타장치', 1);

COMMIT;

SELECT * FROM BOM;

SELECT BOM1.ITEM_NAME, BOM1.ITEM_ID, BOM2.ITEM_NAME PARENT_ITEM
FROM BOM BOM1, BOM BOM2
WHERE BOM1.PARENT_ID = BOM2.ITEM_ID(+)
ORDER BY BOM1.ITEM_ID;

---

계층쿼리형 문법

-- 1)
SELECT ITEM_NAME, ITEM_ID, PARENT_ID
FROM BOM
START WITH PARENT_ID IS NULL -- 계층형구조에서 루트(부모행)을 지정
CONNECT BY PRIOR ITEM_ID = PARENT_ID; -- 1) PRIOR 자식컬럼 = 부모컬럼. 부모에서 자식으로 트리 구성(TOP DOWN)

-- 2)루트의 부모가 없기 때문에 자신만 출력
SELECT ITEM_NAME, ITEM_ID, PARENT_ID
FROM BOM
START WITH PARENT_ID IS NULL -- 계층형구조에서 루트(부모행)을 지정
CONNECT BY ITEM_ID = PRIOR PARENT_ID; -- 자식컬럼 = PRIOR 부모컬럼. 자식에서 부모로 트리 구성(BOTTOM UP)

-- 3)본체를 루트(부모행)으로 부모 -> 자식 트리구성
SELECT ITEM_NAME, ITEM_ID, PARENT_ID
FROM BOM
START WITH ITEM_ID = 1002 -- 루트를 1002로 설정
CONNECT BY PRIOR ITEM_ID = PARENT_ID;

-- 4)랜카드를 자식으로 부모 트리 구성
SELECT ITEM_NAME, ITEM_ID, PARENT_ID
FROM BOM
START WITH ITEM_ID = 1006
CONNECT BY ITEM_ID = PRIOR PARENT_ID;

-- 들여쓰기 효과 및 계층형 목록 출력
-- LEVEL 의사컬럼 : 깊이(DEPTH)
-- LPAD(' ', 2*(LEVEL-1)) : ' '빈문자 1길이를 2번째 파라미터수만큼 왼쪽에 채우는 기능
SELECT LPAD(' ', 2*(LEVEL-1)) || ITEM_NAME AS ITEM_NAMES,
        ITEM_ID, 
        PARENT_ID
FROM BOM
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ITEM_ID = PARENT_ID;

---

CONNECT_BY_ROOT

-- CONNECT_BY_ROOT는 계층형 쿼리에서 최상위 로우를 반환하는 연산자다.
-- 연산자이므로 CONNECT_BY_ROOT 다음에는 표현식이 온다.

SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name, LEVEL,
CONNECT_BY_ROOT department_name AS root_name
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR department_id  = parent_id;

---

CONNECT_BY_ISLEAF

-- CONNECT_BY_ISLEAF는 CONNECT BY 조건에 정의된 관계에 따라 해당 로우가 최하위 자식 로우이면 1을
-- 그렇지 않으면 0을 반환하는 의사컬럼
-- LEAF NODE : 자식노드가 없는 노드
-- NON LEAF NODE : 자식노드가 있는 노드

SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name, LEVEL,
CONNECT_BY_ISLEAF
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR department_id  = parent_id;

---

SYS_CONNECT_BY_PATH (colm, char)

-- SYS_CONNECT_BY_PATH는 계층형 쿼리에서만 사용할 수 있는 함수로,
-- 루트 노드에서 시작해 자신의 행까지 연결된 경로 정보를 반환한다.
-- 이 함수의 첫 번째 파라미터로는 컬럼이, 두 번째 파라미터인 char은 컬럼 간 구분자를 의미한다.
-- 그럼 부서에 대한 경로 정보를 추출해 보자.

-- 주의사항 : 두번째 매개변수인 구분자로 해당 컬럼 값에 포함된 문자는 사용할 수 없다는 점을 주의

SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name, LEVEL,
SYS_CONNECT_BY_PATH( department_name, '|')
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR department_id  = parent_id;

---

CONNECT_BY_ISCYCLE : 데이터가 서로 참조를 하여, 무한루프가 발생이 될때 원인 찾을때 사용

-- 루프 알고리즘에서 주의할 점은 조건을 잘못 주면 무한루프를 타게 된다는점
-- 계승형 쿼리에서도 부모-자식 간의 관계를 정의하는 값이 잘못 입력되면 무한루프를 타고 오류가 발생한다

-- 시나리오 : 생산팀(170)의 부모 부서는 구매/생산부(30)인데, 구매/생산부의 parent_id 값을 생산부로 변경
            -- 두 부서가 상호 참조가 되어 무한루프가 발생할 것이다

-- 1) 구매/생산부의 PRRENT_ID 값을 생산부로 변경
UPDATE DEPARTMENTS
    SET PARENT_ID = 170
WHERE DEPARTMENT_ID = 30;

-- 2) 구매/생산부를 [부모(루트) -> 자식] 트리구조 형태를 출력
SELECT DEPARTMENT_ID, LPAD(' ' , 3 * (LEVEL-1)) || DEPARTMENT_NAME, LEVEL, PARENT_ID
FROM DEPARTMENTS
START WITH DEPARTMENT_ID = 30
CONNECT BY PRIOR DEPARTMENT_ID = PARENT_ID; -- CONNECT BY loop in user data

-- 3) 무한루프가 발생된 데이터 찾기
SELECT DEPARTMENT_ID, LPAD(' ' , 3 * (LEVEL-1)) || DEPARTMENT_NAME, LEVEL, CONNECT_BY_ISCYCLE AS ISLOOP, PARENT_ID
FROM DEPARTMENTS
START WITH DEPARTMENT_ID = 30
CONNECT BY NOCYCLE PRIOR DEPARTMENT_ID = PARENT_ID;

-- 4) 원인 데이터 : 수정작업
UPDATE DEPARTMENTS
    SET PARENT_ID = 10
WHERE DEPARTMENT_ID = 30;

-- 5) 쿼리 테스트
SELECT DEPARTMENT_ID, LPAD(' ' , 3 * (LEVEL-1)) || DEPARTMENT_NAME, LEVEL, PARENT_ID
FROM DEPARTMENTS
START WITH DEPARTMENT_ID = 30
CONNECT BY PRIOR DEPARTMENT_ID = PARENT_ID;