21.7.27 Rest_Api(2) , 답변형 게시판

---

답글형 게시판 만들기

테이블 설계

 

설계 SQL 구문

drop table board;

create table repboard(
board_no number,--게시글번호
parent_no number,--부모글번호:원글인경우 0 , 답글인경우 부모글 번호값
board_id varchar2(10),
board_title varchar2(100),
board_content varchar2(1000),
board_viewcount number(5),
board_dt date
);

alter table repboard
add constraint board_no_pk primary key (board_no);

alter table repboard
add constraint board_id_fk foreign key(board_id) REFERENCES customer(id);

alter table repboard
MODIFY board_dt date DEFAULT sysdate;

DROP SEQUENCE board_seq;
CREATE SEQUENCE board_seq;

--글쓰기 (글번호1)
insert into repboard(board_no, parent_no, board_id, board_title, board_content, board_viewcount)
values(board_seq.NEXTVAL, 0, 'id1', '제목1', '내용1', 0);

select * from repboard;

--글쓰기 (글번호2)
insert into repboard(board_no, parent_no, board_id, board_title, board_content, board_viewcount)
values(board_seq.NEXTVAL, 0, 'id2', '제목2', '내용2', 0);

--답글쓰기(글번호:3, 부모글번호:2)
insert into repboard(board_no, parent_no, board_id, board_title, board_content, board_viewcount)
values(board_seq.NEXTVAL, 2, 'id3', '제목2-답1', '내용3', 0);

--답글쓰기(글번호:4, 부모글번호:2)
insert into repboard(board_no, parent_no, board_id, board_title, board_content, board_viewcount)
values(board_seq.NEXTVAL, 2, 'id1', '제목2-답2', '내용4', 0);

--답글쓰기(글번호:6, 부모글번호:1)
insert into repboard(board_no, parent_no, board_id, board_title, board_content, board_viewcount)
values(board_seq.NEXTVAL, 1, 'id8', '제목1-답1', '내용5', 0);

commit;

select level, repboard.* 
from repboard
start with parent_no = 0
CONNECT BY PRIOR board_no =parent_no
order SIBLINGS by board_no desc;

--글수정
update repboard
set BOARD_CONTENT = '수정내용'
where board_no = 4 AND board_id = 'id1';

--글삭제 : 원글인경우 답글들도 모두 삭제
DELETE from repboard
where (board_no = 4 AND BOARD_ID = 'id1');
delete from repboard
where parent_no=1;

rollback;