뷰(view)
: 다른 테이블에서 파생된 테이블로, 물리적 데이터가 저장되는 것이 아닌 논리적으로 존재하는 것
💡 뷰(view)가 필요한 이유
1. 데이터 보안상의 이유로 민감한 컬럼의 데이터와 특정 데이터를 감춰서 보여주기 위해서
(뷰는 테이블처럼 데이터를 저장하고 있지 X. 그냥 조회만 할 수 있는 것 (ocp 시험))
create or replace view emp20
as
select empno, ename, job, mgr, hiredate, deptno
from emp;
select * from emp20;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));2. 복잡하고 긴 쿼리문을 심플하게 작성하기 위해서 (개발자 입장에서 중요한 사용 이유)
create or replace view emp_dept
as
select e.ename, e.sal, e.job, d.loc, d.dname
from emp e, dept d
where e.deptno = d.deptno;
-- view 사용전:
select e.ename, e.sal, e.job, d.loc, d.dname
from emp e, dept d
where e.deptno = d.deptno;
-- view 사용후:
select *
from emp_dept;
-- view 사용전:
select e.ename, e.sal, e.job, d.loc, d.dname
from emp e, dept d
where e.deptno = d.deptno and d.loc='DALLAS';
-- view 사용후:
select *
from emp_dept
where loc='DALLAS';문제1. emp21 테이블로 뷰를 생성하는데 나이와 이메일주소는 안보이게하고 다른 컬럼만 보이게 해서 뷰를 생성하시오. 뷰이름은 emp211 로 하시오
create or replace view emp211
as
select empno, ename, birth, telecom, major, address
from emp21;
select * from emp211;💡 뷰를 수정할 때 alter view 명령어는 없음.
create or replace view로 뷰를 수정함 (ocp 시험)
문제2. 직업과 직업별 토탈월급을 출력하는데 직업이 SALESMAN은 제외하고 출력하고 직업별 토탈월급이 3000 이상인것만 나오게하고 직업별 토탈월급이 높은것부터 출력하시오
select job, sum(sal)
from emp
where job != 'SALESMAN'
group by job
having sum(sal) >= 3000
order by sum(sal) desc;문제3. 문제2번의 결과를 emp_sum 이라는 이름으로 뷰를 생성하시오
create or replace view emp_sum
as
select job, sum(sal) sumsal # 그룹함수는 별칭을 꼭 지정해줘야됨
from emp
where job != 'SALESMAN'
group by job
having sum(sal) >= 3000
order by sum(sal) desc;
select * from emp_sum;문제4. 예제 답
create or replace view emp23
as
select empno, ename, job, mgr, deptno
from emp
where job='SALESMAN';
select * from emp23;💡 view의 종류 2가지
| 단순 view | 복합 view | |
|---|---|---|
| 테이블의 갯수 | 1개 | 2개이상 |
| 그룹함수 | 포함안함 | 포함 |
| group by | 포함안함 | 포함 |
| 데이터 수정 여부 | 가능 | 불가능할 수 있음 |
문제5. 위에서 만들었던 emp20 뷰를 select 하시오
select * from emp20;문제6. emp20 뷰를 update 하는데 이름이 BLAKE 인 사원의 직업을 SALESMAN로 변경하시오
update emp20
set job='SALESMAN'
where ename='BLAKE';
select * from emp20;ㄴ emp 테이블도 같이 update 됨
문제7. 직업, 직업별 토탈월급을 출력하는 뷰를 emp_job 이라는 이름으로 생성하시오
create or replace view emp_job
as
select job, sum(sal) sumsal
from emp
group by job;
select * from emp_job;📌 group 함수로 view를 만들 때는 컬럼별칭을 꼭 써줘야됨
문제8. emp_job 뷰를 수정하는데 직업이 SALESMAN의 sumsal 을 9000 으로 수정하시오
update emp_job
set sumsal = 9000
where job='SALESMAN';
오류: ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다ㄴ 복합뷰라서 데이터 수정 불가능
문제9. EMP 와 DEPT 를 조인해서 이름과 월급과 부서위치를 출력하는 뷰를 emp_dept 라는 이름으로 생성하시오
create or replace view emp_dept
as
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno;
select * from emp_dept;문제10.emp_dept 뷰에서 KING의 부서위치를 DALLAS 로 변경하시오
update emp_dept
set loc='DALLAS'
where ename='KING';
select * from emp_dept;KING과 같은 loc인 사람들의 loc가 모두 DALLAS 바뀌었음
--> 조인했기 때문
💡 조인으로 만든 복합 뷰를 수정했더니 실제 테이블이 수정되어버림
dba가 위와 같이 변경되면 안되게 뷰를 만들 때 신경써야함
변경해도 상관없는 거면 변경해도 되는데, 대부분의 경우가 변경하면 안되는 경우가 많기 때문에, 뷰를 만들 때 뷰를 수정하지 못하도록with read only라는 옵션을 써서 뷰 생성을 권장
문제11. 다시 rollback 하고 emp_dept 뷰를 생성하는데 절대로 수정하지 못하게 뷰를 생성하시오
create or replace view emp_dept
as
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno
with read only;update emp_dept
set loc='DALLAS'
where ename='KING';
SQL 오류: ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.💡 VIEW를 수정하지 못하게 하는 옵션 2가지
- with read only : 뷰에 DML 작업을 아예 못하게 하는 옵션
- with check option : 뷰 생성시 WHERE 절에 기술한 조건에 위배되었을 때만 업데이트 못하게 막는 옵션
문제12. 월급이 2000 이상인 사원들의 모든 컬럼을 가져오는 쿼리문을 뷰로 생성하시오
create or replace view emp_2000
as
select *
from emp
where sal >= 2000;
select * from emp_2000;문제13. emp_2000 뷰의 데이터를 수정하는데 이름이 KING 인 사원의 월급을 6000으로 수정하시오
update emp_2000
set sal=6000
where ename='KING';문제14. emp_2000 뷰의 데이터를 수정하는데 KING 인 사원의 월급을 1300으로 수정하시오
update emp_2000
set sal = 1300
where ename='KING';
ROLLBACK;문제15. 이번에는 다음과 같이 emp_dept 뷰를 생성하시오
create or replace view emp_2000
as
select *
from emp
where sal >= 2000
with check option;💡 with check option 을 쓰게 되면 where 절에 기술한 조건에 위배되게 emp_2000을 수정이나 입력할 수 X
-- 오류: ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배됨
update emp_2000
set sal = 1000
where ename='SCOTT';
-- 이건 가능
update emp_2000
set sal = 6000
where ename='SCOTT';문제16. (OCP 시험용 실험) emp 테이블이 drop 된 상태에서 emp 테이블과 관련한 뷰를 생성하면 생성되겠는가?
drop table emp;
-- 에러발생: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
-- 테이블 없이 뷰를 만들 수 없음
create or replace view emp_25
as
select empno, ename, sal, job, deptno
from emp;문제17. 휴지통에서 emp 테이블을 빼 내세요
flashback table emp to before drop;flashback table emp to before drop;
문제18. 그동안 만들었던 뷰가 뭐가 있는지 조회하고 view 를 전부 삭제하시오
select *
from user_views;
select 'drop view ' || view_name || ';'
from user_views;관련한 OCP 문제
✔️ 유력한 답: A,B,E (A,B,F / A,B,G도 맞음)
A. 뷰는 뷰에 대한 권한을 다시 부여할 필요 없이 업데이트할 수 있다 - 참
뷰 정의를 업데이트해도 해당 뷰에 부여된 기존 권한들이 자동으로 보존됩니다.
B. 뷰를 생성하기 위해서는 뷰를 정의하는 쿼리의 테이블들이 항상 존재해야 한다 - 참
Oracle은 뷰 생성 시점에 참조되는 모든 테이블이 존재해야 하며, 존재하지 않는 테이블을 기반으로 한 뷰 생성을 허용하지 않습니다.
C. WITH CHECK 절은 뷰를 조회할 때 특정 행들이 표시되지 않도록 방지한다 - 거짓
WITH CHECK OPTION은 조회 시 표시되는 행에는 영향을 주지 않습니다. 오직 뷰의 WHERE 조건을 위반하는 DML 작업(INSERT/UPDATE)만을 방지합니다.
D. 데이터 조작 언어(DML)는 항상 뷰에서 사용할 수 있다 - 거짓
많은 뷰들이 업데이트 불가능합니다(예: 조인, GROUP BY, DISTINCT, 집계 함수 등이 포함된 뷰).
E. GROUP BY 절이 포함된 정의 쿼리를 가진 뷰를 사용하여 하나 이상의 행을 삽입하면 오류가 발생한다 - 참
GROUP BY 절이 있는 뷰는 집계된 데이터를 나타내므로 개별 행이 아니어서 본질적으로 업데이트할 수 없습니다.
F. GROUP BY 절이 포함된 정의 쿼리를 가진 뷰를 사용하여 하나 이상의 행을 삭제하면 오류가 발생한다 - 참
E와 같은 이유로 GROUP BY가 있는 뷰는 DML 작업을 통해 수정할 수 없습니다.
G. WITH CHECK 절은 특정 행들이 업데이트되거나 삽입되지 않도록 방지한다 - 참
WITH CHECK OPTION은 뷰의 WHERE 조건을 통해 보이지 않는 행을 생성하는 INSERT/UPDATE 작업을 방지합니다.
참인 세 개의 문장은: A, B, E입니다 (또는 A, B, F나 A, B, G도 가능)
