계층형 게시판 구조(Oracle)

2010. 12. 21. 12:40 from PL/SQL
테이블 생성
create table board (
    no number primary key,
    title varchar2(100),
    contents varchar2(4000),
    writer varchar2(20),
    regdate date default sysdate,
    hit number default 0,
    grp number,
    seq number default 1,
    lvl number default 0
);

시퀀스 생성
create sequence board_no_seq start with 1 increment by 1;

새글 작성
insert into board
(no, title, contents, writer, grp) values
(board_no_seq.nextval, [제목], [내용], [작성자], board_no_seq.currval);

답글 작성
update board
set seq = seq + 1
where grp = [게시물번호] and seq > (select seq from board where no = [게시물번호]);

insert into board
(no, title, contents, writer, grp, seq, lvl) values
(board_no_seq.nextval, [답글제목], [답글내용], [작성자],
(select grp from board where no = [게시물번호]),
(select seq from board where no = [게시물번호]) + 1,
(select lvl from board where no = [게시물번호]) + 1);

게시물 목록
select * from board
order by grp desc, seq;

Posted by NuBiFoRM :