ex) 어제 이어서 함
create table hr.dept(
dept_id number constraint dept_pk primary key,
dept_name varchar2(30) constraint dept_name_uk unique)
tablespace users;
create table hr.emp(
id number,
name varchar2(30) constraint emp_name_nn not null,
sal number,
dept_id number,
constraint emp_id_pk primary key(id),
constraint emp_sal_ck check(sal between 0 and 30000),
constraint emp_dept_id_fk foreign key(dept_id) references hr.dept(dept_id))
tablespace users;
■ flashback table (10g)
- 삭제한 테이블을 복원하는 SQL문
- purge : 영구히 삭제
# 휴지통 보기
select * from user_recyclebin;
# 휴지통 비우기
purge recyclebin;
# 테이블 삭제 후 휴지통 조회
drop table hr.emp;
select * from user_recyclebin;
▶ EMP를 drop 하면 바로 휴지통으로 삭제하는 것이 아니라 BIN$..로 rename을 한 것이고, 아직 users tablespace 안에 가지고 있다. 왜냐하면 잘못 삭제하면 복원할 수 있도록 되어있다. 영구 삭제하려면 purge 하면 된다.
▶ BIN$ 는 purge 하기 전까지 남아있기도 하지만 만약 users의 공간이 부족하면 가장 오래된 데이터부터 조금씩 purge 하기 시작한다.
# 조회할 수 있다.
select * from "BIN$kXjVhjMWT16pGn9rrm5Hog==$0";▶ 특수문자가 들어있기 때문에 큰따옴표로 묶어줘야 한다.
select * from user_tables where table_name in('EMP', 'DEPT');
select * from user_objects where object_name in('EMP', 'DEPT');
select * from user_tab_columns where table_name in('EMP', 'DEPT');
select * from user_constraints where table_name in('EMP', 'DEPT');▶ 출력하면 emp 제약조건등 다 삭제되어 있다.
# 삭제된 테이블을 복원하고 싶을 경우
select * from user_recyclebin;
flashback table emp to before drop;▶ 테이블은 원래 이름으로 복원이 되지만 제약조건, 인덱스 이름은 원래 이름으로 복원이 안 된다. 직접 이름을 수정해야 한다.
select * from user_recyclebin;
▶ 휴지통에서 복원이 되었다.
select * from hr.emp;
# 테이블 삭제 후 동일한 이름으로 테이블 생성 가능
drop table hr.emp;
select * from user_recyclebin;
create table hr.emp
as select * from hr.employees;
select * from hr.emp;
# 다시 drop 할 경우
drop table hr.emp;
select * from user_recyclebin;
# emp 테이블을 복원하고 싶을 경우
flashback table emp to before drop;
select * from user_recyclebin;▶ recyclebin 안에 테이블 이름이 동일한 경우 가장 최근에 삭제한 테이블을 기준으로 복원한다.
# emp 테이블을 다시 복원하고 싶을 경우
- 하지만 이미 emp 테이블이 복원되어 있다.
flashback table emp to before drop;▶ 오류 뜬다. 이미 존재하기 때문이다.
# 해결방법
flashback table emp to before drop rename to emp_2024;
select * from hr.emp_2024;
▶ 동일한 테이블을 복원할 경우엔 꼭 rename을 해줘야 오류가 발생하지 않는다.
★ purge를 쓰는 순간 복원이 되지 않는다. 백업 리커버리를 하면 복원할 수도 있지만
flashback으론 복원이 되지 않고 영구히 삭제한다.
■ view
- 하나 이상의 테이블이 있는 데이터를 논리적으로 처리하는 Object
- select문만 가지고 있는 객체이다. (실제 데이터는 가지지 않는다.)
- 볼 수 있는 컬럼들만 따로 테이블을 생성 후 그 테이블만 권한을 주는 방식이다.
- 사용하는 이유 : 간접 엑세스(직접 테이블에 대해서 엑세스하는 것이 아니라) 제공한다.
- 테이블처럼 보이지만 테이블이 아니고 select문만 가지고 있을 뿐이다. (테이블 같이 모아놓은 객체)
- 따로 유지 관리하지 않아도 되고, 스토리지 낭비도 없다.
- view를 생성하려면 CREATE VIEW 시스템 권한이 있어야 한다.
ex) 권한 확인 (내부적으로는 다 view이다.)
select * from user_sys_privs;
select * from role_sys_privs;
select * from session_privs;create table hr.emp_copy
as
select employee_id ,last_name, first_name, job_id, department_id
from hr.employees;
select * from hr.emp_copy;
grant select on hr.emp_copy to insa;
▶ 문제점은 기존 employees 테이블에 update가 발생하면 따로 emp_copy에도 업데이트를 해줘야 하는 불편성이 있다.
, 스토리지 낭비도 생긴다.
▶ insa 유저에서 조회를 해보면 권한 부여한 컬럼만 보이게 된다.
# 해결방법
create view hr.emp_view
as
select employee_id ,last_name, first_name, job_id, department_id
from hr.employees;
select * from hr.emp_view; -- 실행하면 내부적으로 object_type이 view인지 체크한다.
▶ 테이블처럼 보이지만 테이블이 아니고, 스토리지도 쓰지 않는다.
▶ select문만 가지고 있는 것이고, 유지 관리도 필요 없다.
## 권한부여
grant select on hr.emp_view to insa;
select * from user_tab_privs;
# insa 섹션에서 확인
# 테이블만 보고 view테이블인지 아닌지 확인하는 법
select * from user_objects where object_name in ('EMPLOYEES', 'EMP_VIEW');
▶ 둘이 비교해 보면 employees테이블은 OBJECT_TYPE이 TABLE로 되어있어서 데이터 객체 ID가 주어지지만, emp_view는 view인 걸 볼 수 있고, 가상 테이블이기 때문에 데이터 객체 ID도 null로 돼있는 걸 볼 수 있다.
# view 정보 확인하는 법
select * from user_views where view_name = 'EMP_VIEW';
# view 삭제
drop view hr.emp_view;▶ view는 테이블 같은 거(하나의 객체)이기 때문에 purge를 쓰지 않아도 된다.
▶ 관련 권한도 다 회수된다.
create view hr.emp_view
as
select employee_id ,last_name, first_name, job_id, department_id
from hr.employees;
grant select on hr.emp_view to insa;▶ 다시 생성할 때 권한을 재부여해야 한다.
# 이런 수고를 덜어내는 방법
create or replace view hr.emp_view
as
select employee_id ,last_name||' '|| first_name, job_id, department_id
from hr.employees;▶ 하지만 에러 뜬다.
▶ 뷰 생성 시 표현식에 있는 컬럼(last_name||' '|| first_name)에 대해서는 꼭 별칭을 달아줘야 한다.
## 해결방법
- or replace view 활용하기
- or replace view : 기존 뷰 삭제하고 생성한다. 단 권한 부여한 정보는 그대로 있다. 기존 뷰 있으면 권한은 그대로 놨두고, 없으면 다시 생성하라는 뜻
- 삭제 후 다시 권한을 재부여하는 수고가 있으므로 or replace view 쓰는 게 좋다.
- or replace view 쓸 때 주의할 점은 표현식을 쓰는 컬럼들은 꼭 별칭을 달아줘야 에러가 발생하지 않는다.
create or replace view hr.emp_view
as
select employee_id ,last_name||' '|| first_name name, job_id, department_id
from hr.employees;
## insa 유저에서 확인
select * from hr.emp_view;
문제) 부서이름별 총액급여를 access 하는 dept_sum 뷰를 생성한 후 insa 유저한테 dept_sum 뷰에 대한 select 권한을 부여해 주세요.
1) 부서이름별 총액급여 뷰 생성
create or replace view hr.dept_sum
as
select d.department_name, sum(e.salary) sum_sal
from hr.employees e, hr.departments d
where e.department_id = d.department_id
group by d.department_name; ▶ 이 코드로 해도 실행은 잘 되지만 좋은 코드는 아니다. 이유는 join의 일 량이 너무 많다. join의 일 량을 줄이는 만큼 오라클의 성능이 좋아진다.
# 개선방안
create or replace view hr.dept_sum
as
select d.department_name, e.sum_sal
from(select department_id, sum(salary) sum_sal
from hr.employees
group by department_id) e, hr.departments d
where e.department_id = d.department_id;▶ department_id별로 총액을 먼저 구하고, 그 결과 집합을 인라인뷰로 묶어준다. 그 결과 집합 테이블을 e 별칭을 달아주고 부서 테이블 d로 조인을 시켜서 하면 성능이 더 좋은 SQL문이다.
2) dept_sum 유저에게 select 권한 부여
grant select on hr.dept_sum to insa;
3) 권한이 잘 들어갔는지 확인하기
select * from user_tab_privs;
| 특징 | 단순 view | 복합 view |
| 테이블 수 | 1개 | 2개 이상 |
| 함수, group by, join | 없음 | 있음 |
| 데이터 그룹 포함 | 없음 | 있음 |
| 뷰를 통한 DML | 있음 | 없음 |
ex) 뷰에 다음 항목들이 포함되어 있을 경우
1) 그룹함수
2) GROUP BY절
3) DISTINCT(UNIQUE)
4) Pseudo column(가상칼럼)
5) 표현식 (ex) last_name||' '||first_name, salary *12, lower(last_name))
6) 뷰에서 선택되지 않은 컬럼이 not null 제약조건이 생성되어 있다.
▶ 1,2,3,4 내용이 있을 경우엔 delete 작업을 수행할 수 없다.
▶ 1,2,3,4는 복합뷰이고, DML 작업을 수행할 수 없다.
▶ 1,2,3,4,5 내용이 있을 경우엔 update 작업을 수행할 수 없다.
▶ 1,2,3,4,5,6 내용이 있을 경우엔 insert 작업을 수행할 수 없다.
▶ 5,6 단순뷰
ex) test 해보기
1) 원본 테이블 생성
create table hr.test
as
select employee_id id, last_name name, salary sal
from hr.employees
where department_id = 20;
select * from hr.test;
create or replace view hr.test_view
as
select * from hr.test;
select * from hr.test_view;
▶ 단순 뷰이다.
# 권한 부여 및 확인
grant select, insert, update, delete on hr.test_view to insa;
select * from user_tab_privs;
# insa 섹션에서 조회하고 데이터 추가
select * from hr.test_view;
insert into hr.test_view(id, name, sal) values(300, 'james', 1000);
# 뷰 수정 후 조회 (insa 섹션)
update hr.test_view
set sal = 10000
where id = 202;
select * from hr.test_view;
commit;
▶ hr에 넘어와서 조회해도 똑같이 조회가 된다.
# 뷰 삭제 후 조회 (insa)
delete from hr.test_view where id = 300;
commit;
select * from hr.test_view;
▶ hr에 넘어와서 조회해도 똑같이 조회가 된다.
# hr.test 테이블 구조 확인
desc hr.test;
insert into hr.test_view(id, sal) values(400,10000);
▶ 뷰를 통해서 엑세스할 때 누락된 컬럼이 NOT NULL 제약조건이 걸려있으면 에러가 발생한다.
alter table hr.test modify name null;
alter table hr.test modify sal not null;
desc hr.test;
ex) 뷰 생성
create or replace view hr.test_view
as
select id, name from hr.test;# 인사 유저로 가서
select * from hr.test_view;
## 만약에 insert를 하면 에러가 발생한다.
insert into hr.test_view(id, name) values(400,'oracle');
▶ 뷰는 순수히 SELECT 절의 결과를 반환하는 역할만 하기 때문에 직접 데이터 삽입이 불가능하다.
# update는 가능하다.
update hr.test_view
set name = 'oracle'
where id = 202;
select * from hr.test_view;
# delete도 가능하다.
delete hr.test_view where id = 202;
ex) 단순 뷰이다.
create or replace view hr.test_view
as
select id, name, sal from hr.test
with read only;▶ with read only : SELECT 허용, DML 불허
insert into hr.test_view(id, name, sal) values(300, 'james', 1000);delete from hr.test_view where id = 300;update hr.test_view
set name = 'oracle'
where id = 202;
▶ DML은 오류 발생한다. (권한이 있더라도 의미 없다.)
select view_name, read_only from user_views;
▶ Y로 되어있으면 select만 가능하다고 알면 된다.
create or replace view hr.test_view
as
select id, name from hr.test;
select view_name, read_only from user_views;
▶ with read only를 생략하게 되면 N으로 바뀐다.
ex) 테이블 생성
create or replace view hr.test
as
select employee_id id, last_name name, department_id dept_id
from hr.employees
where department_id in(20,30);
select * from hr.test;
# 뷰 생성 후 내가 원하는 정보를 보고 싶을 경우
create or replace view hr.test_view
as
select *
from hr.test
where dept_id = 20;
select * from hr.test_view;
# insa session
select * from user_tab_privs;
# insa session
insert into hr.test_view(id, name, dept_id) values(300,'oracle', 30);
▶ 20번 부서로 뷰를 만들었기 때문에 무조건 20번 부서만 바라보기 때문에 다른 부서 코드가 들어오면 에러가 발생한다.
▶ update를 하면 실행은 돼도 실제로 조회했을 땐 수정이 되지 않는다. 부서 코드를 다른 부서를 코드로 수정하면 에러 발생한다.
# 뷰에 check 제약조건을 거는 방법
create or replace view hr.test_view
as
select *
from hr.test
where dept_id = 20
with check option constraint test_view_ck;▶ with check option constraint [테이블명_ck] 뷰에 check 제약조건 생성
▶ 조건식은 where절이 조건식이 된다.
▶ dept_id = 20인 사람만 허용한다.
select * from user_constraints where table_name = 'TEST_VIEW';
▶ V 표시가 제약조건이 걸려있다고 생각하면 된다.
insert into hr.test_view(id, name, dept_id) values(300,'oracle', 30);▶ insert 작업 시에 다른 부서 코드가 입력되면 오류 발생
update hr.test_view
set dept_id = 30
where id = 201;▶ update 작업 시에 다른 부서 코드로 수정하게 되면 오류 발생한다.
delete hr.test_view where id = 201;
▶ delete 작업은 수행할 수 있다. (복합뷰이기 때문)
'oracle SQL' 카테고리의 다른 글
| 2024.12.16 13일차 수업 (0) | 2024.12.16 |
|---|---|
| 2024.12.12 12일차 수업 (2) | 2024.12.12 |
| 2024.12.11 11일차 수업 (0) | 2024.12.11 |
| 2024.12.10 10일차 수업 (2) | 2024.12.10 |
| 2024.12.09 9일차 수업 (2) | 2024.12.09 |
