[SQL] 데이터 조작

다중 테이블 INSERT문의 유형

• 무조건 INSERT ALL
• 조건부 INSERT ALL
• 조건부 INSERT FIRST
• 피벗팅 INSERT

1. 무조건 INSERT ALL

insert all을 사용하면 into절을 하나 이상 쓸 수 있음

CREATE TABLE sal_history 
AS SELECT employee_id AS empid 
         ,hire_date   AS hiredate 
		 ,salary      AS sal 
   FROM employees 
   WHERE 1 = 0 ; 
   
CREATE TABLE mgr_history 
AS SELECT employee_id AS empid 
         ,manager_id  AS mgr
		 ,salary      AS sal 
   FROM employees 
   WHERE 1 = 0 ; 

-- 각각 insert into
INSERT INTO sal_history 
SELECT employee_id
       ,hire_date  
       ,salary     
 FROM employees 
WHERE employee_id > 200 ; 

SELECT * FROM sal_history ;

INSERT INTO mgr_history 
SELECT employee_id
      ,manager_id
  	  ,salary     
  FROM employees 
 WHERE employee_id > 200 ; 

SELECT * FROM mgr_history ;

ROLLBACK ;

-- 위에 insert into했던 식의 결과와 같은 내용
INSERT ALL 
 INTO sal_history VALUES (empid, hiredate, sal)
 INTO mgr_history VALUES (empid, mgr, sal)
SELECT employee_id AS empid 
      ,hire_date   AS hiredate
      ,manager_id  AS mgr
      ,salary      AS sal 
  FROM employees
WHERE employee_id > 200 ; 

-- 조회 시 똑같은 내용 나옴
SELECT * FROM sal_history ;
SELECT * FROM mgr_history ;

ROLLBACK ;

2. 조건부 INSERT ALL

INSERT ALL 
 WHEN sal > 10000 THEN 
   INTO sal_history VALUES (empid, hiredate, sal)
 WHEN mgr < 200   THEN 
 INTO mgr_history VALUES (empid, mgr, sal)
SELECT employee_id AS empid 
      ,hire_date   AS hiredate
      ,manager_id  AS mgr
      ,salary      AS sal 
  FROM employees
WHERE employee_id > 200 ; 

-- 201번과 205번이 겹침
SELECT * FROM sal_history ;
SELECT * FROM mgr_history ;

ROLLBACK ;

3. 조건부 INSERT FIRST

첫번째 조건에 만족하는 순간, sal_history에 다 들어가고
그 행은 mgr_history에는 입력대상으로 사용하지 X

INSERT FIRST
 WHEN sal > 10000 THEN 
   INTO sal_history VALUES (empid, hiredate, sal)
 WHEN mgr < 200   THEN 
 INTO mgr_history VALUES (empid, mgr, sal)
SELECT employee_id AS empid 
      ,hire_date   AS hiredate
      ,manager_id  AS mgr
      ,salary      AS sal 
  FROM employees
WHERE employee_id > 200 ; 

-- 번호가 겹치지 않게 나옴
SELECT * FROM sal_history ;
SELECT * FROM mgr_history ;

ROLLBACK ;

4. 피벗팅 INSERT

: 비정규화 되어있는 테이블의 데이터들을
정규화 된 테이블로 옮기는 작업이 가능하다는 의미

한번의 INSERT문으로 여러 개의 행 정보를 저장할 수 있는 방법

INSERT ALL
	INTO sales_info VALUES (employee_id, week_id, sales_MON)
    INTO sales_info VALUES (employee_id, week_id, sales_TUE)
    INTO sales_info VALUES (employee_id, week_id, sales_WED)
    INTO sales_info VALUES (employee_id, week_id, sales_THUR)
    INTO sales_info VALUES (employee_id, week_id, sales_FRI)
    SELECT employee_id, week_id, sales_MON, sales_TUE,
    		sales_WED, sales_THUR, sales_FRI
    FROM sales_source_data;

---

MERGE 문

INSERT, UPDATE, DELETE 3가지 명령어를 동시에 수행할 수 있는 방법

• 행이 존재 --> UPDATE 수행
• 새 행인 경우 --> INSERT 수행

MERGE INTO

DROP TABLE copy_emp PURGE ; 

CREATE TABLE copy_emp 
AS SELECT * FROM emp WHERE deptno = 30;

UPDATE copy_emp 
SET sal = sal + 5 
WHERE COMM IS NOT NULL ; 

SELECT * FROM copy_emp ;
SELECT * FROM emp ;

MERGE INTO copy_emp c 
USING emp e 
ON (c.empno = e.empno) 
WHEN MATCHED THEN 
UPDATE 
SET c.sal  = e.sal 
   ,c.comm = e.comm 
WHEN NOT MATCHED THEN 
INSERT (c.empno, c.ename, c.job, c.sal, c.deptno) 
VALUES (e.empno, e.ename, e.job, e.sal, e.deptno) ;

SELECT * FROM copy_emp ;

COMMIT ;

FLASHBACK TABLE 문

• 테이블을 이전 상태로 되돌릴 수 있음
• 현재 위치에서 수행됨
• 기준: 특정 시점 또는 SCN(System Change Number)
  (SCN: 순차적으로 증가하는 번호가 계속 만들어지고 있고, 그 번호를 기준으로 DB의 시점을 관리 할 수 있음)

예제

-- emp3 테이블 삭제
DROP TABLE emp3;

-- emp3 테이블이 휴지통에 들어가 있는 거 확인
SELECT original_name, operation, droptime
FROM recyclebin;

-- 테이블 복구
FLASHBACK TABLE emp3 TO BEFORE DROP;

Flashback query/version query

실수로 잘못된 쿼리를 복구할 때 유용함

SELECT empno, ename, sal, 
       versions_starttime,
       versions_endtime,
       versions_startscn,
       versions_endscn,
       versions_operation
FROM copy_emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE 
WHERE deptno = 30 
ORDER BY empno, versions_startscn ASC NULLS FIRST ; 

delete copy_emp
where empno = 7499;

select * from copy_emp;
commit;

SELECT * 
FROM copy_emp AS OF SCN 2625167 - 1 
WHERE empno = 7499 ;

commit;