계층구조 테이블의 Query

계층 구조의 테이블을 SELECT하는 방법은 일반 테이블의 SELECT 방법과 조금 다르게
이루어 집니다. 계층 구조의 모델링을 Recusive Referential(회귀적 참조)라고도 합니다.
그럼 Table을 만들고 데이터를 넣은 다음에 SELECT 해 보겠습니다. 데이터는 11번가의
분류를 참조 했습니다. (절대 PR하기 위한 건 아닙니다.ㅋㅋㅋ)

-- 테이블 생성
CREATE TABLE CATEGORY
(
  CATEGORY_ID      NUMBER(6)                    NOT NULL,
  CATEGORY_NM      VARCHAR2(60 BYTE),
  PAR_CATEGORY_ID  NUMBER(6)
);

-- PK
ALTER TABLE CATEGORY ADD (
  CONSTRAINT PK_CATEGORY
 PRIMARY KEY
 (CATEGORY_ID)
    USING INDEX);

-- FK
ALTER TABLE CATEGORY
ADD CONSTRAINTS FK_CATEGORY FOREIGN KEY (PAR_CATEGORY_ID)
REFERENCES CATEGORY(CATEGORY_ID);

-- DATA INSERT
INSERT INTO CATEGORY VALUES ('100001','카테고리최상위','');

INSERT INTO CATEGORY VALUES ('100002','브랜드패션','100001');

INSERT INTO CATEGORY VALUES ('100003','의류','100001');

INSERT INTO CATEGORY VALUES ('100004','잡화/뷰티','100001');

INSERT INTO CATEGORY VALUES ('100005','식품/유아동','100001');

INSERT INTO CATEGORY VALUES ('100006','리빙/건강','100001');

INSERT INTO CATEGORY VALUES ('100007','레저/자동차','100001');

INSERT INTO CATEGORY VALUES ('100008','디지털/가전','100001');

INSERT INTO CATEGORY VALUES ('100009','도서/잡화','100001');

INSERT INTO CATEGORY VALUES ('100010','브랜드 여성의류/언더웨어','100002');

INSERT INTO CATEGORY VALUES ('100011','브랜드 남성의류/언더웨어','100002');

INSERT INTO CATEGORY VALUES ('100012','브랜드 캐주얼의류','100002');

INSERT INTO CATEGORY VALUES ('100013','TV/냉장고/세탁기','100008');

INSERT INTO CATEGORY VALUES ('100014','주방/이미용/생활가전','100008');

INSERT INTO CATEGORY VALUES ('100015','전기매트/가습기/난방가전','100008');

INSERT INTO CATEGORY VALUES ('100016','DSLR/디카/액세서리','100008');

INSERT INTO CATEGORY VALUES ('100017','MP3/AV/음향가전','100008');

INSERT INTO CATEGORY VALUES ('100018','노트북/데스크탑','100008');

INSERT INTO CATEGORY VALUES ('100019','모니터/프린터/잉크','100008');

INSERT INTO CATEGORY VALUES ('100020','PC부품/주변기기','100008');

INSERT INTO CATEGORY VALUES ('100021','저장장치/스마트패드/게임','100008');

INSERT INTO CATEGORY VALUES ('100022','휴대폰/액세서리','100008');

INSERT INTO CATEGORY VALUES ('100023','LED TV','100013');

INSERT INTO CATEGORY VALUES ('100024','3D TV','100013');

INSERT INTO CATEGORY VALUES ('100025','LCD TV','100013');

INSERT INTO CATEGORY VALUES ('100026','PDP TV','100013');

INSERT INTO CATEGORY VALUES ('100027','브라운관 TV','100013');

INSERT INTO CATEGORY VALUES ('100028','TV 주변기기','100013');

INSERT INTO CATEGORY VALUES ('100029','양문형냉장고','100013');

INSERT INTO CATEGORY VALUES ('100030','일반형냉장고','100013');

INSERT INTO CATEGORY VALUES ('100031','김치냉장고','100013');

INSERT INTO CATEGORY VALUES ('100032','기능성냉장고','100013');

INSERT INTO CATEGORY VALUES ('100033','드럼세탁기','100013');

INSERT INTO CATEGORY VALUES ('100035','삼성전자 매장','100013');

INSERT INTO CATEGORY VALUES ('100036','리퍼/반품/전시','100013');

INSERT INTO CATEGORY VALUES ('100037','렌탈서비스','100013');

INSERT INTO CATEGORY VALUES ('100038','삼성전자','100023');

INSERT INTO CATEGORY VALUES ('100039','LG전자','100023');

INSERT INTO CATEGORY VALUES ('100040','브랜드기타','100023');

INSERT INTO CATEGORY VALUES ('100041','삼성전자','100024');

INSERT INTO CATEGORY VALUES ('100042','LG전자','100024');

INSERT INTO CATEGORY VALUES ('100043','소니','100024');

INSERT INTO CATEGORY VALUES ('100044','노트북','100018');

INSERT INTO CATEGORY VALUES ('100045','브랜드PC','100018');

INSERT INTO CATEGORY VALUES ('100046','조립PC본체','100018');

INSERT INTO CATEGORY VALUES ('100047','조립PC세트','100018');

INSERT INTO CATEGORY VALUES ('100048','태블릿PC','100018');

INSERT INTO CATEGORY VALUES ('100049','중고/리퍼/반품/전시','100018');

INSERT INTO CATEGORY VALUES ('100050','렌탈서비스','100018');

INSERT INTO CATEGORY VALUES ('100051','노트북액세서리','100018');

INSERT INTO CATEGORY VALUES ('100052','삼성전자','100044');

INSERT INTO CATEGORY VALUES ('100053','애플','100044');

INSERT INTO CATEGORY VALUES ('100054','한성','100044');

INSERT INTO CATEGORY VALUES ('100055','코어i7','100052');

INSERT INTO CATEGORY VALUES ('100056','코어i5','100052');

INSERT INTO CATEGORY VALUES ('100057','코어i3','100052');

INSERT INTO CATEGORY VALUES ('100058','코어2듀오','100052');

INSERT INTO CATEGORY VALUES ('100059','AMD','100052');

INSERT INTO CATEGORY VALUES ('100060','i7','100054');

INSERT INTO CATEGORY VALUES ('100061','i5','100054');

INSERT INTO CATEGORY VALUES ('100062','펜티엄 이하','100054');

INSERT INTO CATEGORY VALUES ('100063','CPU','100020');

INSERT INTO CATEGORY VALUES ('100064','메인보드','100020');

INSERT INTO CATEGORY VALUES ('100065','데스크탑용 메모리(RAM)','100020');

INSERT INTO CATEGORY VALUES ('100066','노트북용 메모리(RAM)','100020');

INSERT INTO CATEGORY VALUES ('100067','기타PC부품','100020');

INSERT INTO CATEGORY VALUES ('100068','코어i7','100063');

INSERT INTO CATEGORY VALUES ('100069','코어i5','100063');

INSERT INTO CATEGORY VALUES ('100070','코어i3','100063');

INSERT INTO CATEGORY VALUES ('100071','코어i3/i5/i7','100064');

INSERT INTO CATEGORY VALUES ('100072','코어2듀오이상','100064');

INSERT INTO CATEGORY VALUES ('100073','스포츠의류/운동화/용품','100007');

INSERT INTO CATEGORY VALUES ('100074','등산/아웃도어/캠핑/낚시','100007');

INSERT INTO CATEGORY VALUES ('100075','바다낚시장비','100074');

INSERT INTO CATEGORY VALUES ('100076','낚시대/뜰채','100075');

INSERT INTO CATEGORY VALUES ('100077','바다 낚시 세트','100075');

INSERT INTO CATEGORY VALUES ('100078','바늘류','100075');

INSERT INTO CATEGORY VALUES ('100079','갯바위릴대','100076');

INSERT INTO CATEGORY VALUES ('100080','바다루어대','100076');

INSERT INTO CATEGORY VALUES ('100081','우럭/지깅 선상대','100076');

SELECT *
  FROM CATEGORY;

-- 이제 계층 구조 SELECT 입니다.
SELECT LEVEL,LPAD(' ', 2*LEVEL-1)||CATEGORY_ID||' '||CATEGORY_NM
  FROM CATEGORY A
START WITH PAR_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PAR_CATEGORY_ID
ORDER BY LEVEL, CATEGORY_ID;
위 는 방향성이 위에서 아래 쪽입니다.
반대로 아래에서 위 쪽으로 찾아 갑니다.

SELECT LEVEL,LPAD(' ', 2*LEVEL-1)||CATEGORY_ID||' '||CATEGORY_NM
  FROM CATEGORY A
START WITH CATEGORY_ID = 100055
CONNECT BY PRIOR PAR_CATEGORY_ID = CATEGORY_ID;
START WITH 문과 COONECT BY 문을 첫번째 QUERY문과 비교해 보십시요.

데이터의 갯수가 많으면 자신의 부모를 찾기 힘듦니다.
그럴때 자신의 부모가 보이면 좋겠지요. 그런 함수가 있습니다.

SELECT LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(CATEGORY_NM,'-') PATH
  FROM CATEGORY A
START WITH PAR_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PAR_CATEGORY_ID
ORDER BY LEVEL, CATEGORY_ID;
결과를 보시면 아실겁니다.
그럼 참고하시고...
감사합니다.