[Oracle] DML명령 (서브쿼리 이용)

1. UPDATE 명령

UPDATE 테이블명
   SET (컬럼명, 컬럼명, ....) = (서브쿼리)
[WHERE 조건];

• SET 절에 하나 이상의 컬럼을 기술하려면 반드시 ( ) 안에 기술해야하고
  서브쿼리의 SELECT절의 컬럼과 갯수, 순서가 반드시 일치해야 한다.
• (서브쿼리와 테이블명에 대한 조건을 작성해야한다)

---

2020년 1월 제품별 매입수량을 구하여 재고수불 테이블을 갱신하시오.
단, 처리일자는 2020/03/31이다.

UPDATE REMAIN A
       SET (A.REMAIN_I,A.REMAIN_J_99,A.REMAIN_DATE) = 
                  ( SELECT A.REMAIN_I + B.BQTY,
                           A.REMAIN_J_99 + B.BQTY,
                           TO_DATE('20200331')
                    FROM (SELECT BUY_PROD,
                                 SUM(BUY_QTY) AS BQTY
                            FROM BUYPROD
                           WHERE BUY_DATE BETWEEN TO_DATE('20200101') AND TO_DATE('20200131')
                           GROUP BY BUY_PROD) B
                   WHERE B.BUY_PROD(+)=A.PROD_ID)
                   -- 전체를 수정하면 변함없는 것은 NULL출력 WHERE절을 통해 바꾼것만 지정
 WHERE A.PROD_ID IN(SELECT DISTINCT BUY_PROD
                      FROM BUYPROD
                     WHERE BUY_DATE BETWEEN TO_DATE('20200101') AND TO_DATE('20200131'));

2. INSERT 명령 (배운것 x)

INSERT INTO 테이블명[(컬럼명,...)] --INSERT,CREATE 서브쿼리는 괄호안에 들어가지 않음
    서브쿼리

• INSERT 문에 사용되는 서브쿼리는 '( )'와 VALUES절을 생략함
• '(컬럼명,...)'이 사용된 경우 서브쿼리의 SELECT 절의 컬럼의 갯수, 순서, 타입이 동일해야함

---

재고수불테이블(REMAIN)에 상품테이블의 자료 중 상품코드, 적정재고를 이용하여 모든 상품정보를 입력하시오

[자료] REMAIN_YEAR에는 '2020' PROD_ID : 상품테이블의 PROD_ID REMAIN_J_00 : 상품테이블의 적정재고(PROD_PROPERSTOCK) REMAIN_I : 0 REMAIN_O : 0 REMAIN_J_99 : 상품테이블의 적정재고(PROD_PROPERSTOCK) REMAIN_DATE : '2019/12/31'

  INSERT INTO REMAIN(REMAIN_YEAR,PROD_ID,REMAIN_J_00,REMAIN_I,REMAIN_O,
                     REMAIN_J_99,REMAIN_DATE)
    SELECT '2020',PROD_ID,PROD_PROPERSTOCK,0,0,PROD_PROPERSTOCK,TO_DATE('20191231')
      FROM PROD;

3. DELETE 명령

DELETE FROM 테이블명
WHERE 조건 -- (조건에 서브쿼리)

• 테이블 하나의 행을 지울 때 사용
• 컬럼 단위의 삭제는 불가능하기 때문에 DELETE와 FROM 사이에 아무것도 없음

---

사원테이블에 퇴직처리를 위하여 RETIRE 테이블을 생성하시오
테이블명 : RETIRE

 

컬럼명	타입	기본값	PK/FK
EMPLOYEE_ID	NUMBER(6)		PK / FK
DEPARTMENT_ID	NUMBER(4)		FK
JOB_ID	VARCHAR2(10)		FK
RETIRE_DATE	DATE	SYSDATE

트리거를 통해 정보를 EMPLOYEES 에서 RETIRE로 이동시키기

 CREATE OR REPLACE TRIGGER TG_EMP_DELETE
    BEFORE DELETE ON EMPLOYEES
    FOR EACH ROW
BEGIN
    INSERT INTO RETIRE
        VALUES (:OLD.EMPLOYEE_ID, :OLD.DEPARTMENT_ID, :OLD.JOB_ID, SYSDATE) ;
END;

DELETE 작업을 수행하면 RETIRE 테이블로 들어감

DELETE FROM EMPLOYEES
WHERE HIRE_DATE<=TO_DATE('20170630');