merge, insert, alter, delete

Merge문

오라클SQL과 PL/SQL을 다루는 기술 참고

merge문은 조건을 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 insert, 있으면 update를 수행하는 문장이다.

Merge into [스키마.]테이블명 Using (update나 insert될 데이터 원천) On (update될 조건) When matched then -- 일치하면 Set 컬럼 = 값, 컬럼2 = 값2, …. Where update 조건 Delete where update_delete 조건 When not matched then   --일치하지않으면 Insert (컬럼, 컬럼2, …) values (값1, 값2, …) Where insert 조건;

 

[최종]

이제 사원 테이블을 검색해 1) 관리자 사번(manager_id)이 146번인 사원을 찾아 2)ex3_3 테이블에 있는 사원의 사번과 일치하면 보너스 금액(bonus_amt)에 자신의 급여(salary)의 1%를 보너스로 갱신하고, 3) ex3_3 테이블에 있는 사원 사번과 일치하지 않으면 1)의 결과의 사원을 신규로 입력(이때 보너스 금액은 급여의 0.1%로 한다)하는데, 이때 급여가 8000 미만인 사원만 처리해 보자.

 

merge into ex3_3 d

using (select employee_id, salary, manager_id from employees where manager_id = 146) b

on(d.employee_id = b.employee_id)

when matched then update set d.bonus_amt = d.bonus_amt + b.salary * 0.01

when not matched then insert (d.employee_id, d.bonus_amt) values (b.employee_id, b.salary *.001) where (b.salary < 8000);

delete문도 사용 가능

merge into ex3_3 d

using (select employee_id, salary, manager_id from employees where manager_id = 146) b

on(d.employee_id = b.employee_id)

when matched then update set d.bonus_amt = d.bonus_amt + b.salary * 0.01 delete where (B.employee_id = 161)

when not matched then insert (d.employee_id, d.bonus_amt) values (b.employee_id, b.salary *.001) where (b.salary < 8000);

 

 

로직이 복잡하니 일단 최종 답을 직접 계산해 보고 merge문을 작성한 후 실행해 두 결과를 비교해 보자.

 

 

select * from ex3_3 order by employee_id;

 

-  Insert 문 (데이터 추가)

Insert into [스키마.]테이블명 (컬럼1, 컬럼2, …) values (값1, 값2, …);

 테스트를 위한 테이블 생성

create table enc_up( id number(20), jumin varchar(50));

ex)
insert into enc_up values(1,'111111-1111111');

insert into enc_up values(2,'211111-1111111');

 

위와 같이 모든 컬럼을 추가할 때는 컬럼명을 생략 해도 된다.

데이터 추가 확인

-  Alter문 (테이블 변경)

alter문 테스트를 위한 테이블을 먼저 생성

create table ex2_10( col1 varchar2(10) not null, col2 varchar(10) null, create_date date default sysdate);

1)    컬럼명 변경:col1 컬럼 이름을 col11로 변경

Alter table [스키마.]테이블명 rename column 변경전컬럼명 to 변경후컬럼명;

ex) alter table ex2_10 rename column col1 to col11;

Desc 테이블명 명령어로 테이블에 있는 컬럼 내역 확인

ex) desc ex2_10;

2)    컬럼 타입 변경: col2 컬럼을 varchar(10)에서 varchar2(30)으로 변경

Alter table [스키마.]테이블명 modify 컬럼명 데이터타입;

ex) alter table ex2_10 modify col2 varchar2(30);

desc ex2_10;

참고로 number에서 varchar2로 변환할 때 안에 이미 숫자 데이터가 있으면 에러가 발생하므로 숫자 데이터를 삭제하고 변경해야함.

3)    컬럼 추가:col3 number 타입으로 신규 생성

Alter table [스키마명.]테이블명 add 컬럼명 데이터타입;

ex) alter table magicdb_test add JUMIN_ENC varchar(30);

ex) alter table ex2_10 add col3 number;

4)    컬럼 삭제:col3 컬럼을 삭제

Alter table [스키마.]테이블명 drop column 컬럼명;

ex) alter table ex2_10 drop column col3;

5)    제약조건 추가: col11에 기본키 추가

Alter table [스키마.]테이블명 add constraints 제약조건명 primary key (컬러명, ..);

alter table ex2_10 add constraints pk_ex2_10 primary key (col11);

select constraint_name, constraint_type, table_name, search_condition from user_constraints where table_name = 'EX2_10';

6)    제약조건 삭제: col11에서 기본키 삭제

alter table [스키마.]테이블명 drop constraints 제약조건명;

alter table ex2_10 drop constraints pk_ex2_10;

테이블 복사

프로젝트를 하다 보면 기존에 있는 테이블을 복사해서 사용할 경우가 발생한다. 예를 들어, 사용하고 있는 기존 테이블의 테이블의 구조를 자주 변경한다면 미리 복사해 만들어 테스트해보거나, 테이블 데이터 자체를 백업용으로 만들어 놓을 수도 있다.

Create table [스키마.] 테이블명 as Select 컬럼1, 컬럼2, …. From 복사할 테이블명;

이렇게 처리하는 것을 CREATE TABLE …AS의 앞 글자를 따서 ‘CTAS(씨타스라고 읽음)’라고 부르기도 하는데 정식 명칭은 아니다. 이 구문을 사용하면 테이블 구조와 데이터가 모두 신규 테이블로 복사 된다.

ex) create table ex2_9_1 as select * from ex2_9;

-  Delete문 (데이터 삭제)

Delete [from] [스키마.]테이블명 where delete 조건;

ex)

delete from employees where department_id = 111111;   -> department_id가 111111인 데이터만 사라진다.

delete departments;    -> departments의 모든 데이터가 삭제 된다.

 

데이터를 다룰 때 select ,insert, update, merge, delete문이 전부라고 해도 과언이 아니다.