혼공 SQL(Oracle) - 시퀀스, 페이지네이션(ROWNUM)

시퀀스란?

• 유일(UNIQUE)한 값을 생성해주는 오라클 객체이다.

• 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.

• 보통 PRIMARY KEY 값을 생성하기 위해 사용 한다.

• 시퀀스는 테이블과는 독립적으로 저장되고 생성된다. ( 인덱스는 테이블 삭제시 같이 삭제됨)

• AUTOINCREMNET의 역할과 유사하다.
  ​

CREATE SEQUENCE 시퀀스1;

-- 시쿼스 테스트
SELECT 시퀀스1.NEXTVAL FROM DUAL; -- 다음 시퀀스 값
SELECT 시퀀스1.CURRVAL FROM DUAL; -- 현재 시퀀스 값 

-- 시퀀스 옵션 시작값 10, 증가는 10
CREATE SEQUENCE 시퀀스2
START WITH 10
INCREMENT BY 10;
DROP SEQUENCE 시퀀스2;

-- 시쿼스 텟스트
SELECT 시퀀스2.NEXTVAL FROM DUAL; -- 다음 시퀀스 값
SELECT 시퀀스2.CURRVAL FROM DUAL; -- 현재 시퀀스 값 

테이블에 적용

-- 부서 테이블 만들기
CREATE TABLE 부서(
    부서번호 NUMBER PRIMARY KEY,
    부서이름 VARCHAR2(100)
)

INSERT INTO 부서 VALUES(시퀀스2.NEXTVAL, '개발부');
INSERT INTO 부서 VALUES(시퀀스2.NEXTVAL, '안기부');
INSERT INTO 부서 VALUES(시퀀스2.NEXTVAL, '재경부');

-- 업데이트시 WHERE 절이 없으므로 테이블 수만큼 시퀀스 실행.!
UPDATE 부서 SET 부서번호 = 시퀀스1.NEXTVAL;
UPDATE 부서 SET 부서번호 = 시퀀스2.NEXTVAL;

시퀀스1 적용(1시작, 1증가)

시퀀스2 적용(10시작, 10증가)

페이지네이션(ROWNUM)

• 전체 데이터가 아닌 특정 열의 데이터만 들고오기 위해서 사용.

SELECT ROWNUM, EMP.*
FROM EMPLOYEES EMP
WHERE ROWNUM < 10;

위의 쿼리에서 WHERE 절에 바로 ROWNUM 조건을 BETWEEN 6 AND 10을 사용하는 것은 불가능하다.

왜냐하면, ROWNUM 은 반환되는 쿼리결과의 임시 행번호이기때문에, 반드시 1부터 나와야하기 때문이다. 무조건 1번부터 차례대로 붙여지므로, '<=10'과 같은 조건은 가능하지만 '=5' 나 '>=6' 등의 1번 외의 숫자이상부터 출력할 수는 없다. 그래서 페이징이나 특정 행을 추출할 때, ROWNUM이 포함된 SELECT문 바깥에 한번 더 SELECT를 감싸준다.

중간순서를 불러오면 빈값이 나옴

SELECT ROWNUM, EMP.*
FROM EMPLOYEES EMP
WHERE ROWNUM Between 11 and 20;

그래서 서브쿼리를 사용해서 먼저 행번호를 매겨놓아야 된다. 안의 ROWNUM을 별칭을 써서 지정해줘야 한다.!!

-- 
SELECT *
FROM (SELECT ROWNUM AS RN, EMP.*
        FROM EMPLOYEES EMP)
WHERE RN Between 11 and 20;

ORDER BY (정렬이 들어갈때)

• 서브쿼리 안에서 먼저 정렬, 이후 ROWNUM 실행해아함

실데이터

정렬과 ROWNUM을 같이 사용했을 시 -> 잘못된 값이 나옴

-- 정렬이 들어갈때 ( 월급이 큰 10명순)
SELECT *
FROM (SELECT ROWNUM AS RN, EMP.*
        FROM EMPLOYEES EMP
        ORDER BY salary DESC)
WHERE RN Between 11 and 20;

정렬후 ROWNUM을 실행시 -> 정상

-- 올바른 정렬
 SELECT ROWNUM, A.*
FROM (
    SELECT * FROM ( SELECT NVL(SALARY,0)SALARY FROM EMPLOYEES )
    ORDER BY SALARY DESC
) A
WHERE ROWNUM <= 5;