SQL_DML

지난 DB 수업 복귀

SELECT , 함수(단일행 / 그룹), JOIN(ON, USING, OUTER), 단일행 서브쿼리/ 복수행 서브쿼리, DDL(5가지 제약조건 NOT NULL, PRIMARY, REFERENCES, CHECK), 외래키, ...

# UPDATE 수정

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

UPDATE table_name
SET col_name = value , [, col = val, ...]
[ WHERE condition ] ;

SET 서브쿼리 사용 가능, DEFAULT 옵션 사용 가능(DEFAULT 옵션이 들어간 COLUMN에 대해서 적용 가능, DEFAULT 없으면 NULL 삽입)

(SUBQUERY 활용시 단일 행, 단일 열 서브쿼리로 수행)

DDL 수행 후 종료하면 AUTO Commit 수행됨(그렇게 되면 ROLLBACK 수행 불가)

 

SUBQUERY로 return 되는 값을 받아와서 수정도 가능

UPDATE	DEPARTMENT
SET	DEPT_NAME = '전략기획팀'
WHERE 	DEPT_ID = '90';

-- UPDATE SUB QUERY
UPDATE  EMPLOYEE
SET 	(JOB_ID, SALARY) = (SELECT  JOB_ID, SALARY
			    FROM	EMPLOYEE
			    WHERE	EMP_NAME = '성해교')
WHERE	EMP_NAME = '심하균'

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

-- WHERE SUB QUERY
UPDATE		EMPLOYEE
SET		BONUS_PCT = 0.3
WHERE		DEPT_ID = (SELECT 	DEPT_ID
			   FROM		DEPARTMENT
			   WHERE	DEPT_NAME = '해외영업2팀');

# UPDATE와 무결성 제약조건

UPDATE에 있어서도 무결성 중요

-- 무결성 침해(에러 발생)
UPDATE 		EMPLOYEE
SET		DEPT_ID = '65'
WHERE		DEPT_ID IS NULL;

# INSERT 삽입

특정 컬럼 데이터를 넣어주고 싶으면 COL 세팅 VALUE, 데이터 타입(필요에 따라 캐스팅가능)

INSERT INTO table_name [( col_name [, col_nam2, ...] ) ]
VALUES ( value1 [, value2, ...]);

데이터 타입 일치, 순서 일치, 개수 일치 해야한다!

INSERT를 통해 NULL 입력(암시적 방법 / 명시적 방법)

암시적 방법은 INSERT INTO 절에서 해당 컬럼 이름 생략

명시적 방법은 VALUE 절에서 NULL 키워드나 ''(empty string) 사용

-- INSERT 암시적 NULL 값 삽입
INSERT INTO EMPLOYEE (EMP_ID, EMP_NO, EMP_NAME, PHONE, HIRE_DATE, 
		      JOB_ID, SALARY, BONUS_PCT, MARRIAGE)	
VALUES			('880', '860412-23776100', '한채연', '01933826620', 
		   	 '06/01/01', 'J7', 3000000, 0, 'N');		

INSERT DEFAULT 옵션 활용가능

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

INSERT SUBQUERY

INSERT INTO table_name [col_name, ...]
Subquery ;

INSERT SUBQUERY

CREATE 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)) ;

데이터 타입 및 순서 고려해서 잘못 입력하지 않도록 하기

# DELETE 

DELETE [FROM] tab_name
[WHERE	condition];

TRUNCATE(메모리 구조나 여러 측면에서 DELETE 보다 선호됨)

TRUNCATE TABLE table_name ;

ALTER TABLE EMPLOYEE
DISABLE CONSTARINTS FK_DEPTID;
TRUNCATE TABLE tab_name;

참조관계에 있는 것들 삭제 (Deletion Rule - SET NULL)

RESTRICTED / CASCADE / SET NULL 삭제 시 제약 조건 설정

-- 제약조건 변경 후 DELETE 통한 삭제
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)

개발업무 자체에서 트랜잭션을 제어할 일이 없다

유저 트랜잭션이 있을 수도 있지만 WAS 같은 곳에 위임을 시킴

(배치 대량의 INSERT 작업 수행 시 일괄 배치 수행 시 ALL OR NOTHING 이기에 수행 실패위험, 그래서 INPUT DATA 유효성 검증이 가장 중요함)

 

데이터 일관성을 유지시키려는 목적으로 사용하는 논리적으로 연관된 작업들의 집합

COMMIT - 변경된 데이터를 저장하고 트랜잭션 종료하는 명령

ROLLBACK - 변경 작업을 취소하고 트랜잭션을 종료하는 명령

SAVEPOINT - 트랜잭션의 특정 시점을 기록하는 명령

(참고로 DDL 수행시 AUTO COMMIT 수행되므로 주의)

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;

# 동시성 제어

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

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

다중 세션에 대한 처리 주의(락 및 루프에 대한 주의)