학습 과정 공유 및 후기/SQLD
[ SQLD ] Oracle SQL (12) - MERGE, TRANSACTION
커피마시는어린왕자
2020. 5. 29. 12:41
1. MERGE 문의 이해
- MERGE문은 조건에 따라서 데이터의 삽입, 갱신, 삭제 작업을 한 번에 할 수 있다.
- 해당 행이 존재하는 경우 UPDATE(DELETE포함 할 수 있음)를 수행하고, 새로운 행일 경우 INSERT를 수행한다.
- 대상 테이블에 대한 UPDATE/INSERT 조건은 ON절에 의해 결정된다.
- MERGE문에서 CLOB 사용시 업데이트 할 내용이 2000bytes가 넘을때 ORA-00600 오류가 발생하며, patch set 11.2.0.2 버전으로 해결할 수 있다.
[ 형식 ]
MERGE INTO TABLE NAME
USING
ON (조인 조건) ---> MERGE
WHEN MATCHED THEN ---> 해당 행이 존재하면 UPDATE
UPDATE SET
WHEN NOT MATCHED THEN ---> 존재하지 않으면 INSERT
INSERT ... VALUES ...
SQL> CREATE TABLE EMP_MASTER
2 AS
3 SELECT * FROM EMP WHERE DEPTNO = 30;
Table created.
SQL> INSERT INTO EMP_MASTER(EMPNO) VALUES(111);
1 row created.
SQL> INSERT INTO EMP_MASTER(EMPNO) VALUES(222);
1 row created.
SQL> INSERT INTO EMP_MASTER(EMPNO) VALUES(333);
1 row created.
Q1. 사원 EMP_MAIN 테이블을 EMP_MASTER 테이블과 비교해서, 동일한 사원번호의 데이터가 있으면 EMP_MASTER 테이블의 급여, COMM를 EMP 테이블의 값으로 수정하고 해당 사우너번호를 가진 사원이 없으면 EMP_MASTER에 EMP 테이블의 데이터를 이용해서 입력하는 작업을 수행하자.
EMP_MASTER 테이블의 봉급과 커미션을 0으로 수정 후 실행
더보기
# STEP 1. 초기화
SQL> UPDATE EMP_MASTER
2 SET SAL = 0, COMM = 0;
# STEP 2. 출력해본다
SQL> SELECT EMPNO, SAL, COMM FROM EMP_MASTER;
EMPNO SAL COMM
---------- ---------- ----------
7499 0 0
7521 0 0
7654 0 0
7698 0 0
7844 0 0
7900 0 0
111 0 0
222 0 0
333 0 0
9 rows selected.
# STEP 3. MERGE 한다
SQL> MERGE INTO EMP_MASTER T
2 USING EMP E
3 ON (T.EMPNO = E.EMPNO)
4 WHEN MATCHED THEN
5 UPDATE SET T.SAL = E.SAL, T.COMM = E.COMM
6 WHEN NOT MATCHED THEN
7 INSERT (EMPNO, ENAME, SAL, COMM) VALUES (E.EMPNO, E.ENAME, E.SAL, E.COMM);
13 rows merged.
# STEP 4. 결과를 출력해본다
SQL> SELECT EMPNO, SAL, COMM FROM EMP_MASTER;
EMPNO SAL COMM
---------- ---------- ----------
7499 1600 300
7521 1250 500
7654 1250 1400
7698 2850
7844 1500 0
7900 950
7839 5000
7782 2450
7566 2975
1
7934 1300
7902 3000
7369 800
111 0 0
222 0 0
333 0 0
2. TRANSACTION
- 논리단위를 형성하는 DML(Data Manipulation Language)문의 모음을 말한다.
- 하나 또는 두 개 이상의 SQL문으로 이루어진 단위
- 하나의 트랜잭션 안에 모든 SQL은 동일한 효과를 가진다.
- COMMIT, ROLLBACK으로 모듈 단위를 실행한다.
- TRANSACTION 이벤트 발생 원리
- TRANSACTION 이벤트 예시 : COMMIT, ROLLBACK, SQL 실행 종료 시, 시스템 장애 및 고장, DDL 발생 (CREATE)
- 실행 가능한 첫 번째 SQL문이 실행될 때 시작되어 다음 이벤트가 발생하면 종료된다.
- TRANSACTION이 종료가 되면, 실행 가능한 SQL구문이 다음 TRANSACTION을 자동으로 시작한다.
- 데이터 추가(INSERT) -> 데이터 삭제(DELETE) -> COMMIT : 트랜잭션 종료
- 트랜잭션 시작 -> 데이터 추가(INSERT) -> 데이터 삭제(DELETE) -> ROLLBACK : 트랜잭션 종료
- 트랜잭션 시작 -> 데이터 추가(INSERT) -> 데이터 삭제(DELETE) -> CREATE : 트랜잭션 종료
- TRANSACTION 명령어 종류 : COMMIT, ROLLBACK, SAVEPOINT
- 암시적 TRANSACTION
- 자동 COMMIT 이 발생할 경우 ☕
- DDL, DCL이 실행되는 경우, COMMIT/ROLLBACK을 명시하지 않고 정상적으로 SQLPLUS가 종료될 때
- 자동 ROLLBACK
- 자동 COMMIT 이 발생할 경우 ☕
- 명시적 TRANSACTION
- COMMIT : 보류 중인 모든 데이터의 변경 내용을 영구저장하고 현재의 트랜잭션을 종료한다.
- SAVEPOINT 이름 : 현재에 트랜잭션 내에 저장 지점을 표시한다
- ROLLBACK : 보류 중인 모든 데이터의 변경내용을 버리고, 현재의 트랜잭션을 종료한다.
- ROLLBACK TO SAVEPOINT 이름 : 트랜잭션 저장점으로 ROLLBACK 하여 저장점 이후에 생성된 SQL 문장이 있는 내용 및 저장점을 버린다.
- 암시적 TRANSACTION
SQL> CREATE TABLE EMP_RES
2 AS
3 SELECT EMPNO, ENAME
4 FROM EMP;
Table created.
SQL> DELETE FROM EMP_RES;
13 rows deleted.
SQL> INSERT INTO EMP_RES VALUES(111,'111');
1 row created.
SQL> SAVEPOINT A;
Savepoint created.
SQL> UPDATE EMP_RES
2 SET ENAME = '3333'
3 WHERE EMPNO = 111;
1 row updated.
SQL> ROLLBACK TO A;
Rollback complete.
SQL> DELETE FROM EMP_RES;
1 row deleted.
SQL> COMMIT; --> SAVEPOINT A 이후에 실행된 INSERT, DELETE 결과가 테이블에 영구적으로 저장된다.
Commit complete.
SQL> CREATE SEQUENCE MYSC
2 START WITH 1
3 INCREMENT BY 5
4 MAXVALUE 20
5 NOCACHE
6 NOCYCLE;
# MYSC.CURRVAL : 현재 시퀀스 확인
# MYSC.NEXTVAL : 실행
Sequence created.