251030 TIL

DML(Cont.)

MERGE(9i)

• INSERT, UPDATE, DELETE문을 한꺼번에 수행하는 SQL 문

MERGE INTO hr.dw_emp d
USING hr.oltp_emp o
ON (d.employee_id = o.employee_id)
WHEN MATCHED THEN
    UPDATE SET
        d.salary = o.salary * 1.1
    DELETE WHERE o.flag = 'd'
WHEN NOT MATCHED THEN
    INSERT (d.employee_id, d.last_name, d.salary, d.department_id) 
    VALUES (o.employee_id, o.last_name, o.salary, o.department_id);

문제

-- [문제51] emp_copy 테이블에 있는 departmetn_name값을 departments테이블에 있는 department_name값을 이용해서 수정해주세요.

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

ALTER TABLE hr.emp_copy ADD department_name varchar2(30);

-- 1) correlated subquery를 이용한 UPDATE 이용
SELECT c.department_id, d.department_id, d.department_name
FROM hr.departments d, emp_copy c
WHERE d.department_id = c.department_id;

SELECT * FROM hr.emp_copy;

UPDATE hr.emp_copy c
SET department_name = (SELECT department_name
                        FROM hr.departments
                        WHERE department_id = c.department_id);

SELECT * FROM hr.emp_copy;

ROLLBACK;

-- 2) MERGE문 이용
MERGE INTO hr.emp_copy c
USING hr.departments d
ON (c.department_id = d.department_id)
WHEN MATCHED THEN
    UPDATE SET c.department_name = d.department_name;

SELECT * FROM hr.emp_copy;

ROLLBACK;

DDL(Data Definition Language)

컬럼 추가

ALTER TABLE hr.emp ADD job_id varchar2(20);
ALTER TABLE hr.emp ADD dept_id varchar2(20);

컬럼 타입, 크기 수정

ALTER TABLE hr.emp MODIFY job_id varchar2(30);
ALTER TABLE hr.emp MODIFY dept_id number(3);

컬럼 삭제

ALTER TABLE hr.emp DROP COLUMN job_id;

컬럼 비활성화

ALTER TABLE hr.emp SET UNUSED COLUMN dept_id;

• 8i버전부터 가능
• 하나 이상의 열을 사용되지 않음으로 표시하여 시스템 리소스 요구량이 낮아질 때 삭제할 수 있는 기능
• 비활성화 후 활성화할 수 없음 → 복구 작업 수행해야 함

비활성화 컬럼 조회

SELECT * FROM user_unused_col_tabs;

• 비활성화한 컬럼의 테이블명과 개수만 보여줌
• 컬럼명을 기록해놔야 함

비활성화 컬럼 삭제

ALTER TABLE hr.emp DROP UNUSED COLUMNS;

테이블 이름 수정

RENAME emp TO emp_new; 

ALTER TABLE hr.emp RENAME TO emp_new;

• 소유자명 사용 X

컬럼 이름 수정

ALTER TABLE hr.emp RENAME COLUMN id TO emp_id;

제약 조건 이름 수정

ALTER TABLE hr.emp RENAME CONSTRAINT emp_id_pk TO emp_pk;

인덱스 이름 수정

ALTER INDEX emp_id_pk RENAME TO emp_id_idx;

제약조건

• 데이터에 대한 규칙을 만든다
• 데이터에 대한 품질을 향상시키기 위해서 만든다

제약 조건 관련 뷰

user_constraints

• 사용자가 소유한 테이블의 제약 조건 정보

SELECT * FROM user_constraints WHERE table_name = 'EMPLOYEES';

user_cons_columns

• 제약 조건이 적용된 컬럼 정보

SELECT * FROM user_cons_columns WHERE table_name = 'EMPLOYEES';

dba_constraints

• 모든 사용자의 제약 조건 정보
• DBA만 접근 가능

SELECT * FROM dba_constraints WHERE owner = 'HR' AND table_name = 'EMPLOYEES';

dba_cons_columns

• 제약 조건이 적용된 컬럼 정보
• DBA만 접근 가능

SELECT * FROM dba_cons_columns WHERE owner = 'HR' AND table_name = 'EMPLOYEES';

인덱스 관련 뷰

user_indexes

• 사용자가 소유한 인덱스 정보

SELECT * FROM user_indexes WHERE table_name = 'EMP';

user_ind_columns

• 인덱스 구성 컬럼 정보

SELECT * FROM user_ind_columns WHERE table_name = 'EMP';

dba_indexes

• 모든 사용자 인덱스 정보
• DBA만 접근 가능

SELECT * FROM dba_indexes WHERE owner = 'HR' AND table_name = 'EMP';

dba_ind_columns

• 인덱스 구성 컬럼 정보
• DBA만 접근 가능

SELECT * FROM dba_ind_columns WHERE index_owner = 'HR' AND table_name = 'EMP';

1. PRIMARY KEY

• 테이블 대표키
• UNIQUE(유일키) 값만 입력
• NULL 값은 허용할 수 없다. NOT NULL
• 테이블당 하나만 생성
• 자동으로 UNIQUE INDEX 생성
• 종속되는 행 삭제 불가

PK 제약 조건 추가

ALTER TABLE hr.emp ADD CONSTRAINTS emp_id_pk PRIMARY KEY(id);

ALTER TABLE hr.emp ADD PRIMARY KEY(id);

• 제약 조건 이름을 생략하게 되면 SYS_C 숫자 형식으로 제약 조건 이름이 생성됨

PRIMARY KEY 제약 조건에 의한 오류

• PK 컬럼에 중복 값 입력 불가
• PK 컬럼에 null 입력 불가

PK 제약 조건 삭제

ALTER TABLE hr.emp DROP CONSTRAINT emp_id_pk;

ALTER TABLE hr.emp DROP PRIMARY KEY;

2. FOREIGN KEY

• 참조무결성 제약조건, 외래키
• 동일한 테이블이나 다른 테이블의 PRIMARY KEY, UNIQUE KEY 제약 조건을 참조
• 중복값 허용, NULL 값 허용

FK 제약 조건 추가

ALTER TABLE hr.emp ADD CONSTRAINT emp_dept_id_fk
FOREIGN KEY(dept_id) references hr.dept(dept_id);

FOREIGN KEY 제약 조건에 의한 오류

• 참조해야 하는 primary key 에 데이터가 없어서 오류 발생

• 참조하고 있는 자식 데이터가 있는 경우 삭제 불가
  • 참조하고 있는 자식 데이터가 없으면 삭제 가능

• 참조하고 있는 제약 조건이 있을 경우 테이블 삭제 불가

• 참조하고 있는 제약 조건이 있을 경우 PK/UNIQUE 제약 조건 삭제 불가

CASCADE

• 나를 참조하고 있는 제약 조건을 먼저 삭제해줌
• 제약 조건 삭제 후 테이블 삭제

DROP TABLE hr.dept CASCADE CONSTRAINT PURGE;

• 제약 조건 삭제 후 PK 삭제

-- 해결방법 1
ALTER TABLE hr.emp DROP CONSTRAINT emp_dept_id_fk;
ALTER TABLE hr.dept DROP PRIMARY KEY;

-- 해결방법 2
ALTER TABLE hr.emp DROP PRIMARY KEY CASCADE;

FOREIGN KEY 제약 조건 삭제

ALTER TABLE hr.emp DROP CONSTRAINT emp_dept_id_fk;

3. UNIQUE 제약 조건

• 유일한 값만 입력, 수정
• NULL 값 허용
• 자동으로 UNIQUE INDEX 생성

UNIQUE 제약 조건 추가

ALTER TABLE hr.dept ADD CONSTRAINT dept_name_uk UNIQUE(dept_name);

UNIQUE 제약 조건에 의한 오류

• dept_name 컬럼에 unique 제약조건이 생성되어 있기 때문에 중복값 입력 불가
• null은 입력 가능

UNIQUE 제약 조건 삭제

ALTER TABLE hr.dept DROP CONSTRAINT dept_name_uk;

ALTER TABLE hr.dept DROP UNIQUE(dept_name);

• UNIQUE 제약 조건은 한 테이블에서 여러 개 있을 수 있으므로 컬럼명 지정

4. CHECK 제약 조건

• 조건 값이 TRUE인 경우 입력, 수정할 수 있도록 하는 제약 조건
• NULL, 중복 값 허용

CHECK 제약 조건 추가

ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal >= 1000 AND sal <= 2000);

CHECK 제약 조건에 의한 오류

• sal 컬럼의 check 제약 조건식에 위반된 데이터 입력 및 수정 불허
• CHECK 제약 조건에 대해 FALSE이면 오류 발생

• null은 입력 가능

CHECK 제약 조건 삭제

ALTER TABLE hr.emp DROP CONSTRAINT emp_sal_ck;

5. NOT NULL 제약 조건

• NULL 값을 불허하는 제약 조건
• NOT NULL 제약 조건은 무조건 열 레벨 정의

NOT NULL 제약 조건 추가

ALTER TABLE hr.emp MODIFY name CONSTRAINT emp_name_nn NOT NULL;

• ALTER TABLE 테이블명 MODIFY 컬럼명 CONSTRAINT 제약조건명 NOT NULL;
• 다른 제약 조건과 추가하는 방법이 다르므로 주의

CHECK 제약 조건에 의한 오류

• name 컬럼에 not null 제약 조건이 생성되어 있기 때문에 null 값은 불허

CHECK 제약 조건 삭제

ALTER TABLE hr.emp DROP CONSTRAINT emp_name_nn;

ALTER TABLE hr.emp MODIFY name NULL;

테이블 생성 시 제약 조건 설정

DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;
DROP TABLE hr.dept CASCADE CONSTRAINT PURGE;

CREATE TABLE hr.dept(
    id number CONSTRAINT dept_pk PRIMARY KEY,  -- 열 레벨 정의
    dept_name varchar2(30))
TABLESPACE users;

CREATE TABLE hr.emp(
    id number CONSTRAINT emp_id_pk PRIMARY KEY, -- 열 레벨 정의
    name varchar2(30) CONSTRAINT emp_name_nn NOT NULL,  -- 열 레벨 정의
    sal number,
    dept_id number CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(id),    -- 열 레벨 정의
    CONSTRAINT emp_name_uk UNIQUE(name),    -- 테이블 레벨 정의
    CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000)) -- 테이블 레벨 정의
TABLESPACE users;

DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;

CREATE TABLE hr.emp(
    id number,
    name varchar2(30) CONSTRAINT emp_name_nn NOT NULL, -- NOT NULL 제약 조건은 열 레벨만 가능
    sal number,
    dept_id number,
  CONSTRAINT emp_id_pk PRIMARY KEY(id),   -- 테이블 레벨 정의
  CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES hr.dept(id),  -- 테이블 레벨 정의
    CONSTRAINT emp_name_uk UNIQUE(name),    -- 테이블 레벨 정의
    CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000)) -- 테이블 레벨 정의
TABLESPACE users;

DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;

CREATE TABLE hr.emp(
    id number,
    name varchar2(30) 
        CONSTRAINT emp_name_nn NOT NULL 
        CONSTRAINT emp_name_uk UNIQUE,  -- 열 레벨 정의
    sal number,
    dept_id number,
  CONSTRAINT emp_id_pk PRIMARY KEY(id),   -- 테이블 레벨 정의
  CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES hr.dept(id),  -- 테이블 레벨 정의
    CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000)) -- 테이블 레벨 정의
TABLESPACE users;