오라클 12c과 11g의 구동 방식 차이 (페이징)

처음부터 3개 출력하기

SELECT * FROM emp WHERE ROWNUM <= 3;
SELECT * FROM emp FETCH FIRST 3 ROWS ONLY;

 

내림 차순 정렬까지 한다면

SELECT * FROM emp
ORDER BY sal DESC
FETCH FIRST 3 ROWS ONLY;

 

예전에 했던 페이징 방식

SELECT * FROM(
       SELECT ROWNUM rnum, tb.* FROM (
              SELECT empNo, name, sal FROM emp
              ORDER BY sal DESC
       ) tb WHERE ROWNUM <= 30
) WHERE rnum >= 21;

--21부터 30까지 목록 표현

이건 11g 방식이다

 

12g 방식의 페이징 처리 방법

SELECT empNo, name, sal FROM emp
ORDER BY sal DESC
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

먼저 sal 순으로 내림찬순 정렬 먼저 하고

20줄을 OFFSET(건너 뛴다)를 하고 나서 그 다음 10개를 출력하겠다. 라는 뜻이다.

 

MY SQL에서의 페이징 처리 방식은 오라클과 크게 다르지 않다.

     SELECT empNo, name, sal
     FROM emp
     ORDER BY sal DESC
     LIMIT  20, 10;
     
     -- MY SQL 페이징 처리 방식

함수만 다를 뿐이다.

 

ROW 행 뿐만 아니라 PERCENT 퍼센트도 구할 수 있다.

SELECT empNo, name, sal FROM emp
ORDER BY sal DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

FETCH FRIST 조건 다음에 ROWS가 아니라 PERCENT를 붙여주면 된다.

 

 

*응용

남자 중에서 급여 내림차순 정렬하여 11번에서 16번까지 출력

    SELECT empNo, name, sal
    FROM emp
    WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 1
    ORDER BY sal DESC
    OFFSET 10  ROWS FETCH FIRST 6 ROWS ONLY;

 

INVISIBLE column

컬럼 숨기기

CREATE TABLE  test (
       num    NUMBER  PRIMARY  KEY,
       name  VARCHAR2(30)   NOT  NULL,
       tel      VARCHAR2(30)  INVISIBLE
);

tel 부분에 INVISIBLE 주기

 

DESC test; 로 확인을 해봐도 표시 되지 않는다.

 

SELECT * FROM col WHERE tname = 'TEST'; -- 이걸로도 확인 불가능

SELECT * FROM cols WHERE table_name = 'TEST'; -- 이걸로 확인 가능

 

이름을 명시 하면 값을 줄 수 있음.

INSERT INTO test (num, name, tel)VALUES(2, 'b', '010');

 

그러나 SELECT * FROM test; 전체 출력했을때는 안나옴

출력도 이름을 명시해야함.

 

SELECT num, name, tel FROM test;

이건 결과가 나온다.

 

컬럼을 VISIBLE / INVISIBLE로 바꾸기

 ALTER  TABLE  test  MODIFY (tel  VISIBLE);

ALTER  TABLE  test  MODIFY (tel  INVISIBLE);

 

IDENTITY column

ANSI SQL에서 지원하는 자동으로 숫자가 되는 컬럼

오라클은 내부적으로 시퀀스를 이용한다.

 

 

CREATE  TABLE  test (
       num   NUMBER  GENERATED  AS  IDENTITY  PRIMARY  KEY,
       subject  VARCHAR2(1000) NOT NULL
);

테이블을 만들때 GENERATED AS IDENTITY를 입력해주면 된다.

이러면 num에는 값을 안넣어도 알아서 자동적으로 넘버링이 된다.

INSERT INTO test(subject) VALUES ('a');
INSERT INTO test(subject) VALUES ('b');
INSERT INTO test(subject) VALUES ('c');

subject에만 값을 넣어본다

하지만 NUM에도 자동적으로 넘버링이 되어있다.

 

 

BY DEFAULT ON NULL

특정 값으로 아이덴티티 컬럼 값을 지정 가능하다.

NULL이면 아이덴티티 값으로 값을 추가한다.

 

CREATE  TABLE  test (
       num   NUMBER  GENERATED  BY DEFAULT ON NULL AS IDENTITY,
       subject  VARCHAR2(1000) NOT NULL
);

테이블 만들때 역시 추가해준다.

 

       INSERT INTO test(subject) VALUES ('a'); -- 1 a 추가
       INSERT INTO test(num, subject) VALUES (9, 'b'); -- 9 b 추가
       INSERT INTO test(num, subject) VALUES (null, 'c'); -- 2 c 추가
       INSERT INTO test(subject) VALUES ('d'); -- 3 d 추가

그리고 값을 넣을때 b 부분에서 9라는 숫자를 지정해 주었기 때문에

결과 값은

이렇게 나온다.

 

BY DEFAULT 옵션 제거하기

ALTER  TABLE  test MODIFY ( num GENERATED  ALWAYS AS IDENTITY );

 

 

DEFAULT 

11g는 시퀀스의 값을 DEFAULT로 사용 불가

12c 부터 가능하다.

 

CREATE SEQUENCE test_seq;

       CREATE  TABLE  test (
                  num  NUMBER  DEFAULT  test_seq.NEXTVAL,
                  subject  VARCHAR2(30) NOT NULL
        );

num에 값을 지정해주지 않으면 자동으로 디폴트 값으로 test_seq 시퀀스가 실행된다.

(자동으로 숫자를 채운다는 말)

        -- 시퀀스 다음 값 : 시퀀스이름.NEXTVAL
        -- 시퀀스 현재 값 : 시퀀스이름.CURRVAL