붓, 그리다
뷰 veiw 생성/삭제/수정 본문
오라클객체
테이블, 뷰, 시컨스, 시노닝(동의어), 인덱스,,권한종류,복구,,
[ 뷰 ]
1) 뷰의 개요
- 가상의 테이블, 실제 존재하는 테이블이 아니다.
- 저장공간이 따로 없고 텍스트로 저장
- 실행하면 테이블처럼 보임
- 뷰는 자주 사용되고 반복적일 때도 사용
*테이블로 저장하는 것과 뷰로 저장하는 것의 차이점
- 뷰로 저장(텍스트로 저장)하면 데이터 공간을 많이 차지하지 않는다.
2) 뷰의 사용 목적
- 편리성 추구 : 복잡한 SQL문장을 쉽게 실행 가능
- 보안성
Q2. emp테이블에서 20번 부서의 근무하는 사원의 정보를 검색할 수 있는 SQL문장을 뷰로 작성하시오.
create view 뷰이름(v_xxx) as 실행시킬 SQL 문장
-> view는 DDL
1) 뷰 작성하기
create view emp_20
as select * from b_emp3 where deptno=20;
1행에 오류:
ORA-01031: 권한이 불충분합니다
-> 오라클10g 이후부터 뷰를 작성할 때 권한(create view)이 없으면 작성할 수 없다.
2) 권한 주기(create view, create synonym)
SQL> conn sys/sys1234 as sysdba
연결되었습니다.
SQL> grant create view, create synonym to scott,test01,test1;
권한이 부여되었습니다.
*) 관리자가 계정을 생성할 때 뷰와 시노님의 권한을 같이 줄 경우 문장
grant connect, resource, create view, create synonym to 사용자명;
3) 뷰 생성하기
SQL> conn scott/tiger
연결되었습니다.
SQL> create view emp_20
2 as select * from b_emp3 where deptno=20;
뷰가 생성되었습니다.
4) 뷰 실행하기
select * from 실행시키고자하는 뷰명;
SQL> select * from emp_20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH SALES MAN 7902 80/12/17 800
20
7566 JONES MANAGER 7839 81/04/02 2975
20
7788 SCOTT ANALYST 7566 87/04/19 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 87/05/23 1100
20
7902 FORD ANALYST 7566 81/12/03 3000
20
-> 정해진 SQL구문만 실행, 정보를 보호할 수 있으며 편리성을 추구한다.
Q3. 뷰를 사용할 때 주의할 점 : 뷰가 실행이 안되는 경우
-> 뷰를 통해서 만들어진 테이블의 구조에 문제가 발생 할 때
1) 뷰 생성
create view emp_name
as select empno, ename from b_emp3
where ename like 'A%';
뷰가 생성되었습니다.
SQL> select * from emp_name;
EMPNO ENAME
---------- ----------
7499 ALLEN
7876 ADAMS
-> 뷰의 모태가 되는 테이블의 구조를 수정해보자
Q4. 오라클에서 임의로 특정테이블의 필드를 사용하지 못하게 하기(백업 테이블에 사용)
alter table 수정할 테이블명 set unused column 사용하지 못하게 할 필드명
1) ename 필드를 사용하지 못하게 해보자
SQL> alter table b_emp3 set unused column ename;
테이블이 변경되었습니다. -> 테이블 필드가 없다고 보면 됨
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)
2) 뷰 실행하기
SQL> select * from emp_name;
select * from emp_name
*
1행에 오류:
ORA-04063: view "SCOTT.EMP_NAME"에 오류가 있습니다
-> 지금 상태에서 살릴 방법이 없음
Q5. 불필요한 뷰를 삭제하기
drop view 삭제시킬 뷰 이름
*create view 안에 뷰를 수정, 삭제할 기능이 포함
1) 뷰 삭제하기
SQL> drop view emp_name;
뷰가 삭제되었습니다.
Q6. 데이터 딕셔너리 : user_views(오라클의 시스템테이블)
-> 뷰의 정보를 볼 수 있다.
*뷰 생성-> 텍스트로 저장(SQL구문)
SQL> desc user_views
이름 널? 유형
----------------------------------------- -------- --------------------------
VIEW_NAME(뷰이름) NOT NULL VARCHAR2(30)
TEXT_LENGTH(뷰의 문자열 길이) 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)
1) 생성된 뷰의 구조 확인(emp_20)
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> ed
file afiedt.buf(이)가 기록되었습니다
1* select view_name, text_length, text from user_views
SQL> /
VIEW_NAME TEXT_LENGTH TEXT
--------------- ----------- ----------------------------------------
EMP_20 95 select "EMPNO","ENAME","JOB","MGR","HIRE
DATE","SAL","COMM","DEPTNO" from b_emp3
*SQL plus 명령어는 Buffer에 기록되지 않음
-> 생성된 뷰의 구조를 확인하는 것을 자주 사용함 -> 이것을 뷰로 생성해보기
2) user_views테이블을 조회하는 뷰를 작성
create view v_search
as select view_name, text
from user_views;
뷰가 생성되었습니다.
SQL> select * from v_search;
VIEW_NAME TEXT
--------------- ----------------------------------------
EMP_20 select "EMPNO","ENAME","JOB","MGR","HIRE
DATE","SAL","COMM","DEPTNO" from b_emp3
V_SEARCH select view_name, text
from user_views
-> 뷰를 조회하는 뷰를 실행함
Q7. 만들어진 뷰를 나중에 다시 수정하고 싶을 경우
-> 뷰를 어떻게 만드냐에 따라 수정이 가능하고 불가능
*뷰를 작성할 때, 처음부터 수정까지 감안하고 생성하라
create [or replace] view 뷰 이름 as SQL구문
1) b_emp4 테이블에서 부서번호가 10번인 데이터를 찾아서 그 사원의 사번, 이름, 입사일만 출력시켜주는 뷰를 작성하시오.
(단, employee_no, employee_name, e_hiredate 필드 이름을 임의로 변경) -> 가상 테이블의 필드명
SQL> create or replace view v_emp_10(employee_no, employee_name, e_hiredate)
as select empno, ename, hiredate from b_emp4
where deptno=10;
뷰가 생성되었습니다.
SQL> select * from v_emp_10;
EMPLOYEE_NO EMPLOYEE_N E_HIREDA
----------- ---------- --------
7782 CLARK 81/06/09
7839 KING 81/11/17
7934 MILLER 82/01/23
SQL> select * from v_search;
VIEW_NAME TEXT
--------------- ----------------------------------------
EMP_20 select "EMPNO","ENAME","JOB","MGR","HIRE
DATE","SAL","COMM","DEPTNO" from b_emp3
V_EMP_10 select empno, ename, hiredate from b_emp
4
where deptno=10
V_SEARCH select view_name, text
from user_views
2) 뷰 수정하기(=덮어쓰기와 같다)
create or replace view v_emp_10(id, job2, hire)
as select empno, job, hiredate from emp
where deptno=20 or ename like '%D';
뷰가 생성되었습니다.
SQL> select * from v_emp_10;
ID JOB2 HIRE
---------- --------- --------
7369 SALES MAN 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 개의 행이 선택되었습니다.
[뷰의 종류] ppt 참조
-> 뷰를 통해서 데이터 관리가 가능한가?
1) 단순뷰
- 하나의 테이블로 만들어진 뷰, DML이 가능
- 그룹함수, distinct 사용 불가
2) 복합뷰
- 여러개의 테이블로 만들어진 뷰, DML이 불가
- 그룹함수, distinct 사용가능
Q8. 부서번호(view_30)가 30번인 사원의 이름, 급여, 부서번호만 보여주는 뷰를 작성하시오.
1) 단순뷰 생성(DML이 가능한지 확인)
create view view_30
as select ename, sal, deptno
from b_emp4
where deptno=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 개의 행이 선택되었습니다.
2) ALLEN이 1개월 감봉 (수정)
update 수정할뷰이름 set 수정할필드명=수정할값,,,
where 조건식;
-> insert, delete 역시 수정할 테이블명은 수정할 뷰이름으로 변경하면 된다.
EX1) 수정하기
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 개의 행이 선택되었습니다.
EX2) Rollback (insert, update, delete 기준, DDL은 불가)
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 개의 행이 선택되었습니다.
-> 범위를 지정하지 않으면 모두 원상태로 돌아감
Q9. 단순뷰에서도 그룹함수 사용이 가능하다.
<문제> 부서별로 최대 급여를 조회할 수 있는 뷰를 작성하시오. (단, 뷰의 내용도 수정이 가능하게 설정할 것) 부서번호, 최대급여를 출력
create or replace view v_maxsal
as select deptno, max(sal) as "최대 급여"
from emp
group by deptno;
as select deptno, max(sal)
*
2행에 오류:
ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다
-> 뷰를 작성하여 실행시킬 SQL 구문의 가상필드는 별칭을 부여해야함
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 create or replace view v_maxsal
2 as select deptno, max(sal) as "최대 급여"
3 from emp
4* group by deptno
SQL> /
뷰가 생성되었습니다.
SQL> select * from v_maxsal;
DEPTNO 최대 급여
---------- ----------
30 2850
20 3000
10 5000
-> 단순뷰에서 그룹함수를 사용이 가능하지만 별칭을 부여 해야 한다.
Q10. 복합뷰 : DML 사용 불가능, 조인/서브쿼리 주로 사용
1) 조인 : 사원의 이름과 부서명을 출력시켜주는 뷰를 작성(단, 부서이름은 v_dname)
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 개의 행이 선택되었습니다.
-> DML(insert, update, delete )사용 불가
Q11. 서브쿼리로 복합뷰 생성하기
문제) 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=upper('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 개의 행이 선택되었습니다.
1) smith의 급여
select sal from emp
where ename='SMITH';
2) smith보다 급여를 많이 받는 사람
select ename, sal, deptno
from emp
where sal> 800
[ 단순뷰의 예외 조항 : DML이 사용 불가 ]
1) delect가 안되는 경우
: 그룹함수, group by, distinc를 사용하는 경우(즉, 계산필드
2) insert, update가 안되는 경우
: 그룹함수, group by, distinc, 별칭부여(가상필드=그룹함수=계산필드)를 사용하는 경우
3) rownum을 사용하는 경우
-> 존재하지 않는 가상 필드를 이용하는 경우 insert, update, delete 실행이 안된다.
Q12. [with check option] : 뷰에 제약조건을 부여하는 경우
- 정해진 규칙에 따라서 insert, update, delete를 적용하기 위해
~ with check option constraint 제약조건이름;
b_emp4테이블을 이용해서 emp30(30번 부서)만 가진 뷰를 작성(with check option 뷰를 부여해서 작성하시오. 모든 정보 *)
create or replace view emp30
as select * from b_emp4
where deptno=30 // 제약조건에 해당
with check option constraint emp30_ck;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 81/05/01 2850
30
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7900 JAMES CLERK 7698 81/12/03 950
30
6 개의 행이 선택되었습니다.
2) empno가 7566인 사원의 부서번호를 SQL작성하시오
update emp30
set deptno=30
where empno=7566;
0 행이 갱신되었습니다.
Q13. 데이터를 입력하는데 id 값이 100이하인 경우에만 입력을 허용해주는 뷰를 작성하시오. (v_test1)
1) 테이블 생성
create table test1(id number);
2) 뷰를 통해 데이터를 입력(제약조건을 걸어서)
create view v_test1
as select * from test1
where id < 100 // 제약조건
with check option constraint test1_ck; // where 조건에 만족하지 않는 것을 입력하지 못함
뷰가 생성되었습니다.
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
SQL> insert into v_test1 values(101);
insert into v_test1 values(101)
*
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
Q14. 뷰를 작성할 때 with read only 경우(읽기 전용 뷰로 실행만 가능, 수정이 불가능)
문제) 부서번호가 30번인 부서이름을 출력시켜주는 뷰
1) 읽기 전용 뷰 생성
create or replace view dept30
as select deptno, dname from dept
where deptno=30
with read only; // 읽기 전용 뷰가 됨
2) 데이터 delect 하기
delete from dept30; // 테이블명 대신 뷰 이름
*
1행에 오류:
ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
Q15. emp 테이블에서 가장 최근에 입사한 5명의 사원의 이름과 입사일을 출력하시오.
-> 오라클 페이징 처리
-> 내부적으로 순위를 매기면서 출력(Top-N)
-> N=5
형식) ★
select rownum "별칭", 필드명1,,,
from (서브쿼리
select 필드명,,,
from 테이블명 // from에서도 서브쿼리 나올 수 있음
order by top-N 필드명(정렬 기준이 되는 필드명) desc[asc]
) 별칭
where 조건식(rownum <= top=N)에 맞는 값을 부여;
EX)
select rownum as num, ename, hiredate
from (
select ename, hiredate
from emp
order by hiredate desc
)
where rownum <=5; [%num]
NUM ENAME HIREDATE
---------- ---------- --------
1 ADAMS 87/05/23
2 SCOTT 87/04/19
3 MILLER 82/01/23
4 JAMES 81/12/03
5 FORD 81/12/03
'Oracle' 카테고리의 다른 글
| 인덱스, 시퀀스 (0) | 2017.06.16 |
|---|---|
| TCL 트랜잭션(commit, rollback, savepoint) (1) | 2017.06.16 |
| 집합연산자 (0) | 2017.06.16 |
| 서브쿼리 (0) | 2017.06.16 |
| 조인 개요 및 작성법 (0) | 2017.06.16 |
Comments