DML(Data Manipulation Language, 데이터 조작어)
INSERT(삽입): 테이블에 새 행 추가
UPDATE(갱신): 테이블의 기존 행 수정
DELETE(삭제): 테이블에서 기존 행 제거
트랜잭션 --> 논리적 작업 단위를 형성하는 DML문 모음
✔️ 1. INSERT(삽입)
INSERT INTO table | (column |, column...|) |
VALUES (value [, value...]) ;
- 숫자는 상관 없지만,
문자와 날짜 값은 작은 따옴표로 묶음 - 한 번에 한 개의 행만 삽입
INSERT INTO copy_dept(department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);Null 값을 가진 행 삽입
1) 암시적 방법
: 열 리스트에서 열 생략
INSERT INTO copy_dept (department_id, department_name)
VALUES (80, 'Purchasing');2) 명시적 방법
: VALUES절에서 NULL 키워드 지정
INSERT INTO copy_dept
VALUES (90, 'Finance', NULL, NULL);- 컬럼명 생략 가능
📌 COMMIT(커밋)
COMMIT;
- 변경된 사항을 저장할 때 사용
- DB에 영구적으로 저장됨
INSERT INTO copy_emp
VALUES (113,'Louis', 'Popp', 'LPOPP', '515.124.4567',
CURRENT_DATE, 'AC_ACCOUNT', 6900, NULL, 205, 90);
-- TO_DATE 사용
INSERT INTO copy_emp
VALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561',
TO_DATE('2003/02/03', 'YYYY/MM/DD'),
'SA_REP', 11000, 0.2, 100, 80);스크립트 작성
& 치환 변수
INSERT INTO copy_dept
(department_id, department_name, location_id)
VALUES (&department_id, '&department_name',&location);
다른 테이블에서 행 복사
- VALUES절 사용 X
INSERT INTO copy_emp(employee_id, last_name, email, hire_date, job_id, salary, commission_pct)
SELECT employee_id, last_name, email, hire_date, job_id, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';✔️ 2. UPDATE(갱신)
UPDATE table
SET column = value [, column = value, ...][WHERE condition] ;
- 필요한 경우, 한 번에 두 개 이상의 행을 갱신
WHERE절을 지정하면 특정 행에서 값이 수정됨
UPDATE copy_emp
SET department_id = 50
WHERE employee_id = 113;WHERE절을 생략하면 테이블의 모든 행에서 값이 수정됨
UPDATE copy_emp
SET department_id = 110;- SET column_name
=NULL을 지정하여 열 값을 NULL로 갱신
📌 ROLLBACK(롤백)
ROLLBACK;
이전 상태로 되돌리고자 할 때 사용
예시 1
UPDATE copy_emp
SET (job_id,salary) = (SELECT job_id,salary
FROM copy_emp
WHERE employee_id = 104)
WHERE employee_id = 103;예시 2
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM copy_emp
WHERE employee_id = 103)
WHERE job_id = (SELECT job_id
FROM copy_emp
WHERE employee_id = 125);- 125번의 job_id를 검색해서
그 job_id를 쓰는 사원들을 찾아놓고
103번의 department_id와 동일한 department_id로 UPDATE
✔️ 3. DELETE(삭제)
WHERE절 없으면, 테이블의 모든 행 제거 가능
DELETE [FROM] table
[WHERE condition] ;
WHERE절을 지정하면 특정 행이 삭제됨
DELETE FROM copy_dept
WHERE department_name = 'Finance';WHERE절을 생략하면 테이블의 모든 행이 삭제됨
DELETE FROM copy_emp;서브쿼리로도 사용가능
DELETE FROM copy_emp
WHERE department_id = (SELECT department_id
FROM copy_dept
WHERE department_name = 'Shipping');☑️ TRUNCATE (DDL)
TRUNCATE는 DDL(Data Definition Language, 데이터 정의어)
- 테이블은 빈 상태로, 테이블 구조는 그대로 남겨둔 채
테이블에서 모든 행 제거
⚠️ 자동 COMMIT이 동반되어, ROLLBACK 불가능!
TRUNCATE TABLE table_name ;
예제
TRUNCATE TABLE copy_emp;데이터베이스 트랜잭션
- 데이터를 일관성 있게 변경하는 여러 DML문
- 하나의 DDL문
- 하나의 DCL(Data Control Language, 데이터 제어어)문
데이터베이스 트랜잭션: 시작과 종료
- 첫번째 DML SQL문이 실행될 때 시작 = 자동으로 실행
- 다음 상황 중 하나가 발생하면 종료됨
- COMMIT 또는 ROLLBACK 실행
- DDL 또는 DCL문 실행(자동 커밋)
- 유저가 SQL Developer 또는 SQL*PLUS를 종료
- 시스템 중단
COMMIT 및 ROLLBACK 문의 이점
- 데이터
일관성보장 - 변경 사항을 영구 적용하기 전에 데이터 변경 사항
검토 - 논리적으로 관련된 작업
그룹화
SAVEPOINT: 변경 사항을 표시자로 롤백
- SAVEPOINT 문을 사용하여 현재 트랜잭션에서 표시자 생성
ROLLBACK TO SAVEPOINT문을 사용하여, 해당 표시자로 롤백- ROLLBACK을 정의할 때 부분적인 ROLLBACK 가능
UPDATE...
SAVEPOINT update done;
INSERT...
ROLLBACK TO update done;
예제
DELETE copy_dept
WHERE department_id = 100 ;
SAVEPOINT A ;
DELETE copy_dept
WHERE department_id = 90 ;
SAVEPOINT B ;
DELETE copy_dept
WHERE department_id = 80 ;
SELECT * FROM copy_dept ;
-- department_id = 80이 복구됨
ROLLBACK TO SAVEPOINT B ;
SELECT * FROM copy_dept ;
-- department_id = 90이 복구됨
ROLLBACK TO A ;
SELECT * FROM copy_dept ;
-- 수정사항 저장
COMMIT ; COMMIT 또는 ROLLBACK 이전의 데이터 상태
- 이전의 데이터 상태 복구 가능
- 현재 세션은 SELECT문을 사용하여 DML 작업 결과 확인 가능
- 다른 세션은 현재 세션이 실행한 DML문의 결과를 볼 수 X
- 영향을 받는 행이 잠기므로(LOCK), 다른 세션이 영향을 받는 행의 데이터를 변경할 수 X
COMMIT 후의 데이터 상태
- 데이터 변경사항이 데이터베이스에 저장됨
- 이전의 데이터 상태를 겹쳐 씀
- UNDO(언두) 데이터가 필요없어져서 비워짐
- 모든 세션이 결과 확인 가능
- 영향을 받는 행의 잠금이 해제되어, 이러한 행을 다른 세션에서 조작 가능
- 모든 저장점이 지워짐
ROLLBACK 후의 데이터 상태
보류 중인 모든 변경 사항을 폐기
- 데이터 변경사항이 실행 취소됨
- 이전의 데이터 상태가 복원됨
- 영향받는 행의 잠금이 해제됨
⚠️ commit을 진행한 후 rollback을 실행시키면
commit 이후로 rollback 됨!
SELECT문의 FOR UPDATE절
(권장되는 건 아닌데 참고용)
SELECT(검색)는 기본적으로 Lock(락)을 걸지 않음
FOR UPDATE절을 사용하면, 검색된 행을 기반으로 Lock(락)이 요청됨
ROLLBACK 또는 COMMIT을 실행하는 경우에만 Lock이 해제됨
A> SELECT *
FROM copy_dept
WHERE manager_id IS NULL
FOR UPDATE ;
B> ROLLBACK ;
B> UPDATE copy_dept
SET location_id = 4000
WHERE department_id = 90 ;
A> ROLLBACK ; 예제
SELECT e.employee_id, e.salary, e.commission_pct
FROM employees e JOIN departments d
USING (department_id)
WHERE job_id = 'ST_CLERK'
AND location_id = 1500
FOR UPDATE OF e.salary
ORDER BY e.employee_id;- employees 및 departments 테이블의 행이 잠김
- FOR UPDATE OF column_name을 사용하여 변경할 열을 지정하면,
특정 테이블의 해당 행만 잠김
