095 복잡한 쿼리를 단순하게 하기 1(VIEW)
오라클의 database 오브젝트 5개
1. table : 행(row)과 컬럼(column)으로 이루어진 기본 데이터 저장소
2. view : 데이터를 저장하지는 않고, 테이블의 데이터를 볼 수 있게 해주는 db object
3. index
4. sequence
5. synonym
* View의 종류 2가지
| 단순 VIEW | 복합 VIEW | |
| 테이블의 갯수 | 1개 | 2개 이상 |
| group 함수 또는 GROUP BY절 | 포함 안 함 | 포함 |
| DML 여부(Insert, update...) | 가능 | 불가능할 수도 있다 |
* View를 왜 사용하는가?
1. 보안상의 이유
특정 컬럼을 노출하지 않고 데이터를 액세스 할 수 있게 하려고
ex) 테이블을 공개해야 개발이 가능한데, 특정 데이터를 노출하지 않게 하고 싶을 때 (월급같은 경우)
2. 복잡한 쿼리문을 심플하게 작성하기 위해
3. 특정 DATA를 갱신하지 못하게 할 때, 또는 전체 DATA의 DML 여부를 막게하고 싶을 때
중요 포인트
* View는 데이터를 저장하지 않고 그냥 테이블을 바라보는 것. (밑에 보다 자세한 설명 있음)
목차
1. 보안상의 이유
* create 대신 create or replace한 이유
2. 복잡한 쿼리문을 심플하게 작성하기 위해
* 복합 view dml 명령이 가능한 경우, 불가능한 경우
* group by절을 사용해서 만든 복합 view는 data update
3. 특정 data를 갱신하지 못하게 할 때, 또는 전체 data의 dml 여부를 막게 하고 싶을 때
* with check option
* with read only
1. 보안상의 이유
예시
CREATE VIEW emp_view
AS
SELECT empno, ename, JOB, mgr, hiredate, deptno
FROM emp;SELECT * FROM emp_view;
view를 생성할 때 나열하지 않은 sal(월급), comm(커미션)이 제외된 채 데이터를 보여줌.
문제 536. emp_view의 데이터를 update하는데 KING의 직업을 SALESMAN으로 변경하시오.
UPDATE emp_view
SET job = 'SALESMAN'
WHERE ename = 'KING';
SELECT *
FROM emp_view;
뷰를 통해 확인 = 업데이트 됨
SELECT *
FROM emp;
원본테이블 확인 = 원본 테이블의 데이터도 바뀐 것을 확인할 수 있음.
정리
: view를 업데이트 했다는 것은 원본 테이블을 업데이트했다는 것과 같다.
view는 데이터를 저장하지 않고 그냥 테이블을 바라보는 쿼리문임.
emp_view를 업데이트했다고 생각하지만, emp 테이블이 업데이트 된 것.
업데이트 된 emp테이블을 view로 보고 있다고 생각하면됨.
보안상의 이유로 특정 데이터만 보여주는 것이 바로 view.
보여주는 데이터를 업데이트 하는 것은 상관 없다는 맥락으로 view를 생성해 특정 데이터만 보여준다고 생각해야 함.
보다 정확한 이해를 위해 실행계획 확인해보기
EXPLAIN PLAN FOR
UPDATE emp_view
SET job = 'SALESMAN'
WHERE ename = 'KING';SELECT * FROM TABLE (dbms_xplan.DISPLAY);
update 대상이 emp_view가 아니라 emp인 것을 볼 수 있음!!
문제 538. 직업이 ANALYST, SALESMAN, CLERK인 사원들만 모든 컬럼으로 emp_view2를 생성하시오.
(보안상의 이유로 president, manager 데이터는 노출x)
CREATE OR REPLACE VIEW emp_view2
AS
SELECT *
FROM emp
WHERE job In ('ANALYST', 'SALESMAN', 'CLERK');
* create 대신 create or replace한 이유
이미 view가 존재할 경우 오류가 날 수 있으므로
view 있는 경우 replace
view 없는 경우 create 하라는 의미
SELECT * FROM EMP_VIEW2;
2. 복잡한 쿼리문을 심플하게 작성하기 위해
문제 540. 이름, 월급, 부서위치를 출력하는 view를 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;
부서위치가 'DALLAS'인 사원명, 부서위치를 출력한다면?
1. view를 사용할 경우
SELECT ename, loc
FROM emp_dept -- 뷰 이름
WHERE loc = 'DALLAS';
2. view를 사용하지 않을 경우 (쿼리가 훨씬 복잡)
SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.loc = 'DALLAS';문제 541. emp_dept에서 KING의 부서위치를 필라델피아로 변경하세요.
UPDATE emp_dept
SET loc = 'PHILADELPHIA'
WHERE ename = 'KING';오류 보고 -
SQL 오류: ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.
복합 View는 업데이트가 되는 경우도, 안되는 경우도 있는데 이 경우에는 불가능
king의 loc를 바꾸는 순간 dept 테이블의 위치도 같이 바뀌면서 모든 데이터에 영향을 미칠 수 있어서 불가능
문제 542. emp_dept라는 view에서 KING의 월급을 9000으로 변경하시오.
ALTER TABLE dept
ADD CONSTRAINT dept_pk PRIMARY KEY(deptno);UPDATE emp_dept
SET sal = 9000
WHERE ename = 'KING';이 경우 업데이트를 해도 테이블 전체에 영향을 미치지는 않아서 가능
조인으로 만든 view는 update가 m 쪽에 해당하는 테이블의 데이터는 update가 되고,
1쪽에 해당하는 테이블의 데이터는 update가 안됨.
조인 조건을 확인 했을 때 1:m관계를 파악하면 조인 가능 여부를 예상할 수 있음.
문제 544. 위의 결과를 출력하는 view를 job_sum이라는 이름으로 생성하시오.
CREATE VIEW job_sum
AS
SELECT JOB, SUM(sal)
FROM emp
GROUP BY JOB;오류 보고 -
ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다
00998. 00000 - "must name this expression with a column alias"
CREATE VIEW job_sum
AS
SELECT JOB, SUM(sal) as sumsal
FROM emp
GROUP BY JOB;그룹 함수를 사용한 열에 별명 지정
SELECT * FROM job_sum;
문제 545. job_sum 뷰에서 직업이 CLERK의 sumsal을 7000으로 변경하시오.
UPDATE job_sum
SET sumsal = 7000
WHERE job = 'CLERK';SQL 오류:
ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다
01732. 00000 - "data manipulation operation not legal on this view"
group by절을 사용해서 만든 위의 복합 view는 data를 update할 수 없다.
문제 546. 위의 job_sum이라는 view를 delete로 지우시오 .
DELETE from job_sum;SQL 오류:
ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다
01732. 00000 - "data manipulation operation not legal on this view"
group by절을 사용해서 만든 위의 복합 view : update뿐 아니라 delete, insert 다 안됨.
3. 특정 DATA를 갱신하지 못하게 할 때, 또는 전체 DATA의 DML 여부를 막게하고 싶을 때
view의 옵션 2가지
1. with check option : 특정 데이터를 갱신 못하게 할 때
2. with read only : 모든 데이터에 대해서 DML을 막고 싶을 때
특정 테이블에 절대 DML못하게 막으라고 한다면?
: VIEW로 만들고 해당 view를 통해 데이터 조회하게 하면 됨. (with read only로 뷰 생성)
CREATE VIEW emp77
AS
SELECT *
FROM emp
WITH READ ONLY;DML 명령어 insert, delete, update 다 안.됨.
문제 547. 부서번호가 10번 20번인 사원들의 모든 컬럼을 가져오는 뷰를 생성하시오.
CREATE OR REPLACE VIEW emp547
AS
SELECT *
FROM emp
WHERE deptno IN (10, 20);SELECT * FROM emp547;
문제 548. emp547 뷰를 수정하는데 KING의 월급을 8400으로 수정하시오.
UPDATE emp547
SET sal = 8400
WHERE ename = 'KING';
1 행 이(가) 업데이트되었습니다.
→ 복합 뷰 아닌 단순 뷰라 문제 없이 업데이트
문제 549. emp547 뷰를 수정하는데 KING의 부서번호를 30번으로 수정하시오.
UPDATE emp547
SET deptno = 30
WHERE ename = 'KING';1 행 이(가) 업데이트되었습니다.
SELECT * FROM emp547;
update는 가능하지만 emp547에서는 더 이상 조회되지 않음.
(특정 부서번호만 조회되는 검색 조건을 바탕으로 만든 뷰이므로, 부서번호가 다른 king은 이제 안보임.)
원본 테이블에 KING의 부서번호는 30번으로 잘 업데이트 되어 있음.
문제 550. emp547 뷰를 다시 만드는데 deptno만큼은 갱신되지 못하게 뷰를 생성하시오.
(뷰 생성시 where절 조건에 위배되는 뷰 갱신은 불가능하게 설정)
ROLLBACK;CREATE OR REPLACE VIEW emp547
AS
SELECT *
FROM emp
WHERE deptno IN (10,20)
WITH CHECK OPTION;UPDATE emp547
SET deptno = 30
WHERE ename = 'KING';오류 보고 -
SQL 오류: ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
01402. 00000 - "view WITH CHECK OPTION where-clause violation"
정리 :
* View
1. 보안상의 이유로 특정 컬럼을 노출하지 않고 데이터를 액세스 할 수 있게 하려고 사용한다.
2. 복잡한 쿼리문을 심플하게 작성하기 위해 사용한다.
3. 특정 DATA를 갱신하지 못하게 할 때, 또는 전체 DATA의 DML 여부를 막게하고 싶을 때 사용
1. with check option : 특정 데이터를 갱신 못하게 할 때
2. with read only : 모든 데이터에 대해서 DML을 막고 싶을 때
4. group by 절, group함수 사용한 복합 뷰는 DML 명령어 사용 불가하다.
5. 복합 view는 DML 명령이 가능한 경우, 불가능한 경우가 있다
6. view의 업데이트는 곧 원본 테이블의 업데이트이다.