[Oracle] 뷰 (View) 단일뷰/복합뷰

<뷰> View

- 물리적인 테이블을 근거한 논리적인 가상테이블.

 (가상: 실질적으로 데이터를 저장하고 있지 않는다.)

   ----> 독립적인 테이블은 아니다!!

   

- 기본테이블에 대한 하나의 쿼리문.(text를 저장하는 객체) 

- 자주 사용되는 복잡하고 긴 쿼리문을 저장하는 객체.

- 특정컬럼만 보여지게끔하여 보안을 목적으로 사용.

형식) CREATE VIEW 뷰이름

     AS select_statement;

     create view emp_copy

     as select * from emp;

---> ORA-01031: insufficient privileges (권한 불충분!!)

<권한부여> - 관리자

  형식) GRANT system_privilege

      TO user_name;

      

<권한회수> - 관리자

  형식) REVOKE system_privilege

      FROM user_name;

      

- 관리자 계정 연결      

  SQL> conn system/oracle    

      

- 뷰객체 생성 권한 ---> scott

  grant CREATE VIEW to scott;

  ==> Grant succeeded.

  

conn scott/tiger  

<데이터베이스 관리자가 가지는 시스템권한>

CREATE USER - 새롭게 사용자를 생성하는 권한

DROP USER - 사용자를 삭제하는 권한

DROP ANY TABLE - 임의의 테이블을 삭제할 수 있는 권한

QUERY REWRITE - 질의 재작성을 할 수 있는 권한

BACKUP ANY TABLE - 임의의 테이블을 백업할 수 있는 권한

<데이터베이스 관리권한>

CREATE SESSION - 데이터베이스에 접속할 수 있는 권한

CREATE TABLE - 사용자 스키마에서 테이블을 생성할 수 있는 권한.

CREATE VIEW - 사용자 스키마에서 뷰을 생성할 수 있는 권한.

CREATE SEQUENCE - 사용자 스키마에서 시퀀스을 생성할 수 있는 권한.

CREATE PROCEDURE - 사용자 스키마에서 함수을 생성할 수 있는 권한.  

SQL> create view emp_copy

     as select * from emp;

    ==> View created.

    

desc emp_copy    

    

SQL> desc emp_copy

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)    

    

문제) 30번 부서 사원들의 정보를 출력.

 select empno,ename,sal, deptno 

 from emp_copy

 where deptno=30;   

    

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7499 ALLEN                      1600         30

      7521 WARD                       1250         30

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7844 TURNER                     1500         30

      7900 JAMES                       950         30    

        

문제) emp(원본,물리적테이블)테이블에 홍길동 사원을 추가하시오!!

  insert into emp (empno, ename, sal, deptno)

             values (8000,'홍길동',3000, 30);

  ==> 추가는 emp(TABLE), 조회는 emp_copy(VIEW)

  

 select empno,ename,sal, deptno 

 from emp_copy

 where deptno=30;             

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7499 ALLEN                      1600         30

      7521 WARD                       1250         30

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7844 TURNER                     1500         30

      7900 JAMES                       950         30

      8000 홍길동                      3000         30   

---> emp_copy는 기준테이블(emp)을 참조하는 (논리적인) 테이블이기 때문!!

문제) 30번 부서에 근무하는 사원의 사원번호, 사원명, 급여를 자주 조회한다.

     이를 뷰로 생성하시오.

     

   create view 뷰명

   as 서브쿼리;

   

   create view emp_copy

   as select empno, ename, sal

      from emp

      where deptno=30;  

---> ORA-00955: name is already used by an existing object

  

   create view emp_30

   as select empno, ename, sal

      from emp

      where deptno=30;

   ==> 뷰 생성

   

   select * from emp_30;

SQL> select * from emp_30;

     EMPNO ENAME                       SAL

---------- -------------------- ----------

      7499 ALLEN                      1600

      7521 WARD                       1250

      7654 MARTIN                     1250

      7698 BLAKE                      2850

      7844 TURNER                     1500

      7900 JAMES                       950

      8000 홍길동                     3000

      

<View 형식>

   CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름 [(alias)]

   AS select_statement

   [WITH CHECK OPTION [CONSTRAINT 제약명]]

   [WITH READ ONLY [CONSTRAINT 제약명]];

   ---> 뷰 emp_30에 입사일, 부서번호를 추가!!

   create view emp_30

   as select empno, ename, sal, hiredate, deptno

      from emp

      where deptno=30; 

   ---> ORA-00955: name is already used by an existing object

        ---> 이미 객체이름을 사용중에 있습니다!!

   create or replace view emp_30

   -- 존재하지 않는 뷰는 새로 생성하고 존재하는 뷰의 경우에는 text내용만 변경해라!!

   as select empno, ename, sal, hiredate, deptno

      from emp

      where deptno=30; 

    ==> 뷰생성 성공!!

뷰관련 시스템 테이블 ---> user_views

   desc user_views

   

VIEW_NAME : 생성된 뷰이름

TEXT      : 뷰의 내용(select문!!)

select view_name, text from user_views;

VIEW_NAME      TEXT

------------------------------------------------------------

EMP_COPY     select "EMPNO","ENAME","JOB","MGR",

             "HIREDATE","SAL","COMM","DEPTNO" from emp

EMP_30       select empno, ename, sal, hiredate, deptno

             from emp

             where deptno=30

문제) abc테이블(존재하지 않는 테이블)의 모든 컬럼을 조회하는 뷰를 생성하시오.

  create or replace view abc_view

  as select * from abc;  

---> ORA-00942: table or view does not exist

  create or replace force view abc_view

  as select * from abc;  

---> Warning: View created with compilation errors.  

문제) emp_30(empno,ename,sal,hiredate,deptno)에서 mgr을 조회하시오.

   select empno, ename

   from emp_30;

   

     EMPNO ENAME

---------- --------------------

      7499 ALLEN

      7521 WARD

      7654 MARTIN

      7698 BLAKE

      7844 TURNER

      7900 JAMES

      8000 홍길동

   select empno, ename, mgr

   from emp_30;

   ---> ORA-00904: "MGR": invalid identifier

         ---> emp_30(뷰)이 참조하는 emp(테이블)에는 mgr이 존재하지만

              emp_30(뷰)에 정의되지 않은 mgr은 조회 불가!!

              

<뷰에 대한 DML>

 delete from emp where empno=8000;

 drop view emp_copy;

 

 create table emp_copy

 as select * from emp;

 create or replace view emp_30

 as select empno,ename,sal,deptno

    from emp_copy

    where deptno=30;

 select * from emp_30;

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7499 ALLEN                      1600         30

      7521 WARD                       1250         30

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7844 TURNER                     1500         30

      7900 JAMES                       950         30

   ▶ 뷰를 통한 입력

 insert into emp_30  values (8000,'나길동',3000,30);

 insert into emp_30  values (6000,'너길동',3000,30);

  ==> 입력성공

SQL> select * from emp_30;

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7499 ALLEN                      1600         30

      7521 WARD                       1250         30

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7844 TURNER                     1500         30

      7900 JAMES                       950         30

      8000 나길동                      3000        30

select empno, ename, sal, deptno from emp_copy;

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7369 SMITH                       800         20

      7499 ALLEN                      1600         30

      7521 WARD                       1250         30

      7566 JONES                      2975         20

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7782 CLARK                      2450         10

      7788 SCOTT                      3000         20

      7839 KING                       5000         10

      7844 TURNER                     1500         30

      7876 ADAMS                      1100         20

      7900 JAMES                       950         30

      7902 FORD                       3000         20

      7934 MILLER                     1300         10

      8000 나길동                     3000         30

      6000 너길동                     3000         30

  insert into emp_30  values (8002,'길라임',3000, 20);

  --> 입력성공

 

SQL> select * from emp_30;

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7499 ALLEN                      1600         30

      7521 WARD                       1250         30

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7844 TURNER                     1500         30

      7900 JAMES                       950         30

      8000 나길동                      3000         30

      6000 너길동                      3000         30

SQL> select empno,ename,sal,deptno from emp_copy where deptno=20;

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7369 SMITH                       800         20

      7566 JONES                      2975         20

      7788 SCOTT                      3000         20

      7876 ADAMS                      1100         20

      7902 FORD                       3000         20

      8002 길라임                     3000         20

      

   ▶ 뷰를 통한 삭제

   delete from emp_30;

   --> 8행 삭제

   

   select * from emp_30;

   --> 조회된 행 없음!!

   

SQL> select count(*) from emp_copy;

  COUNT(*)

----------

         9   

   

   CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름 [(alias)]

   AS select_statement

   [WITH CHECK OPTION [CONSTRAINT 제약명]]

   [WITH READ ONLY [CONSTRAINT 제약명]]; 

   

   create or replace view emp_20

   as select empno, ename, sal, deptno

      from emp_copy

      where deptno=20

   with read only;

   ==> view생성 성공!!

   

   select * from emp_20;

   

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7369 SMITH                       800         20

      7566 JONES                      2975         20

      7788 SCOTT                      3000         20

      7876 ADAMS                      1100         20

      7902 FORD                       3000         20

      8002 길라임                     3000         20   

   

<뷰 통한 입력>

  insert into emp_20 values (9000,'김주원',9000, 20);

  ---> ORA-42399: cannot perform a DML operation on a read-only view  

  

<뷰 통한 삭제>

  delete from emp_20;

  ---> ORA-42399: cannot perform a DML operation on a read-only view  

<뷰 통한 수정>

  update emp_20 set sal=2800 where empno=7369;

  ---> ORA-42399: cannot perform a DML operation on a read-only view  

----------------------------------------------------------------

<<View 형식>>

   CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름 [(조회된 컬럼에 대한 alias)]

   AS select_statement

   [WITH CHECK OPTION [CONSTRAINT 제약명]]

   [WITH READ ONLY [CONSTRAINT 제약명]];

drop table emp_copy;

create table emp_copy

as select * from emp;

create or replace view emp_30

as select empno, ename, sal, deptno

   from emp_copy

   where deptno=30;

  

select * from emp_30;   

   

     EMPNO ENAME                       SAL     DEPTNO

---------- -------------------- ---------- ----------

      7499 ALLEN                      1600         30

      7521 WARD                       1250         30

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7844 TURNER                     1500         30

      7900 JAMES                       950         30

create or replace view emp_30 (사원번호, 사원명, 급여, 부서번호)

as select empno, ename, sal, deptno

   from emp_copy

   where deptno=30;

  사원번호         사원명              급여      부서번호

---------- -------------------- ---------- ----------

      7499 ALLEN                      1600         30

      7521 WARD                       1250         30

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7844 TURNER                     1500         30

      7900 JAMES                       950         30

문제) emp_30뷰에서 사원번호와 사원명만 조회하시오!!

   select empno, ename

   from emp_30;

  ---> ORA-00904: "ENAME": invalid identifier (ename컬럼을 인식X)

  ---> 조회컬럼에 별명을 지정한 경우 별명을 통한 조회를 해야 함!!

  

   select 사원번호, 사원명

   from emp_30;

  사원번호 사원명

---------- --------------------

      7499 ALLEN

      7521 WARD

      7654 MARTIN

      7698 BLAKE

      7844 TURNER

      7900 JAMES

문제) 'ALLEN'사원을 20번 부서로 이동하시오. (emp_30뷰 통해)

     update emp_30 set 부서번호=20

           where 사원번호=7499;

문제) '카톡'사원을 10번 부서에 추가하시오. (emp_30뷰 통해) 

     insert into emp_30 (사원번호, 사원명, 급여, 부서번호)

                values (8000, '카톡', 1000, 10);         

 select * from emp_30;

SQL> select * from emp_30;

  사원번호 사원명                   급여     부서번호

---------- -------------------- ---------- ----------

      7521 WARD                       1250         30

      7654 MARTIN                     1250         30

      7698 BLAKE                      2850         30

      7844 TURNER                     1500         30

      7900 JAMES                       950         30

create or replace view emp_30 (사원번호, 사원명, 급여, 부서번호) 

as select empno,ename,sal,deptno

   from emp_copy

   where deptno=30 with check option;

문제) 'JAMES'사원을 20번 부서로 이동하시오. (emp_30뷰)

  update emp_30 set 부서번호=20

  where 사원번호=7900;

---> ORA-01402: view WITH CHECK OPTION where-clause violation

문제) '길라임' 사원을 10번 부서에 추가하시오. (emp_30뷰)

  insert into emp_30 (사원번호, 사원명, 급여, 부서번호)

                values (8005, '길라임', 1000, 10);         

---> ORA-01402: view WITH CHECK OPTION where-clause violation

  insert into emp_30 (사원번호, 사원명, 급여, 부서번호)

                values (8005, '길라임', 1000, 30); 

---> 입력성공!!  

문제) 10번, 20번 부서에 근무하는 사원의 사원번호, 사원명, 급여, 부서명을 조회하는

    emp_join뷰를 생성하시오.

    

  create table dept_copy

  as select * from dept;

  

  <조인하는 서브쿼리>

  select empno, ename, sal, dname

  from emp, dept

  where emp.deptno IN (10,20)

        and emp.deptno=dept.deptno;

  

  select empno, ename, sal, dname

  from emp natural join dept

  where deptno in (10,20);

  

  <view 생성>

  create view 뷰명

  as 서브쿼리;

  

  create or replace view emp_join

  as select empno, ename, sal, dname

  from emp natural join dept

  where deptno in (10,20);

  ---> 뷰생성

  

SQL> select * from emp_join;

     EMPNO ENAME                       SAL DNAME

---------- -------------------- ---------- ----------------------------

      7369 SMITH                       800 RESEARCH

      7566 JONES                      2975 RESEARCH

      7782 CLARK                      2450 ACCOUNTING

      7788 SCOTT                      3000 RESEARCH

      7839 KING                       5000 ACCOUNTING

      7876 ADAMS                      1100 RESEARCH

      7902 FORD                       3000 RESEARCH

      7934 MILLER                     1300 ACCOUNTING

  

insert into emp_join (empno, ename, sal, dname)

             values (8000,'홍길동',3000,'RESEARCH');

===> 실행에러

             

update emp_join set sal=2600 where empno=7934;

===> 성공

delete from emp_join;             

===> 성공

===> 복합뷰에 대해서 READ ONLY 제약을 걸지 않았을 때, 왜 INSERT만 에러가나고 UPDATE와 DELETE는 성공하는지 의문이다 .. 

===> 복합뷰(조인테이블)에서는 기본적으로 DML사용 불가!!

===> DML을 방지하기 위해서는 반드시 view생성시 with read only를 적용해야 함!!