뷰

쿼리에 이름을 붙여 테이블처럼 쓰는 뷰를 만드는 방법과, 뷰를 고치면 기반 테이블이 바뀌는 점, WITH CHECK OPTION과 WITH READ ONLY로 뷰를 통한 변경을 제한하는 방법

VIEWCREATE VIEWCREATE OR REPLACE VIEWWITH CHECK OPTIONWITH READ ONLYUSER_VIEWS

#핵심 요약

CREATE VIEW 이름 AS SELECT...로 만듦
복잡한 SQL을 단순하게 쓸 수 있음

뷰를 수정하면 그 뷰의 기반 테이블도 수정됨

집계 함수를 뷰에 쓸 때는 컬럼 별칭을 줘야 함

뷰를 통한 변경을 조건 안으로 제한하려면 WITH CHECK OPTION, 모두 막으려면 WITH READ ONLY를 씀

#개념과 동작 원리

뷰 변경 실습: 부서 20번만 보이는 뷰에서 부서번호를 30번으로 바꾸면, 바뀐 행은 뷰 조건을 벗어나 뷰에서 사라짐
WITH CHECK OPTION을 주면 뷰 조건을 벗어나는 변경을 막음

실습에서는 조인 뷰(emp와 dept)를 통해 KING의 부서 위치를 바꾸려다 실패하고, dept.deptno에 primary key를 건 뒤에는 성공했다고 적었음
집계 뷰의 평균 월급은 바꿀 수 없었음
단일 테이블 뷰와 조인, 집계 뷰는 갱신 가능 조건을 구분해야 함

뷰 자체는 데이터를 저장하지 않으며 갱신 가능한 뷰를 통한 DML이 기반 테이블을 바꿈
WITH CHECK OPTION은 뷰 조건을 벗어나게 하는 입력, 변경을 제한함

뷰를 통한 변경과 함께 확인할 UPDATE, DELETE 문법은 UPDATE와 DELETE에서 다룸

#주요 용어와 구성 요소

구문	의미
본질적으로 갱신 가능한 뷰	뷰 열이 단일 기반 테이블의 열에 대응하고 DISTINCT, 집계, 그룹 등 갱신을 막는 요소가 없어야 함
키 보존 테이블	기반 테이블의 키가 조인 결과에서도 유일한 테이블이며 조인 뷰 DML은 한 기반 테이블만 변경함

#필수 명령어 또는 SQL

항목	실행 조건
작업 대상	emp 기반 뷰, 집계 뷰 정의, 뷰를 통한 emp.job 변경과 삭제문 생성
필요 권한	CREATE VIEW와 기반 객체 직접 권한, DML 대상 권한
바꿀 입력값	뷰명, 조건, 열 별칭과 과제의 주소 열, 값
결과 해석	삭제문 생성 SELECT는 문자열만 출력함 실제 DROP을 실행해야 뷰가 삭제되며 연습 답은 기재 없음

#뷰 만들기와 뷰를 통한 수정

SQL Developer 또는 SQL*Plus객체 또는 설정을 변경함

CREATE VIEW emp2
AS
SELECT empno, ename, job, mgr, hiredate, deptno
  FROM emp;

UPDATE emp2 SET job = 'AAAA' WHERE ename = 'KING';

결과 해석: emp2를 고쳤지만 실습 emp의 KING 직업도 바뀜
실습에서는 같은 이름의 테이블이 있으면 먼저 DROP TABLE emp2를 했음

SQL Developer 또는 SQL*Plus객체 또는 설정을 변경함

CREATE VIEW job_sumsal
AS
SELECT job, SUM(sal) AS sumsal
  FROM emp
 GROUP BY job;

CREATE OR REPLACE VIEW emp_avgsal
AS
SELECT deptno, ROUND(AVG(sal)) AS avgsal
  FROM emp
 GROUP BY deptno;

결과 해석:

실습의 job_sumsal에는 ORDER BY job ASC도 있었음
집계식에 별칭(sumsal, avgsal)을 주지 않으면 뷰를 만들 수 없음

#뷰 변경 제한

뷰를 통한 변경 실습은 다음과 같음

SQL Developer 또는 SQL*Plus객체 또는 설정을 변경함

CREATE OR REPLACE VIEW emp_deptno_20
AS
SELECT * FROM emp WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT emp_deptno_20_ck;

CREATE OR REPLACE VIEW emp_view_ro
AS
SELECT * FROM emp
WITH READ ONLY;

결과 해석: 첫 뷰에서는 부서번호를 20이 아닌 값으로 바꿀 수 없고, 둘째 뷰에는 INSERT, UPDATE, DELETE를 할 수 없음

#뷰 목록과 일괄 삭제

SQL Developer 또는 SQL*Plus조회만 함

SELECT 'DROP VIEW ' || view_name || ';'
  FROM user_views;

결과 해석: 내가 가진 뷰마다 삭제 문장을 만들어 줌
그 문장을 실행하면 뷰가 지워지고 기반 테이블은 남음

실습 이수자 평가 과제(답 없음): emp19에서 서울 거주 사원 뷰를 만들되 어떤 DML도 할 수 없게 하기

#구문과 옵션 설명

구문	의미
CREATE OR REPLACE VIEW	기존 뷰 정의를 교체하는 구문임
WITH CHECK OPTION	뷰의 조건을 벗어나는 입력, 변경을 제한함
WITH READ ONLY	뷰를 통한 DML을 막음
USER_UPDATABLE_COLUMNS	뷰 열별 INSERTABLE, UPDATABLE, DELETABLE 여부를 확인하는 사전 뷰임

#사용 조건과 주의사항

다른 스키마 객체에 의존하는 뷰 소유자의 기반 객체 권한은 역할을 통한 권한이 아니라 직접 부여된 권한이어야 함
조인 뷰를 변경하려면 변경할 열이 키 보존 테이블에서 오는지 확인해야 함