간단한 게시판 조회 방식을 통해서
SEQUENCE, ROWNUM , ROW_NUMBER() 차이를 알아보자
1. 게시판을 필드 입력위한 테이블을 만들어보자
일단 여기서는 우리가 원하는 목표를 위해 기존에 있던 테이블과 연관시켜보았따.
TBL_NEWSAWON 이라는 테이블에는 SANO (사원번호), DEPTNO(부서번호) column 이 들어가 있다.
CREATE TABLE TBL_BOARD
(BID NUMBER
,DEPTNO NUMBER(2)
,SANO NUMBER
,BPASSWD VARCHAR2(20)
,BDATE DATE DEFAULT SYSDATE
,TITLE VARCHAR(500)
,CONTENT VARCHAR2(4000)
,CONSTRAINT TBL_BOARD_SANO_FK FOREIGN KEY(SANO) REFERENCES TBL_NEWSAWON(SANO)
,CONSTRAINT TBL_TOTDEPTBOARD_DEPTNO_FK FOREIGN KEY(DEPTNO) REFERENCES TBL_NEWDEPT(DEPTNO)
);
BID는 게시판의 게시글 번호이다. -
김연아 갤러리라는 DCINSIDE의 게시판을 가보면 게시글마다 숫자를 볼수 있다.
밑에 그림을 보면 각 게시글마다 제일 왼쪽에 번호가 있다.
일반적으로 게시판의 글을 1번부터 매겨지는데. 이것은 SEQUENCE 를만들어지정하게된다.
DEPTNO는 부서번호, SANO는 사원번호,
BPASSWD, BDATE, TITLE, COTENT 등은 암호, 글쓴날짜, 제목, 내용등으로 정의했다.
따라서 위테이블 만드는 쿼리문에서 BID 라는것이 게시판에서 쓸 번호로 정의했는데
이것은 사용자가 입력하는것이 아니라 사용자가 게시판을 글을쓸때 (즉 쿼리문으로는 INSERT명령이 적용될것이다)
1번부터 자동으로 매겨지게 할려면. 이것은 SEQUENCE 를 만들어서 지정할수 있다.
CREATE SEQUENCE SEQ_BID
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
NOCACHE;
=> CREATE SEQUENCE SEQ_column명 : 시퀀스를 만들고
START WITH : 1번부터 시작한다는 의미이다.
INCREMENT BY 1 : 1씩 늘어난다는 의미
MAXVALUE : 최대치를 의미하는데 무한대로 한다면 NOMAXVALUE 라고 적는다.
CYCLE 과 CACHE 는 각기 순환시킬것인지 캐쉬를 사용할지를 의미한다.
참고> 시퀀스도 만들기위한 권한이 필요하다 없다면 SYS에서 권한을 주자.
GRANT CREATE SEQUENCE TO 권한받을계정이름;
또한 테이블을 DROP할때 SEQUENCE도 같이 삭제 해야함을 기억하자.
2. 이제 테이블에 VALUE값을 INSERT 시켜본후 테이블에 필드를 채웠다. 밑에 그림과 같다.
참고로 INSERT 시킬때
INSERT INTO TBL_BOARD (BID, SANO, BPASSWD, TITLE, CONTENT,DEPTNO)
VALUES (SEQ_BID.NEXTVAL, 2004, 'WWW', '영어가 좋아요', '과연 좋을까요?',
(SELECT DEPTNO
FROM TBL_NEWSAWON
WHERE SANO=2004) );
TBL_NEWSAWON 정보를 이용해야하므로 위와같은 쿼리문으로 INSERT 시켰다.
(VALUES 에서도 SELECT 구문이 사용함에 참고하자)
3. 이제 사원번호가 3001번인 사원이 접속해서 자신의 부서사람들만이 쓴글을 읽도록
쿼리문을 구성해보자.
SELECT BID, SANO, BDATE , TITLE, CONTENT
FROM TBL_BOARD
WHERE DEPTNO = (SELECT DEPTNO
FROM TBL_NEWSAWON
WHERE SANO = 1003);
위처럼하면
결과물은 성공적이다. BID번호가 일련적이지 않다. BID번호를 그대로 추출해내기 때문이다.
4 3번의 문제를 해결해 보자.
ROWNUM , ROW_NUMBER() 을쓰면 해결된다.
1) ROW_NUMBER() = RANK 함수처럼 순위를 보여주는 함수이다.
SELECT ROW_NUMBER() OVER(ORDER BY BID) AS "게시물번호",
SANO, BDATE, TITLE, CONTENT
FROM TBL_BOARD
WHERE DEPTNO = (SELECT DEPTNO
FROM TBL_NEWSAWON
WHERE SANO = 1003);
위에처럼 BID 번호를 OVER 인자로 넣어서 새로운 ROW_NUMBER() 함수를 이용한다면
밑에처럼 글자가 일련의 순서를 가진 숫자를 가지도록 게시물번호로 표시가 가능하다.
2) ROWNUM 을 이용해보자
SELECT ROWNUM AS "게시물번호",
SANO, BDATE, TITLE, CONTENT
FROM TBL_BOARD
WHERE DEPTNO = (SELECT DEPTNO
FROM TBL_NEWSAWON
WHERE SANO = 1003);
ROW_NUMBER()함수를 쓸때와 결과는 같다.
5 . 그렇다면 ROW_NUMBER, ROWNUM 의 차이는 무엇일까?
ROW_NUMBER()의 경우는 OVER 를 통해서 범위를 지정해 일련번호를 주는것이고
ROWNUM의 경우는 단순하게 데이터가 입력한 순서대를 표시해준다는 차이점이있다.