KIC/ORACLE

[Oracle] 뷰

octopengj 2020. 10. 12. 14:38

뷰의 개요

  • 가상의 테이블

  • 테이블처럼 보이나 실제 존재하는 테이블이 아니다.

  • 저장공간이 없다. -> 텍스트로 저장(뷰의 내용(SQL문장))

뷰를 사용하는 목적

  • 편리성 추구 -> 복잡한 SQL구문을 쉽게 실행할 수 있다.

  •  

    보안목적으로 사용

     

     

<문1> emp 테이블에서 20번부서에 근무하는 사원의 정보를 검색할 수 있는 SQL을 뷰로 작성하시오.

 

 

형식) create view 뷰이름(v_xxx)

 

        as SQL 구문

 

sql> create view emp_20 as select * from b_emp3 where deptno=20;

SQL> create view emp_20 as select * from b_emp3 where deptno=20;
create view emp_20 as select * from b_emp3 where deptno=20
            *
1행에 오류:
ORA-01031: 권한이 불충분합니다
--> 현재 scott으로 로그인 상태이다.
--> scott으로 뷰작업하려면 관리자로 로그인하여 권한을 부여받아야 함.

oracle 8i,9i -> 에러유발x

 

oralce 10g 이상 -> 보안 -> create view 권한 (보안이 중요해짐)

 

 

 

scott으로 뷰작업하려면 관리자로 로그인하여 권한을 부여받아야 함.

 

SQL> conn sys/sys12345 as sysdba
연결되었습니다.
SQL> grant create view to scott;
권한이 부여되었습니다.

 

scott 로그인 후 생성

SQL> conn scott
비밀번호 입력:
연결되었습니다.
SQL> create view emp_20 as select * from b_emp3 where deptno=20;

뷰가 생성되었습니다.

뷰를 실행

형식) select * from 실행시키고자하는 뷰명;

sql> select * from emp_20;

 

 

뷰를 사용할 때 주의할 점

 

  • 뷰가 실행이 안되는 경우

  • 뷰를 통해서 만들어진 테이블의 구조에 문제발생

 

 

 

<문2> A로 시작하는 사원의 정보를 출력하는 SQL

 

 

emp_name 이름

 

 

 

create view emp_name

as select empno,ename from b_emp3

where ename like 'A%';

 

SQL> create view emp_name
  2  as select empno,ename from b_emp3
  3  where ename like 'A%';

뷰가 생성되었습니다.
SQL> select * from emp_name;

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7876 ADAMS

 

형식) alter table 수정t명 set unused column 사용하지 못하게 필드명

SQL> alter table b_emp3 set unused column ename;

테이블이 변경되었습니다.    //복구->rollback=>복구X
SQL> desc b_emp3;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
SQL> select * from emp_name;
select * from emp_name
              *
1행에 오류:
ORA-04063: view "SCOTT.EMP_NAME"에 오류가 있습니다

 

 

<문3>

 

더이상 불필요로하는 뷰를 삭제=>drop view 삭제시킬 뷰이름

->create view 권한->뷰를 수정,삭제할 수가 있다.

 

SQL> drop view emp_name;

뷰가 삭제되었습니다.

 

 

<문4>

데이터 딕셔너리 -> user_views(시스템 테이블) => 저장

                          user_constraints(제약조건)

뷰를 생성 => 텍스트로 저장(SQL구문)

 

-- desc user_views -> 유저확인

SQL> desc user_views
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME //뷰이름                                  NOT NULL VARCHAR2(30)
 TEXT_LENGTH  //뷰의 문자열길이(SQL문 길이)             NUMBER
 TEXT  //뷰의 내용 -> 텍스트                           LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)
 EDITIONING_VIEW                                    VARCHAR2(1)
 READ_ONLY  // 읽기 전용                              VARCHAR2(1)

4개의 필드 확인 필요

 

SQL>select view_name, text_length, text from user_views;

SQL> select view_name, text_length, text from user_views;

VIEW_NAME                      TEXT_LENGTH
------------------------------ -----------
TEXT
--------------------------------------------------------------------------------
EMP_20                                  95
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from b_emp3

지금까지 만들어진 뷰 확인

 

-- 테이블 길이 조절

SQL>col view_name format a15

SQL>col text_length format 99,990

SQL>col text format a40

SQL> col view_name format a15
SQL> col text_length format 99,990
SQL> col text format a40
SQL> select view_name, text_length, text from user_views;

VIEW_NAME       TEXT_LENGTH TEXT
--------------- ----------- ----------------------------------------
EMP_20                   95 select "EMPNO","ENAME","JOB","MGR","HIRE
                            DATE","SAL","COMM","DEPTNO" from b_emp3


-- 자주 사용하는 뷰 생성

 

create view v_search as select view_name, text_length, text from user_views;

SQL> create view v_search as select view_name, text_length, text from user_views;

뷰가 생성되었습니다.
SQL> select * from v_search;

VIEW_NAME       TEXT_LENGTH TEXT
--------------- ----------- ----------------------------------------
EMP_20                   95 select "EMPNO","ENAME","JOB","MGR","HIRE
                            DATE","SAL","COMM","DEPTNO" from b_emp3

V_SEARCH                 51 select view_name, text_length, text from
                             user_views

 

 

 

<문5> b_emp4테이블에서 부서번호가 10번인 데이터를 찾아서 그 사원의 사번, 이름, 입사일만 출력하는 뷰를 작성하시오. 

(employee_no, employee_name, e_hiredate) 임의로 필드이름을 변경해서 작성

 

 

만들어진 뷰를 나중에 다시 수정하고 싶다? -> 수정 불가

뷰를 작성할 때 처음부터 수정까지 하기를 원할 경우? create [or replace]

 

형식) create view 뷰이름 as sql구문 => 수정X -> 삭제하고 새로 작성해야 된다.

create [or replace] view 뷰이름 as sql구문 => 수정가능

 

 

-- emp4 테이블생성

create table b_emp4 as select * from emp;

SQL> create table b_emp4 as select * from emp;

테이블이 생성되었습니다.

 

-- 뷰 생성

create or replace view v_emp10(employee_no, employee_name, e_hiredate) as select empno, ename, hiredate from b_emp4 where deptno=10;

SQL> create or replace view v_emp10(employee_no, employee_name, e_hiredate) as select empno, ename, hiredate from b_emp4 where deptno=10;

뷰가 생성되었습니다.

 

-- 뷰 생성 확인

SQL> select * from v_emp10;

SQL> select * from v_emp10;

EMPLOYEE_NO EMPLOYEE_N E_HIREDA
----------- ---------- --------
       7782 CLARK      81/06/09
       7839 KING       81/11/17
       7934 MILLER     82/01/23

 

-- 수정 가능하다

SQL> create or replace view v_emp10(id, job2, hire) as select empno, job, hiredate from b_emp4 where deptno=20 or ename like '%D%';

SQL> create or replace view v_emp10(id, job2, hire) as select empno, job, hiredate from b_emp4 where deptno=20 or ename like '%D%';

뷰가 생성되었습니다.
SQL> select * from v_emp10;

        ID JOB2      HIRE
---------- --------- --------
      7369 CLERK     80/12/17
      7521 SALESMAN  81/02/22
      7566 MANAGER   81/04/02
      7788 ANALYST   87/04/19
      7876 CLERK     87/05/23
      7902 ANALYST   81/12/03

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

 

 

 

<문6> 뷰의 종류

뷰를 통해서 테이블관리가 가능하다. (insert, update, delete)

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

    • 하나의 테이블로 생성

    • 그룹 함수의 사용이 불가능

    • DISTINCT 사용이 불가능

    • DML(insert/update/delete) 사용 가능

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

    • 여러 개의 테이블로 생성

    • 그룹 함수의 사용이 가능

    • DISTINCT 사용이 가능

    • DML(insert/update/delete) 사용 불가능

 

단순뷰

b_emp4 => DML

부서번호가 30인 사원의 이름, 급여, 부서번호만 보여주는 뷰를 작성?(view_30)

 

create view view_30 as select ename, sal, deptno from b_emp4 where deptno=30;

SQL> create view view_30 as select ename, sal, deptno from b_emp4 where deptno=30;

뷰가 생성되었습니다.

select * from view_30;

SQL> select  * from view_30;

ENAME             SAL     DEPTNO
---------- ---------- ----------
ALLEN            1600         30
WARD             1250         30
MARTIN           1250         30
BLAKE            2850         30
TURNER           1500         30
JAMES             950         30

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

 

형식) update 수정t명 set 수정할 필드명 = 수정할 값,,,

        where 조건식

=> update view_30 set sal=0

     where ename='ALLEN';

SQL> update view_30 set sal=0 where ename='ALLEN';

1 행이 갱신되었습니다.

 

SQL> select * from view_30;

ENAME             SAL     DEPTNO
---------- ---------- ----------
ALLEN               0         30
WARD             1250         30
MARTIN           1250         30
BLAKE            2850         30
TURNER           1500         30
JAMES             950         30

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

ALLEN sal = 0 확인 가능

 

-- 원래대로 복구 (rollback)

    무조건 취소되는건 아님 (insert, update, delete만 복구가능)

SQL> rollback;

롤백이 완료되었습니다.

SQL> select * from view_30;

ENAME             SAL     DEPTNO
---------- ---------- ----------
ALLEN            1600         30
WARD             1250         30
MARTIN           1250         30
BLAKE            2850         30
TURNER           1500         30
JAMES             950         30

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

 

 

<문7> 단순뷰에서도 그룹함수 사용이 가능

부서별로 최대급여를 조회할 수 있는 뷰를 작성하시오.(뷰의 내용도 수정이 가능하게 설정할 것)

  • 부서별로 = group by deptno

  • 최대급여 = v_maxsal

sql> create or replace view v_maxsal as select deptno, max(sal) from emp group by deptno;

SQL> create or replace view v_maxsal as select deptno, max(sal) from emp group by deptno;
create or replace view v_maxsal as select deptno, max(sal) from emp group by deptno
                                                  *
1행에 오류:
ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다

정상적인 방법으로는 단순뷰에서는 그룹함수 사용이 불가능하다 - > 별명을 사용하면 가능.

create or replace view v_maxsal as select deptno, max(sal) as "최대급여" from emp group by deptno;

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

 

 

<문8>

복합뷰

 

sql> create or replace view v_dname as select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

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 개의 행이 선택되었습니다.

 

 

<문9> 복합뷰(서브쿼리)

SMITH보다 많은 급여를 받는 사원의 이름, 급여, 부서번호를 조회할 수 있는 SQL작성(단 뷰이름 v_smith 경우에 따라서는 뷰의 내용도 수정)

 

 

create or replace view v_smith as select ename, sal, deptno from emp where sal > (select sal from emp where ename='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 개의 행이 선택되었습니다.

단순뷰 => DML이 가능 but 예외조항

  1. delete가 안되는 경우 => 그룹함수, group by, distinct를 사용하는 경우

  2. insert, update가 안되는 경우 => 그룹함수, group by, distinct를 사용하는 경우

정리: 존재하지 않는 가상필드를 이용하는 경우 insert, update, delete 실행이 안된다.

 

 

 

 

<문10> b_emp4테이블을 이용해서 emp30(30번 부서만 가진)뷰를 작성 with check option을 부여해서 작성하시오(모든 정보 *)

 

뷰에도 제약조건을 부여할 수 있다.

[with check option] -> 정해진 규칙 - > insert, update, delete

 

 

형식) ~ with check option constraint 제약조건이름;

sql> create or replace view emp30 as select * from b_emp4 where deptno=30 with check option constraint emp30_ck;

SQL>  create or replace view emp30 as select * from b_emp4 where deptno=30 with check option constraint emp30_ck;

뷰가 생성되었습니다.

 

empno-> 7566 인 직원 => deptno가 30이 아닌 20부서이다. 이런 경우에 부서번호를 30번으로 변경하고 싶다?

SQL> select empno, deptno from b_emp4;

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7499         30
      7521         30
      7566         20
      7654         30
      7698         30
      7782         10
      7788         20
      7839         10
      7844         30
      7876         20

     EMPNO     DEPTNO
---------- ----------
      7900         30
      7902         20
      7934         10

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

sql> update emp30 set deptno=30 where empno=7566;

SQL> update emp30 set deptno=30 where empno=7566;

0 행이 갱신되었습니다.

수정이 안된다..... 20번 부서이기 때문에 안된다. (30번부서가 아니니까)

 

 

 

<문11> 데이터를 입력하는데 id값이 100이하인 경우에만 데이터 입력을 허용해주는 뷰를 작성(v_test1)

 

sql> create table test1(id number); // 만약 id >= 1 and id < 100 제약을 걸고 싶다면 다음과 같이 작성

sql> create view v_test1 as select * from test1 where id < 100 with check option constraint test1_ck;

                                                                                                (constraint test1_ck 생략 가능)

SQL>  create table test1(id number);

테이블이 생성되었습니다.

SQL> create view v_test1 as select * from test1 where id < 100 with check option constraint test1_ck;

뷰가 생성되었습니다.

 

---행 만들기

형식) insert into 뷰이름 values(값);

sql> insert into v_test1 values(1);

SQL> insert into v_test1 values(1);

1 개의 행이 만들어졌습니다.

SQL> insert into v_test1 values(2);

1 개의 행이 만들어졌습니다.

SQL> insert into v_test1 values(3);

1 개의 행이 만들어졌습니다.

SQL> insert into v_test1 values(4);

1 개의 행이 만들어졌습니다.

SQL> insert into v_test1 values(5);

1 개의 행이 만들어졌습니다.

SQL> select * from v_test1;

        ID
----------
         1
         2
         3
         4
         5

100이 넘어감다면? 다음과 같이 오류가 발생한다. (where id < 100 with check option 조건을 주었기 때문에)

SQL> insert into v_test1 values(101);
insert into v_test1 values(101)
            *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

 

 

 

<문12> 뷰를 작성할 때 with read only를 부여하는 경우(읽기전용뷰) -> 수정불가

 

부서번호가 30번인 부서이름을 출력시켜주는 뷰를 작성(with read only) => 삭제?

sql> create or replace view dept30 as select deptno, dname from dept where deptno=30 with read only;

SQL> create or replace view dept30 as select deptno, dname from dept where deptno=30 with read only;

뷰가 생성되었습니다.

 

 

-- 삭제를 해볼까?

형식) delete from 뷰이름명 where 조건식

sql> delete from dept30;

SQL> delete from dept30;
delete from dept30
            *
1행에 오류:
ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

안된다 select * from dept30;와 같이 읽기만 해야한다.

SQL> select * from dept30;

    DEPTNO DNAME
---------- --------------
        30 SALES

 

 

 

'KIC > ORACLE' 카테고리의 다른 글

[Oracle] 트랜잭션  (0) 2020.10.12
[Oracle] 인덱스, 시퀀스  (0) 2020.10.12
[ Oracle] 서브쿼리, 집합연산자  (0) 2020.10.12
[Oracle] 조인개요 및 작성법  (0) 2020.10.12
[Oracle] 그룹함수, DDL, 테이블 제약조건  (0) 2020.10.12