DML

UPDATE

테이블에 포함된 기존 데이터를 수정

전체 데이터 건수(행 수)는 달라지지 않음

UPDATE table_name

SET column_name = value [, column = value ...]

[WHERE condition];

DDL과 DML을 함께 쓰면 자동 Commit이 되는 것을 주의

SET절의 서브 쿼리

// SET절의 서브쿼리
-- 단일행 서브쿼리
UPDATE	EMPLOYEE
SET		(JOB_ID, SALARY) = (SELECT	JOB_ID, SALARY
							FROM	EMPLOYEE
							WHERE	EMP_NAME = '성해교')
WHERE	EMP_NAME = '심하균';

-- same as...
UPDATE	EMPLOYEE
SET		  JOB_ID = (SELECT	JOB_ID
				    FROM	EMPLOYEE
				    WHERE	EMP_NAME = '성해교')

		, SALARY = (SELECT	SALARY
					FROM	EMPLOYEE
					WHERE	EMP_NAME = '성해교')
WHERE	EMP_NAME = '심하균';

DEFAULT

Table 생성 시 DEFAULT 값이 부여된 컬럼에 대해 DEFAULT값 할당

// DEFAULT
UPDATE	EMPLOYEE
SET		MARRIAGE = DEFAULT
WHERE	EMP_ID = '210';

DEFAULT 값이 설정되지 않은 경우에는 NULL이 적용된다

WHERE절의 서브 쿼리

// WHERE절의 서브쿼리
UPDATE	EMPLOYEE
SET		BONUS_PCT = 0.3
WHERE	DEPT_ID = (SELECT	DEPT_ID
				   FROM		DEPARTMENT
				   WHERE	DEPT_NAME = '해외영업2팀');

UPDATE와 무결성 제약조건

데이터 무결성이 손상되는 변경 작업은 허용되지 않음

부모에 의존하는 외래 키 컬럼을 UPDATE 하려면 부모에 존재하는 데이터여야 함

// UPDATE와 무결성
UPDATE	EMPLOYEE
SET		DEPT_ID = '65'
WHERE	DEPT_ID IS NULL;

-- ORA-02291: integrity constraint (HR.FK_DEPTID) violated - parent key not found

---

INSERT

INSERT INTO table_name [(column_name [, column_name...])]

VALUES (value1 [, value2 ...]);

VALUE절의 입력값과 INSERT INTO절의 컬럼은 데이터 타입, 순서, 개수가 일치해야 함

NULL 입력

• 암시적
  • INSERT INTO절에서 해당 컬럼 이름 생략
• 명시적
  • VALUES 절에서 NULL 키워드나 ''(Empty String)사용

// INSERT NULLINSERT INTO	EMPLOYEE(EMP_ID, EMP_NO, EMP_NAME, PHONE, HIRE_DATE, JOB_ID, SALARY, BONUS_PCT, MARRIAGE)
VALUES		('880', '860412-2377610', '한채연', '0193382662', '06/01/01', 'J7', 3000000, 0, 'N');

DEFAULT

DEFAULT 설정된 컬럼 이름을 생략하면 DEFAULT 값이 입력됨

DEFAULT 키워드를 기술하여 명시적으로 표현

DEFAULT 값이 설정되지 않은 경우 DEFAULT 키워드를 사용해도 NULL이 입력됨

• -> ANSI 권고사항: NULL을 입력하지 말고 DEFAULT를 써라

// DEFAULTINSERT INTO	EMPLOYEE(EMP_ID, EMP_NO, EMP_NAME, SALARY, MARRIAGE)
VALUES		('860', '810429-2165344', '선예진', DEFAULT, DEFAULT);

INSERT에서의 Subquery

INSERT INTO table_name [column name ...]subquery;

AS 키워드 사용 X

// Subquery in INSERT INTOCREATE TABLE	EMP(
  EMP_ID	CHAR(3)
, EMP_NAME	VARCHAR2(20)
, DEPT_NAME	VARCHAR2(20));

INSERT INTO EMP
(SELECT				EMP_ID, EMP_NAME, DEPT_NAME
 FROM				EMPLOYEE
 LEFT OUTER JOIN	DEPARTMENT USING(DEPT_ID));

INSERT와 무결성 제약조건

데이터 무결성이 손상되는 변경 작업은 허용되지 않음

부모에 의존하는 외래 키 컬럼을 INSERT 하려면 부모에 존재하는 데이터여야 함

---

DELETE

DELETE [FROM] table_name

[WHERE condition];

DELETE와 무결성 제약조건

자식에서의 삭제는 무결성 위배 X

참조 데이터가 있는 경우 삭제 불가능할 수도 있음

TRUNCATE

DELETE에 WHERE조건절을 주지 않으면 전체 삭제

• -> TRUNCATE를 사용하는 것이 더 좋다

수행속도가 빠르고 조건을 줄 수 없어 테이블 전체의 모든 레코드를 삭제할 때 쓰는 구문

DELETE는 DML이기 때문에 Commit과 Rollback이 가능하나 TRUNCATE는 불가능

제약조건이 있는 경우 TRUNCATE 사용 불가능(부모의 경우)

Deletion Rule - SET NULL

EMPLOYEE 테이블의 MGR_ID 컬럼은 EMP_ID 컬럼을 참조

참조하는 EMP_ID 컬럼이 삭제되면 참조하고 있는 값들을 NULL로 변경하도록 함

// DELETION RULE - SET NULL
 ALTER TABLE EMPLOYEE DROP CONSTRAINTS FK_MGRID;

 ALTER TABLE EMPLOYEE ADD CONSTRAINTS FK_MGRID FOREIGN KEY(MGR_ID)
 REFERENCES EMPLOYEE ON DELETE SET NULL;

 DELETE FROM EMPLOYEE WHERE EMP_ID = '141';

Deletion Rule - CASCADE

참조하는 EMP_ID 컬럼이 삭제되면 참조하고 있는 값들을 NULL로 변경하도록 함

// DELETION RULE - SET NULL
 ALTER TABLE EMPLOYEE DROP CONSTRAINTS FK_MGRID;

 ALTER TABLE EMPLOYEE ADD CONSTRAINTS FK_MGRID FOREIGN KEY(MGR_ID)
 REFERENCES EMPLOYEE ON DELETE SET NULL;

 DELETE FROM EMPLOYEE WHERE EMP_ID = '141';

---

트랜잭션(Transaction)

ALL or NOTHING

• 일부 COMMIT, ROLLBACK은 없다

현업에서 만날 일은 거의 없다

DDL이 들어가면 Auto Commit이 된다

• COMMIT
  • 테이블에 직접적으로 데이터를 반영
  • 변경된 데이터를 저장하고 트랜잭션을 종료하는 명령
• ROLLBACK
  • 원복작업
  • 변경 작업을 취소하고 트랜잭션을 종료하는 명령
  • SAVEPOINT savepoint_name
    • 트랜잭션의 특정 시점을 기록하는 명령
  • ROLLBACK TO savepoint_name
    • 지정한 특정 시점으로 데이터 상태를 되돌릴 수 있음

// SAVEPOINT
ALTER TABLE EMPLOYEE
DISABLE CONSTRAINTS FK_MGRID;

SAVEPOINT S0;

INSERT INTO	DEPARTMENT
VALUES		('40', '기획전략팀', 'A1');

SAVEPOINT S1;

UPDATE	EMPLOYEE
SET		DEPT_ID = '40'
WHERE	DEPT_ID IS NULL;

SAVEPOINT S2;

DELETE FROM EMPLOYEE;

// ROLLBACK
ROLLBACK TO S2;

SELECT	COUNT(*)
FROM 	EMPLOYEE;

SELECT	COUNT(*)
FROM	EMPLOYEE
WHERE	DEPT_ID = '40';

ROLLBACK TO S1;

SELECT	COUNT(*)
FROM 	DEPARTMENT
WHERE	DEPT_ID = '40';

SELECT	COUNT(*)
FROM	EMPLOYEE
WHERE	DEPT_ID = '40';

ROLLBACK TO S0;

SELECT	COUNT(*)
FROM 	DEPARTMENT
WHERE	DEPT_ID = '40';

---

잠금(Lock)

동시성 제어

• 다수 사용자들이 동시에 동일한 데이터에 접근하여 변경 시도 가능

무결성을 보장하기 위해 동시성을 제어하는 것이 필요함

---

출처: SHINSEGAE I&C 인턴십