(1) 오라클 데이터베이스를 이용한 답변형 게시판 작성하기

답변형 게시판 만들기

SQL (for oracle)

-- 글목록 출력 (등록순)
-- 게시판 페이지 알고리즘 Top-N Query 사용
SELECT id, p_id, subject, create_date
FROM (
    SELECT a.id, a.p_id, a.subject, a.create_date,
      ROWNUM AS rnum
    FROM test_boards a
    WHERE ROWNUM <= :MAX_ROWS)
WHERE rnum >= :MIN_ROWS
ORDER BY id DESC;

-- 글목록 출력 (답변형)
SELECT id, p_id, title, create_date
FROM (
    SELECT a.id, a.p_id, LPAD(' ', 2 * (LEVEL - 1)) || a.subject title,
      a.create_date, ROWNUM AS rnum
    FROM test_boards a
    WHERE ROWNUM <= :MAX_ROWS
    START WITH a.p_id IS NULL
    CONNECT BY PRIOR a.id = a.p_id
    ORDER SIBLINGS BY a.id DESC)
WHERE rnum >= :MIN_ROWS;

-- 글올리기
INSERT INTO test_boards VALUES (
  (SELECT MAX(id) + 1 FROM test_boards), NULL, :SUBJECT, :CONTENT, SYSDATE);

-- 글내용 보기
SELECT id, p_id, subject, content, create_date
FROM test_boards
WHERE id = :ID;

-- 답글쓰기
INSERT INTO test_boards VALUES (
  (SELECT MAX(id) + 1 FROM test_boards), :PNUM,
  '[엮인글] ' ||  :SUBJECT, :CONTENT, SYSDATE);

-- 글 삭제 (자식 글이 존재하면 삭제 불가)
SELECT CONNECT_BY_ISLEAF FROM test_boards
WHERE id = :ID
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id;

DELETE FROM test_boards WHERE id = :ID

-- 글 수정
UPDATE test_boards
SET subject = :SUBJECT, content = :CONTENT, create_date = SYSDATE
WHERE id = :ID;