14장 뷰

목 차 뷰란? 뷰의 내부구조 뷰를 사용하는 이유 뷰의 종류 뷰의 제거 뷰의 변경 실습 준비과정 뷰의 내부구조 뷰를 사용하는 이유 뷰의 종류 단순 뷰와 컬럼 별칭 복합 뷰 뷰의 제거 뷰의 변경 뷰의 생성시 지정하는 FORCE/NOFORCE 옵션 With Check Option With Read Only 인라인 뷰 Top-N 분석

뷰 뷰란? 물리적인 테이블을 근거한 논리적인 가상 테이블 기본 테이블에서 파생한 객체 기본 테이블에 대한 하나의 쿼리문 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용. 가상 테이블 : 실질적으로 데이터를 저장하고 있지 않음. 마치 테이블을 사용하는 것과 같이 뷰를 사용할 수 있음

실습을 위한 준비과정 - 주간 System 으로 접속해서 Scott의 emp, dept 테이블을 조회할수 있는 권한을 scott_j에게 부여 SQL>Conn system/soft SQL>GRANT select on scott.emp TO scott_j; SQL>GRANT select on scott.dept TO scott_j; Scott의 dept, emp를 이용 복사 테이블인 dept_copy, emp_copy 생성 SQL> conn scott_j/tiger SQL> CREATE TABLE dept_copy AS SELECT * FROM scott.dept; SQL> CREATE TABLE emp_copy AS SELECT * FROM scott.emp; SQL> SELECT * FROM dept_copy; SQL> SELECT * FROM emp_copy; 앞에서 수행했으면 안해도 됨.

실습을 위한 준비과정- 야간 System 으로 접속해서 Scott의 emp, dept 테이블을 조회할수 있는 권한을 scott_y에게 부여 SQL>Conn system/soft SQL>GRANT select on scott.emp TO scott_y; SQL>GRANT select on scott.dept TO scott_y; Scott의 dept, emp를 이용 복사 테이블인 dept_copy, emp_copy 생성 SQL> conn scott_y/tiger SQL> CREATE TABLE dept_copy AS SELECT * FROM scott.dept; SQL> CREATE TABLE emp_copy AS SELECT * FROM scott.emp; SQL> SELECT * FROM dept_copy; SQL> SELECT * FROM emp_copy; 앞에서 수행했으면 안해도 됨.

뷰 정의 CREATE VIEW 뷰명 AS select_statement 30번 부서의 사원번호, 이름, 부서번호를 조회하라 SQL> SELECT empno, ename, deptno FROM emp_copy WHERE deptno=30; SQL>CREATE VIEW emp_view30    AS    SELECT empno, ename, deptno FROM emp_copy    WHERE deptno=30; SQL> DESC emp_view30; SQL> SELECT * FROM emp_view30; 서브쿼리를 매번 사용하지 않고 해당 정보를 얻을 수 있음

뷰의 내부구조 SELECT * FROM emp_view30; 뷰는 데이터를 저장하고 있지 않은데도 질의 문을 수행할 수 있는 이유? 기본 테이블인 emp와 연결되어 있음 user_views 데이터 딕셔너리의 text 컬럼에서 확인

뷰의 내부구조 –cont’d 뷰가 기본 테이블을 이용하여 쿼리문을 수행한 것이란 것을 증명 뷰에 행 추가시 뷰와 기본 테이블에 새로운 행 추가. 뷰는 데이터를 저장한 기본 테이블을 볼 수 있도록 한 창임. 기본 테이블의 내용이 바뀐 것이고 그 내용을 뷰라는 창을 통해서 봄. DML 문을 사용하여 뷰를 변경하면 기본 테이블이 변경됨.

뷰의 내부구조 –cont’d user_views 데이터 딕셔너리 조회 SQL> DESC user_views SQL> SELECT view_name, text FROM user_views; 1행을 입력하고 뷰와 기본테이블 조회 SQL> INSERT INTO emp_view30 VALUES (1111, ‘AAAA’, 30); SQL> SELECT * FROM emp_view30; SQL> SELECT * FROM emp_copy; SQL> SELECT * FROM scott.emp; Text : view 를 정의하는 서브쿼리문 저장 DML에 의해 기본테이블도 뷰와같은 결과 복사전 원래 테이블은 변화 없음.

뷰와 테이블의 차이 뷰가 테이블과 다른 것은 emp_copy 는 emp를 복사한 테이블이므로

뷰를 사용하는 이유 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화시킴. 보안에 유리. 사원 테이블의 전체 내용 조회 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화시킴. 보안에 유리. 사원 테이블의 전체 내용 조회 SQL> SELECT * FROM emp_copy; 사원 테이블을 이용한 뷰 생성 SQL> CREATE VIEW emp_view AS SELECT empno, ename, job, mgr, hiredate, deptno FROM emp_copy; 뷰를 통한 사원테이블 내용 조회 SQL> SELECT * FROM emp_view;

뷰를 사용하는 이유 : 보안 뷰를 사용하여 하나의 테이블을 여러 용도로 사용 가능.

뷰의 종류 단순 뷰/복합 뷰 - 기본 테이블의 개수에 따라 단순 뷰 복합 뷰 기본 테이블이 한 개인 경우 DML문 실행 가능 생성되는 뷰의 컬럼명을 명시하지 않으면 기본테이블명 상속 복합 뷰 기본 테이블이 여러 개인 경우

컬럼 별칭 생성되는 뷰의 컬럼명을 명시해서 사용 생성되는 뷰의 컬럼명을 명시해서 사용 SQL> CREATE VIEW emp_view10(사원번호, 이름, 부서번호) AS SELECT empno, ename, deptno    FROM emp_copy    WHERE deptno=10; SQL> SELECT * FROM emp_view10; SQL> DESC emp_view10

함수를 포함한 뷰 함수에 대해서는 컬럼 별칭을 부여해서 뷰 생성. 사원테이블에서 부서별 급여 총계를 구하는 뷰 생성 SQL> CREATE VIEW dept_sum   AS   SELECT deptno, SUM(sal)   FROM emp_copy   GROUP BY deptno; 오류 : 함수에 대한 별칭이 없음 SQL> CREATE VIEW dept_sum   AS   SELECT deptno, SUM(sal) sum_sal   FROM emp_copy   GROUP BY deptno;

함수를 포함한 뷰 –cont’d 함수를 포함하여 생성된 뷰에 DML 문을 적용하면 오류 발생 예) sum_sal 컬럼은 기본 테이블에는 존재하지 않는 컬럼이므로 데 이터 입력은 이치에 맞지 않음 뷰의 내용 조회 SQL> SELECT * FROM dept_sum; 뷰에 값 입력 SQL> INSERT INTO dept_sum VALUES (40, 3000);   오류 발생

복합뷰 기본 테이블이 여러 개인 경우 복합 뷰의 사용용도 두 개 이상의 테이블을 조인하는 경우 뷰로 정의하면 편리함. 사원번호, 이름, 부서명으로된 뷰 생성 SQL>CREATE VIEW emp_view_join AS SELECT e.empno, e.ename, d.dname FROM emp_copy e, dept_copy d WHERE e.deptno = d.deptno;  SQL>SELECT * FROM emp_view_join;  

뷰 제거 뷰의 제거 DROP VIEW view_name user_views 데이터 딕셔너리 조회 SQL> SELECT view_name, text FROM user_views; 뷰의 삭제 SQL> DROP VIEW emp_view30; SQL> DROP VIEW emp_view10; SQL> DROP VIEW emp_view; 기본 테이블에는 영향 없음.

뷰 변경 뷰의 변경은 해당 뷰를 삭제하고 다시 생성 가능. 뷰의 삭제 및 생성, 조회 SQL> DROP VIEW dept_sum; SQL> CREATE VIEW dept_sum AS SELECT deptno, SUM(sal) sum_sal, AVG(sal) avg_sal FROM emp_copy GROUP BY deptno; SQL> SELECT * FROM dept_sum; user_views 데이터 딕셔너리 조회 SQL> SELECT view_name, text FROM user_views;

뷰 변경 –cont’d CREATE OR REPLACE VIEW view_name AS select_statement 기존의 뷰이름을 사용해서 재 생성 SQL> CREATE OR REPLACE VIEW emp_view_join AS SELECT e.empno, e.ename, d.dname FROM emp_copy e, dept_copy d WHERE e.deptno = d.deptno; SQL> SELECT view_name, text FROM user_views;

FORCE/NOFORCE 옵션 NOFORCE 옵션 FORCE 옵션 뷰의 생성시 존재하는 기본 테이블을 이용한 쿼리문으로 생성해야 함. FORCE 옵션 기본 테이블이 존재하지 않는 경우에도 경고와 함께 뷰 생성.

FORCE/NOFORCE 옵션 –cont’d SQL>CREATE OR REPLACE VIEW view_employees AS SELECT * FROM employees; 오류: 테이블이 존재하지 않음 SQL>CREATE OR REPLACE FORCE VIEW view_employees AS SELECT * FROM employees; SQL> SELECT view_name, text FROM user_views; 경고 : 컴파일오류와 함께 뷰 생성 View_employees 뷰 생성 확인

WITH CHECK OPTION 부서번호 20인 사원으로만 emp_view20 생성 SQL>CREATE OR REPLACE NOFORCE VIEW emp_view20 AS SELECT empno, ename, deptno FROM emp_copy WHERE deptno=20; 7369번 사원의 이름 수정 및 조회 SQL>UPDATE emp_view20 SET ename=‘SOFT’ WHERE empno=7369; SQL> SELECT * FROM emp_view20; 7369번 사원의 부서번호 수정 및 조회 SET deptno=40 SQL> SELECT * FROM emp_copy ; 20번 부서 직원만 조회됨 emp_copy 확인 (7369번의 deptno=40) 기본테이블이 존재하는 경우만 뷰 생성 40번 부서로 수정 가능

WITH CHECK OPTION WITH CHECK OPTION : where조건에 사용된 컬럼 값을 변경 불가 SQL>CREATE OR REPLACE NOFORCE VIEW emp_chk20 AS SELECT empno, ename, deptno FROM emp_copy WHERE deptno=20 WITH CHECK OPTION; 부서번호는 변경 불가 SQL> SELECT view_name, text FROM user_views; 7369번 사원의 이름 수정 및 조회 SQL>UPDATE emp_chk20 SET ename=‘SOFT’ WHERE empno=7566; SQL> SELECT * FROM emp_chk20; 7369번 사원의 부서 수정 및 조회 SET deptno=40 오류 : 부서번호 변경은 with check option 에 위배

WITH READ ONLY With read only 옵션 : 기본 테이블의 내용 변경 불가. , DML문(Update, Insert, Delete)도 수행 불가 SQL> CREATE OR REPLACE NOFORCE VIEW emp_chk30 AS SELECT empno, ename, deptno FROM emp_copy WHERE deptno=30 WITH READ ONLY; SQL>UPDATE emp_chk30 SET ename=‘SOFT’ WHERE empno=7499; 오류 : 가상열 사용 불가 Emp_chk30은 읽기만가능, 수정불가

인라인 뷰 인라인 뷰 메인 쿼리문의 FROM절 내부(테이블명)에 사용된 서브 쿼리문 서브 쿼리는 별칭이 부여되며 이 별칭이 뷰. 부서별 가장 최근에 입사한 사원보다 먼저 입사한 사원의 정보 SQL>SELECT src.empno, src.ename, src.hiredate, src.deptno, max_hiredate   FROM emp_copy src, ( SELECT deptno, MAX(hiredate) max_hiredate                           FROM emp_copy                          GROUP BY deptno)  des   WHERE src.deptno=des.deptno AND src.hiredate < des.max_hiredate; 인라인뷰

Top-N 분석 SQL> SELECT rowid, rownum, empno FROM emp_copy; 컬럼 의미 rowid 주소로서 row가 실제로 저장되어있는 (Tree형 구조) 고유주소 rownum row의 출력 순서대로 부여되는 serial No. 급여를 내림차순으로 정렬한 사원테이블을 인라인뷰로 구성 SQL> SELECT rownum, ename, sal FROM ( SELECT ename, sal FROM emp_copy ORDER BY sal DESC ); 인라인 뷰

Top-N 분석 급여를 가장 많이 받는 3명을 구하는 예제 SQL> SELECT rownum, ename, sal FROM ( SELECT ename, sal FROM emp_copy ORDER BY sal DESC ) WHERE rownum <= 3; 인라인 뷰 가장 최근에 입사한 직원 5명에 대한 정보 출력 SQL> SELECT rownum, empno, ename, hiredate FROM ( SELECT empno, ename, hiredate FROM emp_copy ORDER BY hiredate DESC ) WHERE rownum <= 5;