Oracle SQL : View(뷰)

Oracle SQL : View 

---

ACADEMY.sql

0.00MB

EMP_EXAMPLE.sql

0.00MB

star.sql

0.00MB

test_data_eng.txt

0.03MB

-- ACADEMY.sql -> EMP_EXAMPLE.sql -> star.sql -> test_eng.txt sql문 적용하기

뷰.sql

0.01MB

---

View

1. 뷰는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블이다.
2. 뷰는 저장장치 내에 물리적으로 존재하지 않지만 사용자에게 있는 것처럼 간주된다.
3. 뷰는 데이터 보정작업, 처리과정 시험 등 임시적인 작업을 위한 용도로 활용된다.
4. 뷰는 조인문의 사용 최소화로 사용상의 편의성을 최대화 한다.

뷰의 특징

1. 뷰는 기본테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 유사하다.
2. 뷰는 가상 테이블이기 때문에 물리적으로 구현되어 있지 않다.
3. 데이터의 논리적 독립성을 제공할 수 있다.
4. 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해진다.
5. 뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있다.
6. 기본 테이블의 기본키를 포함한 속성(열) 집합으로 뷰를 구성해야하지만 삽입, 삭제, 갱신, 연산이 가능하다.
7. 정의된 뷰는 다른 뷰의 정의에 기초가 될 수 있다.
8. 뷰가 정의된 기본 테이블이나 뷰를 삭제하면 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제 된다.

뷰의 동작순서

• 사람 쿼리 수행 : select * from course_view_20;
• 뷰의 text 컬럼안에 자료인 select문을 수행
• 물리적인 데이터 조회
• 물리적인 데이터 조회에 의한 결과 수행
• 1 로 결과를 리턴

뷰를 사용하는 이유

• 긴 쿼리문, 많이 쓰는 쿼리문을 뷰로 만들기
• 보안에 유리 ( 가상으로 만든 논리적인 데이터를 보여주는 것이기 때문에 보안에 유리)

뷰를 만드는 방법

• select 문을 이용하는 방법
• insert 문을 이용하여 뷰안에 데이터 추가 

insert into emp_view30 values(1111, 'aaaa',30); 
select * from emp_view30;

 물리적 데이터에도 추가 된다.

 

 

---

뷰 생성

-- View
create table emp_copy as
select * from emp;


select * from emp_copy;

emp 테이블을 view 로 만든 SQL문

 

 

항상 특정 컬럼을 보기 위해 긴 쿼리문으로 보았던것을 뷰로 저장해 뷰로 볼 수 있다.

--deptno 30인 사원의 사번, 이름, 부서번호를 검색
-- select empno, ename, deptno from emp where DEPTNO=30; -- 이 쿼리문을 view로 만든다.
create view emp_view30
as select empno, ename, deptno from emp where DEPTNO=30; -- 뷰 생성

select * from emp_view30;
select ename from emp_view30;

 

 

select * from COURSE; -- 물리적 테이블
select * from COURSE where COU_ID=20;

 

create view course_view_20 (cou_id, cou_name)
as select cou_id, COU_NAME from COURSE where COU_ID=20;

select * from course_view_20;

 

create view emp_view_dept as
select e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
from emp_copy e, dept d
where e.deptno=d.deptno;
-- 이렇게 긴 쿼리문을 create view emp_view_dept as 만 붙여
-- 뷰로 만들어 보는 것이다.

 

뷰 수정, 삽입, 삭제

 

select * from emp_view30;
insert into emp_view30 values(2222, 'bbbb',30); -- 뷰에 데이터 삽입
-- 물리적 데이터는 추가되었다.
--view 로 데이터 수정하기
update emp_view30 set ename='감격' where empno=2222;
select * from emp; -- view를 이용해서 물리적 데이터에 값을 삽입, 수정이 된다.
-- view 테이블을 이용하여 데이터 삭제하기
delete from emp_view30 where ename='감격';
-- 삭제도 가능하다.
commit;

 

특정 컬럼을 못보게 하기 위해 권한을 제어하고 싶을 때

-- 급여를 못 보도록 권한을 제어하고 싶을 때
create view emp_view
as
    select empno, ename, job, mgr, hiredate, deptno from emp;
select * from emp_view; -- 필요하지 않은 컬럼을 제외 시켜 생성한다.

급여를 못보도록 뷰를 생성한다.

 

---

모든 뷰 보기

select * from user_views;

뷰 테이블 삭제

-- 뷰를 삭제하기
drop view emp_view_dept;
drop view course_view_20;
drop view emp_view30;
drop view emp_view;

 

---

---

뷰(View)에 다양한 옵션

 

OR REPLACE

create or replace view emp_view30
    -- or replace 는 없으면 새로 만들거나, 존재하면 새로 고치라는 옵션이다.
as
select empno, ename, sal, comm, deptno from emp_copy where deptno=30;
-- 뷰의 구조 보기
desc emp_view30;
select * from emp_view30;

 

-- FORCE는 기존테이블이 없어도 뷰를 만들 때 사용한다. ( 거의 사용하지 않는다. )
-- NOFORCE 는 물리적 테이블이 있어야 뷰를 만드는 것 default 해당됨.

select * from employees; -- 없는 테이블

create or replace force view employees_view
as
    select empno, ename, deptno from employees where deptno=30;
select * from user_views; -- 뷰는 생성이 되었지만 실제로 보려면 오류가 발생한다.
select * from employees_view;
-- 추상처럼 만들어 놓고 나중에 물리적 테이블이 필요하면 그때 넣어서 사용하기 위해 사용함

 

-- 존재하는 테이블인 경우
create or replace view emp_view30
as
    select empno, ename, sal, comm, deptno from emp_copy where deptno=30;

 

update emp_view30 set deptno=20 where sal>=400;

-- 해설 : 급여 400이상인 사번 30이 20으로 바뀌었기 때문에 뷰에서 보이지 않는다.

 

WITH CHECK OPTION;

create or replace view emp_view30
as
    select empno, ename, sal, comm, deptno
    from emp_copy where DEPTNO=30
with  check option; -- 컬럼 값이 deptno 30인 사람은 바꾸지 못하게끔 한다.
--with  check option;
--update에서 다른 컬럼을 바꿔도 되지만 deptno를 바꿀 수 없도록 설정하는 옵션이다.

--with check option;
--update에서 다른 컬럼을 바꿔도 되지만 deptno를 바꿀 수 없도록 설정하는 옵션이다.

update emp_view30 set comm=100 where empno=1002; -- 가능(해설 : 컬럼 deptno를 수정하는게 아니기 때문이다.)
update emp_view30
set deptno = 20

아래는 수정할 수 없는 구문이다.(오류)

update emp_view30
set deptno = 20
where sal>250; -- with check option에 deptno 30번인 사람은 바꿀 수 없다. (불가능)

 

create or replace view emp_view30
as
    select empno, ename, sal, comm, deptno
    from emp_copy where DEPTNO=30
with  check option; -- 컬럼 값이 deptno 30인 사람은 바꾸지 못하게끔 한다.
--with  check option;
--update에서 다른 컬럼을 바꿔도 되지만 deptno를 바꿀 수 없도록 설정하는 옵션이다.

update emp_view30 set comm=100 where empno=1002; -- 가능(해설 : 컬럼 deptno를 수정하는게 아니기 때문이다.)
update emp_view30
set deptno = 20
where sal>250; -- with check option에 deptno 30번인 사람은 바꿀 수 없다. (불가능)

update emp_view30
set ename='홍길동' where sal>250;
-- 가능 (해설 : set 구문에서 컬럼 deptno를 수정하는게 아니기 때문이다.)
select * from emp_view30;

 

---

select * from MEMBER;
-- member 테이블 view 만들기
-- no, name, passwd, id

create or replace view v_m_a
as select no, name, passwd, id
from MEMBER where passwd='a1234' -- password를 변경하지 못하게 끔
with check option;

-- update하기
update v_m_a set name='aaa' where passwd='a1234';
update v_m_a set no='101' where passwd='a1234';
update v_m_a set passwd='1234' where passwd='a1234'; -- 패스워드 변경 불가
select *
from v_m_a;

 

---

---

WITH READ ONLY;

이 옵션은 읽기(보기)만 가능하며, update,insert,delete 등 뷰를 변경 할 수 있는 권한을 없애는 것이다.

-- 모든 컬럼을 읽기로 변경하기 WITH READ ONLY

create or replace view view_read30
as
select *
from emp_copy where DEPTNO=30 with read only;

select * from view_read30;

update view_read30 set ENAME='홍길동' where DEPTNO=30; -- 수정 불가, 보기만 가능
insert into view_read30 values(1020, '김명수'); -- 삽입 불가, 보기만 가능
delete from view_read30 where DEPTNO=30; -- 삭제 불가, 보기만 가능

 

---

---

인라인 뷰로 TOP-N

---

ROWNUM 컬럼 성격 

ROWNUM 컬럼은 오라클의 내부적으로 부여되는데 INSERT 문을 이용하여 입력하면 입력한 순서에 따라 1씩 증가되면서 값이 지정됩니다. 데이터가 입력된 시점에서 결정되는 ROWNUM 컬럼 값은 바뀌지 않습니다.

 

select rownum, empno, ename, hiredate from emp order by ename ;

 

insert 했을 때 자동으로 레코드 넘버가 부여된다.

select rownum, empno, ename, hiredate from emp order by ename desc;

오름차순, 내림차순을 하더라도 rownum (행의 번호, 즉 추가한 레코드의 번호)가 동일하다.

 

입사일 순서대로 테이블 정렬하여 뷰 생성하기

create or replace view view_hire
as
    select empno, ename, hiredate from emp order by HIREDATE;

select * from view_hire;

---

select ROWNUM, empno, ename, hiredate from view_hire where ROWNUM<=5;

-- view hire 뷰에서 rownum가 5보다 작거나 같으면 출력

View로 만들었던 것을 또 View로 만들기

create or replace view v_view_hire2
as
select empno, ename, hiredate from view_hire where ROWNUM<=5;

select * from v_view_hire2;

 

 

 

---

인라인 뷰란? 

• SQL문 내부에 뷰를 정의하고 이를 테이블처럼 사용하는 것
• 다른 쿼리에서 사용할 필요 없이 해당 SQL에서만 필요한 VIEW일 경우 번거럽게 뷰를 생성하지 말고
• SQL 문장의 FROM 절에 view의 서브쿼리를 바로 적어서 사용하는 것

emp, dept테이블을 조회하여 부서번호와 부서별 최대급여 및 부서명을 출력하기

SELECT e.deptno, d.dname, e.sal 
FROM (Select deptno, max(sal) sal from emp group by deptno) e, dept d;

괄호안에 들어간 ( select ) 문을 인라인 뷰라고 하며, e 는 별칭이다.

 

---

-- 1단계 뷰로 만든다
create or replace view vv
as
select empno, ename, hiredate from emp order by HIREDATE;

-- 2단계 뷰에서 검색한다.
select ROWNUM, empno, ename, hiredate
from vv -- 뷰네임
where ROWNUM <= 5;

기존에는 이런 방식을 해왔지만 인라인 뷰를 사용하게 되면
인라인 뷰로 한번에 할 수 있는 것이다.
--> 인라인 뷰

-- 인라인뷰로 입사일이 빠른 사람만 5명만 출력하기

 select rownum, ve.empno, ve.ename, ve.HIREDATE
    from (select empno, ename, hiredate from emp order by HIREDATE ) ve -- ve = 별칭
    where rownum <= 5;

-- ve 는 ( select ) 문으로 만들어진 가상 테이블뷰이며, ( select )문은 인라인 뷰라고 한다.

 

 

---

예제 

 1. 사원 테이블을 기본 테이블로 하여 부서별
최대 급여와 최소급여을 출력하는 뷰를 SAL_VIEW란 이름으로 작성하시오

 

CREATE VIEW SAL_VIEW
AS
SELECT D.DNAME, MAX(E.SAL) MAX_SAL, MIN(E.SAL) MIN_SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME;

 

 

---

2. 인라인 뷰를 사용하여 급여를 많이 받는 순서대로 3명만 출력하시오

CREATE OR REPLACE VIEW SAL_TOP3_VIEW -- 뷰 생성
(RANK, EMPNO, ENAME, SAL) -- alias
AS
SELECT ROWNUM, EMPNO, ENAME, SAL
FROM (SELECT EMPNO, ENAME, SAL FROM EMP_COPY -- 인라인 뷰
ORDER BY SAL DESC -- 급여 내림차순은 급여가 많은 것부터 출력된다.
)
WHERE ROWNUM <= 3; -- 3명만 출력

 

---

3. professor테이블과 department테이블을 조인하여
교수번호와 교수이름, 소속 학과이름을 조회하는 view를 생성하시오. view 이름은 v_prof_dept2로 하세요

create or replace view v_prof_dept2 -- view 명
as
    select p.PROFNO, p.NAME, d.DNAME -- 교수 번호, 교수명, 소속 학과
    from PROFESSOR p, DEPARTMENT d -- 별칭
    where p.deptno=d.DEPTNO;

 

---

4. inline view를 사용하여 student테이블과 departmant테이블을 사용하여
학과별로 학생들의 최대키와 최대 몸무게, 학과 이름을 출력하세요.

 

select d.dname 학과명, s.max_height 최대키, s.max_weight 최대몸무게
from (select deptno1, max(height) max_height, max(weight) max_weight from STUDENT group by deptno1) s,
  DEPARTMENT d
where d.DEPTNO = s.DEPTNO1;

 

---

 

5. student테이블과 departmemt테이블을 사용하여 학과 이름, 학과별 최대키, 학과별로
가장 키가 큰 학생들의 이름과 키를 inline view를 사용하여 출력하세요.

 

select d.dname 학과명, m.height 학과_최대키, s.NAME 학생명, s.HEIGHT 키
from (select deptno1, max(height) height from STUDENT group by deptno1)
    m, -- 학생중에서 최대 키를 가진 사람을 학과번호로 그룹을 짓는다.
     STUDENT s, DEPARTMENT d where s.DEPTNO1=d.DEPTNO
and m.height=s.HEIGHT
and s.DEPTNO1=m.DEPTNO1;

 

---

 

6. student테이블에서 학생의 키가 동일 학년의 평균 키보다 큰 학생들의 학년과
이름 키, 해당 학년의 평균키를 출력하되 inline view를 사용해서 출력하세요.
 (학년 컬럼으로 오름차순 정렬해서 출력하세요)

 

select s.grade 학년, s.name 학생명, s.height 학생키, a.height 평균키
from ( select GRADE, avg(HEIGHT) height from STUDENT group by GRADE) a, STUDENT s
        where s.HEIGHT > a.height -- 학생키가 평균키보다 큰 조건
        and s.GRADE=a.GRADE -- 같은 학년의 키만 평균
        order by s.GRADE; -- 오름차순

 

---

7. professor테이블을 조회하여 교수들의 급여 순위와 이름과 급여을 출력하시오.
단 급여 순위는 급여가 많은 사람부터 1~5위까지 출력하세요

select ROWNUM 급여순위, name 이름, pay 급여
from (select name, pay from PROFESSOR order by pay desc) -- 급여 내림차순
where ROWNUM between 1 and 5; -- 1~5까지의 범위 지정  ( >= | <= )

 

 

---

8. 아래 화면과 같이 교수 테이블을 교수 번호로 정렬하되
 3건씩 분리해서 급여 합계와 급여 평균을 출력하세요

select num 번호, profno 교수번호, NAME 교수명, pay 급여, sum(pay) 급여합계, round(AVG(pay), 1) 급여평균
from ( select PROFNO, name, pay, rownum num from PROFESSOR )
group by ceil(num/3), rollup ((profno,name,pay,num))  -- 올림함수 ceil, rollup은 총계를 구할 때 사용한다.
order by ceil(num/3) ; -- CEIL() 함수는 천장이라는 뜻을 가지고 있다는 것을 생각->n보다 크거나 같은 정수중 제일 작은 정수
                    -- 번호를 3으로 나누었을때 나머지가 3의 배수인 숫자와 가깝게 취급하는 것이다.

-- CEIL() 함수는 천장이라는 뜻을 가지고 있다는 것을 생각->n보다 크거나 같은 정수중 제일 작은 정수
-- 번호를 3으로 나누었을때 나머지가 3의 배수인 숫자와 가깝게 취급하는 것이다.