계층형쿼리

• 리버스 엔지니어링 : 이미 테이블이 만들어져있는 상태에서 관계를 설계하고 erd를 그리는 것

• employee_id 는 같은 manager_id(매니저 또한 employee이므로)를 가질 수 있다.
  -> 자기참조 관계를 가지고 있다.

• 오라클에서는 계층형쿼리를 만들 수 있다.
  

select employee_id, first_name, manager_id
from employees
START WITH manager_id is null  // null 체크는 == 를 쓰지 않는다.
CONNECT BY PRIOR employee_id = manager_id;  // PRIOR : 기준
// 매니저아이디가 null부터 현재 employee_id를 매니저로 삼고있는 애들을 출력해라.

• 현재 행의 employee를 기준으로 같은 manager id를 찾고 있으면 현재행이랑 비교해서 나아가고, 없으면 노드가 끝나고 상위 레벨로 올라가서 데려옴(상위행)

• select 구문 처리 순서
  FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
• sequence를 사용할때 중간에 번호가 껑충 뛰는 경우가 있음. 캐시에 일련번호를 발급을 미리 해놓는데, 사용자가 그걸 다 사용했을 경우 더 발급함.
  • 20개를 다 사용하지 않았는데 db가 강제로 꺼지게 되면 캐시메모리는 사라져버림 ->
  • nocache; 이용

DROP TABLE board;
-- VARCHAR2 최대가 4000이다.
create table board(
board_num NUMBER,
board_id VARCHAR2(10),
board_title VARCHAR2(100),
board_content VARCHAR2(2000),
board_dt DATE,
board_view_cnt NUMBER(3)
);

ALTER TABLE board 
ADD CONSTRAINT board_num_pk PRIMARY KEY(board_num)
ADD CONSTRAINT board_id_fk FOREIGN KEY(board_id) REFERENCES customer (id);

DROP TABLE board_reply;

CREATE TABLE board_reply(
board_reply_num NUMBER, --댓글번호가 PK여야한다
board_num NUMBER, --원글번호 -> PK가 이게 되면 원글에 대한 댓글 하나밖에 못쓴다.
board_reply_id VARCHAR2(10), -- 댓글 작성자 원글번호랑 복합키가 된다면? 원글에 대한 댓글 여러개는 가능하나 댓글작성자가 그 원글에 댓글에 하나만 달 수 있다.
board_reply_content VARCHAR2(100), --댓글 내용 
board_reply_dt DATE DEFAULT SYSDATE, -- 댓글작성일
board_reply_parent_num NUMBER --부모 댓글 번호
);


ALTER TABLE board_reply
ADD CONSTRAINT board_reply_num_pk PRIMARY KEY(board_reply_num)
ADD CONSTRAINT board_num_fk FOREIGN KEY(board_num) REFERENCES board (board_num)
ADD CONSTRAINT board_reply_id_fk FOREIGN KEY(board_reply_id) REFERENCES customer (id);

ALTER TABLE board_reply
ADD CONSTRAINT board_reply_parent_fk FOREIGN KEY(board_reply_parent_num) 
REFERENCES board_reply (board_reply_num);

-----------------------
-- 게시글 추가 
INSERT INTO board(board_num, board_id, board_title, board_content, board_view_cnt)
values(1, '1d1', '글1', '글1내용', 0);

INSERT INTO board(board_num, board_id, board_title, board_content, board_view_cnt)
values(2, 'id1', '글2', '글2내용', 0);

-----------------------
-- 댓글 추가
---- (댓)1 번글에 대한 댓글1
INSERT INTO board_reply(board_reply_num, board_num, board_reply_id, board_reply_content, board_reply_parent_num)
values(1, 1, 'id1', '글1댓글내용1', null);
---- (댓)1 번글에 대한 댓글2
INSERT INTO board_reply(board_reply_num, board_num, board_reply_id, board_reply_content, board_reply_parent_num)
values(2, 1, 'id1', '글1댓글내용2', null);  -- null은 첫 댓글이라는 뜻(대댓글아님) --
---- (대댓)1 번글에 대한 댓글1에대한 대댓글1
INSERT INTO board_reply(board_reply_num, board_num, board_reply_id, board_reply_content, board_reply_parent_num)
values(3, 1, 'id1', '글1댓글내용1대댓1', 1);

commit;

-----------------------------------------------------
select b.board_num, b.board_title, b.board_content, b.board_dt, r.board_reply_num, r.board_reply_content, r.board_reply_dt
from board b join board_reply r on (b.board_num = r.board_num)
order by b.board_dt asc;

select * from board b join board_reply r on (b.board_num = r.board_num)
order by b.board_dt asc;


-- 댓글을 최신순부터 보이도록 --
select level, board_reply.* 
from board_reply
start with board_reply_parent_num is null
connect by prior board_reply_num = board_reply_parent_num
order siblings by board_reply_num desc;
-- level : 계층을 알려주는 pseudo 컬럼, 들여쓰기 같은거 할 때 이용할 수 있음. --

-- 댓글이 있는 글에 대한 댓글들 출력 --
SELECT *
FROM board b full outer JOIN (
    SELECT level, board_num, board_reply_num, board_reply_content, board_reply_parent_num 
    FROM board_reply
    START WITH board_reply_parent_num IS NULL
    CONNECT BY PRIOR   board_reply_num= board_reply_parent_num
    ORDER SIBLINGS BY board_reply_num DESC)r
ON (b.board_num = r.board_num);


---- 게시글 추가 ---
drop sequence board_seq;
create sequence board_seq start with 3 nocache;
-- 캐시에 일련번호를 발급을 미리 해놓는데, 사용자가 그걸 다 사용했을 경우 더 발급함.
-- 20개를 다 사용하지 않았는데 db가 강제로 꺼지게 되면 캐시메모리는 사라져버림 -> sequence를 사용할때 중간에 번호가 껑충 뛰는 경우가 있음.
-- nocache; 이용

drop sequence board_rep_seq;
create sequence board_rep_seq start with 4 nocache;


insert into board(board_num, board_id, board_title, board_content, board_view_cnt)
values (board_seq.NEXTVAL, 'id1', '글 테스트', '내용 테스트', 0);
rollback; -- 롤백시에 sequence는 돌아가지 않음(롤백안됨), 테이블 객체만 롤백(트랜잭션에 해당, sequence는 비해당) --

-- 잘못된 sql구문에도 시퀀스 객체는 일련번호 증가됨
insert into board(board_num, board_id, board_title, board_content, board_view_cnt)
values (board_seq.NEXTVAL, 'id1dd', '글 테스트', '내용 테스트', 0);

select board_seq.CURRVAL FROM board;


--------------게시글 수정 ------------------------
update board
set board_content='내용1 수정'
where board_num = 1;
commit;
select board_num, board_content from board where board_num = 1;

---------- 게시글 삭제 -----------------
delete board
where board_num = 1;

delete board_reply -- 게시글 삭제시 댓글도 삭제하려면, cascade 주거나 이 sql 실행 --
where board_num = 1;

rollback;

• 모든 글에대한 댓글들 (+댓글이 없는 글)을 select해올 때...

  • 기준점이 왼쪽, inline (서브쿼리)의 기준도 유지해야한다.
    -> 양쪽 기준을 모두 맞추려면 full join( full outer join)을 사용해야 한다.
    -> left, right join으로는 적용되지 않음.
    -> left join을 사용할 경우 서브쿼리의 기준을 만족하지 않고 board가 기준이기 때문에 board로 맞춰서 select 해옴.

• 댓글의 대댓글을 level로 구별해보자.
  

• level은 pseudo column으로 가짜컬럼. select할 때 편의성을 위해 사용하는 가짜

  • 하지만 level을 이용해서 출력할 때 level값에 따라 1은 댓글, 2는 대댓글 식으로 사용하고 싶어서 Vo객체에 필드 선언해준다.
    

• Vo 또는 DTO에서 null값이 들어갈 수 있는 컬럼이면,
  int 보다 Integer Wrapper Class를 사용하는 것이 좋다!