-- 문제1
create table members(
m_name varchar2(3) not null,
m_num number(1) constraint mem_memnum_pk primary key,
reg_date date constraint mem_regdate_uk unique not null, -- 사실 날짜는 unique 들어가면 안됨
gender varchar2(1) constraint mem_gender_ck check (gender in ('M', 'F')),
loca number(4) constraint mem_loca_loc_locid_fk references locations (location_id)
);
commit;
insert into members values ('AAA', 1, '2018-07-01', 'M', 1800);
insert into members values ('BBB', 2, '2018-07-02', 'F', 1900);
insert into members values ('CCC', 3, '2018-07-03', 'M', 2000);
insert into members values ('DDD', 4, sysdate, 'M', 2000);
-- 문제2
select m_name, m_num, street_address, location_id
from members m
join locations l
on m.loca = l.location_id
order by m_num asc;
뷰 (view)
외우지 않아도 되고 이해하기
다른 뷰를 기초로 하는 논리적 테이블 = 물리적X, 실제로는 존재하지 않는 가상의 테이블
view는 제한적인 자료만 보기 위해 사용할 수 있는 가상테이블의 개념
필요한 컬럼만 저장해 두면 관리가 용이
뷰를 통해서 데이터에 접근하면 원본 데이터는 안전하게 보호 가능
정보를 여러 사람이 자주 사용해야 한다면 뷰를 만들어 놓고 뷰를 참조하면 됨
특징
단순 뷰
복합 뷰
테이블 수
하나의 원본 테이블
두 개 이상 (조인을 통해 생성된)
함수 포함
x
o
데이터 그룹 포함
x
o
뷰를 통한 DML (연산?)
o
x
-- 뷰 정보 확인
select * from user_views;
select * from emp_details_view;
-- 단순 뷰
-- 뷰의 컬럼 이름은 함수같은 가상표현식이면 안됩니다.
create or replace view view_emp
as (select employee_id,
first_name || ' ' || last_name as name,
job_id,
salary
from employees
where department_id = 60
);
select * from view_emp;
-- 복합 뷰
-- 여러 테이블을 조인하여 필요한 데이터만 저장하고 빠른 확인을 위해서 사용
create or replace view view_emp_dept_job
as (select
e. employee_id,
first_name || ' ' || last_name as name,
d.department_name,
j.job_title
from employees e
left outer join departments d
on e.department_id = d.department_id
left outer join jobs j
on e.job_id = j.job_id)
order by employee_id;
select * from view_emp_dept_job;
권한, 생성, 수정
1. 권한
정보확인을 빨리 하기 위해 권한 확인
Role롤 출력권한 출력
2. 생성
create view 문장 내에 서브쿼리 작성
뷰의 구조 확인!! 필수열 별칭을 사용한 열 이름 제어 가능
복합 뷰 생성
부서이름: departments 테이블과의 조인을 통해 알고, 직무이름: jobs 테이블과의 조인을 통해 알 수 있음 ↓
3. 수정
열 별칭들은 서브쿼리에서의 열과 동일한 순서로 나열
이미 이름이 존재해도, 그 소유자에 대한 이전 버전을 새로운 뷰 구문으로 대체 동일 이름으로 만들면 수정됨
4. 삭제
기본 테이블 기반으로 데이터 손실없이 뷰 삭제
-- 뷰의 수정 (동일 이름으로 만들면 수정됨)
create or replace view view_emp_dept_job
as (select
e. employee_id,
first_name || ' ' || last_name as name,
e.salary, -- 추가
d.department_name,
j.job_title
from employees e
left outer join departments d
on e.department_id = d.department_id
left outer join jobs j
on e.job_id = j.job_id)
order by employee_id;
-- 뷰를 이용하면 데이터를 손쉽게 조회가능
-- job_title별 샐러리 평균
select job_title, avg(salary) as 평균 from employees e
left outer join jobs j
on e.job_id = j.job_id
group by job_title
order by avg(salary) desc;
select job_title, avg(salary) as 평균
from (select *
from employees e
left join jobs j on e.job_id = j.job_id)
group by job_title
order by 평균;
select job_title, avg(salary) as 평균
from view_emp_dept_job
group by job_title
order by 평균;
-- 뷰의 삭제
drop view 뷰이름;
뷰를 이용한 DML 연산
desc view_emp_dept_job;
-- 뷰를 통한 DML은 제한이 많습니다.
-- 가상열 칼럼이 있다면 허용되지 않음
insert into view_emp_dept_job(name, employee_id) values ('xxx', 300);
-- 원본테이들의 null을 허용하지 않는 경우도 안됩니다.
insert into view_emp_dept_job(employee_id, salary) values(100, 10000);
-- join된 뷰의 경우도 허용되지 않습니다.
insert into view_emp_dept_job(employee_id, job_title) values(300, 'xxx');
뷰에 대한 DML 연산이 조건을 만족할 때만 수행
뷰 값이 수정되면 실제 테이블에도 반영되기 때문에 뷰를 읽기 용도로만 사용
with check option - 읽기만 허용, 조건 컬럼 값을 변경하기 못하게 되는 옵션 with read only - select만 허용하는 옵션 with read only - 읽기 전용 뷰
with chek option 사용과 errorwith read only로 DML 연산이 수행될 수 없게 함
-- 뷰의 옵션
-- with check option - 조건컬럼 제약
create or replace view view_emp_test
as (select employee_id, first_name, department_id
from employees
where department_id in (60, 70, 80)
)
with check option;
select * from view_emp_test;
-- with read only - 읽기전용뷰 (조회만 가능)
create or replace view view_emp_test
as (select employee_id, first_name, department_id
from employees
where department_id in (60, 70, 80)
)
with read only;
인라인 뷰(inline view)
별칭을 사용하는 서브 쿼리, 스키마 객체가 x
from 절에 서브쿼리가 온 것 (from절에는 테이블, 뷰가 올 수 있음)
뷰도 독립적인 select문으로 from절에 사용하는 서브쿼리도 하나의 뷰로 볼 수 있음 - 이게 인라인뷰
