24.06.26(수) 오라클 수업 CTAS/CASCADE/SET NULL/RENAEM/flashback table/systimestamp

제약조건은 데이터의 품질을 좋게하기 위해서 만들어진 기능이다.

나의 삶의 품질을 좋게하기 위해서 어떠한 제약조건을 주어야 할까?

 

--CTAS로 테이블 
:복제를 하면 테이블 구조(컬럼), 데이터, NOT NULL 제약조건은 복제가 된다.
CREATE TABLE
AS subquey

-CASCADE CONSTRAINT:나를 참조하고 있는 제약조건들을 먼저 지우고 테이블을 삭제(아마 데이터 )다 지워버려
(어제 한 데이터 지우고 오늘 새로운 데이터로 시작하기 위해서!)
DROP TABLE hr.dept CASCADE CONSTRAINT PURGE;
DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;

■제약조건이 붙은 테이블 삭제하는 방법
◎DROP CONSTRAINT: 만약 child row를 살려달라고 하면 제약조건을 취하하고 삭제.
ALTER TABLE 소유자.테이블 DROP CONSTRAINT 제약조건이름;

◎ON DELETE CASCADE 옵션
child row도 같이 싹 다 지워버려!
ALTER TABLE 소유자.테이블 ADD CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼)
REFERENCES 소유자.테이블(컬럼) ON DELETE CASCADE;

◎ON DELETE SET NULL 옵션
: primary key에 해당하는 행을 삭제할 때 자식데이터에는 종속된 필드값을 null값으로 변형 
ALTER TABLE 소유자.테이블 ADD CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼)
REFERENCES 소유자.테이블(컬럼) ON DELETE SET NULL;

■CREATE TABLE하면서 제약조건 같이 설정하기
- 제약조건은 여러개 구성할 수 있다.
- 열레벨 : 열을 정의하면서 제약조건도 같이 정의
주의1) 테이블 이름 쓰지 마
주의2) NOT NULL은 열레벨에만 가능
- 테이블레벨
주의1) 테이블 이름을 써줘야 해

CREATE TABLE 소유자.테이블(
            컬럼명 타입 CONSTRAINT 제약조건이름 PRIMARY KEY, --- 열레벨
            컬럼명 타입 CONSTRAINT 제약조건이름 NOT NULL      --- NOT NULL은 열레벨만!
                              CONSTRAINT 제약조건이름 UNIQUE,        --- 한 컬럼에 제약조건 여러개 걸 수 있어.
            컬럼명 타입,                                                            --- 테이블레벨
    CONSTRAINT 제약조건이름 UNIQUE(컬럼),
            CONSTRAINT 제약조건이름 (FOREIGN KEY(컬럼)) REFERENCES 테이블(컬럼), --- FOREIGN KEY(컬럼) 삭제 가능
            CONSTRAINT 제약조건이름 CHECK(조건));

나를 참조하고 있는 foreign key 제약조건이 있어서 오류발생이 일어날 때 해결책
1. DROP CONSTRAINT로 fk 제약족건 지우고 pk제약조건 지운다.
ALTER TABLE hr.dept DROP CONSTRAINT emp_dept_id_fk; --나를 참조하고있는 fk찾아서 지우고
ALTER TABLE hr.dept DROP CONSTRAINT dept_dept_id_pk; --삭제한다.

2.CASCADE로 지운다(데이터가 다 지워짐)
ALTER TABLE hr.dept DROP CONSTRAINT dept_dept_id_pk CASCADE;

■이름 수정하기
◎ 테이블 이름 수정(RENAME)
#방법1 - RENAME사용
RENAME old TO new;

--이름을 바꾸는 사람은 소유자다. 그래서 소유자 이름을 구지 표현할 이유가 없다.
RENAME hr.emp_new TO hr.emp;--(X)

#방법2 - ALTER TABLE 사용하기
ALTER TABLE old RENAME TO new;
ALTER TABLE hr.old RENAME TO new;
ALTER TABLE hr.old RENAME TO hr.new;--(X)

◎ 컬럼 이름 수정
ALTER TABLE 소유자.테이블 RENAME COLUMN old TO new;

◎제약조건 이름 수정
ALTER TABLE 소유자.테이블 RENAME CONSTRAINT old TO new;

◎인덱스 이름 수정
ALTER INDEX old RENAME TO new;


■flashback table
- 삭제한 테이블을 복원하는 SQL문
- 윈도우즈에 휴지통같은 기능

show recyclebin;
- 삭제된 테이블 복원된다.(단, 제약조건, 인덱스도 복원되지만 원래 이름으로 복원되지 않는다. 복원 후에 이름을 수정해야 한다.)

-- USER관점
SELECT * FROM user_recyclebin;

--DBA관점
SELECT * FROM dba_recyclebin;

--복원하기
FLASHBACK TABLE 테이블명 TO BEFORE DROP;

-- 테이블을 삭제하는 순간 인덱스와 제약조건도 삭제 됨.
-- flashback 한 후에는 제약조건이름과 인덱스 이름이 삭제돼서 user_조건으로 확인하고 직접 수정해야한다.
SELECT * FROM user_constraints WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_cons_columns WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_indexes WHERE table_name IN ('EMP','DEPT');
SELECT * FROM user_ind_columns WHERE table_name IN ('EMP','DEPT');

-- 복원할 때 복원해야할 테이블이름과 동일한 이름이 있는 경우 오류발생
해결방법) RENAME을 이용해서 이름 바꿔 복원하자!
FLASHBACK TABLE emp TO BEFORE DROP RENAME TO emp_2024;

- 휴지통 비우기
PURGE RECYCLEBIN;

# PURGE:테이블 영구히 삭제
-recyclebin에 들어가지 않는다.
DROP TABLE hr.dept PURGE;

■날짜 타입
--제품의 유효기간, 게런티 기간을 계산할 때 쓰임.

SELECT
sysdate, --서버시간대
systimestamp,--서버시간대
current_date,--클라이언트 세선 시간대(클랑이언트 타임존에 따라 시간이 다름)
current_timestamp, --클라이언트 세선 시간대(타임존의 지역이름이 보이냐 안 보이냐)
localtimestamp--클라이언트 세선 시간대
FROM dual;

#날짜타입 설명
date 타입 : 년월일, sysdate, current_date
timestamp(9):년월일시분초.9자리(초이하 9자리까지,  localtimestamp
timestamp(9) with time zone : 년월일시분초.9자리, timezone, systimestamp, current_timestamp
timestamp(9) with local time zone : 보는 지역에 따라 날짜시간이 자동으로 정규화해주는 날짜 타입

--시간대 바꾸기
ALTER SESSION SET TIME_ZONE = '+09:00';---이게 한국 시간대

◎to_date 함수
문자 날짜를 날짜형으로 변환하는 함수
SELECT to_date('20240626','yyyymmdd') FROM dual;

◎to_timestamp 함수
문자 날짜를 날짜형(timestamp)으로 변환하는 함수
SELECT to_timestamp('2024-06-26 16:21:30.123456','yyyy-mm-dd hh24:mi:ss.ff') FROM dual; --ff는 자동으로 해달라는 뜻

◎to_timestamp_tz 함수
문자 날짜를 날짜형(timestamp with time zone)으로 변환하는 함수
SELECT to_timestamp tz('20240626','yyyymmdd') FROM dual;
SELECT to_timestamp_tz('2024-06-26 16:21:30.123456','yyyy-mm-dd hh24:mi:ss.ff') FROM dual;
SELECT to_timestamp_tz('2024-06-26 16:21:30.123456789 +09:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm') FROM dual; 

◎to_yminterval 함수
문자날짜를 날짜형(interval year to month)으로 변환 하는 함수
to_yminterval('년수-개월수')

◎to_dsinterval 함수
문자날짜를 날짜형(interval day to second)으로 변환하는 함수
to_dsinterval('일수 시분초')

#날짜 계산
날짜 + 일 수 = 날짜
날짜 - 일 수 = 날짜
날짜 - 날짜 = 일수
날짜 + 날짜 = 오류
날짜 + interval year to month = 날짜
날짜 + interval day to second = 날짜