Or14View

/**************************************

파일명 : Or14View.sql

View

설명 : 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 employee_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 where view_name=upper('v_employees1');

/*

-View의 수정

사용법 : 

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

    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_employees1;

select * from v_employees1;

/*

- 읽기전용 View만들기

사용법 : 

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

    as

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

    with rad only;

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

단, select문은 가능하다.

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

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

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

뷰이름 : v_employ_readonly

*/

create view v_employ_readonly (emp_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 오류: ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

/*

-with check option

 : 해당 뷰를 볼수 있는 범위내에서만 updaste, 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 values

(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, first_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

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

*/

create view v_emp_year_sal

    (emp_id, full_name, sal, annual_sal)

as select

    employee_id, first_name ||' '|| last_name, salary,

    (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;