11. ORCLE 12c부터 변경된 사항 - 페이징처리

■ 오라클 12C부터 변경된 사항
 ※ 12C부터 추가된 새로운 기능
    ◎ Top-N 기능 : 반환되는 행수를 제한 할 수 있다  - 중요한 컬럼 
      -----------------------------------------------
      --
-- 처음음 레코드 부터 3개만 출력

    SELECT * FROM emp
    FETCH FIRST 3 ROWS ONLY; -->3개의 데이터만 가져옴 
    
-- 급여 내림차순 정렬하여 처음 레코드 부터 3개만 출력
    SELECT * FROM emp
    ORDER BY sal DESC
    FETCH FIRST 3 ROWS ONLY;  

-- 급여 내림차순 청렬하여 2개의 레코드를 건너뛰고 3개만 출력
    SELECT * FROM emp
    ORDER BY sal DESC
    OFFSET 2 ROWS FETCH FIRST 3 ROWS ONLY; 
    
-- 급여 상위 10% 
    SELECT * FROM emp
    ORDER BY sal DESC
    FETCH FIRST 10 PERCENT ROWS ONLY;
    
★ 페이징 처리 (서브쿼리이용) : 11g - sal 내림차순 정렬하여 21~30 번째 레코드 출력 : 이방식이 조금 더 속도가 빠름 

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

★ 페이징 처리 : Ton-N - sal 내림차순 정렬하여 21~30 번째 레코드 출력 (MariaDB 와 유사)
    SELECT name, sal
    FROM emp 
    ORDER BY sal DESC
    OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY; -- 오프셋다음에 음수와도 상관 없음 
    --> 20개를 건너뛰고 10개를 가져온다는 의미     

    --> 11 및 12 버전 페이징처리쿼리 두가지 경우 다 기억해두기 실무에서 중요한 쿼리 

 

★ MariaDB / MySQL 페이징 처리

   SELECT name, sal

   FROM emp

   ORDER BY sal DESC

   LIMIT 20, 10;  --> 20개를 건너뛰고 10개를 가져온다는 의미 

    ◎ INVISIBLE column : 보이지 않는 컬럼으로 생성 
      -----------------------------------------------
      --
 
    CREATE TABLE test(
        num NUMBER PRIMARY KEY,
        name VARCHAR2(30) NOT NULL,
        tel VARCHAR2(30) INVISIBLE 
    );
    
    -- desc 나 col 로는 tel 컬럼은 보이지 않음
    DESC test;
    SELECT * FROM col WHERE tname = 'TEST';
    
    -- cols 로는 tel 컬럼이 보이며 hidden_column에 YES로 출력
        SELECT * FROM cols WHERE table_name = 'TEST';
    INSERT INTO test VALUES(1, 'a'); -- 가능
    
    INSERT INTO test VALUES(2, 'b', '010'); -- 에러 
    
    INSERT INTO test (num, name, tel)VALUES(2, 'b', '010'); -- 가능
    COMMIT;
    
    -- SELECT 예
    SELECT * FROM test; -- tel은 보이지 않음 
    SELECT num, name, tel FROM test; -- tel 보임
    
    -- VISIBLE로 변경
    ALTER TABLE test MODIFY (tel VISIBLE);
    SELECT * FROM test; -- tel 보임

    -- INVISIBLE로 변경
    ALTER TABLE test MODIFY (tel INVISIBLE);    
    SELECT * FROM test; -- tel은 보이지 않음 
    
    UPDATE test SET tel = '010-1111-1111' WHERE num = 1;
    COMMIT;
    SELECT num, name, tel FROM test; -- tel 보임
    
    -- INVISIBLE 컬럼에 NOT NULL 제약 추가
    ALTER TABLE test MODIFY ( tel NOT NULL);
    
    INSERT INSERT INTO test VALUES (3, 'c');
        -- 에러 : 제약조건은 INVISIBLE 컬럼에서도 적용 


    
DROP TABLE test PURGE;



    ◎ IDENTITY column
      : 자동으로 숫자를 증가하는 컬럼
      : 내부적으로 시퀀스를 사용 => 되도록 안 쓰는 것이 좋음 
      -----------------------------------------------
      --

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

INSERT INTO test(subject) VALUES ('a');
INSERT INTO test(subject) VALUES ('b');
SELECT * FROM test;

ROLLBACK;

INSERT INTO test(subject) VALUES ('a');
INSERT INTO test(subject) VALUES ('b');
SELECT * FROM test; => 내부적으로 시퀀스가 돌아가기 때문에 번호가 3,4번이 나옴
COMMIT;

-- IDENTITY 에서 사용하는 시퀀스 확인 
    SELECT * FROM user_objects;
    -- ISEQ$$_nnnnn
    
    SELECT ISEQ$$_nnnnn.CURRVAL FROM dual; 
    -- IDENTITY 컬럼은 기본적으로 ALWAYS 속성이 부여 되어 INSERT 나 UPDATE 로 수정할 수 없다.
       INSERT INTO test(num, subject) VALUES (5, 'c');
        -- 에러
        
-- 수정 가능한 IDENTITY 컬럼
    CREATE TABLE test2 ( 
        num NUMBER GENERATED BY DEFAULT AS IDENTITY,
        subject VARCHAR2(100) NOT NULL  
    );
    
   INSERT INTO test2(subject) VALUES ('a');
   INSERT INTO test2(subject) VALUES ('b'); 
   
   INSERT INTO test2(num, subject) VALUES (100, 'c'); 
   
   SELECT * FROM test2;




    ◎ DEFAULT 값
      -----------------------------------------------
      -- DEFAULT 시퀀스 사용 가능 

    SELECT * FROM seq;
    
    drop table test2 purge;
    
    CREATE SEQUENCE test_seq;
    
    CREATE TABLE test (
        num NUMBER DEFAULT test_seq.NEXTVAL,
        subject VARCHAR2(100) NOT NULL,
        PRIMARY KEY(num)
    );
    
    INSERT INTO test(subject) VALUES ('a');
    INSERT INTO test(subject) VALUES ('b');
    COMMIT;
    SELECT * FROM test;
    
-- null 을 대신하는 DEFAULT 
    CREATE TABLE test2 (
        num NUMBER PRIMARY KEY,
        subject VARCHAR2(100) DEFAULT 'ORACLE',
        city VARCHAR2(100) DEFAULT ON NULL '기타' 
    );
    
    INSERT INTO test2(num) VALUES (1);
                    -- 1 ORACLE 기타
    INSERT INTO test2(num, subject, city) VALUES (2, null, null);
                    -- 2 NULL 기타
    SELECT * FROM test2;