SQL - DAY 13

BUMSOO·2024년 6월 26일
sql오라클 DBA

CTAS를 수행하면 테이블의 구조,데이터,NOT NULL 제약조건만 복제된다.

CREATE TABLE hr.emp
AS 
SELECT * FROM hr.employees;

CREATE TABLE hr.dept
AS 
SELECT * FROM hr.departments;

SELECT * FROM user_constraints WHERE table_name IN ('EMP','DEPT');

제약조건 옵션

ON DELETE CASCADE

  • PRIMARY KEY에 해당하는 행을 삭제하려고 하는 순간 FOREIGN KEY쪽에 자식 데이터가 있으면 삭제가 안된다. 이 문제를 해결하기 위해 먼저 자식데이터를 삭제 하면 부모 키를 삭제 할 수 있다.
ALTER TABLE hr.emp ADD CONSTRAINT emp_dept_id_fk FOREIGN KEY(department_id) REFERENCES hr.dept(department_id)
ON DELETE CASCADE;

ON DELETE SET NULL

  • PRIMARY KEY에 해당하는 행을 삭제하려고 하는 순간 FOREIGN KEY쪽에 자식 데이터가 있으면 삭제가 안된다. 이 문제를 해결하기 위해 먼저 자식데이터의 필드값을 NULL값으로 수정하고 부모키를 삭제한다.
ALTER TABLE hr.emp ADD CONSTRAINT emp_dept_id_fk FOREIGN KEY(department_id) REFERENCES hr.dept(department_id)
ON DELETE SET NULL;

CREATE TABLE 제약조건

  • NOT NULL 제약조건은 열레벨 정의에서만 가능
  • 열레벨 정의에서는 제약조건 뒤 컬럼명 불필요
  • 테이블 레벨 정의에서는 제약조건 뒤 컬럼명 필요
  • foreign key 열 정의 제약조건 시 foreign key(컬럼명)은 삭제해야한다.
CREATE TABLE hr.emp(
    -- 열레벨 정의
    id number CONSTRAINT emp_id_pk PRIMARY KEY,
    name varchar2(30) CONSTRAINT emp_name_nn NOT NULL -- NOT NULL 제약조건은 무조건 열 레벨로만 정의
                      CONSTRAINT emp_name_uk UNIQUE , -- 제약조건을 여러개 할때 ,는 쓰지 않는다 
    sal number,
    dept_id number CONSTRAINT emp_dept_id_fk  REFERENCES dept(dept_id) ON DELETE SET NULL --FOREING KEY 제약조건을 열정의로 하려면 REFERENCE만 쓰면 된다.
    -- 테이블 레벨 정의
    CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000),
    CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ON DELETE SET NULL
    );

테이블 이름 수정

방법 1)
RENAME old_name TO new_name;

방법 2)
ALTER TABLE old_name RENAME TO new_name;
ALTER TABLE 소유자.old_name RENAME TO new_name;

  • 이름을 수정한다고 해서 기존에 걸려있던 제약조건들이 사라지지 않는다.
  • 이름에 소유자명을 작성하면 안된다. 
RENAME hr.emp_new TO hr.emp; -- 오류발생, 소유자 이름 사용하지 말자.

컬럼 이름 수정

ALTER TABLE 소유자.테이블 RENAME COLUMN old to new;

ALTER TABLE hr.emp RENAME COLUMN id TO emp_id;

제약조건 이름 수정

ALTER TABLE 소유자.테이블 RENAME CONSTRAINT old TO new;

  • PRIMARY KEY, UNIQUE 제약조건 이름 변경시 unique index 이름은 변하지 않음(초기 인덱스 이름은 제약조건 이름을 따라간다.)
ALTER TABLE hr.emp RENAME CONSTRAINT emp_id_pk TO emp_pk;

인덱스 이름 수정

ALTER INDEX 인덱스이름 RENAME TO 새로운 이름;

ALTER INDEX emp_id_pk RENAME TO emp_id_idx;

FlashBack Table

삭제한 테이블을 복원하는 SQL문
FLASHBACK TABLE 복원테이블명 TO BEFORE DROP;
❗단, 제약조건,인덱스도 복원되지만 원래 이름으로 복원되지 않습니다. 복원 후에 수정작업 필수

FLASHBACK TABLE emp TO BEFORE DROP;

SHOW RECYCLEBIN

SHOW RECYCLEBIN

  • DROP한 table, index를 보여주는 휴지통
  • 동일한 명령문
    SELECT * FROM user_recyclebin;

  • RECYCLEBIN 안의 테이블을 보고 싶으면
    SELECT * FROM "OBJECT_NAME"; 을 해주면 조회 가능하다.

  • 오라클에서 테이블을 DROP시 진짜로 삭제 하는것이 아니라 테이블 명을 BIN$.... 으로 이름 변경 후 RECYCLEBIN에 보관 하는 형태이다.

  • RECYCLEBIN 안에 중복된 테이블명이 있다면 복원 시 최근 DROP된 테이블 부터 복원이 된다.

  • 복원시 동일한 테이블 명이 이미 존재시 RENAME을 통해 이름변경을 해줘야 한다.
    FLASHBACK TABLE 오리지널 테이블명 TO BEFORE DROP RENAME TO 변경 테이블명

FLASHBACK TABLE emp TO BEFORE DROP RENAME TO emp_2024;
  • 소유자 TABLESPACE 스토리지 공간의 여유가 있을때 까지 보관 하다가 공간이 부족시 가장 오래된 순으로 PURGE 시킴

PURGE RECYCLEBIN

recyclebin(휴지통)을 영구히 삭제 시킴

PURGE RECYCLEBIN;

PURGE

테이블을 영구히 삭제

DROP TABLE hr.dept PURGE;

날짜 타입

SELECT 
    sysdate,
    systimestamp,
    current_date,
    current_timestamp,
    localtimestamp
FROM dual;

  • date : 년월일, sysdate, current_date

  • timestamp(9) : 년월일시분초.9자리, localtimestamp

  • timestmap(9) with time zone : 년월일시분초.9자리 timezone, systimestamp, current_timestamp

  • timestamp(9) with local time zone : 보는 지역에 따라 날짜시간이 자동으로 정규화해주는 날짜 타입

  • interval year to month : to_yminterval 함수로 문자를 날짜형으로 변환하면 해당 타입으로 됨

  • interval day to second : to_dsinterval 함수로 문자를 날짜형으로 변환하면 해당 타입으로 됨

to_date 함수

문자 날짜를 날짜형(date)으로 변환하는 함수

    SELECT to_date('20240626','yyyymmdd') FROM dual;

to_timestamp 함수

  • 문자 날짜를 날짜형(timestamp)으로 변환하는 함수
  • ss.ff로 timestamp 9자리를 표시
  SELECT to_timestamp('20240626','yyyymmdd') FROM dual;
    
  SELECT to_timestamp('2024-06-26 16:21:30.123456','yyyy-mm-dd hh24:mi:ss.ff') FROM dual; -- ss.ff 입력 시 자동으로 9자리 입력

to_timestamp_tz 함수

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

to_yminterval 함수

  • 문자날짜를 날짜형(interval year to month)으로 변환 하는 함수
  • to_yminterval('년수-개월수')
  • 년수와 개월수를 같이 사용할때는 11개월을 넘어가면 안된다.
SELECT 
    sysdate,
    add_months(sysdate,14),
    sysdate + to_yminterval('1-2') p,-- 현재 날짜에서 1년2개월을 더해줌
    sysdate - to_yminterval('1-2') m, -- 현재 날짜에서 1년2개월을 빼줌
    sysdate + to_yminterval('-1-2') m_1 -- 현재 날짜에서 1년2개월을 빼줌
FROM dual;

to_dsinterval 함수

  • 문자날짜를 날짜형(interval day to second)으로 변환하는 함수
  • to_dsinterval('일수 시분초')
SELECT
    localtimestamp,
    localtimestamp + to_dsinterval('100 10:00:00.123456789'),
    localtimestamp + to_dsinterval('100 00:00:00')
FROM dual;

날짜 계산

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

CREATE TABLE hr.time_test_1(
    a interval year(3) to month,
    b interval day(3) to second);
    
INSERT INTO hr.time_test_1(a,b) VALUES(to_yminterval('10-00'), to_dsinterval('100 00:00:00'));

SELECT localtimestamp + a, localtimestamp + b 
FROM hr.time_test_1;

[13일차 후기]

오늘은 교육 첫날 이후로 처음으로 하나증권 구내식당이 아닌 다른 곳에서 외식을 하였다. 보승회관이라는 곳에서 국밥을 먹었는데 국밥러버 로써 보승회관은 꽤나 맛집이었다.근대 역시 강남이라 그런지 국밥이 무슨 만원이나.. 물론 그냥 아무 생각 없이 주는 대로 먹는 구내식당도 좋지만, 가끔은 오늘처럼 다른것도 먹는게 기분전환 겸 좋은거 같다. 오늘 수업에서는 크게보면 제약조건 옵션, CREATE TABLE 시 제약조건 거는법, 이름 수정, FLASHBACK을 이용한 RECYCLEBIN에서의 테이블 복구, 날짜 타입을 배웠다. PK컬럼에 FOREIGN KEY 제약조건을 건 뒤 PK값을 삭제하려고 하면 참조무결성 오류가 나온다.(물론 참조하지 않는 PK값은 그냥 삭제 가능하다) 이러한 오류 없이 삭제 하기위해서는 제약조건 추가시 옵션을 같이 걸 필요가 있는데, 바로 CASCADE, SET NULL 옵션이다. CASCADE 옵션같은 경우 부모테이블 키값을 삭제시 그것을 참조 하고 있는 키의 행도 같이 지운다. 하지만 보통의 업무상 행 전체를 지우는건 문제가 될 수 있는데 그럴때 유용한게 SET NULL 옵션이다. 이 옵션은 행 전체를 지우지는 않고 삭제한 부모 키 값을 참조 하고 있는 자식테이블의 필드 값을 NULL로 만들어준다. 이러한 옵션이 실제 업무상에서는 더 유용할 것 같다. 그리고 이전까지 제약 조건 주는 방법은 ALTER TABLE ADD CONSTRAINT 이런식으로 테이블 생성 후 주었다면, CREATE TABLE 시 처음부터 제약조건을 주는 방법을 배웠다. 여기에도 방법은 2가지가 있었는데, 열레벨 정의 와 테이블레벨 정의다. 근대 내 스타일은 열 레벨 정의 쪽이라 해당 방법을 자주 쓰지 않을까 싶다.(NOT NULL 조건은 무조건 열레벨 으로만 사용 가능하다) 오늘 배웠던 것중 가장 인상 깊었던 내용은 FLASHBACK 과 RECYCLEBIN에 대한 내용이었다. 오라클에도 윈도우의 휴지통 같은 기능이 있는데 그것이 바로 RECYCLEBIN이었다. 우리가 그동안 DROP을 하면서 이건 테이블을 삭제 한다는 개념을 가지고 있었는데, 정확히 말하면 삭제가 아닌 테이블 명을 "BIN$..." 이런식으로 변경 한 후 RECYCLEBIN(휴지통)으로 옮기는 작업이었다. 그래서 DROP 시 PURGE 옵션을 주지 않았다면 해당 테이블은 RECYCLEBIN에 남게 되는데 이건 그럼 언제까지 있느냐 하면 해당 테이블의 TABLESPACE의 스토리지가 꽉 찰때 까지 남아있게 되며, 공간 초과시 오래된 순부터 영구히 삭제가 된다. 이런걸 보면 정말 오라클은 잘 만든 프로그램이라는 생각을 또 한번 갖게 되었다. 마지막으로는 교육기간 초반에 잠깐 배웠단 날짜 타입에 대해 좀더 자세히 배웠는데, 역시 날짜는 민감한 타입이라 그런지 종류도 엄청 많았다. 기존에 알고 있던 date 타입에 추가로 5개타입을 더 배웠으니... 근대 정말 활용만 잘하면 유용할 내용이라 확실히 기억해 두는게 좋겠다. 오늘은 내가 배웠던 내용들에 대해 복습 하는 느낌으로 후기를 작성해 봤다. 확실히 이렇게 작성해보니 내 스스로 개념을 정리하는 느낌이 들어 좋은 것 같다.

profile
BUMSOO
이전 포스트

SQL - DAY 12

다음 포스트

SQL - DAY 14

0개의 댓글