오라클 - 계층형 쿼리의 응용

[참고] 관련 포스트

[오라클] 계층형 쿼리 ( START WITH ... CONNECT BY )

계층형 쿼리를 이용한 샘플예제입니다. 실제 프로젝트에서 사용하기위한 쿼리로 적합하지 않습니다.

CREATE TABLE TEST_BOARDS (

ID NUMBER(5) NOT NULL, -- ID

P_ID NUMBER(5) NULL, -- 상위 ID값

SUBJECT VARCHAR2(30) NOT NULL, -- 게시판 제목

CONTENT VARCHAR2(100) NULL, -- 내용

CREATE_DATE DATE DEFAULT SYSDATE); -- 생성일자

-- 샘플 데이터

INSERT INTO TEST_BOARDS VALUES (1, NULL, '테스트1...','테스트합니다',SYSDATE);

INSERT INTO TEST_BOARDS VALUES (2, NULL, '테스트2...','테스트합니다',SYSDATE);

INSERT INTO TEST_BOARDS VALUES (3, 1, '답변글 테스트1...','답변글 테스트합니다',SYSDATE);

INSERT INTO TEST_BOARDS VALUES (4, 3, '답변글 테스트2...','답변글 테스트합니다',SYSDATE);

INSERT INTO TEST_BOARDS VALUES (5, 1, '답변글 테스트1...','답변글 테스트합니다',SYSDATE);

INSERT INTO TEST_BOARDS VALUES (6, 2, '공부해요!','재미있게 공부해봅시다.',SYSDATE);

1	SELECT * FROM TEST_BOARDS;

-- 계층형 쿼리 이용

SELECT ID, ROWNUM,

LPAD(' ', 2* (LEVEL-1)) || SUBJECT

FROM TEST_BOARDS

START WITH P_ID IS NULL

CONNECT BY PRIOR ID = P_ID;

-- 리프노드 찾기 ( CONNECT_BY_ISLEAF )

-- 계층형 쿼리에서 해당 로우가 리프노드인지(자식노드가 없는노드인지)

-- 여부를 체크하여 리프노트에 해당할 경우 1을 그렇지 않을경우 0을 반환

-- 해당 게시글의 상위 게시내용은 '[상위글]' 문자를 붙여 표현하였고 실제 클릭했던

-- 게시판 항목인 ID값이 3인 항목의 게시 내용이 그 아래에 조회

SELECT ID, DECODE(CONNECT_BY_ISLEAF, 1, ' '||CONTENT,'[상위글] '||CONTENT||CHR(13)) CONTENTS

FROM TEST_BOARDS

WHERE CONNECT_BY_ROOT ID = 1 -- 루트노드의 ID의 값이 1인것만 조회

START WITH P_ID IS NULL

CONNECT BY PRIOR ID = P_ID

AND ID = 3 -- 클릭한 게시판 항목의 ID값 ( ID값이 3인 항목과 이 항목의 상위 노드를 추출)

ORDER BY LEVEL;

[추가]

계층형 쿼리에서는

제일먼전 START WITH절이 수행

그 다음에 CONNECT BY 절을 처리

그 다음에 WHERE 절

Colored By Color Scripter™

-- CONNECT BY 절에 AND 조건을 추가하게 되면

-- 클릭한 게시판 항목의 ID값 ( ID값이 3인 항목과 이 항목의 상위 노드를 추출)

-- 또한 P_ID 값이 NULL인 루트노드가 모두 추출된다.

SELECT ID, P_ID, SUBJECT

FROM TEST_BOARDS

START WITH P_ID IS NULL

CONNECT BY PRIOR ID = P_ID

AND ID = 3;

계층형 쿼리의 응용 - 답변형 계시판

단축키

Articles