KITRI 공공데이터 기반 JAVA Programming과정 6일차

--test_seq시퀀스생성

CREATE SEQUENCE test_seq

CREATE TABLE test_tb(

a number PRIMARY KEY,

b varchar2(10)

)

ALTER TABLE test_tb

MODIFY b varchar2(2) NOT NULL

INSERT INTO test_tb(a, b) VALUES(test_seq.NEXTVAL, 'b1')

SELECT *

FROM test_tb

--비정상데이터 추가시 오류발생. 이미 발생된 시퀀스값은 유지됨.

INSERT INTO test_tb(a, b) VALUES(test_seq.NEXTVAL, 'bbbbb2') 

SELECT test_seq.CURRVAL

FROM DUAL

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

>> 계층형 쿼리 <<

SELECT LEVEL, employee_id, first_name, last_name, manager_id

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id

SELECT LEVEL, LPAD(' ', LEVEL*1, '*') || employee_id, first_name, last_name, manager_id

FROM employees

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id

--답변형게시판의 계층형 쿼리

SELECT * 

FROM (SELECT ROWNUM rn, a.*

      FROM (SELECT board_seq, parent_seq, board_subject

            FROM repboard

            ORDER BY board_seq DESC) a

      ) b

--WHERE rn BETWEEN 1 AND 3

START WITH parent_seq = 0

CONNECT BY PRIOR board_seq = parent_seq

SELECT *

FROM (SELECT ROWNUM rn, a.* 

      FROM (SELECT board_seq, parent_seq, board_subject

            FROM repboard

            ORDER BY board_seq DESC) a

      START WITH parent_seq = 0

      CONNECT BY PRIOR board_seq = parent_seq

  )

WHERE rn BETWEEN 1 AND 3

DESC repboard

--글쓰기SQL

INSERT INTO repboard(board_seq, parent_seq, 

                     board_subject, board_writer,

                     board_contents, board_date,

                     board_password, board_viewcount)

VALUES(9, 0, 's9', 'w9', 'c9', SYSTIMESTAMP, 'p9', 0)

select *

from repboard

--글상세조회

SELECT * 

FROM repboard

WHERE board_seq = 9

--글조회수 1증가하기

UPDATE repboard

SET board_viewcount = board_viewcount + 1

WHERE board_seq = 9

--답글쓰기SQL

INSERT INTO repboard(board_seq, parent_seq, 

                     board_subject, board_writer,

                     board_contents, board_date,

                     board_password, board_viewcount)

VALUES(10, 9, 's10', 'w10', 'c10', SYSTIMESTAMP, 'p10', 0)

--비밀번호확인SQL

SELECT board_password 

FROM repboard

WHERE board_seq = 9 AND board_password = 'p9'

--글수정SQL

UPDATE repboard

SET board_subject = 'su9', board_writer = 'wu9',

    board_contents = 'cu9', board_password = 'pu9'

WHERE board_seq = 9

--글삭제SQL

DELETE FROM repboard

WHERE board_seq = 9