2022.1.4 오늘의 공부

▣오늘의 주요 키워드▣

1. flashback table
2. 날짜타입
3. view

---

★ Flashback table
-삭제한 테이블을 복원하는 SQL 명령어

show recyclebin
select * from user_recyclebin;
purge recyclebin -- 휴지통 비우기

show recyclebin

purge recyclebin

- 현재 recyclebin에 아무것도 없기에 show recyclebin코드는 오류가 나온다.

 

create table emp_2022  
as select * from employees;
- employees테이블을 복제하여 emp_2022테이블 생성 (ctas)
select * from emp_2022;

 

drop table emp_2022; --테이블 삭제
select * from emp_2022; --확인
select * from user_recyclebin; --recyclebin확인

drop table 후 확인

recyclebin 확인

- drop table emp_2022로 삭제를 했을 때 recyclebin에 휴지통처럼 완전 삭제되지 않고 남아있음을 확인할 수 있다.

 

create table emp_test11 as select * from "BIN$HIPh1ZkRTC+XEXZR7S5keA==$0"; --object name
select * from emp_test11;

- recyclebin에 있는 emp_2022 테이블의 object_name으로 복제하여 새로운 테이블을 다시 만들고 확인한다.

 

select * from user_recyclebin; -- 휴지통 확인

-복제한 테이블 또한 recyclebin에 있는 것을 확인할 수 있다.

flashback table emp_2022 to before drop; -- 삭제한 테이블을 복원하는 sql문

select * from user_recyclebin; -- 확인
select * from emp_2022;

-삭제한 emp_2022 테이블을 flashback 함수를 사용하여 복구하였다.

select * from user_constraints where table_name = 'EMP_2022'; --제약조건 확인

alter table emp_2022 rename constraint "BIN$3ClC4G3ZQCWGRTew+bDIQQ==$0" to emp_2022_name_nn; -- 원본이름으로 복원이 되지 않아서 이름을 변경해주기
select * from user_constraints where table_name = 'EMP_2022';

- 제약조건의 이름을 변경하였다.

drop table emp_2022 purge;
select * from user_recyclebin;

- drop table emp_2022 purge; 코드를 수행하게 되면 table을 완전 삭제한다. 다시 돌릴 수 없다.(shift + delete처럼)

 

create table new1 as select * from employees; -- 테이블을 생성하고 복제
desc new1 -- 컬럼,타입,not null(제약조건 이름은 바뀌어서 들어감)제약조건만 복제됨
select * from user_constraints where table_name in('EMPLOYEES','NEW1');

desc new1

- employees테이블을 new1테이블로 복제했지만 constraint_name은 바뀌어서 들어간다.

alter table new1 add constraint new1_id_pk primary key(employee_id); --기본키 생성하면 unique index도 생성됨
select * from user_constraints where table_name = 'NEW';

- 기본키를 생성했는데 index까지 생성되는 것을 알 수 있다.

drop table new1; -- 테이블 삭제, 연관된 인덱스도 삭제
select * from user_recyclebin; --테이블, index객체 두개 삭제 확인

- new1테이블을 삭제했는데 recyclebin에 new1과 기본키(new1_id_pk) 이렇게 2개가 있는 것을 확인할 수 있다.

select * from user_constraints where table_name = 'NEW';
select * from user_indexes where table_name = 'NEW';

flashback table new1 to before drop; --테이블, 테이블과 관련된 인덱스도 같이 복원
select * from user_recyclebin;
select * from new1;

select * from user_constraints where table_name = 'NEW1'; --new1테이블의 제약조건 확인
select * from user_indexes where table_name = 'NEW1'; --new1테이블의 index확인

alter table new1 rename constraint "BIN$cP5DmW4/STiZ3cKt6pRZTA==$0" to new_id_pk; -- 기본키 제약조건 이름 변경
select * from user_constraints where table_name = 'NEW1';
select * from user_indexes where table_name = 'NEW1';

- 제약조건을 변경해서 확인할 수 있지만 인덱스는 바뀌지 않아 인덱스도 따로 바꿔줘야 한다.

alter index "BIN$GBgJ8CfoTLqFHEQ6vC3EtQ==$0" rename to new_id_idx; --인덱스 이름 변경
select * from user_indexes where table_name = 'NEW1';
select * from user_constraints where table_name = 'NEW1';

purge recyclebin;
-recyclebin 비우기(휴지통 비우기)
select * from user_recyclebin;

create table test1 as select * from employees; -- employees테이블 복제한 test1테이블 생성
select * from test1; -확인
select * from user_constraints where table_name = 'TEST1'; -- test1테이블의 제약조건 확인
drop table test1; --test1테이블 삭제
select * from user_recyclebin; --recyclebin 확인
select * from test1;

create table test1 as select * from employees; --recyclebin에 test1테이블이 있을 때 다시 test1테이블 생성
select * from test1;

flashback table test1 to before drop; -- 오류 : 기존테이블과 동일한 이름이 존재하기때문에 오류발생

flashback table test1 to before drop rename to new_test1; -- test1을 new_test1이름으로 변경해서 복원
select * from new_test1;
select * from user_recyclebin;

drop table test1;
select * from test1;
select * from user_recyclebin;

recyclebin

- recyclebin에 동일한 테이블이 있을 경우에는 가장 최근에 삭제한 테이블을 복원
flashback table test1 to before drop;
select * from user_recyclebin;
select * from test1;
select * from user_recyclebin;

drop table test1;
flashback table test1 to before drop rename to test1_new; --새로운 이름으로 복원시키기

---

★ 날짜 타입

select sysdate, systimestamp, current_date, current_timestamp, localtimestamp
from dual;

- sysdate, systimestamp : 현재 오라클 서버의 날짜 시간 정보

- current_date, current_timestamp, localtimestamp : 현재 새션의 날짜 시간 정보

alter session set time_zone = '+08:00'; -- 현재 접속한 지역의 timezone 수정
select sysdate, systimestamp, current_date, current_timestamp, localtimestamp
from dual;

- sysdate와 systimestamp는 변하지 않고 current_date, current_timestamp, localtimestamp 만 1시간씩 증가한 것을 알 수 있다.

 

★ 날짜 타입

• date : sysdate, current_date
• timestamp(9) : localtimestamp
• timestamp with time zone : systimestamp, current_timestamp
• timestamp with local time zone : 보는 지역에 따라 날짜 시간이 자동 정규화해주는 날짜 타입
• interval year to month : 기간을 나타내는 날짜 타입, 년수, 개월 수
• interval day to second : 기간을 나타내는 날짜 타입, 일수, 시분초. 9자리

desc time_test 
insert into time_test(a,b,c,d,e)
values(sysdate,current_date,localtimestamp,current_timestamp,localtimestamp);
commit;
select * from time_test;

alter session set time_zone = '-08:00';
select sysdate, systimestamp, current_date, current_timestamp, localtimestamp
from dual;
select * from time_test;

1.char -> date 형변환
select to_date('2022-01-04', 'yyyy-mm-dd') from dual;

2.char -> timestamp 형변환
select to_timestamp('2022-01-04 11:30:00', 'yyyy-mm-dd hh24:mi:ss') from dual;

3.char -> timestamp whith time zone 형변환(timestamp with local time zone은 형변환 없음)
select to_timestamp_tz('2022-01-04 11:30:00.123456789 +09:00', 'yyyy-mm-dd hh24:mi:ss.ff tzh:tzm') from dual;

1

2

3

create table time_test_1(
    a interval year(3) to month,
    b interval day(3) to second);
    
insert into time_test_1(a,b) values(to_yminterval('10-11'), to_dsinterval('100 10:00:00')); --년수 개월수 같이쓸 때 11개월까지만 쓸 수 있음
commit;
select a, sysdate + a, b, localtimestamp + b from time_test_1;

- interval year(3) to month 형식의 칼럼 a와 interval day(3) to second 형식의 칼럼 b를 가지는 time_test_1 테이블을 생성하고 to_yminterval('10년-11달'), to_dsinterval('100일 10시간')의 값을 넣어준다.

sysdate + a = 오늘 날짜에 10년 11개월을 더한 값, localtimestamp + b = 오늘 날짜와 시간에 100일 10시간을 더한 값

★char -> interval year to month 형변환함수,to_yminterval('년수-개월수')
select add_months(sysdate,120), sysdate + to_yminterval('10-00') from dual; -- 10년을 더한 것(년수를 개월수로 변경해서)
select add_months(sysdate,-120), sysdate - to_yminterval('10-00') from dual; -- 10년을 뺀 것
select add_months(sysdate, 1), sysdate + to_yminterval('00-01') from dual;  -- 1개월을 더한 것
select add_months(sysdate, -1), sysdate - to_yminterval('00-01') from dual;  -- 1개월을 뺀 것
select sysdate + to_yminterval('10-11') from dual; --10년 11개월을 더한 것

★char -> interval day to second 형변환함수
select to_char((localtimestamp + 100) + 10/24, 'yyyy-mm-dd hh24:mi:ss') from dual; -- to_dsinterval함수를 사용하지 않을 때
select localtimestamp + to_dsinterval('100 10:00:00') from dual; -- to_dsinterval함수를 사용할 때
select * from time_test_1;

※ 날짜 계산

날짜 + 숫자(일수) = 날짜
날짜 - 숫자(일수) = 날짜
날짜 - 날짜 = 숫자(일수)
날짜 + 시간 = 날짜, 시간
날짜 + 날짜 = 오류
날짜 + interval year to month = 날짜
날짜 + interval day to second = 날짜, 시간

 

[문제101]각 사원의 last_name, hire_date 및 근속 연수를 출력하는 query 를 작성합니다. 
사원의 근속 연수가 5 년 이상인 경우 '5 years of service' 를 출력합니다. 
사원의 근속 연수가 10 년 이상인 경우 '10 years of service' 를 출력합니다. 
사원의 근속 연수가 15 년 이상인 경우 '15 years of service' 를 출력합니다. 
어떠한 조건과도 일치하지 않을 경우 'maybe next year!'를 출력합니다.
단 근속 연수를 출력은 case, to_yminterval을 사용하세요.

1)months_between / 12 --12로 나누는 이유는 개월수로 계산하기때문에
select last_name, hire_date,
    case
        when months_between(sysdate,hire_date) / 12 >= 15 then '15 years of service'
        when months_between(sysdate,hire_date) / 12 >= 10 then '10 years of service'
        when months_between(sysdate,hire_date) / 12 >= 5 then '5 years of service'
        else
            'maybe next year!'
    end "awards"
from employees;

2)to_yminterval
select last_name, hire_date, 
    case
        when sysdate - to_yminterval('15-0') >= hire_date then '15 years of service'
        when sysdate - to_yminterval('10-0') >= hire_date then '10 years of service'
        when sysdate - to_yminterval('5-0') >= hire_date then '5 years of service'
        else
            'maybe next year!'
    end "awards"        
from employees;

★ view
- 하나 이상의 테이블이 있는 데이터를 논리적으로 처리하는 오브젝트이다.(object)
- 단지 select 문만 가지고 있는 객체이다.
- 간접 access(직접 데이터가 있는 테이블을 access 하는 게 아니고 뷰를 통해서 테이블을 참조한다.)
- 뷰를 생성하려면 create view 시스템 권한 있어야 한다.

select * from user_sys_privs; -- dba(sys유저)로 부터 직접 받은 시스템권한을 확인

select * from role_sys_privs; -- dba(sys)로 부터 롤을 통해서 받은 시스템권한 확인

select * from session_privs; -- 오라클에 로그인을 할 때 내가 받은 시스템권한을 확인(위 두개를 한 번에 확인가능)

 

select * from user_ts_quotas; --dba(sys)로 부터 받은 특정한 테이블스페이스 사용할 수 있는 정보

drop table copy_emp purge; --기존에 copy_emp 테이블이 있으면 삭제
create table copy_emp --copy_emp 테이블 생성, employees테이블에서 4개의 컬럼 복제
as select employee_id, last_name, email, job_id, department_id
from employees;

select * from copy_emp where employee_id = 100; --copy_emp테이블에서 employee_id가 100인 사원 확인
select * from employees where employee_id = 100; --employees테이블에서 employee_id가 100인 사원 확인

update employees --employees테이블의 employee_id가 100인 사원의 department_id를 10으로 변경
set department_id = 10
where employee_id = 100;

commit; --트랜잭션 종료
select * from employees where employee_id = 100;
select * from copy_emp where employee_id  = 100; -- department_id가 90으로 나오고 바뀌지 않음

grant select on copy_emp to ora; --ora유저에게 copy_emp테이블을 select할 수 있는 권한을 주기

(ora유저의 워크시트에서)
select * from user_tab_privs;
select * from hr.copy_emp;

revoke select on copy_emp from ora; --ora유저에게서부터 copy_emp테이블의 select권한을 회수

select * from user_tab_privs; --아무 권한없음
select * from hr.copy_emp; 테이블 or view가 존재하지 않다는 오류가 발생

create view emp_view --뷰생성
as
select employee_id, last_name, email, job_id, department_id
from employees;

select * from emp_view;

- emp_view select 하는 순간 뷰 생성시에 만들었던 (간접액세스)
 select employee_id, last_name, email, job_id, department_id
 from employees; 문장이 내부적으로 수행된다.

grant select on hr.emp_view to ora; --ora유저에게 hr.emp_view뷰를 select할 수 있는 권한 주기
select * from user_tab_privs; --권한 소유자,부여자, 부여받은 유저를 확인 가능

select * from user_objects where object_name in ('EMPLOYEES', 'EMP_VIEW');
-유저의 객체들을 확인
select * from user_views where view_name = 'EMP_VIEW'; 
- 뷰 정보가 있는 곳

object

view

drop view emp_view; -- emp_view뷰삭제, purge x
-- or replace : 기존 이름으로 뷰가 있으면 drop 하고 create 하는 옵션
create or replace view emp_view --이 이름의 뷰가 있으면 drop하고 다시 create하겠다
as
select employee_id, last_name || ' ' || first_name name, email, department_id --표현식은 꼭 별칭(alias)을 만들자
from employees;
grant select on hr.emp_view to ora; --ora유저에게 hr.emp_view뷰를 select할 수 있는 권한주기

[문제102] 부서이름별, 총액급여, 평균급여를 access하는 dept_sum_sal 뷰를 생성해주세요.
create or replace view dept_sum_sal
as select d.department_name, e.sum_sal, e.avg_sal
        from (select department_id, sum(salary) sum_sal, avg(salary) avg_sal
                from employees
                group by department_id) e, departments d
        where e.department_id = d.department_id;

select * from dept_sum_sal;
drop view dept_sum_sal; -- 뷰는 삭제해도 recyclebin에 들어가지 않음

★단순 뷰

• 뷰안에 select문에 테이블이 하나만 있는 경우, 즉 조인 문장 없는 경우
• 함수를 사용하지 않는 경우
• 뷰를 통해서 dml작업을 수행할 수 있다.

★복합 뷰

• 뷰 안에 select문에 테이블이 여러 개 사용한 경우, 즉 조인 문장이 있는 경우
• 함수를 사용한 경우
• 뷰를 통해서 dml 작업을 수행할 수 없다.

drop table test purge; --test테이블이 있으면 삭제

create table test --테이블 생성 department_id가 20인 값만
as select employee_id id, last_name name, salary sal
from employees
where department_id = 20;

select * from test;

create or replace view test_view --test테이블의 뷰test_view를 생성
as select * from test;

select * from test_view; -- 단순뷰 (함수나 조인 문장이 없음)

test테이블, test_view뷰

insert into test_view(id,name,sal) values(300,'홍길동',1000);
select * from test_view;
select * from test;
-test, test_view 둘다 똑같이 값이 추가가된다.

rollback;

test테이블, test_view뷰

 

-- 단순뷰는 부분적으로 뷰를 통해서 delete할 수 있다.
delete from test_view;
select * from test_view;
select * from test;
- test_view, test 둘 다 모든 값들 삭제
rollback;

test테이블, test_view뷰

-- 단순뷰는 뷰를 통해서 update할 수 있다.
update test_view
set name = '박찬호'
where id = 201;
select * from test_view;
select * from test;
-test, test_view 둘 다 업데이트 됨
rollback;

test테이블, test_view뷰

drop table test purge; 
drop view test_view;
-test를 삭제하면 test_view뷰는 오류가 뜬다.

 

(다시 test테이블을 생성하고)
create or replace view test_view --test_view를 생성
as select id, upper(name) name, sal * 1.1 sal --함수,표현식을 썼으면 별칭 필수!
from test;

select * from test_view; -- 복합뷰(함수나, 표현식을 썼음)

-- 복합뷰는 뷰를 통해서 insert할 수 없다. 오류
insert into test_view(id,name,sal) values(300,'홍길동',1000); -- 오류

-- 복합뷰는 뷰를 통해서 update할 수 없다. 오류
update test_view 
set name = '박찬호'
where id = 201;

-- 복합뷰는 부분적으로 뷰를 통해서 delete할 수 있다.
(복합뷰에 조인 문장이 있는 경우는 delete할 수 없다.)
delete from test_view; --수행 됨
select * from test_view;
select * from test;
rollback;

★ with read only

- 단순 뷰에서 dml작업을 수행할 수 없도록 하기 위한 옵션, 즉 select만 가능하도록 만들 때 사용

create or replace view test_view
as select * from test
with read only;

select * from test_view; --단순뷰, 수행가능

insert into test_view(id,name,sal) values(300,'홍길동',1000); -- 오류발생

delete from test_view; -- 오류발생

update test_view -- 오류발생
set name = '박찬호'
where id = 201;

drop table test purge; --test테이블 삭제

create table test --test 테이블 생성, employees테이블 복제 
as select employee_id id, last_name name, salary sal, department_id dept_id
from employees
where department_id = 20;

select * from test;
 
create or replace view test_view --view생성
as 
select * 
from test 
where dept_id = 20 --check 제약조건의 식
with check option constraint test_view_dept_20_ck; -- check 제약조건


- with check option : 뷰에서 check 제약조건을 생성하는 옵션
- check 제약조건 식 : 뷰생성시 select문의 where절에 있는 내용이 check제약조건 식이 된다.
- test_view를 통해서 20번 부서 정보만 신규데이터로 입력해야 한다.
- test_view를 통해서는 20번이 아닌 다른 부서로 수정할 수 없다.

select * from test_view; --단순뷰
select * from user_constraints where table_name = 'TEST_VIEW'; --CONSTRAINT_TYPE에 V는 뷰에 CHECK제약조건이 걸린것

insert into test_view(id,name,sal,dept_id) values(300,'홍길동',1000,10); 
-- 20번부서가 아닌 10번 부서를 입력해서 오류발생(위에서 where제약조건에 dept_id값이 20인 조건을 걸었기 때문에)
insert into test_view(id,name,sal,dept_id) values(300,'홍길동',1000,20);  -- 수행됨
select * from test_view; --단순뷰
select * from test; -- 실제 테이블
rollback;

update test_view -- 20번 부서가 아닌 다른 부서로 수정하면 오류발생 
set dept_id = 10
where id = 201;

update test_view -- dept_id가 아닌 다른 컬럼의 값은 수정 가능
set name = '홍길동'
where id = 201;

select * from test_view;
select * from test;

delete from test_view; -- 삭제는 가능

rollback;