게시판용 SQL

create table tbl_bbs (seqno number,
title varchar2(500) not null,
content varchar2(2000) not null,
readcnt number default 0,
writer varchar2(50) not null,
regdate date default sysdate,
updatedate date default sysdate
);

alter table tbl_bbs add constraint pk_bbs primary key (seqno);

create sequence seq_bbs;

insert into tbl_bbs(seqno, title, content,writer) values (seq_bbs.nextval, 'title......'||seq_bbs.currval,'contents....'||seq_bbs.currval, 'user00');

create table tbl_user (userid varchar2(50), userpw varchar2(50), username varchar2(100));

alter table tbl_user add constraint pk_user primary key (userid);

insert into tbl_user (userid, userpw, username) values ('user00','user00','Hong Gil Dong');
insert into tbl_user (userid, userpw, username) values ('user01','user01','Gun wo chi');

insert into tbl_user (userid, userpw, username) values ('user02','user02','Sim Chung');
insert into tbl_user (userid, userpw, username) values ('user03','user03','Houng Bu');

insert into tbl_bbs(seqno, title, content,writer)
(select seq_bbs.nextval, 'title......'||seq_bbs.currval,'contents....'||seq_bbs.currval, 'user0'||MOD(seq_bbs.currval,4) from tbl_bbs);

create table tbl_bbs_reply
(bbsno number ,
replydate date default sysdate,
replyer varchar2(50) not null,
reply varchar2(1000)
);


select round(dbms_random.value(0,3)) from dual;

insert into tbl_bbs_reply (bbsno, replyer, reply)
values ( round(dbms_random.value(0,seq_bbs.currval)), 'user0'||round(dbms_random.value(0,3)), 'reply.......' );

insert into tbl_bbs_reply (bbsno, replyer, reply)
(select round(dbms_random.value(0,seq_bbs.currval)), 'user0'||round(dbms_random.value(0,3)), 'reply.......' from tbl_bbs_reply );



select /*+INDEX_DESC(tbl_bbs pk_bbs)*/ seqno,title, content, regdate
from tbl_bbs
where rownum < 20