[SQL] Oracle 뷰(VIEW) 정리 – CREATE / OR REPLACE / WITH CHECK OPTION / WITH READ ONLY

[ 뷰 VIEW ]

1. 뷰(VIEW) 란?

• 실제 존재하지 않는 논리적인 테이블
• 다른 테이블이나 다른 뷰를 기초로 함.

 

2. 뷰(VIEW)를 사용하는 이유

• 접근 제어를 통한 보안 : 데이터 전체를 보여주지 않고 일부만 보여줌.
• 복잡한 질의

 

3. 뷰(VIEW) 생성 전 권한 확인

• 뷰를 생성하기 위해서는 권한이 있어야함
• 없으면 관리자 계정으로 접속해 권한 부여

SELECT * FROM USER_SYS_PRIVS; -- 모든 사용자들의 가지고 있는 권한과 추가 옵션 확인

-- 없는 경우 권한 부여
GRANT 권한명 TO 사용자명
GRANT CREATE VIEW TO user1;

 

4. 뷰(VIEW) 생성

CREATE [OR REPLACE] [FORCE] VIEW 뷰이름 [(컬럼별칭, ...)]
AS
SELECT 컬럼1, 컬럼2, ...
FROM   참조할테이블명
WHERE  조건
[WITH CHECK OPTION [CONSTRAINT 제약조건명]]
[WITH READ ONLY];

 

옵션	설명
OR REPLACE	이미 존재하면 덮어쓰기 (DROP 없이 수정 가능)
FORCE	참조 테이블이 없어도 일단 생성
컬럼별칭	SELECT 컬럼에 이름 붙이기 (표현식 있을 때 필수)
WITH CHECK OPTION	뷰 조건을 벗어나는 DML 차단
WITH READ ONLY	조회 전용, DML 완전 차단

 

1) OR REPLACE

• 기존에 생성된 VIEW가 있으면 덮어씀.
• DROP 없이 수정 가능

CREATE OR REPLACE VIEW 뷰이름 ...

 

2) FORCE 

• 참조테이블 없이도 뷰 생성

 

(1) FORCE 사용하지 않은 경우

CREATE VIEW emp_view AS
SELECT * FROM emp;
-- ORA-00942: table or view does not exist

 

(2) FORCE 사용한 경우

• 없는 테이블로 생성한경우 뷰는 생성되지만 invalid 상태로 등록되고 나중에 참조 테이블이 실제로 만들어지면 그때 정상 동작함

CREATE FORCE VIEW emp_view AS
SELECT * FROM emp;
-- 테이블이나 뷰가 없어도 일단 뷰 자체는 생성

 

3) 컬럼 별칭

• 별칭 사용해도 DML연산시 실제 테이블 데이터 변경 가능

CREATE VIEW emp_view AS
SELECT empno AS employee_no, ename AS employee_name, deptno AS department_no
FROM emp;

또는

CREATE VIEW emp_view (employee_no, employee_name, department_no) AS
SELECT empno, ename, deptno
FROM emp;

 

4) WITH CHECK OPTION

• 뷰를 생성할 때 WHERE 조건을 벗어나는 DML을 막아주는 옵션

 

(1) WITH CHECK OPTION 옵션을 사용하지 않은 경우

CREATE VIEW view_dept10 AS
SELECT empno, name FROM emp
WHERE deptno = 10;

UPDATE view_dept10 
SET deptno = 20 
WHERE empno=1; -- 실행됨

• 생성 직후

empno	name
1	‘kim’
2	‘park’
3	‘lee’

• UPDATE 후 : view_dept1의 조건이 deptno=10이기 때문에 20으로 변경된 1번은 보이지 않음

empno	deptno
2	10
3	10

 

(2) WITH CHECK OPTION 옵션을 사용한 경우

CREATE VIEW view_dept10 AS
SELECT empno, name FROM emp
WHERE deptno = 10;

UPDATE view_dept10 
SET deptno = 20 
WHERE empno=1; -- ORA-01402:	view	WITH	CHECK	OPTION	where-clause	violation

 

5) WITH READ ONLY

• 읽기 전용으로 뷰의 DML 연산 불가

CREATE VIEW emp_readonly_view AS
SELECT * FROM emp
WITH READ ONLY;

 

4. 뷰(VIEW) 삭제

• 뷰를 만든 사람 또는 DROP ANY VIEW 권한을 가진 사람만 뷰 제거가능

DROP VIEW 뷰이름

 

5. 뷰(VIEW)의 DML 연산 : 실제 테이블 변경

• 뷰에 DML 연산을 수행하면 실제 테이블의 데이터가 변경될 수 있지만 몇가지 제약이 있음.

불가 조건	INSERT	UPDATE	DELETE
단순 뷰 (기본)	✅	✅	✅
DISTINCT 사용	❌	❌	❌
그룹 함수 (SUM, AVG, COUNT 등)	❌	❌	❌
GROUP BY 사용	❌	❌	❌
ROWNUM 사용	❌	❌	❌
표현식 Expression 사용(sal * 12, UPPER(ename) 등)	❌	❌	✅
여러 테이블 조인	❌	일부 가능	❌

 

❓표현식 (Expression)
- 단순 컬럼명이 아닌, 연산이나 함수가 적용된 것

 

6. 인라인 뷰(Inline View)

• FROM 절에 서브쿼리

SELECT *
FROM (
    SELECT empno, ename, deptno
    FROM emp
) emp_view;