[SQL Plus] View

view : 저장 공간이 없는 가상의 테이블

사용 목적 1) 편리성 : 복잡한 SQL구문을 쉽게 만들 수 있다.

             2) 보안 : 직접적인 테이블 접근을 제한하여 민감한 자료를 공개하지 않는다.

 

1. 뷰의 생성

형식) create [or replace] view 뷰이름 as sql구문

 ex ) create view v_test as ~                 -> 수정이 불가능한 뷰

 ex ) create or replace view v_test as ~   -> 수정이 가능한 뷰

 

SQL> CREATE OR REPLACE view v_emp_10(employee_no,
employee_name,e_hiredate) as select empno,ename,hiredate
from b_emp5 where deptno=10;

2. 뷰의 삭제

형식) DROP view 뷰이름

SQL> DROP view v_ename;

3. 뷰의 종류

- 단순 뷰: 하나의 테이블로 만들어진 뷰

             DML 가능

             DISTINCT 불가능, 그룹함수 불가능

- 복합 뷰: 여러개의 테이블로 만들어진 뷰(ex. 조인)

             DML 불가능

             DISTINCT 가능,그룹함수 가능

 

4. 단순 뷰 - 그룹함수 사용

Q. 부서별로 최대급여를 조회할 수 있는 뷰를 작성하시오.

(뷰의 내용도 수정이 가능하게 작성, deptno,max(sal) 순으로 작성)

SQL> create or replace view v_maxsal
as select deptno,MAX(SAL) as "최대 급여" from emp
group by deptno;

 

결과>

SQL> select * from v_maxsal;
DEPTNO  최대 급여
---------- ----------
        30       2850
        20       3000
        10       5000

=> 단순 뷰에서 그룹함수를 사용하려면 별칭을 부여해야 된다.

5. 복합 뷰 - 조인

Q.사원의 이름과 부서명을 출력시켜주는 뷰를 작성하시오.

(뷰이름 : v_dname)

SQL> create or replace view v_dname
as select e.ename,d.dname from emp e,dept d
WHERE E.DEPTNO = D.DEPTNO;

 

결과>

SQL> select * from v_dname;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH

ENAME      DNAME
---------- --------------
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 개의 행이 선택되었습니다.

6. 서브쿼리를 이용한 뷰

Q. SMITH보다 많은 급여를 받는 사원의 ename,sal,deptno을 조회하시오.

(뷰 수정 가능하도록 작성, 뷰이름 : v_smith)

SQL> create or replace view v_smith
as select ename,sal,deptno from emp
where sal >(SELECT SAL FROM EMP WHERE ENAME='SMITH');

 

결과>

SQL> select * from v_smith;

ENAME             SAL     DEPTNO
---------- ---------- ----------
ALLEN            1600         30
WARD             1250         30
JONES            2975         20
MARTIN           1250         30
BLAKE            2850         30
CLARK            2450         10
SCOTT            3000         20
KING             5000         10
TURNER           1500         30
ADAMS            1100         20
JAMES             950         30

ENAME             SAL     DEPTNO
---------- ---------- ----------
FORD             3000         20
MILLER           1300         10

13 개의 행이 선택되었습니다.

 

7. 뷰를 통한 제약조건 - with check option

형식) ~ WITH CHECK OPTION constraint 제약조건이름;

Q. b_emp5테이블을 이용해서 30번 부서만 가진 뷰를

with check option을 부여해서 작성하시오.(모든정보*)

SQL> create or replace view emp30
       as select * from b_emp5
       where deptno=30 
       WITH CHECK OPTION constraint emp30_ck;

 

Q. 7566번 사원의 부서번호를 30번으로 변경하는 SQL작성

SQL> update emp30 set deptno=30 where empno=7566;
=> 제약 조건 때문에 수정되지 않음
(조건에 만족하는 30번부서의 데이터에 한해 수정 가능)

 

8. 뷰를 통한 제약조건 - with read only

SQL> create or replace view dept30 as select deptno,dname
from dept where deptno=30 WITH READ ONLY;

 

형식) delete from 뷰이름;
SQL> delete from dept30;
=>읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.