MERGE, MULTI TABLE INSERT (CONDITIONAL/UNCONDITIONAL)

MERGE
데이터가 없으면 INSERT 하고, 해당 DATA가 존재하면 UPDATE

CREATE TABLE TBL_MERGE
(NO NUMBER
,NAME VARCHAR2(10)
,ADDR VARCHAR2(20)
,CONSTRAINT TBL_MERGE_NO_PK PRIMARY KEY(NO)
);

 

INSERT INTO TBL_MEGE VALUES (1,'일지매','서울');
INSERT INTO TBL_MEGE VALUES (2,'이지매','대한민국');

SELECT * FROM TBL_MERGE;
-- 51

MERGE INTO TBL_MERGE@LINK_KYH R
USING  TBL_MERGE L
ON (L.NO = R.NO)
WHEN MATCHED THEN
 UPDATE SET R.NAME - L.NAME, R.ADDR = L.ADDR
WHEN NOT MATCHED THEN
 INSERT (R.NO, R.NAME, R.ADDR)
     VALUES(L.NO,L.NAME,L.ADDR)
;

UPDATE TBL_MERGE SET NAME = '이영애', ADDR ='대한민국'
WHERE NO =2;

SELECT * FROM TBL_MERGE;
--F50

----MULTI TABLE INSERT
-- CONDITIONIAL INSERT FIRSET

SELECT *
FROM SCOTT.EMP;
--52

CREATE TABLE TBL_HIGHSAL
(EMPNO NUMBER(4)
,ENAME VARCHAR2(20)
,JOB VARCHAR2(20)
,SAL NUMBER(4)
,TAX NUMBER(4)
);

CREATE TABLE TBL_NORMALSAL
(EMPNO NUMBER(4)
,ENAME VARCHAR2(20)
,JOB VARCHAR2(20)
,SAL NUMBER(4)
,TAX NUMBER(4)
);

CREATE TABLE TBL_LOWSAL
(EMPNO NUMBER(4)
,ENAME VARCHAR2(20)
,JOB VARCHAR2(20)
,SAL NUMBER(4)
,TAX NUMBER(4)
);

SELECT * FROM TBL_HIGHSAL;
SELECT * FROM TBL_NORMALSAL;
SELECT * FROM TBL_LOWSAL;
--53

-- SAL >= 3000 이상인 직원들은 HIGHSAL에, SAL >= 2000 이고 SAL< 3000인 직원은 NORMALSAL
-- SAL < 2000 인 직원은 LOWSAL에 INSERT 한다!
-- TAX는 HIGH, NORMAL, LOW 각각 SAL의 15%, 3000~2500 13%,
-- 2000~2500 10% , 2000미만은 7%로 한다.
-- INSERT INTO 테이블명 SELECT 문; 응용

SELECT EMPNO, ENAME, JOB, SAL,
CASE WHEN SAL >= 3000 THEN SAL*0.15
 WHEN SAL >= 2500 THEN SAL*0.13
    WHEN SAL >= 2000 THEN SAL*0.10
 ELSE SAL*0.07 END  "TAX"
FROM SCOTT.EMP;
--54

INSERT FIRST WHEN S

AL >= 3000 THEN INTO TBL_HIGHSAL
 VALUES (EMPNO, ENAME, JOB, SAL, TAX)
     WHEN SAL >= 2000 THEN INTO TBL_NORMALSAL
 VALUES (EMPNO, ENAME, JOB, SAL, TAX)
    ELSE  INTO TBL_LOWSAL
 VALUES (EMPNO, ENAME, JOB, SAL, TAX)
SELECT EMPNO, ENAME, JOB, SAL,
CASE WHEN SAL >= 3000 THEN SAL*0.15
 WHEN SAL >= 2500 THEN SAL*0.13
    WHEN SAL >= 2000 THEN SAL*0.10
 ELSE SAL*0.07 END  "TAX"
FROM SCOTT.EMP;

SELECT * FROM TBL_HIGHSAL;
SELECT * FROM TBL_NORMALSAL;
SELECT * FROM TBL_LOWSAL;

--F55

FIRST : 조건 중 1개만 만족하면 수행하고 완료함

-- CONDITIONIAL INSERT ALL

DELETE TBL_HIGHSAL;
DELETE TBL_NORMALSAL;
DELETE TBL_LOWSAL;

CREATE TABLE TBL_MANAGER
(EMPNO NUMBER(4)
,ENAME VARCHAR2(20)
,JOB VARCHAR2(20)
,SAL NUMBER(4)
,TAX NUMBER(4)
);

CREATE TABLE TBL_CLERK
(EMPNO NUMBER(4)
,ENAME VARCHAR2(20)
,JOB VARCHAR2(20)
,SAL NUMBER(4)
,TAX NUMBER(4)
);

CREATE TABLE TBL_SALESMAN
(EMPNO NUMBER(4)
,ENAME VARCHAR2(20)
,JOB VARCHAR2(20)
,SAL NUMBER(4)
,TAX NUMBER(4)
);

INSERT ALL
 WHEN SAL >= 3000 THEN INTO TBL_HIGHSAL
 VALUES (EMPNO, ENAME, JOB, SAL, TAX)
    WHEN SAL >= 2000 THEN INTO TBL_NORMALSAL
 VALUES (EMPNO, ENAME, JOB, SAL, TAX)
    WHEN SAL < 2000 THEN INTO TBL_NORMALSAL
 VALUES (EMPNO, ENAME, JOB, SAL, TAX)
    WHEN JOB ='CLERK' THEN INTO TBL_CLERK
 VALUES (EMPNO, ENAME, JOB, SAL, TAX)
    WHEN JOB ='SALESMAN' THEN INTO TBL_SALESMAN
 VALUES (EMPNO, ENAME, JOB, SAL, TAX)
    WHEN JOB ='MANAGER' THEN INTO TBL_MANAGER
 VALUES (EMPNO, ENAME, JOB, SAL, TAX) 
SELECT EMPNO, ENAME, JOB, SAL,
CASE WHEN SAL >= 3000 THEN SAL*0.15
 WHEN SAL >= 2500 THEN SAL*0.13
    WHEN SAL >= 2000 THEN SAL*0.10
 ELSE SAL*0.07 END  "TAX"
FROM SCOTT.EMP;

SELECT * FROM TBL_HIGHSAL;
SELECT * FROM TBL_NORMALSAL;
SELECT * FROM TBL_LOWSAL;
--F55

SELECT * FROM TBL_MANAGER;
SELECT * FROM TBL_SALESMAN;
SELECT * FROM TBL_CLERK;
--F56

-- UNCONDITIONIAL INSERT ALL

CREATE TABLE TBL_AVGHIGHSAL
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
SAL NUMBER(4)
);

CREATE TABLE TBL_AVGHIGHYEARPAY
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
YEARPAY NUMBER(6)
);

INSERT ALL
 INTO TBL_AVGHIGHSAL
      VALUES (EMPNO, ENAME, SAL)
    INTO TBL_AVGHIGHYEARPAY
      VALUES (EMPNO, ENAME, YEARPAY)

SELECT EMPNO, ENAME, SAL, SAL*12 + NVL(COMM,0) "YEARPAY"
FROM SCOTT.EMP
WHERE SAL >= (SELECT AVG(SAL) FROM SCOTT.EMP);

SELECT * FROM TBL_AVGHIGHSAL;

--57

SELECT * FROM TBL_AVGHIGHYEARPAY;

--58

X`