🔍 뷰

✍ 뷰(View)는 데이터베이스에서 저장된 데이터에 대한 가상의 테이블이다. 뷰는 하나 이상의 기본 테이블(또는 다른 뷰)에서 데이터를 가져와 쿼리를 실행할 때 필요한 데이터를 제한된 형식으로 제공하며 이는 데이터 접근성을 개선하고, 데이터의 일관성과 보안을 유지하고 복잡한 쿼리 작성을 간소화하는 데 도움이 된다.

특징	단순 뷰	복합 뷰
테이블 수	하나	둘 이상
함수 포함	없음	있음
데이터 그룹 포함	없음	있음
뷰를 통한 DML	있음	없음

🔍 뷰 권한과 생성

✍ 사용자에게 부여된 ROLE를 확인하거나 권한을 체크하여 뷰를 생성할 수 있는지 확인하여야 한다.
✍ CREATE VIEW 문장 내에서 서브쿼리를 사용한다.

-- 뷰 정보 확인
SELECT * FROM USER_VIEWS;

-- 뷰 구조 확인
DESC EMP_DETAILS_VIEW;

SELECT * FROM EMP_DETAILS_VIEW;

-- 뷰 생성 권환 확인
SELECT * FROM USER_ROLE_PRIVS;

SELECT * FROM USER_SYS_PRIVS;

-- 뷰 생성
CREATE VIEW EMP_VIEW_DEPT60
AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

DESC EMP_VIEW_DEPT60;

SELECT * FROM EMP_VIEW_DEPT60;

DROP VIEW EMP_VIEW_DEPT60;

CREATE VIEW EMP_DEPT60_SALARY
AS SELECT
    EMPLOYEE_ID AS EMPNO,
    FIRST_NAME || ' ' || LAST_NAME AS NAME,
    SALARY AS MONTHLY_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

SELECT * FROM EMP_DEPT60_SALARY;

-- 뷰를 생성하면 USER_VIEWS에 생성한 뷰가 명시되어 있음을 알 수 있다.
SELECT * FROM USER_VIEWS;

🔍 뷰 수정

✍ CREATE OR PEPLACE를 사용하여 존재하지 않으면 생성, 존재하면 수정을 한다. MERGE를 생각하면 이해에 도움이 될 거 같다.

-- 뷰 수정
-- CREATE OR REPLACE = MERGE처럼 없으면 생성 있으면 수정
CREATE OR REPLACE VIEW EMP_DEPT60_SALARY
AS SELECT EMPLOYEE_ID AS EMPNO,
          FIRST_NAME || ' ' || LAST_NAME AS NAME,
          JOB_ID AS JOB,
          SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_Id = 60;

-- 뷰 구조 변경 확인
SELECT * FROM EMP_DEPT60_SALARY;

🔍 뷰 삭제

✍ 뷰를 생성한 사용자, 권한이 있는 사용자만 뷰를 삭제할 수 있다.

-- 만든사람 또는 DROP ANY VIEW 권한을 가진 사람만 뷰를 삭제할 수 있다.
DROP VIEW EMP_DEPT60_SALARY;

🔍 복합 뷰

✍ 두 개 이상의 테이블을 이용하여 복합 뷰를 생성할 수 있다.

-- 복합 뷰 생성
CREATE VIEW EMP_VIEW
AS SELECT
    e.EMPLOYEE_ID AS ID,
    e.FIRST_NAME AS NAME,
    d.DEPARTMENT_NAME AS DEPARTMENT,
    j.JOB_TITLE AS JOB
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
JOIN JOBS j ON e.JOB_ID = j.JOB_ID;

SELECT * FROM EMP_VIEW;

-- hr 스키마의 대표적인 복합 뷰
SELECT * FROM EMP_DETAILS_VIEW;

🔍 뷰를 이용한 DML

✍ 뷰가 그룹함수, GROUP BY, DISTINCT를 포함한다면 행을 제거할 수 없다.

✍ 행을 제거할 수 없는 조건, 표현식으로 정의된 열, ROWNU 의사열이면 수정할 수 없다.

✍ 제거와 수정할 수 없는 조건, 뷰에 의해 선택되지 않은 NOT NULL열이 기본 테이블에 있을 때 데이터를 추가할 수 없다.

-- 뷰를 이용한 DML연산
CREATE TABLE EMPS AS SELECT * FROM EMPLOYEES;

CREATE OR REPLACE VIEW EMP_DEPT60
AS SELECT * FROM EMPS WHERE DEPARTMENT_ID = 60;

SELECT * FROM EMP_DEPT60;
SELECT * FROM EMP_DEPT60 WHERE EMPLOYEE_ID = 104;

SELECT * FROM EMPS WHERE EMPLOYEE_ID = 104;

-- 뷰 삭제
DELETE FROM EMP_DEPT60 WHERE EMPLOYEE_ID = 104;

-- EMPS 테이블의 EMPLOYEE_ID 104번 사원의 정보가 삭제된 것을 확인
SELECT * FROM EMPS WHERE EMPLOYEE_ID = 104;

-- DISTINCT로 뷰 생성
CREATE OR REPLACE VIEW EMP_DEPT60
AS SELECT DISTINCT * FROM EMPS WHERE DEPARTMENT_ID = 60;

SELECT * FROM EMP_DEPT60;

-- ERROR = DISTINCT, GROUP BY, 그룹 합수 등을 포함한 뷰는 삭제가 불가능하다.
DELETE FROM EMP_DEPT60 WHERE EMPLOYEE_ID = 106;

CREATE OR REPLACE VIEW EMP_DEPT60
AS SELECT EMPLOYEE_ID,
          FIRST_NAME || ' ' || LAST_NAME AS NAME,
          SALARY * 12 AS ANNUAL_SALARY
FROM EMPS WHERE DEPARTMENT_ID = 60;

SELECT * FROM EMP_DEPT60;

-- ERROR = 표현식으로 정의된 열, ROWNUM 의사열, 위 행을 제거할 수 없는 조건들은 수정할 수 없다.
UPDATE EMP_DEPT60 SET ANNUAL_SALARY = ANNUAL_SALARY * 1.1
WHERE EMPLOYEE_ID = 106;

DELETE FROM EMP_DEPT60 WHERE EMPLOYEE_ID = 106;

-- EMPS 테이블의 행까지 삭제
SELECT * FROM EMPS WHERE EMPLOYEE_ID = 106;

--
CREATE OR REPLACE VIEW EMP_DEPT60
AS SELECT EMPLOYEE_ID,
          FIRST_NAME,
          LAST_NAME,
          EMAIL,
          SALARY
FROM EMPS
WHERE DEPARTMENT_ID = 60;

SELECT * FROM EMP_DEPT60;

SELECT * FROM EMPS;

-- 뷰에 의해 선택되지 않은 NOT NULL열이 기본 테이블에 있을 때 (HIRE_DATE, JOB_ID)
INSERT INTO EMP_DEPT60
VALUES (500, 'JinKyoung', 'Heo', 'HEOJK', 8000);

-- EMP_DEPT60 삭제 후 EMP 행 삭제 확인
DELETE FROM EMP_DEPT60 WHERE EMPLOYEE_ID = 103;
SELECT * FROM EMPS WHERE EMPLOYEE_ID = 103;

-- EMP_DEPT60 수정 후 EMPS 행 수정 확인
UPDATE EMP_DEPT60 SET SALARY = 5000 WHERE EMPLOYEE_ID = 107;
SELECT * FROM EMPS WHERE EMPLOYEE_ID = 107;

🔍 WITH CHECK OPTION

✍ DML을 수행하는데 있어서 조건절에 오는 컬럼을 수정할 수 없다.

-- WHERE 조건 DEPARTMENT_ID를 수정할 시 WITH CHECK OPTION 에러를 설정
CREATE OR REPLACE VIEW EMP_DEPT60
AS SELECT EMPLOYEE_ID,
          FIRST_NAME,
          HIRE_DATE,
          SALARY,
          DEPARTMENT_ID
FROM EMPS
WHERE DEPARTMENT_ID = 60
WITH CHECK OPTION;

-- ERROR = WITH CHECK OPTION 에러
UPDATE EMP_DEPT60 SET DEPARTMENT_ID = 10 WHERE EMPLOYEE_ID = 105;

🔍 WITH READ ONLY

✍ 뷰를 생성할 때 WITH READ ONLY를 명시하면 뷰를 이용한 DML 연산이 불가능하다.

-- WITH READ ONLY
CREATE OR REPLACE VIEW EMP_DEPT60
AS SELECT EMPLOYEE_ID,
          FIRST_NAME,
          HIRE_DATE,
          SALARY,
          DEPARTMENT_ID
FROM EMPS
WHERE DEPARTMENT_ID = 60
WITH READ ONLY;

-- ERROR
DELETE FROM EMP_DEPT60
WHERE EMPLOYEE_ID = 105;

🔍 인라인 뷰

✍ 인라인 뷰 서브쿼리는 FROM 절에 서브쿼리가 온 것을 말한다. 보통 FROM 절에는 테이블 또는 뷰가 올 수 있다. 그런데 서브쿼리를 FROM 절에 사용해 하나의 테이블 또는 뷰처럼 사용할 수 있을 뿐 아니라 뷰도 하나의 SELECT문이므로 FROM절에 사용하는 서브쿼리도 하나의 뷰로 볼 수 있다. 그래서 FROM절에 오는 뷰를 인라인 뷰라고 한다.

-- 인라인 뷰
SELECT ROW_NUMBER, FIRST_NAME, SALARY
FROM (SELECT FIRST_NAME, SALARY,
      ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROW_NUMBER
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROW_NUMBER BETWEEN 1 AND 10;

📖 REFERENCE

인프런 오라클 데이터베이스