뷰의 개요
-
가상의 테이블
-
테이블처럼 보이나 실제 존재하는 테이블이 아니다.
-
저장공간이 없다. -> 텍스트로 저장(뷰의 내용(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=>복구XSQL> 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 예외조항
-
delete가 안되는 경우 => 그룹함수, group by, distinct를 사용하는 경우
-
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
5100이 넘어감다면? 다음과 같이 오류가 발생한다. (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 |