SQL - DML, 트랜잭션 TCL

🎯 SQL DML 명령어와 트랜잭션을 알아본다.

 

 

 

 

DML(Data Manipulation Language)

데이터를 추가(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE) 할 때 사용하는 데이터 조작어

 

 

INSERT

테이블에 데이터를 저장하기 위한 데이터 조작어

 

 

단일행 INSERT : 한번에 하나의 행을 테이블에 저장

다중행 INSERT : 서브쿼리를 이용해 한꺼번에 여러행 동시저장

 

 

단일행 INSERT 문

 

INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES (90, '인사과', '서울');


-- 어떤 테이블에 넣을거다 (컬럼명)
-- 데이터값은 (위 컬럼이랑 일대일 대응이 되어야함)

 

컬럼 3개 넣어주기로 했으면 밸류도 똑같이 3개를 넣어줘야 한다.

입력되는 데이터 타입이 컬럼의 데이터타입과 같아야한다.

데이터의 크기는 테이블 만들때 만들어진 컬럼크기와 같거나 작아야한다.

문자 데이터타입이 VARCHAR(10) 이라면 10글자를 넘으면 안됨!

 

INSERT INTO DEPT
VALUES(80, '서울', '인사과');

 

INTO절에 컬럼을 생략해도 테이블의 컬럼 순서에 따라 VALUES절에 데이터를 입력하면 알아서 잘 넣어준다. 이 경우엔 VALUES 절에는 반드시 그 컬럼의 모든 컬럼값을 누락하지 않고 순서대로 지정해야한다. 빠뜨리는게 있으면 값의 수가 충분하지 않다는 에러가 출력된다.

DEPTNO는 중복불가 제약조건(기본키)이 걸려있어서 똑같은 데이터를 한 번 만들고 또 만들 수 없다

 

 

NULL값을 저장하는 방법

 

데이터를 저장하는 시점에서 해당 컬럼값을 모르거나 확정되지 않았을 경우 해당 컬럼값에 널값을 저장할 수 있다.

 

묵시적 방법

INSERT INTO DEPT(DEPTNO, DNAME) 
VALUES(91, '인사과');

 

INTO절에서 해당 컬럼명과 값을 생략하면 자동으로 널값을 저장해준다. 단, 해당 컬럼에 NOT NULL 제약조건이 지정된 경우엔 생략 불가.

테이블에 존재하는 LOC 컬럼을 쓰지 않아서 자동으로 NULL 값이 저장된다. 

 

명시적 방법

VALUES절 컬럼값에 직접 NULL 값 또는 '' 빈문자열을 입력한다.

 

 

다중행 INSERT 문

 

서브쿼리 절을 이용하여 하나의 INSERT 명령문에 여러 행을 동시에 입력가능하다.

 

-- EMP 연습용 복제 테이블 생성 

CREATE TABLE COPY_EMP 
AS 
SELECT EMPNO, ENAME FROM EMP 
WHERE 1=2; 



-- EMP 테이블에서 EMPNO, ENAME 컬럼의 데이터들을 복제 

INSERT INTO COPY_EMP(EMPNO, ENAME) 
SELECT EMPNO, ENAME FROM EMP;

 

기존의 테이블에 저장된 데이터를 서브쿼리를 이용해 복사한 후 INSERT문으로 새로운 행을 생성한다. (*CTAS)

INTO절에서 지정한 컬럼 개수와 데이터타입이 서브쿼리 수행 결과와 반드시 동일해야 한다.

 

 

존재하는 테이블 사용하여 새 테이블 생상하는 방법 CTAS

 

CREATE TABLE 테이블명 [(컬럼명, 컬럼명2)]
AS
SELECT * FROM 테이블  -- 서브쿼리 시작
WHERE 1=2;


서브쿼리에서 WHERE절 조건이 FALSE이면 검색된 데이터가 없기 때문에
서브쿼리에서 사용한 테이블의 구조만 복사되어 새로운 테이블 생성 >> 빈 테이블

조건이 TRUE 거나 WHERE절을 생략하면 서브쿼리가 실행된 데이터까지 포함하여 새로운 테이블 생성

테이블의 구조만 복사하기 위해 WHERE 절의 조건식이 FALSE가 되도록 1=2 같이 설정하는것.

이게 또 NOT NULL 제약조건 제외한 제약조건은 복사되지 않음
따라서 DEPTNO 컬럼은 기본키 설정 안되어있는 상태로 생성됨.

as는 별칭이 아니라 테이블의 구조는 아래와 같다 라는 의미로쓰임



CREATE TABLE MYDEPT
AS
SELECT * FROM DEPT
WHERE 1=2;
-- DEPT 테이블의 구조만 복사함


INSERT INTO MYDEPT
SELECT DEPTNO, DNAME, LOC FROM DEPT;
-- DEPT 테이블의 몇가지 컬럼의 데이터를 새 테이블에 넣어준다

 

문장의 앞글자를 따서 CTAS라고 부른다. INTO절 다음에 데이터를 가져오는 서브쿼리 실행한다. 서브쿼리의 실행 결과를 이용해 INSERT문이 실행되기 때문에 서브쿼리의 실행결과가 여러개면 자동으로 여러개의 데이터가 INSERT된다.

 

 

CREATE TABLE COPY_EMP
AS
SELECT EMPNO, ENAME FROM EMP
WHERE 1=2;


INSERT INTO COPY_EMP (EMPNO, ENAME)
SELECT EMPNO, ENAME FROM EMP;


SELECT * FROM COPY_EMP;

 

서브쿼리를 먼저 보면, EMP테이블에서 저 두컬럼만 가진 구조를 복사해와서 새 테이블을 만든다. 그 다음 EMP의 두 컬럼의 데이터를 새 테이블 안에 그대로 넣는다. 항상 서브쿼리 실행결과와 INTO절 컬럼 개수가 일치하는지 확인할 것.

 

 

다중 테이블에 대한 다중행 INSERT 문

 

한번의 INSERT로 여러 테이블에 복수 데이터를 저장할 수 있다.

 

무조건 INSERT ALL

조건 INSERT ALL

조건 INSERT FIRST

 

 

무조건 INSERT ALL

 

한번에 다중테이블에 다중 데이터를 저장할 수 있다.

특별한 조건 없이 여러테이블에 저장이 된다.

when절 조건식을 생략하는 경우(조건이 없음)를 무조건 INSERT ALL 이라고 부른다.

서브쿼리를 실행한 결과가 INTO절에서 지정한 테이블1과 테이블2에 자동으로 INSERT된다.

먼저 서브쿼리에서 필요한 자료를 다 뽑아오고

INSERT문에서 필요한 컬럼들을 테이블 각각에 따로 적어준다.

밸류 절에 사용된 컬럼명과 서브쿼리에서 사용된 컬럼명이 반드시 동일해야된다.

 

-- 테이블 2개 먼저 생성

CREATE TABLE SAL_HISTORY
AS
SELECT EMPNO, HIREDATE, SAL
FROM EMP
WHERE 1=2; 


CREATE TABLE MGR_HISTORY
AS
SELECT EMPNO, MGR, SAL
FROM EMP
WHERE 1=2;


-- 데이터 입력

INSERT ALL
INTO SAL_HISTORY VALUES (EMPNO, HIREDATE, SAL)
INTO MGR_HISTORY VALUES (EMPNO, MGR, SAL)
SELECT EMPNO, HIREDATE, SAL, MGR
FROM EMP;


-- 데이터가 잘 들어갔는지 확인

SELECT * FROM SAL_HISTORY;
SELECT * FROM MGR_HISTORY;

 

 

 

조건 INSERT ALL

 

WHEN절에서 지정한 조건을 만족하는 행만 INSERT하는 방법

 

-- 드랍으로 이미 생성되어 있는 테이블을 먼저 삭제해줘야 다시 예제연습을 할수있음

DROP TABLE SAL_HISTORY;
DROP TABLE MGR_HISTORY;


-- 테이블 생성

CREATE TABLE SAL_HISTORY
AS SELECT EMPNO, HIREDATE, SAL
FROM EMP
WHERE 1=2;

CREATE TABLE MGR_HISTORY
AS
SELECT EMPNO, MGR, SAL
FROM EMP
WHERE 1=2;


-- 조건에 해당하는 데이터만 저장

INSERT ALL
WHEN SAL < 2500 THEN
INTO SAL_HISTORY VALUES(EMPNO, HIREDATE, SAL)
WHEN SAL > 2500 THEN
INTO MGR_HISTORY VALUES (EMPNO, MGR, SAL)
SELECT EMPNO, HIREDATE, SAL, MGR
FROM EMP;


-- 확인

SELECT * FROM SAL_HISTORY;
SELECT * FROM MGR_HISTORY;

 

 

 

조건 INSERT FIRST

 

WHEN 절에 지정된 조건이 중복되는 경우에 첫번째 조건에 일치하는 테이블만 저장되고 이후에는 조건이 일치해도 테이블에 저장되지 않는 방법. INSERT ALL 을 INSERT FIRST로 바꾸면 된다.

 

DROP TABLE SAL_HISTORY;
DROP TABLE MGR_HISTORY;



-- 테이블 3개 생성

CREATE TABLE SAL_HISTORY
AS
SELECT EMPNO, HIREDATE, SAL
FROM EMP
WHERE 1=2;

CREATE TABLE MGR_HISTORY
AS
SELECT EMPNO, MGR, SAL
FROM EMP
WHERE 1=2;

CREATE TABLE TEST_HISTORY
AS
SELECT EMPNO, SAL
FROM EMP
WHERE 1=2;



-- 데이터 저장

INSERT FIRST
WHEN SAL=800 THEN
INTO SAL_HISTORY VALUES(EMPNO, HIREDATE, SAL)
WHEN SAL <2500 THEN
INTO MGR_HISTORY VALUES (EMPNO, MGR, SAL)
ELSE
INTO TEST_HISTORY VALUES (EMPNO, SAL)
SELECT EMPNO, HIREDATE, SAL, MGR
FROM EMP;


-- sal 800은 두가지 조건에 중복되어 두 테이블에 동시에 들어갈 수 있다.
-- INSERT FIRST를 사용하면 sal 800은 첫번째 조건에만 저장된다.
-- ELSE를 사용하여 그 밖의 데이터도 정리할 수 있다.




SELECT * FROM SAL_HISTORY;
-- 월급이 800 인 애만 여기 들어갔고
SELECT * FROM MGR_HISTORY;
-- 2500 보다 낮은 애들은 여기
SELECT * FROM TEST_HISTORY;
-- 그밖의 애들은 다 여기.

 

 

 

UPDATE

 

테이블에 저장된 행들을 수정하는 명령어다.

한번에 여러개의 행을 업데이트 가능.

 

UPDATE DEPT
SET DNAME = '경리과', LOC = '부산'
WHERE DEPTNO = 90;

SELECT * FROM DEPT;

 

DEPT 테이블에서 WHERE 조건에 해당되는 데이터를 찾아가서 SET 절에 적힌대로 수정한다.

WHERE가 빠져버리면 이 테이블의 데이터 전체가 수정되기 때문에 필요한 부분만 조건절 잡고 수정해야한다

 

 

서브쿼리를 이용한 복수 컬럼 변경


UPDATE EMP
SET JOB = (SELECT JOB
	    FROM EMP
	    WHERE EMPNO =7900),
    SAL = (SELECT SAL
	    FROM EMP
	    WHERE EMPNO =7844)
WHERE EMPNO = 9001;


SELECT * FROM EMP;

 

set절에서 서브쿼리를 사용하면 다른 테이블에 저장된 데이터를 사용하여 특정 컬럼값을 변경할 수 있다

 

 

MYDEPT테이블에서 부서번호가 60인 사원의 부서명을
DEPT 테이블의 부서번호가 10인 부서의 부서명으로 수정하고 
부서위치는 DEPT에서 부서번호가 20인 부서위치로 수정


UPDATE MYDEPT
SET DNAME = (SELECT DEPTNO
	      FROM DEPT
	      WHERE DEPTNO = 10),
	LOC = (SELECT LOC
	       FROM DEPT
    	       WHERE DEPTNO = 20)
WHERE DEPTNO = 60;

 

서브쿼리는 SET 절에 써주고 업데이트 문 자체의 WHERE 조건절을 달아주는 거 잊지말기.

 

 

 

DELETE

 

테이블에 저장된 행들을 삭제하는 명령문

 

DELETE [FROM] TABLE 
[WHERE 조건];

 

WHERE절에 조건을 지정해서 조건에 일치하는 행들만 삭제한다.

조건식이 없으면 테이블의 모든 데이터가 삭제되니 주의해야 한다.

 

DELETE FROM DEPT
WHERE DEPTNO =91;


SELECT * FROM DEPT;



-- 서브쿼리 및 다른 테이블 기반을 이용한 데이터 삭제

DELETE FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME = '경리과');

 

DELETE문의 WHERE절에 서브쿼리를 사용하면 서브쿼리 실행된 결과값으로 데이터를 삭제할 수 있다. 다른테이블에 저장된 데이터를 사용하여 특정 데이터 삭제 가능!

 

 

 

MERGE

 

컬럼의 구조가 같은 2개의 테이블을 비교하여 하나의 테이블로 합치기 위한 명령어.

 

MERGE INTO 테이블1 별칭  -- 병합한 결과 저장되는 테이블명 지정
USING (테이블명2 | 뷰 | 서브쿼리) 별칭 -- 병합할 테이블명 OR섭쿼리
ON (조인조건)  -- INTO절의 테이블과 USING절의 테이블간의 조인조건
WHEN MATCHED THEN    -- 조인조건이 일치하면 UPDATE 문장 실행
UPDATE SET
컬럼명 = 값,
컬럼명1=값1
[WHERE 조건식]
[DELETE WHERE 조건식]
WHEN NOT MATCHED THEN  --조인조건 일치하지 않으면 INSERT실행
INSERT (컬럼목록)
VALUES (값 목록)
[WHERE 조건식];

 

 

WHEN 절의 조건절에서 대상 테이블에 해당 행이 이미 존재하면 UPDATE 가 실행 되고 존재하지 않으면 INSERT가 실행된다.

전자상거래 회사에서 판매 현황을 월별로 관리하고 연말에 데이터를 병합할 때 MERGE를 사용할 수 있다.

 

 

-- 테이블 생성

-- 1월판매정보
CREATE TABLE PT_01
(판매번호 VARCHAR2(8),
제품번호 NUMBER,
수량 NUMBER,
금액 NUMBER);

-- 2월 판매정보
CREATE TABLE PT_02
(판매번호 VARCHAR2(8),
제품번호 NUMBER,
수량 NUMBER,
금액 NUMBER);

-- 통합 판매정보
CREATE TABLE P_TOTAL
(판매번호 VARCHAR2(8),
제품번호 NUMBER,
수량 NUMBER,
금액 NUMBER);



-- 데이터 입력

INSERT INTO PT_01 VALUES ('20150101', '1000', 10, 500);
INSERT INTO PT_01 VALUES ('20150102', '1000', 10, 400);
INSERT INTO PT_01 VALUES ('20150103', '1000', 10, 300);

INSERT INTO PT_02 VALUES ('20150201', '1000', 5, 500);
INSERT INTO PT_02 VALUES ('20150202', '1000', 5, 400);
INSERT INTO PT_02 VALUES ('20150203', '1000', 5, 300);
COMMIT;



-- 병합

MERGE INTO P_TOTAL TOTAL  -- 병합된 데이터 저장을 위한 통합테이블
USING PT_01 P01
ON (TOTAL.판매번호 = P01.판매번호)
WHEN MATCHED THEN
UPDATE SET TOTAL.제품번호 = P01.제품번호
WHEN NOT MATCHED THEN
INSERT VALUES (P01.판매번호, P01.제품번호, P01.수량, P01.금액);

MERGE INTO P_TOTAL TOTAL
USING PT_02 P02
ON (TOTAL.판매번호 = P02.판매번호)
WHEN MATCHED THEN
UPDATE SET TOTAL.제품번호 = P02.제품번호
WHEN NOT MATCHED THEN
INSERT VALUES (P02.판매번호, P02.제품번호, P02.수량, P02.금액);



-- 확인

SELECT * FROM P_TOTAL;

 

월별 테이블과 통합 테이블의 조인조건은 ON절에 쓴 판매번호인데, 통합테이블엔 아무것도 들어있지 않기 때문에 두 테이블간 일치하는데이터가 하나도 없는 상태이다. 따라서 WHEN NOT MATCHED의 INSERT문이 실행된다.

실행 결과 토탈 테이블엔 월별 테이블의 데이터가 저장된다. 

만약 월별 데이터가 잘못되어 수정해야하는 상황이라면 토탈 테이블과 다시 병합할 때 WHEN절 조건이 일치해서 UPDATE문이 실행된다.

 

 

CREATE TABLE EMP_M2
AS
SELECT EMPNO, JOB, SAL
FROM EMP
WHERE 1=2;


MERGE INTO EMP_M2 M2
USING EMP B
ON (M2.EMPNO = B.EMPNO)

WHEN MATCHED THEN
UPDATE SET
M2.JOB = B.JOB,
M2.SAL = B.SAL
WHERE B.JOB='CLERK'  -- 조건문을 추가할 수 있다

WHEN NOT MATCHED THEN
INSERT(M2.EMPNO, M2.JOB, M2.SAL)
VALUES (B.EMPNO, B.JOB, B.SAL)
WHERE B.JOB='CLERK';  -- 조건문을 추가할 수 있다


SELECT * FROM EMP_M2;

 

CREATE TABLE EMP_M3
AS
SELECT EMPNO, JOB, SAL
FROM EMP;


MERGE INTO EMP_M3 M3
USING EMP B
ON (M3.EMPNO = B.EMPNO)

WHEN MATCHED THEN
UPDATE SET
M3.JOB = B.JOB,
M3.SAL = B.SAL + 1000
DELETE WHERE (M3.JOB='CLERK')  -- MERGE UPDATE문에 DELETE문을 포함할 수 있다.


WHEN NOT MATCHED THEN
INSERT(M3.EMPNO, M3.JOB, M3.SAL)
VALUES (B.EMPNO, B.JOB, B.SAL+500);


SELECT * FROM EMP_M3;

 

 

 

트랜잭션

데이터베이스의 논리적인 작업 단위

트랜잭션 대상이 되는 SQL 문은 DML문이다.

— 셀렉트문은 직접적 트랜잭션 대상 아니지만 SELECT * FROM EMP FOR UPDATE; 등의 문장은 대상이 될 수 있다.

 

DML명령이 시작되면(추가,수정,삭제 이것들은) 트랜잭션 과정이 자동발생되며 반드시 트랜잭션 종료해줘야 한다.

트랜잭션은 ALL(정상처리 되어 끝난 상태) or NOTHING(원상복구 되어 아무일도 일어나지 않은 상태) 이다. 작업 단위를 전부 적용하거나 전부 취소한다.

트랜잭션이 실행된 다음에 마지막에 커밋(반영)을 할거냐 롤백(원상복구)을 할거냐 명령을 줘야한다. 요게 TCL문장이다.(트랜잭션 제어 명령어)

 

일단 DML문을 작성하고 셀렉트로 확인된 결과는 나만 볼 수 있다.

커밋을 날리기 전까진 나만보는 자료다.

모든 데이터 변경사항은 트랜잭션이 종료되기 전까지는 모두 임시적인 상태다.

변경된 행은 LOCK상태가 되며 트랜잭션 종료 후 잠금이 해제된다.

 

 

데이터 일관성

사용자들에게 가장 최근에 커밋된 데이터를 보여준다.

다른 사용자들이 변경중인 데이터를 볼 수 없게 함으로써 데이터를 검색하는 사용자와 변경하는 사용자들 사이에 일관적인 관점을 제공한다.

 

 

TCL 트랜잭션 제어 명령어

 

COMMIT

모든 데이터의 변경사항을 데이터베이스에 영구 반영한다.

 

SAVEPOINT

진행중인 트랜잭션을 특정 이름으로 지정하는 명령어. 책갈피 기능이다.

하나의 DML 다음에 'SAVEPOINT 이름' 이렇게 지정해두면 나중에 롤백을 하더라도 특정 작업만 취소할 수 있다.

 

ROLLBACK

모든 데이터의 변경사항을 취소하고 변경전의 데이터를 복구한다.

 

 

LOCK 경합

 

DML 사용하는 경우 반드시 트랜잭션을 종료시켜야 한다.

A사용자와 B사용자가 동시에 테이블의 특정 레코드를 수정한다고 하자. A가 먼저 실행했을 경우 얘가 트랜잭션을 명시적으로 종료하지 않으면 B의 명령은 실행되지 않고 무한 대기하게 된다. 이런 상황을 LOCK경합이라고 하며 무한대기는 오라클의 성능을 감소시키니까 주의할것.

 

 

 

😜 NEXT : SQL DDL 공부하기