KH 50일차 - INSERT / UPDATE / DELETE / MERGE (***)

-- ------------------------------------------------------

-- 1. 조건 INSERT FIRST 문

-- ------------------------------------------------------

-- (1) 기존 테이블을 이용하여, 새로운 테이블 생성 (CTAS)

-- ------------------------------------------------------

-- * CTAS: 기존 테이블 스키마 복사 시, NOT NULL 제약조건을 제외

--         한, 그 외 제약조건은 복사되지 않음.

-- ------------------------------------------------------

-- Basic Syntax)

--

--  CREATE TABLE 테이블명 [(컬럼명,컬럼명2)]

--  AS

--  Subquery;

-- ------------------------------------------------------

CREATE TABLE myemp_mgr3 AS

SELECT

    empno,

    ename,

    mgr

FROM

    emp

WHERE

    1 = 2;

-- emp 릴레이션의 데이터를 제외한 스키마만 myemp_mgr3를 만든다.

DESC myemp_mgr3;

DESC myemp_hire3;

-- ------------------------------------------------------

-- (2) 조건 INSERT *FIRST* 문장 수행 (****)

-- ------------------------------------------------------

-- (**주의사항**) VALUES 절에 사용된 컬럼명과 Subquery에서

--      사용된 컬럼명이 반드시 동일해야 한다.

-- ------------------------------------------------------

DELETE myemp_hire3;

DELETE myemp_mgr3;

INSERT FIRST

    WHEN sal = 800 THEN

    INTO myemp_hire3 VALUES (empno, ename, mgr)

    WHEN sal < 2500 THEN

    INTO myemp_mgr3 VALUES (empno, ename, mgr)

    SELECT

        empno,

        ename,

        hiredate,

        sal,

        mgr

    FROM

        emp;

-- 만약 첫번째 WHEN과 두번째 WHEN조건 모두 만족하는 데이터가 있으면 첫번째에만 들어간다.

-- sal = 800 인 사원은, 아래 두 WHEN 절의 조건식을 모두 만족한다. ( 월급이 800이면 2500이하이기 때문에 )

-- 이때, 첫번째 WHEN절에서만 INSERT가 수행되고,

-- 두번째 WHEN절은 설령 조건이 참이어도, 두번째에는 INSERT 수행되지 않는다. (***)

---

-- ------------------------------------------------------

-- 2. UPDATE 문 (**)

-- ------------------------------------------------------

--  가. 테이블에 저장된 데이터 수정.

--  나. 한 번 수행으로, 여러 개의 행들을 수정할 수 있음.

--  다. WHERE 절은 *생략가능* (일반적이지 않음)

--      이 경우엔, 해당 테이블의 모든 데이터가 수정됨.

-- ------------------------------------------------------

-- Basic Syntax)

--  UPDATE 테이블명     -- 변경(수정)할 테이블명 지정.

--  SET                -- 변경할 한개 이상의 컬럼명=값 형식 지정 (***)

--      컬럼명1 = 변경값1,

--      [컬럼명2 = 변경값2],

--      ...

--      [컬럼명n = 변경값n]

--  [WHERE 조건식];

-- ------------------------------------------------------

UPDATE  mydept

SET

    dname = '영업',

    loc = '경기'

WHERE

    DEPTNO = 40;

-- 40번 부서의 dname과 loc 각각의 데이터만 영업과 경기로 변경한다.

-- ------------------------------------------------------

-- 2-1. 서브쿼리(=부속질의)를 이용한 UPDATE 문 (***)

-- ------------------------------------------------------

--  가. UPDATE의 SET 절에서 서브쿼리를 이용하면, 서브쿼리의 실행

--      결과값으로, 테이블 수정가능.

--  나. 이 문장으로, 기존 다른 테이블의 데이터를 이용하여, 혀재

--      지정 테이블의 특정 컬럼값의 변경이 가능.  

-- ------------------------------------------------------

-- Basic Syntax)

--  UPDATE 테이블명     -- 변경(수정)할 테이블명 지정.

--  SET                -- 변경할 한개 이상의 컬럼명 = 값 형식 지정

--      컬럼명1 = (Sub-query 1),    -- 서브쿼리 결과로 컬럼변경

--     [컬럼명2 = (Sub-query 2)],

--      ...

--     [컬럼명n = (Sub-query N)]

--  [WHERE 조건식];

-- ------------------------------------------------------

UPDATE  mydept

SET

    dname = ( SELECT dname FROM dept WHERE deptno = 10 ),

    loc = ( SELECT loc FROM dept WHERE deptno = 20 )

WHERE

    deptno = 40;

-- 서브쿼리를 사용하여 UPDATE문을 작성할 때에는 반드시 서브쿼리가 반환하는 결과가 하나(1x1)이어야 한다. (***)

-- 이 경우에는 mydept 테이블의 부서번호가 40인 부서의 dname과 loc를 각각 부서번호 10번과 20번의 데이터와 같게 변환시키는 것이다.

-- 단, 이렇게 작성될 경우 데이터의 일관성이 깨질 수 있으니 조심해야 한다. (*)

COMMIT; ROLLBACK;

-- 그렇기 때문에 COMMIT과 ROLLBACK을 잘 활용해야 한다.

SELECT

    *

FROM

    mydept;

 

---

-- ------------------------------------------------------

-- 3. DELETE 문

-- ------------------------------------------------------

--  가. 테이블에 저장된 데이터 삭제.

--  나. 한번에 여러 행들을 삭제가능.

--  다. WHERE 절은 *생략가능* (**주의**)

--      생략하면, 지정 테이블의 모든 데이터(행)가 삭제됨. (***)

-- ------------------------------------------------------

-- Basic Syntax)

--  DELETE FROM 테이블명    -- 데이터를 삭제할 테이블 지정

--  [WHERE 조건식];         -- 조건이 참인 행들만 삭제

--  DELETE 테이블명;         -- 테이블의 모든 데이터(행)가 삭제된다.

-- ------------------------------------------------------

DESC mydept;

BEGIN

    DELETE FROM mydept

    WHERE deptno = 30;

    ROLLBACK;

END;

-- 이 경우에는 ROLLBACK이기에 변경내용이 저장되지 않고 전의 상태로 돌아간다. ( 변경내용을 저장하기 위헤서는 COMMIT )

-- ------------------------------------------------------

-- 3-1. 서브쿼리(=부속질의)를 이용한 DELETE 문

-- ------------------------------------------------------

--  가. DELETE 문의 WHERE 절에서, 서브쿼리 사용.

--  나. 서브쿼리의 실행 결과값으로, 테이블의 데이터 삭제가능.

--  다. 이 방법을 사용하면, 기존 테이블에 저장된 데이터를 사용하여,

--      현재 테이블의 특정 데이터 삭제가능.

--  라. 서브쿼리의 실행결과 값의 개수와 타입이, 메인쿼리의 WHERE절

--      에 지정된 조건식의 컬럼의 개수와 타입이 반드시 동일해야 함. (**)

-- ------------------------------------------------------

-- Basic Syntax)

--  DELETE FROM 테이블명        -- 데이터를 삭제할 테이블 지정

--  [ WHERE <**Sub-query**> ]; -- 조건이 참인 행들만 삭제

-- ------------------------------------------------------

DESC mydept;

BEGIN

    -- 서브쿼리를 통한 조건식 지정

    DELETE FROM mydept

    WHERE loc = (

        SELECT

            loc

        FROM

            dept

        WHERE

            deptno = 20

    );

    -- 다중컬럼 조건식 지정 ( Pairwise 방식 )

    DELETE FROM mydept

    WHERE ( loc, dname ) = (

        SELECT

            loc,

            dname

        FROM

            dept

        WHERE

            deptno = 20

     );

    ROLLBACK;

END;

-- 트랜젝션은 반드시 끝내줘야 하며, 하나라도 오류가 발생할 시에는 번경내용을 저장하지 않고 이전의 상태로 돌아간다.

-- COMMIT과 ROLLBACK으로 구분해줘야 한다.

---

-- ------------------------------------------------------

-- 4. MERGE 문 (****)

-- ------------------------------------------------------

--  가. 스키마(구조)가 같은 두 개의 테이블을 비교하여,

--  나. 하나의 테이블로 합침. (***)

--  다. INTO 절 -> 병합결과가 저장될 테이블명 지정.

--  라. USING 절 -> 병합할 대상 테이블/뷰/서브쿼리 지정.

--  마. ON 절 -> INTO 절의 테이블1과 USING 절의 테이블2 간의

--               조인조건 지정.

--  바. WHERE 절로 조건지정 가능.

--  사. DELETE 문도 사용가능.

--  아. ** 앞의 INSERT/UPDATE/DELETE문과의 차이점:

--      저장 또는 수정 대상 테이블을 INTO 절에 지정.

--  자. 응용분야:

--      대표적인 경우, 전자상거래의 물품판매회사가,

--      월별로 판매현황관리, 연말에 월별판매현황 데이터 병합

-- ------------------------------------------------------

-- Basic Syntax)

--  MERGE INTO 테이블1 별칭 ( -> 결과가 지정될 테이블명 지정 )

--      USING (테이블명2 | 뷰 | 서브쿼리) 별칭

--      ON (조인조건)

-- ------------------------------------------------------

-- (1) 조인 조건식이 일치(참이면)하면...

-- -------------------------------------------------------

--      WHEN MATCHED THEN

-- ------------------------------------------------------

--  (1-1). MERGE 테이블의 기존 데이터 변경(갱신).

-- ------------------------------------------------------

-- UPDATE SET

--  컬럼명1 = 값1,

--  ...

--  컬럼명n = 값n

-- [WHERE 조건식]

-- ------------------------------------------------------

--  (1-2). MERGE 테이블의 기존 데이터 삭제.

-- ------------------------------------------------------

--  [DELETE WHERE 조건식]

-- ------------------------------------------------------

-- (2) 조인 조건식이 비일치(거짓이면)하면 ...

-- ------------------------------------------------------

--      WHEN NOT MATCHED THEN

-- ------------------------------------------------------

--  (2-1). MERGE 테이블에 새로운 데이터 생성.

-- ------------------------------------------------------

-- INSERT (컬럼목록)

-- VALUES (값목록)

--  [WHERE 조건식];

-- ------------------------------------------------------

-- ------------------------------------------------------

-- 4-1. 실습용 테이블 및 데이터 생성.

-- ------------------------------------------------------

-- 테이블 생성

DROP TABLE pt_01;

CREATE TABLE pt_01 (

    판매번호 VARCHAR2(8),

    제품번호 NUMBER,

    수량 NUMBER,

    금액 NUMBER

); -- pt_01

DROP TABLE pt_02;

CREATE TABLE pt_02 (

    판매번호 VARCHAR2(8),

    제품번호 NUMBER,

    수량 NUMBER,

    금액 NUMBER

); -- pt_02

DROP TABLE p_total;

CREATE TABLE p_total (

    판매번호 VARCHAR2(8),

    제품번호 NUMBER,

    수량 NUMBER,

    금액 NUMBER

); -- p_total

DESC pt_01;

DESC pt_02;

DESC p_total;

-- 테이블에 데이터 생성하기

BEGIN

    INSERT INTO pt_01 VALUES ( '20170101', 1000, 10, 500 );

    INSERT INTO pt_01 VALUES ( '20170102', 1001, 10, 400 );

    INSERT INTO pt_01 VALUES ( '20170103', 1002, 10, 300 );

    INSERT INTO pt_02 VALUES ( '20170201', 1003, 5, 500 );

    INSERT INTO pt_02 VALUES ( '20170202', 1004, 5, 400 );

    INSERT INTO pt_02 VALUES ( '20170203', 1005, 5, 300 );

    COMMIT;

END;

-- 데이터 확인하기

SELECT

    *

FROM

    pt_01;

-- 1월달 판매현황 데이터 확인

SELECT

    *

FROM

    pt_02;

-- 2월달 판매현황 데이터 확인

 

---

-- ------------------------------------------------------

-- 4-2. MERGE 문 수행 #1

-- ------------------------------------------------------

TRUNCATE TABLE p_total;

MERGE INTO p_total  -- MERGE결과 저장 테이블 지정

    -- MERGE 대상 테이블 지정(Alias 가능, 이때 AS 키워드 사용불가) (***)

    USING pt_01 p01 -- p01은 별칭이다.

    ON (p_total.판매번호 = p01.판매번호)  -- 동등조인조건 (***)

    --  이 경우에는 total 테이블에 데이터가 없기에 조인될 수 없다. -> 모든 데이터가 (2)로 실행

    -- --------------------------------------

    -- (1) JOIN 조건에 일치하는 행들은 ....

    -- --------------------------------------

    WHEN MATCHED THEN

        -- MERGE 대상테이블(p01)의 데이터를 이용하여, (***)

        -- MERGE 저장테이블(total)의 데이터 변경(갱신).

        UPDATE SET

            p_total.제품번호 = p01.제품번호

    -- --------------------------------------

    -- (2) JOIN 조건에 일차하지 않는 행들은 ...

    -- --------------------------------------

    WHEN NOT MATCHED THEN

        -- MERGE 대상테이블(p01)의 데이터를 이용하여, (***)

        -- MERGE 저장테이블(total)의 데이터 신규 생성.

        INSERT

        VALUES ( p01.판매번호, p01.제품번호, p01.수량, p01.금액 );

        -- MERGER INTO를 통해서 통합할 테이블을 지정했기에 INSERT 뒤에 INTO를 할 필요는 없다. (**)

-- MERGE는 정해져있는 것이 아니기에 상황에 맞게 작성해야 한다. (*)

-- MERGE는 조인 조건에 만족할 때와 만족하지 않을때를 구분해줄 뿐이지, 병합 시나리오를 자동으로 만들어주지는 않는다.

-- MERGE할 때, 1월이나 2월 테이블의 데이터를 건드리거나 조작하지 않게 조심해야 한다.

SELECT

    *

FROM

    p_total;

-- MERGE된 결과를 확인해본 결과 1월에 있던 데이터가 조건에 맞게 total에 들어와있음을 알 수 있다.

-- ------------------------------------------------------

-- 4-3. MERGE 문 수행 #2

-- ------------------------------------------------------

MERGE INTO p_total total        -- MERGE 결과 저장 테이블 지정

    USING pt_02 p02

    ON ( total.판매번호 = p02.판매번호 )    -- 동등 조인 조건

    -- --------------------------------------

    -- (1) JOIN 조건에 일치하는 행들은 ....

    -- --------------------------------------

    WHEN MATCHED THEN

        UPDATE SET

            total.제품번호 = p02.제품번호

   

    -- --------------------------------------

    -- (2) JOIN 조건에 일차하지 않는 행들은 ...

    -- --------------------------------------

    WHEN NOT MATCHED THEN

        INSERT

        VALUES ( p02.판매번호, p02.제품번호, p02.수량, p02.금액 );

-- 이 경우에는 현재 2월 판매현황에 있는 데이터가 total에 없기에 (2)으로 들어가서 2월의 데이터를 total에 넣게 된다.

SELECT

    *

FROM

    p_total;

-- MERGE된 결과를 확인해본 결과 1월과 2월에 있던 데이터가 조건에 맞게 total에 들어와있음을 알 수 있다.

 

---

-- ------------------------------------------------------

-- 5. TCL (Transaction Control Language)

-- ------------------------------------------------------

--  가. Transaction (트랜잭션):

--      a. DB의 논리적인 작업단위

--      b. 분리될 수 없는, 한개 이상의 DB조작을 의미

--      c. 하나의 트랜잭션에는, 한 개 이상의 DML문장이 포함가능

--      d. 트랜잭션 대상 SQL문장은, "DML" 문임(****)

--  나. 오라클에서 발생되는, 여러 개의 작업들을, 하나의 작업처럼

--      처리해야 하는 경우에, 트랜잭션이 필요!!! (예: 계좌이체)

--  다. 트랜잭션 제어 명령어(TCL) 제공: (** All or Nothing **)

--      (1) COMMIT (All)

--          - DML문에 의해 실행되었으나, 아직 저장되지 않은 모든 데이터를

--          - 데이터베이스에 저장하고, 현재의 트랜잭션 종료시킴.

--      (2) ROLLBACK [TO SAVEPOINT savepoint] (Nothing)

--          - 저장되지 않은 모든 DB변경사항 취소하고, 현재의 트랜잭션을

--            종료시킴. 트랜잭션 시작 이전의 상태로 복구.

--          - TO SAVEPOINT 키워드 사용하면, 지정된 SAVEPOINT까지만

--            DB변경사항 취소가능 (Partial Rollback)

--      (3) SAVEPOINT

--          - 진행중인 트랜잭션을, 특정이름으로 지정(like 책갈피)

--          - 지정된 이름의 상태로, 실행된 DML 작업의 취소가능

--            (Partial Rollback)

--  라. 락 경합(Race Condition)으로 인한, 데드락(Dead Lock) 발생가능

--      - 락 경합으로 인한 무한대기는, 오라클의 성능을 대폭 감소시키는 원인

--      - 매우 주의해야 함 (***)

-- ------------------------------------------------------

-- ------------------------------------------------------

-- 5-1. 락 경합(Race Condition)으로 인한, 데드락(Dead Lock) 발생

-- ------------------------------------------------------

-- ----------------------

-- (1) A 사용자 DML 수행

-- ----------------------

update dept

set

    loc = '부산'

where

    deptno = 40;

-- ----------------------

-- (2) B 사용자 DML 수행 (A 사용자 DML 수행 후에, 수행)

-- ----------------------

--  A 사용자가 자신의 트랜잭션을 종료(Commit/Rollback)하기 전까지,

--  무한 대기 수행(Infinite awaiting) --> 락 경합(Lock Contention)

-- ----------------------

delete from dept

where

    deptno = 40;