Software Engineer in Park

SELECT TO_DATE('200803'||TO_CHAR(rownum),'YYYYMMDD') AS sbs,
  to_char(TO_DATE('200803'||TO_CHAR(rownum),'YYYYMMDD'),'DY') AS sbs1,
  to_char(TO_DATE('200803'||TO_CHAR(rownum),'YYYYMMDD'),'D') AS sbs2
FROM all_objects
WHERE rownum <= 31
AND rownum > 0;

SELECT sum(decode(A.A2,'1',A.A3,null)) 일,
  sum(decode(A.A2,'2',A.A3,null)) 월,
  sum(decode(A.A2,'3',A.A3,null)) 화,
  sum(decode(A.A2,'4',A.A3,null)) 수,
  sum(decode(A.A2,'5',A.A3,null)) 목,
  sum(decode(A.A2,'6',A.A3,null)) 금,
  sum(decode(A.A2,'7',A.A3,null)) 토
FROM (SELECT rownum + 7 - to_number(TO_CHAR(TO_DATE('200802'||TO_CHAR(rownum),'YYYYMMDD'),'D')) as A1,
    TO_CHAR(TO_DATE('200802'||TO_CHAR(rownum),'YYYYMMDD'),'D') AS A2,
    rownum as A3
  FROM    all_objects
  WHERE rownum <= 31
  AND  rownum > 0
  AND  TO_CHAR(LAST_DAY(TO_DATE('200802'||'01','YYYYMMDD')),'DD')>=LTRIM(TO_CHAR(rownum,'00')) ) A
GROUP BY A.A1;

기존에 있던 테이블의 한 컬럼의 제약조건을 PK로 변경하기

ALTER TABLE board ADD primary key (no)

* 오늘 쓴 글일 경우 시간표현, 어제까지 썼던 글은 날짜로 표현 *

1. DECODE문
select gdate, decode (to_char(gdate, 'yyyymmdd'), to_char(sysdate, 'yyyymmdd'),
                                to_char(gdate, 'hh24:mi:ss'),
                                to_char(gdate, 'yyyy-mm-dd')) as list_date
from board

           gdate               list_date
--------------------------------------
12/01/2006 11:17:53      11:17:53
12/01/2006 13:34:17      13:34:17
12/01/2006 11:18:00      11:18:00
12/01/2006 11:18:07      11:18:07
12/01/2006 11:18:38      11:18:38
12/01/2006 13:33:49      13:33:49
12/01/2006 11:17:25      11:17:25
12/01/2006 11:17:29      11:17:29
12/01/2006 13:33:53      13:33:53
12/01/2006 16:28:31      16:28:31
12/01/2006 15:09:14      15:09:14
12/01/2006 16:27:22      16:27:22
12/01/2006 15:16:31      15:16:31
12/01/2006 16:27:26      16:27:26
11/30/2006 16:44:44      2006-11-30
12/01/2006 16:28:38      16:28:38

--------------------------------------------------------------------------------------

2. CASE문
SELECT gdate, CASE to_char(gdate, 'yyyymmdd')
                              WHEN to_char(sysdate, 'yyyymmdd') THEN to_char(gdate, 'hh24:mi:ss')
                              ELSE to_char(gdate, 'yyyy-mm-dd')
                           END as list_date

FROM board

       gdate                  list_date
-------------------------------------
12/01/2006 11:17:53      11:17:53
12/01/2006 13:34:17      13:34:17
12/01/2006 11:18:00      11:18:00
12/01/2006 11:18:07      11:18:07
12/01/2006 11:18:38      11:18:38
12/01/2006 13:33:49      13:33:49
12/01/2006 11:17:25      11:17:25
12/01/2006 11:17:29      11:17:29
12/01/2006 13:33:53      13:33:53
12/01/2006 16:28:31      16:28:31
12/01/2006 15:09:14      15:09:14
12/01/2006 16:27:22      16:27:22
12/01/2006 15:16:31      15:16:31
12/01/2006 16:27:26      16:27:26
11/30/2006 16:44:44      2006-11-30
12/01/2006 16:28:38      16:28:38

1. 게시판화면

no       family_no    depth       title

7             0               0           3
6             0               0           2
5             6               1             [Re]:23
4             5               2                  [Re]:[Re]:23
3             6               1             [Re]:2
2             3               2                   [Re]:[Re]:22
1             0               0           1

------------------------------------------------------------------------------------
2. DB입력시 쿼리문과 변수

//답글이 중간에 삽입될 경우 해당글보다 최신글은 모두 글번호 +1 한다.
$qry2="update board set no=no+1 where no >= $no";

$family_no = $no + 1;
$depth++;

//insert 문 이용해서 DB에 저장 (공지사항에도 답글은 달수 있지만 상위에 따로 보여주진 않는다.)
$qry1 = "INSERT INTO board VALUES('$no','$name','$title','$content', sysdate, 0, 0, '$family_no', $depth)";

// 답글달기 family_no+1 (no < $no)
$qry3="update board set family_no=family_no+1 where no < $no and family_no >= $no";

// 답글달기 family_no+1 (no > $no)
$qry4="update board set family_no=family_no+1 where no > $no and family_no != 0";

----------------------------------------------------------------------------------
3. DB에서 꺼내서 list출력시 쿼리문

select * from board order by no desc

select no, family_no, depth, name, title, substr(title, 1, $len) as ti, content,
to_char(gdate, 'mm/dd/yyyy') as gdate, click, gongji
from (select no, family_no, depth, name, title, content, gdate, click, gongji,
rownum as rnum from(select * from board order by no desc))
where rnum between '$min' and '$max'

$len: 제목이 너무 길경우 길이제한하는 변수

$min & $max: 게시판의 글을 10개씩 출력해주기 위한 변수