개발초보자

4 분 소요

뷰(view)

  • 테이블과 유사하나 테이블과 달리 물리적 공간이 필요하지 않은 가상의 테이블
  • 테이블과 마찬가지로 select,insert,update,delete 명령 가능

 

  • 사용 이유

    • 보안: 중요한 필드를 제외한 범위만을 조회 할 수 있도록 하기 위해 (테이블에서 공개 가능한 데이터만 제공하기 위해 )

    • 사용 편의성:테이블 데이터가 복잡한경우 필요한 필드만 가져와서 뷰로 만들면 쉽게 사용자가 조회 할 수 있다 (for and user),조인 단순화,사용자에 맞게 컬럼이나 테이블명을 따로 제공 한다

  • 가상테이블 생성
create view v_emp(emp_id,first_name,hiredate,dept_id)
as
select employee_id,frist_name,job_id,hire_date,department_id
from employees
where job_id = 'ST_CLERK';
  • 조회하기 
    select * from v_emp;

EMP_ID	FIRST_NAME	JOB_ID	HIRE_DATE	DEPT_ID
125	Julia	ST_CLERK	05/07/16	50
126	Irene	ST_CLERK	06/09/28	50
127	James	ST_CLERK	07/01/14	50
128	Steven	ST_CLERK	08/03/08	50
129	Laura	ST_CLERK	05/08/20	50
130	Mozhe	ST_CLERK	05/10/30	50
131	James	ST_CLERK	05/02/16	50
132	TJ	ST_CLERK	07/04/10	50
133	Jason	ST_CLERK	04/06/14	50
134	Michael	ST_CLERK	06/08/26	50
135	Ki	ST_CLERK	07/12/12	50
136	Hazel	ST_CLERK	08/02/06	50
137	Renske	ST_CLERK	03/07/14	50
138	Stephen	ST_CLERK	05/10/26	50
139	John	ST_CLERK	06/02/12	50
140	Joshua	ST_CLERK	06/04/06	50
141	Trenna	ST_CLERK	03/10/17	50
142	Curtis	ST_CLERK	05/01/29	50
143	Randall	ST_CLERK	06/03/15	50
144	Peter	ST_CLERK	06/07/09	50
  • 데이터 수정 
    update v_emp set frist_name = 'july'
where first_name = 'Julia';

    EMP_ID	FIRST_NAME	JOB_ID	HIRE_DATE	DEPT_ID
125	july	ST_CLERK	05/07/16	50
  • 데이터 삭제 
    drop view v_emp;

    View V_EMP이(가) 삭제되었습니다.
  • 삭제 후 데이터 다시 추가(create or reaplace view) 
    create or replace view v_emp3(emp_id,first_name,job_id,hire_date,dept_id)
as
select (employee_id,first_name,job_id,hire_date,department_id)
from employees
where job_id = 'SH_CLERK';

    EMP_ID	FIRST_NAME	JOB_ID	HIRE_DATE	DEPT_ID
180	Winston	SH_CLERK	06/01/24	50
181	Jean	SH_CLERK	06/02/23	50
182	Martha	SH_CLERK	07/06/21	50
183	Girard	SH_CLERK	08/02/03	50
184	Nandita	SH_CLERK	04/01/27	50
185	Alexis	SH_CLERK	05/02/20	50
186	Julia	SH_CLERK	06/06/24	50
187	Anthony	SH_CLERK	07/02/07	50
188	Kelly	SH_CLERK	05/06/14	50
189	Jennifer	SH_CLERK	05/08/13	50
190	Timothy	SH_CLERK	06/07/11	50
191	Randall	SH_CLERK	07/12/19	50
192	Sarah	SH_CLERK	04/02/04	50
193	Britney	SH_CLERK	05/03/03	50
194	Samuel	SH_CLERK	06/07/01	50
195	Vance	SH_CLERK	07/03/17	50
196	Alana	SH_CLERK	06/04/24	50
197	Kevin	SH_CLERK	06/05/23	50
198	Donald	SH_CLERK	07/06/21	50
199	Douglas	SH_CLERK	08/01/13	50

 

  • 보안적인 측면에서 사용할때 (select문에서 함수를 써서 만든 필드들은 insert,update를 할수 없음)
create or replace view v_emp3(emp_id,first_name,job_id,hire_date,dept_id)
as
select nvl(employee_id,null),nvl(first_name,null),job_id,nvl(hire_date,null),department_id
from employees
where job_id = 'SH_CLERK';

select first_name from v_emp3

FIRST_NAME
Sarah
Alexis
Anthony
Kelly
Julia
Jennifer
Britney
Kevin
Jean
Timothy
Girard
Douglas
Vance
Samuel
Donald
Randall
Nandita
Martha
Winston
Alana
  • 오류 발생:select문에서 함수를 써서 만든 필드는 insert,update를 할 수 없다 
    update v_emp3 set first_name = 'Julia'
where first_name='july';

    SQL 오류: ORA-01733: 가상 열은 사용할 수 없습니다
01733. 00000 -  "virtual column not allowed here"
  • 문제1 연봉을 구하기 위한 뷰를 만들어 보세요
  • 검색컬럼: 사원번호, first_name, annual_salary 
    create or replace view v_emp_salary(emp_id, first_name, annual_salary)
as
select employee_id, first_name, (salary*12 + nvl(commission_pct, 0)*salary)
from employees;

    EMP_ID	FIRST_NAME	ANNUAL_SALARY
100	Steven	288000
101	Neena	204000
102	Lex	204000
103	Alexander	108000
104	Bruce	72000
105	David	57600
106	Valli	57600
107	Diana	50400
108	Nancy	144096
109	Daniel	108000
110	John	98400
111	Ismael	92400
112	Jose Manuel	93600
  • 뷰의 수정을 막는 조회용 뷰 만들어보기(with read only) 
    create or replace view v_emp
as
select employee_id,first_name(salry*12+nvl(commission_pct,0)*salary)annual_salary
from employees
with read only;

    update v_emp_salary set first_name='kim'
where first_name = 'Steven';
  • 오류 발생 
    SQL 오류: ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
42399.0000 - "cannot perform a DML operation on a read-only view"
  • 문제2: employees 테이블에서 07년도에 입사한 직원의 employee_id, first_name, last_name – email, hire_date, job_id 컬럼값을 갖는 v_emp_07 이름의 뷰를 생성하세요. 
    create or replace view v_emp_07
as
select employee_id,first_name,last_name,email,hire_date,job_id
from employees
where hire_date'07/01/01'and'07/12/31';

    EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	HIRE_DATE	JOB_ID
104	Bruce	Ernst	BERNST	07/05/21	IT_PROG
107	Diana	Lorentz	DLORENTZ	07/02/07	IT_PROG
113	Luis	Popp	LPOPP	07/12/07	FI_ACCOUNT
119	Karen	Colmenares	KCOLMENA	07/08/10	PU_CLERK
124	Kevin	Mourgos	KMOURGOS	07/11/16	ST_MAN
127	James	Landry	JLANDRY	07/01/14	ST_CLERK
132	TJ	Olson	TJOLSON	07/04/10	ST_CLERK
  • 문제 2> employees 테이블에서 department_id와 job_id로 그룹핑하고, 평균 salary가 9000 이상인 사원의 department_id와 job_id, salary_avg 컬럼 값을 가지며 – 평균 salary값이 높은 순으로 정렬한 읽기 전용 뷰를 생성하세요.
  • 뷰이름은 v_high_salary
create or replace view v_high_salary
as
select department_id, job_id, avg(salary) salary_avg
from employees
group by department_id, job_id
having avg(salary) >= 9000
order by salary_avg desc
with read only;

DEPARTMENT_ID	JOB_ID	SALARY_AVG
90	AD_PRES	24000
90	AD_VP	17000
20	MK_MAN	13000
80	SA_MAN	12200
110	AC_MGR	12008
100	FI_MGR	12008
30	PU_MAN	11000
70	PR_REP	10000

문제 3> employees 테이블을 이용해서 부서번호, 사원의 이름과 직속상사의 이름을 출력하는 – 읽기 전용 뷰를 생성하세요. – (부서번호 기준으로 오름차순 정렬,뷰이름: v_emp_mgr, 이름은 first_name과 last_name 모두출력)

create or replace view v_emp_mgr
as
select e.department_id, 
e.first_name || ' ' || e.last_name ename,
m.first_name || ' ' || m.last_name mname
from employees e, employees m
where e.manager_id = m.employee_id
order by e.department_id
with read only;

DEPARTMENT_ID	ENAME	MNAME
10	Jennifer Whalen	Neena Kochhar
20	Michael Hartstein	Steven King
20	Pat Fay	Michael Hartstein
30	Den Raphaely	Steven King
30	Karen Colmenares	Den Raphaely
30	Guy Himuro	Den Raphaely
30	Sigal Tobias	Den Raphaely
30	Shelli Baida	Den Raphaely
30	Alexander Khoo	Den Raphaely

업데이트:

댓글남기기

참고