19.10.14. (스크립트/SYSDATE/답변게시판)

* 게시판 작성 시, 스크립트 꼭 만들어 놓기!!!!

중간에 잘못 됐을 때 cmd창에서 @ + 해당파일드래그 : 처음부터 다시 스크립트 돌려서 오류 수정 가능

(*sql developer에서 하지 말고, 되도록이면 cmd창에서 하기)

​

스크립트로 한번에 돌리면 logtime으로 정렬했을 때, 뒤죽박죽이 된다. 같은 시간이 되기 때문에 !!!!

​

(1) sysdate 복습

​

ex) 3일전, 3시간 전, 시간까지 나타내라

​

select sysdate, sysdate -3, to_char(sysdate - 3/24,'yy.mm.dd hh24:mi:ss')

from dual;

​

(2) Reboard 게시판

​

문1 s10 궁구미 8번글의 첫번째 답글을 다시오. (작성시간 - 2일전)

​

update reboard

set step = step +1

where gcode = 8 and step > 0;

​

insert all

into board (seq, userid, username, email, subject, content, hit, logtime, bcode)

values (BOARD_SEQ.nextval,'merry','사사사','ccc@naver.com','Re :s10 궁구미','궁구미궁구미',0,sysdate-2,4)

into reboard (rseq, seq, gcode, depth, step, pseq, reply)

values (reboard_rseq_seq.nextval,board_seq.nextval,8,1,1,8,0)

select * from dual;

​

update reboard

set reply = reply +1

where seq = 8;

​

Q. 답변 최신글이 위로 올라가게 하려면??? 1,2,3 순으로 !!!!!

​

1.update reboard

set step = step +1

where gcode = (원글의 gcode) and step > (원글의 step)

​

2.insert

​

3.update reboard

set reply = reply +1

where seq = (원글 seq);

​

문2 s10 궁구미 8번글의 두번째 답글을 다시오. (작성시간 - 1일전)

​

update reboard

set step = step +1

where gcode = 8 and step > 0;

​

insert all

into board (seq, userid, username, email, subject, content, hit, logtime, bcode)

values (BOARD_SEQ.nextval,'merry','사사사','ccc@naver.com','Re :s10 궁구미','궁구미궁구미',0,sysdate-1,4)

into reboard (rseq, seq, gcode, depth, step, pseq, reply)

values (reboard_rseq_seq.nextval,board_seq.nextval,8,1,1,8,0)

select * from dual;

​

update reboard

set reply = reply +1

where seq = 8;

​

Q. 8번(원글) - 두번째 답글 - 첫번째 답글 순으로 출력되야 함!!

​

select *

from board b, reboard r

where b.seq = r.seq

and gcode = 8

order by step;

​

문3 s10 궁구미 13번글의 첫번째 답글을 다시오. (작성시간 : 오늘)

​

update reboard

set step = step +1

where gcode = 13 and step > 0;

​

insert all

into board

values (BOARD_SEQ.nextval,'merry','사사사','ccc@naver.com','Re :s10 궁구미','궁구미궁구미',0,sysdate,4)

into reboard

values (reboard_rseq_seq.nextval,board_seq.nextval,13,1,1,13,0)

select * from dual;

​

update reboard

set reply = reply +1

where seq =13;

​

Q. 13번(원글) - 13번의 첫번째 답글 - 8번(원글) - 8번의 두번째 답글 - 8번의 첫번째 답글 순으로 출력되야 함!

select *

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by gcode desc, r.step;

​

문4 s10 궁구미 12번글의 첫번째 답글을 다시오. (작성시간 : 오늘)

​

update reboard

set step = step +1

where gcode = 8 and step > 1;

​

insert all

into board

values (BOARD_SEQ.nextval,'merry','사사사','ccc@naver.com','Re :s10 궁구미','궁구미궁구미',0,sysdate,4)

into reboard

values (reboard_rseq_seq.nextval,board_seq.nextval,8,2,2,12,0)

select * from dual;

​

update reboard

set reply = reply +1

where seq =12;

​

commit;

​

(*insert를 하기 위해서는 12번 원글의 정보를 알아야 한다!)

​

문5

-- page당 5개씩 출력. (변수 &pg)

-- 오늘쓴글 o : 14:25:37

-- 오늘쓴글 x : 19.10.10

-- 게시판번호 4번

​

(틀렸...어렵...;;)

select c.*

from (select rownum rn, a.*, decode(logtime,sysdate, to_char(logtime,'hh24:mi:ss'),to_char('YYYY/MM/DD')) as logtime

from (select *

from board b, reboard r

where b.seq = r.seq and b.bcode = 4

order by gcode desc, step) a

where rownum <=&pg*5) c

where c.rn > &pg *5-5;

​

​

(정답) CASE문으로 ~ (상대비교 가능)

​

1.select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step;

​

2.select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

case

when to_char(b.logtime, 'yymmdd') = to_char(sysdate, 'yymmdd')

then to_char(b.logtime, 'hh24:mi:ss')

else to_char(b.logtime, 'yy.mm.dd')

end logtime,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

​

(* select 뒤에 order by가 처리되기 때문에 rownum이 뒤죽박죽 된다..SELECT문으로 한 번 더 감싸서 처리!!!!)

3. select rownum rn, a.*

from (

select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

case

when to_char(b.logtime, 'yymmdd') = to_char(sysdate, 'yymmdd')

then to_char(b.logtime, 'hh24:mi:ss')

else to_char(b.logtime, 'yy.mm.dd')

end logtime,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

) a

where rownum <= &pg * 5

​

(* rownum은 크다 비교가 안되기 때문에 SELECT문으로 한 번 더 감싸서 처리!!!!)

4.select b.*

from (

select rownum rn, a.*

from (

select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

case

when to_char(b.logtime, 'yymmdd') = to_char(sysdate, 'yymmdd')

then to_char(b.logtime, 'hh24:mi:ss')

else to_char(b.logtime, 'yy.mm.dd')

end logtime,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

) a

where rownum <= &pg * 5

) b

where b.rn > &pg * 5 - 5;

​

정답) DECODE문으로 ~ (동등비교만 가능)

​

1.select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step;

​

2.select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

decode(to_char(b.logtime, 'yymmdd'),

to_char(sysdate, 'yymmdd'), to_char(b.logtime, 'hh24:mi:ss'),

to_char(b.logtime, 'yy.mm.dd')) logtime2,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

​

3. select rownum rn, a.*

from (

select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

decode(to_char(b.logtime, 'yymmdd'),

to_char(sysdate, 'yymmdd'), to_char(b.logtime, 'hh24:mi:ss'),

to_char(b.logtime, 'yy.mm.dd')) logtime2,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

) a

where rownum <= &pg * 5

​

4.select b.*

from (

select rownum rn, a.*

from (

select b.seq, b.userid, b.username, b.email, b.subject, b.content, b.hit, b.bcode,

decode(to_char(b.logtime, 'yymmdd'),

to_char(sysdate, 'yymmdd'), to_char(b.logtime, 'hh24:mi:ss'),

to_char(b.logtime, 'yy.mm.dd')) logtime2,

r.gcode, r.depth, r.step, r.pseq, r.reply

from board b, reboard r

where b.seq = r.seq

and b.bcode = 4

order by r.gcode desc, r.step

) a

where rownum <= &pg * 5

) b

where b.rn > &pg * 5 - 5;