오라클 계층형 게시판용 SQL

예를들어 다음과 같은 구조로 게시판을 만들어야 한다면?


1

  2
 
    3
    
       6
    
    4


  5
 
  7


create table tbl_board (
  seqno number,
  title varchar2(300) not null,
  content varchar2(4000) not null,
  writer varchar2(50) not null,
  regdate date default sysdate,
  updatedate date default sysdate,
  gid number,
  ord number,
  pid number,
  depth number
);

alter table tbl_board add constraint pk_board primary key (seqno);





drop sequence seq_board;

create sequence seq_board increment by 1;

delete  tbl_board;
--원글 등록--
insert into tbl_board (seqno, title, content, writer, gid, ord, pid, depth)
values (seq_board.nextval, 'title1','content1','user00', seq_board.currval, 0, 0, 0);

--2
--select * from tbl_board start with pid= 1 connect by pid = prior seqno;

--select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1;

update tbl_board set ord = ord + 1 where gid = 1 and ord >= (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1);

insert into tbl_board (seqno, title, content, writer, gid, ord, pid, depth)
values (seq_board.nextval, 'title2','content2','user00', 1, (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1), 1, 1);

--3
--select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 2 connect by pid = prior seqno) ) from tbl_board where seqno = 2;

update tbl_board set ord = ord + 1 where gid = 1 and ord >= (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 2 connect by pid = prior seqno) ) from tbl_board where seqno = 2);

insert into tbl_board (seqno, title, content, writer, gid, ord, pid, depth)
values (seq_board.nextval, 'title3','content3','user00', 1, (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 2 connect by pid = prior seqno) ) from tbl_board where seqno = 2), 2, 2);

--4

--select * from tbl_board start with pid= 1 connect by pid = prior seqno;

--select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1;

update tbl_board set ord = ord + 1 where gid = 1 and ord >= (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 2 connect by pid = prior seqno) ) from tbl_board where seqno = 2);

insert into tbl_board (seqno, title, content, writer, gid, ord, pid, depth)
values (seq_board.nextval, 'title3','content3','user00', 1, (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1), 2, 2);


--5

--select * from tbl_board start with pid= 1 connect by pid = prior seqno;

--select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1;

update tbl_board set ord = ord + 1 where gid = 1 and ord >= (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1);

insert into tbl_board (seqno, title, content, writer, gid, ord, pid, depth)
values (seq_board.nextval, 'title3','content3','user00', 1, (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1), 1, 1);


--6

--select * from tbl_board start with pid= 3 connect by pid = prior seqno;

--select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 3 connect by pid = prior seqno) ) from tbl_board where seqno = 3;

update tbl_board set ord = ord + 1 where gid = 1 and ord >= (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 3 connect by pid = prior seqno) ) from tbl_board where seqno = 3);


insert into tbl_board (seqno, title, content, writer, gid, ord, pid, depth)
values (seq_board.nextval, 'title6','content6','user00', 1, (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 3 connect by pid = prior seqno) ) from tbl_board where seqno = 3), 3, 3);


--7

select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1;

update tbl_board set ord = ord + 1 where gid = 1 and ord >= (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1);

insert into tbl_board (seqno, title, content, writer, gid, ord, pid, depth)
values (seq_board.nextval, 'title7','content7','user00', 1, (select (ord + (select count(seqno) + 1  r from tbl_board start with pid= 1 connect by pid = prior seqno) ) from tbl_board where seqno = 1), 1, 2);