Oracle SQL - 계층형 쿼리

Overview

JPA reply ( review 리뷰 )  Oracle sql 문

1. 계층형 쿼리 문서
2. 테이블
3. 리뷰 조회 , 저장 , 삭제 예제

문서 

https://yunamom.tistory.com/247

[Oracle] 계층형 쿼리 이해하기 (설명/예제)

 계층형 쿼리란 무엇인가? 

한 테이블에 담겨 있는 여러 레코드들이 서로 상하 관계(부모, 자식) 관계를 이루며 존재할 때, 

이 관계에 따라 레코드를 hierarchical(상하위) 한 구조로 가져올 때 사용되는 SQL을 의미합니다.

구분	설명
LEVEL	- 현재 테이블에는 존재하지 않는 컬럼 - 오라클의 모든 SQL에서 사용할수 있는 것으로 해당 데이터가 몇 번째 단계이냐를 의미함
START WITH	- 어디부터 시작할 것인지를 정함 ( 최상위 레코드 )
CONNECT BY PRIOR	- 계층 구조에서 각 행의 연결 관계를 설정 - EMP 테이블에서 EMPNO, MGR 같은
PRIOR	- 어디부터 시작할 것인지를 정해 준다.

✨START WITH  

- 계층 질의의 루트(부모행)로 사용될 행을 지정한다.

- 서브 쿼리를 사용할 수도 있다.

START WITH 구문에서는 어떤 레코드를 최상위 레코드로 잡을지 지정한다.

✨CONNECT_BY_ROOT  

계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있습니다.

-PRIOR연산자와 함께 사용하여 계층구조로 표현할 수 있습니다.

-CONNECT BY PRIOR자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리 구성 (Top Down)

-CONNECT BY PRIOR부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)

SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ROOT  empno "Root empno", level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
 

ENAME                    EMPNO  Root empno     LEVEL
------------------     ------- -----------   -------
KING                      7839    7839           1
    JONES                 7566    7839           2
        SCOTT             7788    7839           3
            ADAMS         7876    7839           4
        FORD              7902    7839           3
            SMITH         7369    7839           4

 

계층형 구조 (하향식)

✨CONNECT_BY_ISLEAF  

계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환합니다. 최하위 레벨이면 1, 아니면 0

SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
       CONNECT_BY_ISLEAF "leaf", level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr;
 
 
ENAME                     EMPNO       leaf      LEVEL
-------------------- ---------- ---------- ----------
KING                       7839          0          1
    JONES                  7566          0          2
        SCOTT              7788          0          3
            ADAMS          7876          1          4
        FORD               7902          1          3
    BLAKE                  7698          0          2
        MARTIN             7654          1          3
        TURNER             7844          1          3
        JAMES              7900          1          3
    CLARK                  7782          0          2
        MILLER             7934          1          3

✨SYS_CONNECT_BY_PATH  

계층구조 쿼리에서 현재 로우 까지의 PATH 정보를 가져올수 있습니다.

-- 사이즈조절
COL PATH FORMAT A40 
 
 
-- SYS_CONNECT_BY_PATH 예제
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
       SYS_CONNECT_BY_PATH(ename, '/') "PATH"
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
 
 
ENAME                     EMPNO PATH
-------------------- ---------- ---------------------------
KING                       7839 /KING
    JONES                  7566 /KING/JONES
        SCOTT              7788 /KING/JONES/SCOTT
            ADAMS          7876 /KING/JONES/SCOTT/ADAMS
        FORD               7902 /KING/JONES/FORD
    BLAKE                  7698 /KING/BLAKE
        MARTIN             7654 /KING/BLAKE/MARTIN
        TURNER             7844 /KING/BLAKE/TURNER
        JAMES              7900 /KING/BLAKE/JAMES
    CLARK                  7782 /KING/CLARK
        MILLER             7934 /KING/CLARK/MILLER

Leaf Node(최하위, 맨 마지막 끝) 의 전체 PATH 정보를 출력

SELECT LEVEL, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) path
  FROM emp
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
 
 
    LEVEL PATH
--------- -------------------------
        4 KING,JONES,SCOTT,ADAMS
        3 KING,JONES,FORD
        3 KING,BLAKE,MARTIN
        3 KING,BLAKE,TURNER
        3 KING,BLAKE,JAMES
        3 KING,CLARK,MILLER

✨ORDER SIBLINGS BY  

계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해줍니다.

COL ename FORMAT A25 
COL ename2 FORMAT A10
 
 
-- ORDER SIBLINGS BY 예
-- 정렬이 정상적으로 수행된 것을 확인
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, 
       ename ename2, empno, level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr
 ORDER SIBLINGS BY ename2;
 
 
ENAME                ENAME2          EMPNO      LEVEL
-------------------- ---------- ---------- ----------
KING                 KING             7839          1
    BLAKE            BLAKE            7698          2
        JAMES        JAMES            7900          3
        MARTIN       MARTIN           7654          3
        TURNER       TURNER           7844          3
    CLARK            CLARK            7782          2
        MILLER       MILLER           7934          3
    JONES            JONES            7566          2
        FORD         FORD             7902          3
        SCOTT        SCOTT            7788          3
            ADAMS    ADAMS            7876          4
 
 
 
 
-- ORDER BY 예
-- 정렬이 이상하게 수행된 것을 확인
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, 
       ename ename2, empno, level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr
 ORDER BY ename2;
 
 
ENAME                ENAME2          EMPNO      LEVEL
-------------------- ---------- ---------- ----------
            ADAMS    ADAMS            7876          4
    BLAKE            BLAKE            7698          2
    CLARK            CLARK            7782          2
        FORD         FORD             7902          3
        JAMES        JAMES            7900          3
    JONES            JONES            7566          2
KING                 KING             7839          1
        MARTIN       MARTIN           7654          3
        MILLER       MILLER           7934          3
        SCOTT        SCOTT            7788          3
        TURNER       TURNER           7844          3

 

---

1. 테이블

-- 답변형 게시판
CREATE TABLE TB_REPLY_BOARD
(
    BID           NUMBER NOT NULL
        CONSTRAINT PK_REPLY_BOARD PRIMARY KEY, -- 게시판번호
    BOARD_TITLE   VARCHAR2(256),               -- 제목
    BOARD_CONTENT VARCHAR2(255),               -- 내용
    BOARD_WRITER  VARCHAR2(255),               -- 작성자
    VIEW_CNT      NUMBER DEFAULT 0,            -- 조회수
    BOARD_GROUP   NUMBER,                      -- 트리구조 최상위 부모 노드( 부모가 있을 경우 : 부모번호, 없을 경우 : 자신의 게시판번호 )
    BOARD_PARENT  NUMBER,                      -- 자신의 부모 노드 ( 부모가 있을 경우 : 부모번호, 없을 경우 : 0 )
    DELETE_YN     VARCHAR2(1) DEFAULT 'N',
    INSERT_TIME   VARCHAR2(255),
    UPDATE_TIME   VARCHAR2(255),
    DELETE_TIME   VARCHAR2(255)
);

 

2. 전체 조회

SELECT BID AS bid 
, LPAD('▶', (LEVEL-1))|| board_title   AS BoardTitle
, board_content AS boardContent
, board_writer  AS boardWriter
, view_cnt      AS viewCnt
, board_group   AS boardGroup
, board_parent  AS boardParent
FROM TB_REPLY_BOARD
WHERE BOARD_TITLE LIKE '%'|| :boardTitle ||'%'
AND   DELETE_YN = 'N'
START WITH BOARD_PARENT = 0
CONNECT BY PRIOR BID = BOARD_PARENT
ORDER SIBLINGS BY BOARD_GROUP DESC

 

3. 게시물 저장

INSERT INTO TB_REPLY_BOARD
VALUES(sq_reply_board.nextval
, :#{#replyBoard.boardTitle}, 
:#{#replyBoard.boardContent},
:#{#replyBoard.boardWriter},
0, 
sq_reply_board.CURRVAL, 0, 'N',
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), NULL, NULL)

 

4. 게시물 + 답변글 2개 삭제 

UPDATE TB_REPLY_BOARD
SET DELETE_YN = 'Y'
,DELETE_TIME = TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
WHERE BOARD_GROUP = :boardGroup

 

 

5. JPA 예제

@Repository
public interface ReplyBoardRepository extends JpaRepository<ReplyBoard, Integer> {
    //    계층형 조회(특수) 쿼리 : @Query(, nativeQuery=true)
    @Query(value = "SELECT BID           AS bid " +
            "     , LPAD('▶', (LEVEL-1))|| board_title   AS BoardTitle " +
            "     , board_content AS boardContent " +
            "     , board_writer  AS boardWriter " +
            "     , view_cnt      AS viewCnt " +
            "     , board_group   AS boardGroup " +
            "     , board_parent  AS boardParent " +
            "FROM TB_REPLY_BOARD " +
            "WHERE BOARD_TITLE LIKE '%'|| :boardTitle ||'%' " +
            "AND   DELETE_YN = 'N' " +
            "START WITH BOARD_PARENT = 0    " +
            "CONNECT BY PRIOR BID = BOARD_PARENT  " +
            "ORDER SIBLINGS BY BOARD_GROUP DESC", nativeQuery = true)
    Page<ReplyBoardDto> selectByConnectByPage(
            @Param("boardTitle") String boardTitle,
            Pageable pageable);

    // 게시물 저장함수 : 최초 생성(board_group(그룹번호)), board_parent(부모번호))
    //  => board_group(부모번호 == 자식번호(bid)), board_parent(최초생성시 0, 댓글이 달리면 부모 번호가 들어감)
    // todo : JPA insert문 직접 작성(DML : 테이블 데이터 변경, 트랜잭션을 동반)
    //  =>  @Transactional, @Modifying
    //  => 예 ) 변수 전달  : :#{#replyBoard.boardTitle}
    @Transactional
    @Modifying
    @Query(value = "INSERT INTO TB_REPLY_BOARD " +
            "VALUES(sq_reply_board.nextval, :#{#replyBoard.boardTitle}, " +
            ":#{#replyBoard.boardContent}, " +
            ":#{#replyBoard.boardWriter}, " +
            "0, sq_reply_board.CURRVAL, 0, 'N', " +
            "        TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), NULL, NULL)", nativeQuery = true)
    int insertByBoard(@Param("replyBoard") ReplyBoard replyBoard);

    // 게시물 + 답변글 2개 삭제 함수 : 소프트 삭제 (update 문 직접 작성)
    @Transactional
    @Modifying
    @Query(value ="UPDATE TB_REPLY_BOARD " +
            "SET DELETE_YN = 'Y' " +
            "    ,DELETE_TIME = TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') " +
            "WHERE BOARD_GROUP = :boardGroup", nativeQuery = true)
    int removeAllByBoardGroup(@Param("boardGroup") int boardGroup);
}