[SQL] 객체 종류

뷰(view)

가상테이블(virtual table)이라 부르는 뷰(view)는 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체를 뜻하며, SELECT문을 저장하므로 물리적 데이터를 따로 저장하지는 않는다.

따라서 뷰를 SELECT문의 FROM절에 사용하면 특정 테이블을 조회하는 것과 같은 효과를 얻을 수 있다.

뷰	서브쿼리
SELECT * FROM VW_EMP20;	SELECT * FROM (SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE DEPTNO = 20);

 

뷰의 사용 목적

1. 편리성: SELECT문의 복잡도를 완화하고 싶을 때
2. 보안성: 테이블의 특정 열을 노툴하고 싶지 않을 때

뷰 생성

뷰는 CREATE문으로 생성.

SYSTEM 계정을 SQL Developer에 접속하여 뷰 생성 권한 부여하기

-- 기본 형식

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 (열 이름1, 열 이름2, ...)
    AS (저장할 SELECT문)
  [WITH CHECK OPTION [CONSTRAINT 제약 조건]]
  [WITH READ ONLY [ CONSTRAINT 제약 조건]];

 

요소	설명
OR REPLACE	기존 뷰가 있어도 에러 없이 내용을 싹 바꿔줌( 없으면 만들고, 이미 있으면 덮어)
FORCE	진짜 테이블이 아직 없어도 억지로 뷰를 생성합니다. (나중에 테이블 만들 거라고 가정하고 껍데기만 미리 만들어두는 경우)
NOFORCE	뷰가 가져올 진짜 테이블(기반 테이블)이 없으면 뷰를 못 만듭니다.
뷰 이름	새로 만들 가짜 테이블의 이름입니다. (예: v_member, view_emp 등 보통 앞에 v_를 붙여서 뷰라는 걸 티 냅니다.
열 이름	뷰에서 보여줄 컬럼들의 이름을 새로 지어주는 곳이며ㅡ 생략하면 원래 테이블의 컬럼명을 그대로 사용하고  만약 함수(SUM, AVG) 같은 걸 쓴다면 여기서 이름을 꼭 지어줘야 합니다.
저장할 SELECT문	뷰의 본체이며, 복잡한 조인(JOIN)이나 검색 조건을 여기에 다 적어놓습니다.
WITH CHECK OPTION	뷰의 조건(WHERE)을 위반하는 데이터는 변경/입력 금지
WITH READ ONLY	이 뷰는 무조건 조회(SELECT)만 가능

-- 뷰 생성하기
CREATE VIEW VW_EMP20
    AS (SELECT EMPNO, ENAME, JOB, DEPTNO
          FROM EMP
        WHERE DEPTNO = 20);
        
-- 생성한 뷰 확인하기
SELECT *
  FROM USER_VIEWS;
  
-- 생성한 뷰 조회하기
SELECT *
  FROM VW_EMP20;
  
-- 뷰 삭제하기
DROP VIEW VW_EMP20;

** 뷰에도 데이터 조작어를 직접 사용할 수 있음.

뷰의 주목적이 물리적 데이터를 저장하지 않고 SELECT문만 저장하여 테이블 데이터를 열람하는 것이기 때문.

데이터를 따로 저장하는 것을 허용하는 구체화 뷰 (materialized view)도 있음.

---

인라인 뷰를 사용한 TOP-N SQL 구문

CREATE문으로 객체를 만든 뷰 외에 SQL 구문에서 일회성으로 만들어서 사용하는 뷰를 인라인 뷰(inline view)라고 하는데, SELECT문에서 사용하는 서브쿼리, WITH절에서 미리 이름을 정의해 도구 사용하는 SELECT문 등이 이에 해당됨.

-- ROWNUM을 추가로 조회하기

SELECT ROWNUM, E.*
  FROM EMP E;
  
  
-- EMP 테이블을 SAL 열 기준으로 정렬하기
SELECT ROWNUM, E.*
  FROM EMP E
ORDER BY SAL DESC;
-- ORDER BY를 해도 정해진 ROWNUM은 변하지 않음

-- 인라인 뷰에서 적용하면 정렬된 SELECT문의 결과에 순번을 매겨서 출력 가능
-- 인라인 뷰(서브쿼리 사용)
SELECT ROWNUM, E.*
  FROM (SELECT *
          FROM EMP E
        ORDER BY SAL DESC)E;
        
-- 인라인 뷰(WITH절 사용)
WITH E AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, E.*
  FROM E;

-- 급여가 많은 순으로 3면의 데이터만 출력하려면 ROWNUM을 WHERE절 조건으로 지정.
-- 인라인 뷰를 사용한 TOP-N 추출은 자주 사용됨.
-- 인라인 뷰로 TOP-N 추출하기(서브쿼리 사용)
SELECT ROWNUM, E.*
  FROM (SELECT *
          FROM EMP E
        ORDER BY SAL DESC)E
  WHERE ROWNUM <= 3;
  
-- 인라인 뷰로 TOP-N 추출하기(WITH절 사용)
WITH E AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, E.*
   FROM E
 WHERE ROWNUM <= 3;

** ROWNUM은 의사 열 (pseudo column)이라고 하는 특수 열이며, 의사 열은 실제 테이블에는 없지만 특정 목적을 위해 테이블에 저장한 열처럼 사용할 수 있는 열을 뜻함.

 

OFFSET절, FETCH절을 사용한 TOP-N SQL구문

버전별로 사용하는 기능이 다름.

https://docs.oracle.com/en/cloud/saas/taleo-enterprise/24b/otbou/c-limitingandoffsettingrowsreturned.html

Using Oracle Business Intelligence or Oracle Analytics for Oracle Taleo Enterprise Edition

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROWNUM-Pseudocolumn.html

SQL Language Reference

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROWID-Pseudocolumn.html

SQL Language Reference

---

규칙에 따라 순번을 생성하는 시퀀스

시퀀스(sequence)는 오라클 데이터베이스에서 특정 규칙에 따른 연속 숫자를 생성하는 객체.

대기 순번표와 마찬가지로 사용자에게 계속 다음 번호를 만들어 주는 역할을 함.

연속하는 숫자로 이루어진 웹 서비스의 새로운 게시판 번호나 상품 주문 번호 등을 생성할 때 종종 사용.

-- 기본 형식

SELECT MAX(글 번호) + 1
    FROM 게시판 테이블

구분	시퀀스 (Sequence)	뷰 (View)
정의	자동 번호 생성기	가상의 테이블 (저장된 SELECT)
비유	대기번호표 기계	데이터를 보는 창문, 쿼리 바로가기
데이터 저장	안 함 (다음 번호만 기억)	안 함 (원본 테이블이 저장)
주요 목적	유니크한 기본키(PK) 생성	복잡한 쿼리 단순화, 보안
님 코드의 예	SEQ_BOARD.NEXTVAL	(페이징 쿼리를 뷰로 만들 수 있음)

---

공식 별칭을 지정하는 동의어

동의어(synonym)는 테이블, 뷰, 시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체. 주로 테이블 이름이 너무 길어 불편할 때 좀 더 간단하고 짧은 이름을 하나 더 만들고자 사용함.