[Oracle SQL Developer] 기초 XVI - DML

서브쿼리를 이용한 DML(INSERT, UPDATE, DELETE) 문

---

1. 재고수불테이블 생성
  . 테이블명 : REMAIN
  . 컬럼 :
         년도 : REMAIN_YEAR CHAR(4) NOT NULL,
      상품번호 : REMAIN_PROD VARCHAR2(10) NOT NULL,
      기초재고 : REMAIN_J_00 NUMBER(5) NULL,
         입고 : REMAIN_I    NUMBER(5) NULL,
         출고 : REMAIN_O    NUMBER(5),
      기말재고 : REMAIN_J_99 NUMBER(5),    -- 기초재고 + 입고 - 출고 = 기말재고
      처리일자 : REMAIN_DATE DATE,
      
      기본키 : REMAIN_PROD, REMAIN_YEAR
      외래키 : REMAIN_PROD(PROD);
  
  
  
    CREATE TABLE REMAIN (
    REMAIN_YEAR CHAR(4) NOT NULL,
    REMAIN_PROD VARCHAR2(10) NOT NULL,
    REMAIN_J_00 NUMBER(5),
    REMAIN_I NUMBER(5),
    REMAIN_O NUMBER(5),
    REMAIN_J_99 NUMBER(5), 
    REMAIN_DATE DATE,
    
    CONSTRAINT PK_REMAIN PRIMARY KEY (REMAIN_YEAR, REMAIN_PROD),
    CONSTRAINT FK_REMAIN FOREIGN KEY (REMAIN_PROD)
    REFERENCES PROD(PROD_ID)
    );
    
    SELECT * FROM REMAIN;
    
    EX) REMAIN 테이블에 다음 조건에 맞도록 자료를 삽입하시오.
     ** 서브쿼리를 이용하여 자료를 추가 삽입하는 경우 VALUES 와 '()'를 생략한다.
     INSERT INTO 테이블명[(컬럼LIST)] 서브쿼리
    
    [조건]
    . 처리 연도 : 2005년
    . 처리 대상 : 전체 상품
    . 기초 재고 : PROD 테이블의 PROD_PROPERSTOCK 값
    . 입고/출고 : 0
    . 기말 재고 : PROD_PROPERSTOCK + REMAIN_I - REMAIN_O
    . 처리 일시 : 2005년 03월 31일
    
    INSERT INTO REMAIN(REMAIN_YEAR,REMAIN_PROD,REMAIN_J_00,REMAIN_I,REMAIN_O,REMAIN_J_99,REMAIN_DATE)
      SELECT '2005', PROD_ID, PROD_PROPERSTOCK, 0, 0, PROD_PROPERSTOCK, '20050331'
        FROM PROD;
        
        COMMIT;


  2. UPDATE
    - 단순 UPDATE문
    2.1. 사용형식  
      UPDATE 테이블명[별칭]
         SET 컬럼명 = 값[,
             컬럼명 = 값,
                :
             컬럼명 = 값]
      [WHERE 조건];
      
      
    - 서브쿼리를 이용한 UPDATE문    
      : SET 되는 컬럼의 갯수와 서브쿼리의 컬럼 갯수를 동일하게 설정한다.
    
      UPDATE 테이블명[별칭]
         SET (컬럼명[,컬러명,...])=(서브쿼리)
    
    EX) 2005년 4월 제품별 입고수량을 구하여 REMAIN테이블을 갱신하시오.
    
    -- 2005년 4월 제품별 입고수량을 구하는 SELECT 문
    SELECT BUY_PROD,
           SUM(BUY_QTY) AS IN_AMT
      FROM BUYPROD
     WHERE BUY_DATE BETWEEN '20050401' AND '20050430'
     GROUP BY BUY_PROD
     ORDER BY 1;
     
    UPDATE REMAIN A
       SET (A.REMAIN_I, A.REMAIN_J_99, A.REMAIN_DATE) = 
           (SELECT TA.IN_AMT, (REMAIN_J_00 + TA.IN_AMT - REMAIN_O), '20050430' -- REMAIN_J_99 - REMAIN_O
              FROM (SELECT PROD_ID, (SELECT NVL(SUM(BUY_QTY),0)
                                       FROM BUYPROD
                                      WHERE BUY_DATE BETWEEN '20050401' AND '20050430'
                                        AND C.PROD_ID = BUY_PROD) AS IN_AMT
                      FROM PROD C) TA 
                      -- GROUP BY 를 사용하지않고 물품별 매입의 합계 전체 항목을 조회하는 방법을 사용했다.
                 WHERE A.REMAIN_PROD = TA.PROD_ID)
     WHERE A.REMAIN_YEAR = '2005';
    
    SELECT * FROM REMAIN;
    
    ROLLBACK;
    COMMIT;
    
    
    
    
    
    -- 4월달 매출도 같이 
     UPDATE REMAIN A
       SET (A.REMAIN_O, A.REMAIN_J_99, A.REMAIN_DATE) = 
           (SELECT TA.OUT_AMT, (REMAIN_J_00 + REMAIN_I - TA.OUT_AMT), '20050430'
              FROM (SELECT PROD_ID, (SELECT NVL(SUM(CART_QTY),0)
                                       FROM CART A
                                      WHERE CART_NO LIKE '200504%'--BETWEEN '20050401' AND '20050430'
                                        AND C.PROD_ID = A.CART_PROD) AS OUT_AMT
                      FROM PROD C) TA 
                      -- GROUP BY 를 사용하지않고 물품별 매출의 합계 전체 항목을 조회하는 방법을 사용했다.
                 WHERE A.REMAIN_PROD = TA.PROD_ID)
     WHERE A.REMAIN_YEAR = '2005';
    
    
    
    
    
    
    
  3. DELETE, TRUNCATE(? 길이를 줄이다)
   - 테이블내의 자료 삭제 기능을 제공한다.
   - DELETE 된 자료는 복구(ROLLBACK)가 가능하다.
   - TRUNCATE 된 자료는 복구가 불가능하다.
   
   3.1. 사용형식
     1) 단순 DELETE
     DELETE FROM 테이블명 
      [WHERE 조건];
   
     2) 단순 TRUNCATE
     TRUNCATE TABLE 테이블명
       [WHERE 조건];

   3.2. 서브쿼리를 이용한 자료 삭제 
     DELETE FROM 테이블명
      WHERE 조건(서브쿼리);
   
      EX) REMAIN1 테이블에서 기초재고가 9개인 자료를 서브쿼리를 사용하여 삭제하시오.
      
      SELECT * FROM REMAIN1;
       WHERE REMAIN_J_00 = 9;
      
      DELETE FROM REMAIN1
       WHERE REMAIN_PROD IN (SELECT REMAIN_PROD
                               FROM REMAIN1   
                              WHERE REMAIN_J_00=9)
         AND REMAIN_YEAR = '2005';
              
      ROLLBACK;

     
     EX) REMAIN1 테이블에서 기말재고가 10개 미만인 자료를 삭제하시오.
     
--     DELETE FROM REMAIN1
--      WHERE REMAIN_J_99 <10;
--      
--     SELECT * FROM REMAIN1;
--   
--     TRUNCATE TABLE REMAIN1;
--     
--     DROP TABLE REMAIN1;
--     
--     DELETE FROM REMAIN1;
--     
--     ROLLBACK;   
     
     DROP, CREATE, TRUNCATE 는 ROLLBACK 으로 저장되지 않는다.
   

   
   ** 서브쿼리를 이용해서 테이블을 복사하는 방법
     . 구조와 내용이 모두 복사된다.
     . 복사가 안되는 내용 : 기본키, 외래키는 복사가 불가능하다.
     
     - 사용형식
     CREATE TABLE 테이블명
     AS 
        (서브쿼리);
  
    EX) REMAIN 테이블을 RAMAIN1로 내용만 복사하시오.
    
    CREATE TABLE REMAIN1
     AS 
        (SELECT * FROM REMAIN);
    
    SELECT * FROM REMAIN1;