DAY 31 - ORACLE DB - VIEW / 전체 복습 (2024.08.14)

 [ VIEW ] - 가상(복사본)

- 다른 테이블이나 뷰에 포함된 맞춤표현(virtual table)

 

join하는 테이블의 수가 늘어나거나 질의문이 길고 복잡해지면

작성이 어려워지고 유지보수가 어려울 수 있다.

 

이럴 때는 스크립트를 만들어두거나 stored query를 사용해서

데이터베이스 서버에 저장해두면 필요할 때 마다 호출해서 사용할 수 있다

 

- 뷰와 테이블의 차이는 뷰는 실제로 데이터를 저장하고 있지 않다는 점이다.

- 베이스테이블(Base table) : 뷰를 통해 보여지는 실제테이블

- 선택적인 정보만 제공 가능

 

뷰는 수정까지는 상관없는데 삭제는 안된다.

뷰랑 원본이 연결되어있기 때문에 뷰를 통해 원본을 바꿀 수 있다.

 

[형식]

create [or replace] [force|noforce] view 뷰이름 [(alias [,alias,.....)]

as 서브쿼리

[with check option [constraint 제약조건이름]]

[with read only [constraint 제약조건이름]]

 

or replace 이게 있기  때문에 계속해서 똑같은 걸 만들어줄 수 있다.

---

create or replace view v_view1
as select employee_id, last_name, salary, department_id from employees
where department_id=90;

---

select * from v_view1;

---

delete from v_view1;

 

원본이 있기 때문에 삭제할 수 없다 !!

---

create or replace view v_view2
as select employee_id, last_name, salary, department_id from employees
where salary between 5000 and 10000;

나는 여기서 그냥 별명을 따로 안 붙였는데 만약 컬럼명들을 사원ID, 사원이름, 급여, 부서ID로 했다면

나중에 update할 때 저 컬럼명으로 해야만 수정이 된다 !!

 

---

select * from v_view2;

---

update v_view2 set salary = 12000 where employee_id = 103;

---

select * from v_view2;

 

수정문에서 103사원의 급여를 12000으로 바꿨기 때문에

v_view2의 조건에 안 맞아서 테이블에서 자동으로 빠져나간 것을 볼 수 있다.

---

select * from employees where employee_id=103;

원본인 employees 테이블을 확인해보면 맞게 수정된 것을 볼 수 있다.

---

update employees set salary=9000 where employee_id=103;

다시 103번 사원의 급여를 9000으로 수정한다.

---

select * from employees where employee_id=103;

 원본인 employees 테이블이 맞게 수정되었고,

---

select * from v_view2;-- 103사원이 조건에 맞아서 다시 들어왔다.

 

v_view2에도 103번의 사원이 조건에 맞아졌기 때문에 다시 들어온 것을 확인할 수있다.

---

create or replace view v_view3
as select employee_id 사원번호, last_name 사원명, department_name 부서명 
   from employees
   join departments using(department_id)
   where department_id in(10, 90)
   order by employee_id;

 

 밑에 방식으로 해도 똑같다 !

create or replace view v_view3(사원번호, 사원명, 부서명)
as select employee_id, last_name, department_name 
   from employees
   join departments using(department_id)
   where department_id in(10, 90)
   order by employee_id;

 

---

select * from v_view3;

---

create or replace view v_view4
as select employee_id 사원번호, 
          last_name 사원명, 
          to_char(trunc(salary*1365, -3), '999,999,999') || '원' 급여, 
          to_char(hire_date, 'YYYY"년" MM"월" DD"일"') 입사일, 
          department_name 부서명, 
          city 부서위치
   from employees
   join departments using(department_id)
   join locations using(location_id)
   where department_id in(10, 90)
   order by employee_id;

 

'YYYY"년" MM"월" DD"일"'

따옴표 안에는 쌍따옴표 쳐야한다 !!

---

select * from v_view4;

---

create or replace view v_view4(사원번호,사원명,급여,입사일,부서명,부서위치)
as select employee_id, 
          last_name, 
          to_char(trunc(salary,-3),'99,999')|| '원', 
          to_char(hire_date,'YYYY"년" MM"월" DD"일"'), 
          department_name, 
          city

 

또는

create or replace view v_view4
as select employee_id, 
          last_name, 
          to_char(trunc(salary,-3),'99,999')|| '원' as salary, 
          to_char(hire_date,'YYYY"년" MM"월" DD"일"') as hire_date, 
          department_name, 
          city

 

위의 둘 중 하나로 하면된다.

 

select 하면 컬럼명이 to_char(trunc(salary,-3),'99,999')|| '원' 이렇게 잡힌다.

그러면 원본 테이블 employees에서 to_char(trunc(salary,-3),'99,999')|| '원'을 찾기 때문에 error이 나는 것이다.

그러므로 반드시 별명을 붙여야한다.

 

- 단순 VIEW에서 그룹함수를 사용하기 위해서는 그룹함수가 실제 필드가 아닌 가상 필드이기 때문에

  반드시 그룹함수에 별칭을 지정해야 함

 

※ 수식이 들어가면 반드시 별명 붙이기 !!

---

[ with read only ]

create or replace view v_view5
as select employee_id, last_name, job_id
from employees
where job_id='IT_PROG'
with read only;

---

select * from v_view5;

---

delete from v_view5;

---

update v_view5 set last_name='홍길동' where employee_id=103;

---

[ with check option ]

create or replace view v_view6
as select employee_id, last_name, email, hire_date, job_id
from employees
where job_id='IT_PROG'
with check option;

---

select * from v_view6;

---

insert into v_view6(employee_id, last_name, email, hire_date, job_id)
values(500,'kim','candy','2004-01-01','Sales');

---

update v_view6 set job_id='Sales' where employee_id=103;

---

insert into v_view6(employee_id, last_name, email, hire_date, job_id)
values(500,'kim','candy','2004-01-01','IT_PROG');

---

select * from v_view6;

---

delete from v_view6;

---

 

create table bookshop(
isbn varchar2(10) constraint PISBN primary key,
title varchar2(50) constraint CTIT not null,
author varchar2(50),
price number,
company varchar2(30));

select * from bookshop;

---

insert into bookshop values('is001', '자바3일완성', '김자바', 25000, '야메루출판사');
insert into bookshop values('pa002', 'JSP달인되기', '이달인', 28000, '공갈닷컴'); 
insert into bookshop values('or003', '오라클무작정따라하기', '박따라', 23500, '야메루출판사');

commit;

select * from bookshop;

---

select constraint_name, constraint_type
from user_constraints
where table_name='BOOKSHOP';

※ 테이블명 대문자로 !!!!

---

 

두 가지 방법있다는 거 확인하기 !!

• 방법 1

create table bookorder(
idx number primary key,
isbn varchar2(10) constraint FKISBMN references bookshop,
qty number);

 

• 방법 2

create table bookorder(
idx number primary key,
isbn varchar2(10),
qty number,
constraint FKISBMN foreign key(isbn) references bookshop);

select * from bookorder;

---

select constraint_name, constraint_type
from user_constraints
where table_name='BOOKORDER';

---

create sequence idx_seq
start with 1
increment by 1
nocache
nocycle;

create sequence idx_seq
nocache
nocycle;

 이렇게만 적어도 상관없다.

---

insert into bookorder values(idx_seq.nextval, 'is001', 2);
insert into bookorder values(idx_seq.nextval, 'or003', 3);
insert into bookorder values(idx_seq.nextval, 'pa002', 5);
insert into bookorder values(idx_seq.nextval, 'is001', 3);
insert into bookorder values(idx_seq.nextval, 'or003', 10);

select * from bookorder;

 

---

create or replace view bs_view
as select title 책제목, author 저자, to_char(sum(price*qty), '999,999') 총판매금액 
   from bookshop
   join bookorder using(isbn)
   group by title, author
with read only;

 

※ group by 할 때 title만 하면 안 되는 거 확인하기 !!

---