Or14View

View는 테이블로부터 생성된 가상의 테이블로 물리적으로는 존재하지 않는 테이블

View는 하나 또는 그 이상의테이블로부터 생성된 가상의 테이블이다.

물리적으로나 존재하지 않는 테이블이다.

DB의 선택적인 내용을 보여줄 수 있기 때문에 DB에 대한 엑세스 제한이 가능하다.

복잡한 쿼리문을 통해 얻을 ㅜㅅ 있는 결과를 간단한 쿼리문으로 얻을 수 이싿.

하나의 테이블로 만든 View에서는 DML(insert, update, delete)문을 수행할 수 있지만

여러 테이블로 만든 View에서는 DML문을 수행할 수 없다.

View생성시 order by를 포함할 수 없다.(Oracle 81 이전버전까지)

View의 장점

1) 보안성 : 중요한 컬럼을 숨길수 있어 보안을 유지할 수 있다.

2) 편의성 : 여러개의 테이블 사용을 위한 복잡한 조인문을 사용하더라도 사용자는 이를 몰라도된다.

3) 간결성 : View를 사용하면 Application을 개발할 때 복잡한 쿼리문을 숨길 수 있어 소스가 간결해지낟.

View의 생성

사용법 :

create [or replace] view 뷰이름 [(컬럼1, 컬럼2.....)]

as

select * from 테이블명 조건들; (테이블이 2개 이상이라면 join도 가능)

# 컬럼명은 원본테이블과 다르게 지정할 수 있다.

# 만약 동일한 이름의 뷰가 존재하면 오류가 발생한다.

- 생성된 뷰 정보보기

사용법 :

select * from user_views; 데이=터사전

- 뷰 생성하기

예시] hr계정의 사원테이블에서 직무아이디가 ST_CLERK인 사원의 사원번호, 이름, 직무아이디, 입사명, 부서번호를 조회할 수 있는 뷰를 아래의 조건에 맞게 생성하시오.

뷰 명칭 v_employees1

컬럼명 emp_id, J_id, h_date, dept_id

- 1. 위에서 제시한 조건으로 select문 만들기

create view v_employees1 (emp_id, f_name, j_id, j_date, dept_id)

as

-2. 조건에 만족한다면 위 쿼리를 이용하여 view 생성하기

select employees_id, first_name, job_id, hire_date, department_id

from employees

where job_id = ST_CLERK;

-3. 뷰를 통해 레코드 확인하기

select * from v_employees1;

-4. 생성된 뷰의 정보보기(데이터사전)

select * from user_views whee view_name = upper('v_employees1');

View의 수정

사용법

create or replace view

as

select * from 테이블명 조건1, 조건2.....(join문 가능)

# 해당 뷰가 이미 존재한다면 위 조건으로 수정된다.( 즉, 덮어쓰기 된다.)

# 생성된 뷰가 없다면 새롭게 생성된다. 즉, 처음 생성할 때 replace명령을

사용해도 무방하다. (단, 기존에 만들어진 View가 사라질 수 있으므로 주의를 요한다.)

예시] 위에서 생성된 v_employees1 뷰를 아래 조건에 맞게 수정하시오.

사원테이블의 직무아이디가 ST_MAN인 사원의 사원번호, 이름, 이메일, 매니저아이디를 조회하도록 하시오.

단, 뷰의 칼럼은 e_id, name, email, m_id로 지정하시오.

create or replace view v_employees1 (e_id, name, email, m_id)

as

select employee_id, first_name, email, manager_id

from employees

where job_id ='ST_MAN'; -- create로 생성하면 기존의 뷰가 있으므로 오류 발생

select * from v_employees1;

View 삭제하기

사용법 :

drop view 뷰 이름;

drop view v_employee1;

select * from v_employees1;

-읽기전용 View만들기

사용법 :

create view 뷰이름(컬럼1, 컬럼2...............)

as

select * from 테이블명 조건1, 조건2...............)

with rad only;

# 읽기전용 뷰에서는 DML문(insert, update, delete)을 사용할 수 없다.

단, select문은 가능하다.

예시] 사원번호, 이름, 연봉을 계산하며 출력하는 뷰를 생성하시오.

단, 읽기전용으로 생성해야 하며, 뷰의 컬럼은 emp_id, l_name, annual_sal로 지정하시오.

[연봉=(급여+보너스율)*12

뷰이름 v_employ_readonly

create view v_employ_radonly(ep_id, l_name, annual_sal)

as

select employee_id, last_name, (salary+nvl(commission_pct, 0)) * 12

from employees

with read only;

drop view v_employ_readonly;

-기존테이블 확인

select employee_id, last_name, salary from employees;

- 뷰 확인

select * from v_employ_readonly;

-dml문(update)이 적용되는 지 확인

update v_employ_readonly set l_name='홍길동' where emp_id=100;

-SQL오류 : ORA42399 읽기 전용 뷰에서는 DML작업을 수행할 수 없습니다.

-with check option

:해당 뷰를 볼 수 있는 범위내에서만 update, insert가 가능하도록 하는 옵션.

즉, 뷰의 조건을 where id ='100'이라고 설정했다면 id가 100인 데이터만 insert, update될 수있도록 하는 옵션

예시] 직무아이디가 ST_MAN인 사원을 조회할 수 있는 뷰를 새엇ㅇ하시오.

사원번호, 이름, 이메일, 입사일, 직무아이디 View에서는

e_idx, f_name, l_name, email, h_date, j_id로 표현하시오.

create view v_emp_no_withcheck

(e_idx, f_name, l_name, email, h_date, j_id)

as select

employee_id, first_name, last_name, email, hire_date, job_id

from

employees

where job_id ='ST_MAN';

- 뷰를 통해서 레코드 확인

select * from v_emp_no_withcheck;

-위에서 생성한 뷰를 통해 insert를 시도

insert into v_emp_no_withcheck;

-위에서 생성한 뷰를 통해 insert를 시도

(300, '홍', '길동', 'insert@kosmo.co.kr',sysdate,'MK_MAN');

레코드가 정상적으로 입력이 되었으나 이는 적절치 않다.

v_emp_no_withcheck 뷰는 업무아이디가 ST_MAN인 사원만 조회할 목적으로

생성되었으므로 해당 뷰를 통해 다른 업무를 가진 사원을 입력하는 것은

사용목적에 위배되는 사항이다. 이런 업무적 오류를 막기위해 with check option을 사용한다.

- employees 테이블에서 직접 확인

select * from employees where employee_id =300;

--with check option 옵션을 사용하여 뷰 수정

create or replace view v_emp_no_withcheck

(e_idx, f_name, l_name, email, h_date, j_id)

as select

employee_id, fist_name, last_name, email, hire_date, job_id

from

employees

where job_id = 'ST_MAN'

WITH CHECK OPTION

--위에서 수정한 뷰를 통해 insert를 시도

insert into v_emp_no_withcheck values

(301, '홍', '길동', 'insert@kosmo.co.kr',sysdate, 'MK_MAN');

ORA 01402 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다.

즉, 해당 뷰의 조건과 다른 레코드는 입력을 원천적으로 막을 수 있게 되었다.

View 활용하기

뷰생성시 문자열함수 사용하기

예시) 사원테이블에서 부서번호가 50, 80인 사원만 조회하는 뷰를 생성하시오.

출력할 컬럼은 사원번호, 전체이름(성+이름), 전화번호, 보너스율, 입사일, 부서번호이다.

-- 위에서 생성된 뷰에서 날자를 0000-00-00형태로 변경

create or replace view v_employ_fullname

(emp_id, full_name, p_number, c_percent, h_date, dept_id)

as select

employee_id, concat(concat(first_name, ' '), last_name), phone_number,

nvl(commission_pct, 0), to_char(hire_date, 'yyyy-mm-dd'),

department_id

from employees

where department_id in(50,80);

select * from v_employ_fullname;

update v_employ_fullname set full_name = '마이클잭슨'

where emp_id =120;

SQL오류 ORA 01733 가상 열은 사용할 수 없습니다.

full_name은 first_name, last_name을 합쳐서 만든 가상의 컬럼이므로

해당 컬럼에는 업데이트 할 수 없다.

update v_employ_fullname set p_number ='010-1234-5678'

where emp_id=120;

p_number에 해당하는 원본테이블의 컬럼이 존재하므로 뷰와 실제테이블에 업데이트 됨.

select * from v_employ_fullname where emp_id =120;

select * from employees where employee_id = 120;

예시] 연봉을 출력하는 뷰를 새엉하시오.

출력내용 : 사원번호, 전체이름(이름+성), 월급, 연봉

에ㅕㄴ봉계산식 : (급여 *12)+ (급여*12)*보너스율)

View 이름 : v_emp_year_sal

단, 이름을 합칠 때는 concast을 사용하지 말고 || 를 사용한다.

create view v_emp_year_sal

(emp_id, full_name, sal, annual_sal)

as select

employee_id, first_name||' '|| last_name, slaary,

(salary*12) + (salary *12) *nvl(commission_pct, 0)

from employees

where 1=1;

select * from v_emp_year_sal;

- 금액이 출력되는 부분에 세자리마다 컴마를 찍어서 출력되도록 뷰를 수정하시오.

create or replace view v_emp_year_sal

(emp_id, full_name, sal, annual_sal)

as select

employee_id, first_name||' 'last_name, to_char(salary, '$999,000'),

to_char((salary*12) + (salary*12)*nvl(commission_pct, 0), '$999,000')

from employees

where 1=1;

조인을 통한 View새성

예시) 사원테이블과 부서테이블을 조인하며 사원번호, 전체이름, 부서번호, 부서명 입사일자를 출력하는 뷰를 생성하시오.

뷰의 컬럼명은 위 조건과 같이 한글로 지정하시오.

create view v_emp_join(사원번호, 전체이름, 부서번호, 부서명, 입사이랒)

as select

employee_id, first_name ||' '||last_name, D.department_id,

department_name, hire_date

from

employees E inner join departments D

on E.department_id = D.department_id

where 1=1;

select * from v_emp_join;

--뷰를 확인할 수 있는 데이터 사전

select * from user_views;