Oracle - DML

 

 

 

 

 

DML(Data Manipulation Language)

데이터베이스에 데이터를 추가, 갱신 또는 삭제하고자 한다면 DML 문장 실행

 

 

새로운 행을 데이터베이스에 추가하는 INSERT문

기존 행을 수정하기 위한 UPDATE문

기존 행을 삭제하기 위한 DELETE문

 

---

 

DML 구문의 실행을 완전히 보장하기 위해서는 트랜잭션이 필요하다

 

그렇다면 트랜잭션은 무엇일까?

트랜잭션은 작업의 논리적인 단위 형태인 DML 문장의 모음을 말한다

 

보통 트랜잭션에 대해 설명할 때 은행 거래를 예시로 들어 설명한다

ATM 기계를 사용한다고 하면 현재는 거래가 완전히 성공적으로 끝난 후에야

완전한 거래로 승인하고 거래 도중 뭔가 오류가 발생했을 때는

거래 자체를 없었던 거래로 되돌림으로써 거래 금액 손실을 방지한다

 

이를 그대로 데이터베이스에 적용하면, 특정 테이블에서 데이터를 읽어 조작 후

다른 테이블에 데이터를 입력하거나 갱신, 삭제하는데 처리 도중 오류가 발생하면

모든 작업을 원상태로 되돌리고, 처리 과정이 모두 성공했을 때만

최종적으로 데이터베이스에 반영하는 것이 트랜잭션 처리이다

 

---

 

 

DML에 대해 알아보자

 

 

 

INSERT문

 

[ INSERT문 형식 ]

 

 

 

만약 테이블 구조가 어떻게 만들어졌는지 확인하고 싶다면?

 

DESC 테이블명;

 

 

 

어떻게 사용할까?

             

          원래는 INSERT INTO departments (department_id, department_name, manager_id, location_id)

                     VALUES (280, 'Data Analytics', null, 1700);

   

     이처럼 열을 지정할 때는 열에 해당하는 값이 삽입될 것이고 어떤 특정 열을 작성하지 않으면 값이 없다고 판단       될 것이나, 위의 경우는 열을 별도로 지정하지 않았기 때문에 모든 열에 해당하는 값을 무조건 작성해줘야 한다

 

 

 

 

 

특정 열을 작성하지 않으면 값이 없다고 판단한다

 

 

위의 예시의 경우 manager_id열의 값이 존재하지 않기 때문에 insert할 때 굳이 작성하지 않았다

 

 

 

 

 

 

그렇다면 다른 테이블에 있는 행을 가져와서

새로운 테이블에 삽입하고 싶다면 어떻게 해야 할까?

 

서브쿼리를 사용하면 가능하다

                      select_sub_query 

                        : 행을 리턴할 서브쿼리로 insert절의 열 목록에서 열의 갯수와 데이터 타입은

                          서브쿼리에 있는 값의 갯수와 데이터타입과 일치해야 함

 

 

 

 

다음의 예시를 통해 확인해봅시다

 

manager이라는 테이블을 먼저 생성

 

새로 생성한 manager이라는 테이블에 행을 삽입할 것인데

이떄 직접 작성한 행이 아닌 다른 테이블에 있는 행을 삽입할 것이다

 

INSERT INTO managers(employee_id, first_name, job_id, salary, hire_date)

일반 insert절과 똑같이 삽입할 테이블과 그 테이블에 넣을 열을 지정한다

 

그런 다음 각 열에 들어갈 값을 employees 테이블에서 선택한다

managers에 들어갈 열과 employees에서 뽑을 열의 갯수는 같아야 한다

 

 

 

 

 

UPDATE문

 

[ UPDATE구문 형식 ]

 

 

 

어떻게 사용할까?

update를 사용하기 위해 사본 테이블을 먼저 생성했다

 

사본 테이블에서 수정할 행을 뽑아보면 위와 같이 나온다

 

 

위의 행에서 salary열을 수정하면 위와 같이 작성하면 된다

주의할 점! update 사용할 때 update와 set키워드 사이에 테이블명을 작성해야 한다

 

---

 

update문에 서브쿼리를 사용할 수 있다

 

 

 

어떻게 사용하냐면 말이지..

 

수정할 column을 선택하고 그 column에 어떤 값을 넣을 것인지를 서브쿼리로 정한다

 

job_id, salary, manager_id를 수정할 것인데

emps 테이블에서 employee_id가 108인

사원들의 job_id, salary, manager_id로 변경할 것임

 

 

 

 

 

 

DELETE문

[ DELETE문 형식 ]

 

 

 

삭제 실행할 때에는 반드시 확인하는 습관을 가져야 한다!

ROLLBACK 명령으로 실행 취소가 가능하지만

그럴 경우 트랜잭션 완료 이후 모든 변경 내용이 취소될 수 있다

 

 

employee_id가 104인 데이터를 모두 삭제

 

 

department_name이 'Shipping'인 depts 테이블의 department_id를 산출한

다음 그 데이터랑 department_id가 같은 데이터를 선택해서 모두 삭제

 

 

 

 

그럼 무조건 다 삭제할 수 있는 것인가?

 

무조건적으로 다 삭제가 가능한 건 아니야!

 

위의 예시를 보면 알 수 있는데, employee_id열은 manager_id열에서 참조하고 있으며

104번 사원의 매니저는 103번 사원으로 지정되어 있기 때문에 103번 사원은 삭제되지 않는다

 

 

 

 

MERGE문

[ MERGE문 형식 ]

 

 

데이터베이스에 INSERT 또는 UPDATE할 때에 데이터가 존재하는지 여부를 체크하고

존재하면 UPDATE를 하고, 존재하지 않으면 INSERT를 수행할 수 있게 한다

 

 

 

어떻게 사용되는지 확인해볼까?

먼저 사본 테이블 생성하는데 

데이터는 포함하지 않는구조만 있는 테이블을 생성

 

새로 생성한 테이블에 데이터를 추가

 

 

 

1. 동일한 테이블 구조를 가지고 있는 임시 테이블로부터 데이터를 옮기는 MERGE구문

 

 

 

위의 예시를 좀 더 자세하게 뜯어봅시다

 

 

어떤 테이블에 병합할 것인지 확인하고

그 테이블에 어떤 정보를 병합할 것인지를 조건을 제시한다

 

emps_it 라는 테이블에 병합을 할 것이고

병할할 데이터는 job_id가 'IT_PROG'인 데이터를

employees 테이블에서 추출(조인 사용)

 

 

 

만약 위에서 추출한 데이터가 병합할 테이블에 존재한다면 수정을 하고 존재하지 않으면 새로 삽입한다

 

그럼 다음의 테이블이 추출된다

 

 

 

 

 

2. 다른 테이블에서 데이터를 비교하여 가져오는 것이 아니라,

직접 값을 넣고자 할 때는 dual(가짜 테이블)이용

 

 

 

 

 

 

 

CTAS (사본테이블)

[ CTAS문 형식 ]

 

 

CTAS (Create Table As Select)구문은

현재 있는 테이블과 같은 구조를 갖는

테이블을 생성할 수 있도록 해야 한다

 

CATS 구문을 이용한 테이블 복제 시에

NOT NULL 제약조건을 제외한

다른 제약조건은 복사되지 않는다

 

 

 

 

위에서 계속해서 사본 테이블을 이용했는데

그렇다면 사본 테이블은 대체 어떻게 만들어지는 것일까?

 

두 가지의 형식으로 생성가능하다

 

 

1. 테이블의 구조와 데이터 모두를 저장한 테이블 생성

employees에 있는 모든 데이터를 선택한 다음 그걸 emp2라는 사본 테이블에 복사

 

 

 

2. 테이블의 데이터는 없고 구조만 있는 테이블 생성

employees에 있는 데이터는 선택하지 않고 오로지 구조만 추출해서 emp3라는 사본 테이블에 복사