230420 마흔 두 번째 수업_SQL

BIGIN 실행부:

<조건문>

1) IF 조건식 THEN 실행내용 END IF; (단일 IF문)

--사번이 201인 사원의 사번, 이름, 급여, 보너스율(%) 출력
--단, 보너스를 받지 않는 사원은 보너스율 출력전에 '보너스를 지급받지 않는 사원입니다' 출력
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS,0)
        INTO EID, ENAME, SALARY, BONUS
        FROM EMPLOYEE
    WHERE EMP_ID = 201;
    
    DBMS_OUTPUT.PUT_LINE('사번: ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름: ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여: ' || SALARY);
    IF BONUS = 0
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    END IF;
    DBMS_OUTPUT.PUT_LINE('보너스율: ' || BONUS*100 || '%');
END;
/

2) IF 조건식 THEN 실행내용 ELSE 실행내용 END IF; (IF ELSE문)

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS,0)
        INTO EID, ENAME, SALARY, BONUS
        FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    DBMS_OUTPUT.PUT_LINE('사번: ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름: ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여: ' || SALARY);
    IF BONUS = 0
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('보너스율: ' || BONUS*100 || '%');
    END IF ;
END;
/

*실습문제*

/*
    레퍼런스 변수 : EID, ENAME, DTITLE, NCODE
    참조컬럼: EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
    일반변수: TEAM(소속)
    
    실행: 사용자가 입력한 사번의 사번, 이름, 부서명, 근무국가코드를 변수에 대입
        단, NCODE값이 KO일 경우 => TEAM 변수에 '국내팀'
            NCODE값이 KO아닐 경우 => TEAM변수에 '해외팀'
    출력: 사번, 이름, 부서명, 소속
    
*/
DECLARE 
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    NCODE LOCATION.NATIONAL_CODE%TYPE;
    TEAM VARCHAR2(20); 
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
        INTO EID, ENAME, DTITLE, NCODE
        FROM EMPLOYEE
        JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
        JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
    WHERE EMP_ID = &사번;
    
    IF NCODE = 'KO'
        THEN TEAM := '국내팀';
    ELSE 
        TEAM := '해외팀';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('사번: ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름: ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('부서명: ' || DTITLE);
    DBMS_OUTPUT.PUT_LINE('소속: ' || TEAM);
END;
/

3) IF-ELSE IF문
IF 조건식1
THEN 실행내용1
ELSIF 조건식2
THEN 실행내용2
ELSIF 조건식2
THEN 실행내용3
ELSE
실행내용4
END IF;

-- 사용자로부터 점수를 입력받아 학점 출력
-- 변수1 == 점수, 변수2 == 학점
DECLARE
    SCORE NUMBER;
    GRADE VARCHAR2(1);
BEGIN
    SCORE := &점수;
    IF SCORE >= 90 THEN GRADE := 'A';
    ELSIF SCORE >= 80 THEN GRADE := 'B';
    ELSIF SCORE >= 70 THEN GRADE := 'C';
    ELSIF SCORE >= 60 THEN GRADE := 'D';
    ELSE GRADE := 'F';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('당신의 점수는 ' || SCORE || '점이고, 학점은 ' || GRADE || '학점입니다');
END;
/

*실습문제*

/*
    사용자에게 입력받은 사번의 사원의 급여를 조회하여 SAL변수에 대입 
    500만 이상이면 '고급'
    300만 이상이면 '중급'
    300만 미만이면 '초급'
    
    출력: 해당 사원의 급여 등급은 ??입니다.
*/
DECLARE
    SAL EMPLOYEE.SALARY%TYPE;
    SALGRADE VARCHAR2(20);
    EID EMPLOYEE.EMP_ID%TYPE;
BEGIN
    SELECT SALARY, EMP_ID
        INTO SAL, EID
        FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    IF SAL >= 5000000 THEN SALGRADE := '고급';
    ELSIF SAL >= 3000000 THEN SALGRADE := '중급';
    ELSE SALGRADE := '초급';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('해당 사원의 급여 등급은 ' || SALGRADE || '입니다.');
END;
/

4) CASE 비교대상자
WHEN 비교할값1 THEN 실행내용1
WHEN 비교할값2 THEN 실행내용2
WHEN 비교할값3 THEN 실행내용3
ELSE 실행내용4
END;

    - SWITCH문과 비교했을 때
    SWITCH(변수) (        -> CASE
        CASE ?? :        -> WHEN
            실행내용;       ->THEN
        CASE ?? :        
            실행내용;
        DEFAULT :       ->ELSE
            실행내용;
    )
    

DECLARE
    EMP EMPLOYEE%ROWTYPE; --전체행 가져오기
    DNAME VARCHAR2(30);
BEGIN
    SELECT *
        INTO EMP
        FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    DNAME := CASE EMP.DEPT_CODE
        WHEN 'D1' THEN '인사관리부'
        WHEN 'D2' THEN '회계관리부'
        WHEN 'D3' THEN '마케팅부'
        WHEN 'D4' THEN '국내영업부'
        WHEN 'D8' THEN '기술지원부'
        WHEN 'D9' THEN '총무부'
        ELSE '해외영업부'
    END;
    
    DBMS_OUTPUT.PUT_LINE(EMP.EMP_NAME || '은(는) ' || DNAME || '입니다');
END;
/

LOOP:

1) BASIC LOOP문

[표현식]

LOOP
반복문으로 실행할 구문;
빠져나갈 수 있는 구문;
END LOOP;

* 반복을 빠져나오는 조건문 2가지 
1) IF 조건식 THEN EXIT; END IF;
2) EXIT WHEN 조건식;

-- 1~5까지 1씩 증가하면서 출력
-- 1) IF 조건식으로
DECLARE
    I NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
        I := I+1;
        
        IF I = 6 THEN EXIT;
        END IF;
    END LOOP;
END;
/

-- 2) EXIT로
DECLARE
    I NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
        I := I+1;
        
        EXIT WHEN I=6;
    END LOOP;
END;
/

2) FOR LOOP문 \[표현식] > FOR 변수 IN [REVERSE] 초기값..최종값 LOOP 반복해서 실행할 구문; END LOOP;

BEGIN
    FOR I IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

--REVERSE
BEGIN
    FOR I IN REVERSE 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

DROP TABLE TEST;

CREATE TABLE TEST(
    TNO NUMBER PRIMARY KEY,
    TDATE DATE
);

CREATE SEQUENCE SEQ_TNO
    INCREMENT BY 2;

BEGIN
    FOR I IN 1..100 --행 100개
    LOOP
        INSERT INTO TEST VALUES(SEQ_TNO.NEXTVAL, SYSDATE);
    END LOOP;
END;
/
SELECT * FROM TEST;

3) WHILE LOOP문: 그냥 LOOP문으로 해도 됨

[표현식]

WHILE 반복문이 실행될 조건
LOOP
반복적으로 실행할 구문;
END LOOP;

DECLARE
    I NUMBER := 1;
BEGIN
    WHILE I < 6
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
        I := I+1;
    END LOOP;
END;
/

예외처리부:

예외(EXCEPTION): 실행 중 발생하는 오류

[표현식]

EXCEPTION
WHEN 예외명1 THEN 예외처리구문1;
WHEN 예외명2 THEN 예외처리구문2;
WHEN OTHERS(부모) THEN 예외처리구문3;

    --자바에서 예외처리 비교
    TRY {
        예외가 날 상황
    } CATCH(예외명) {          -- WHEN
        실행구문                --THEN
    } CATCH(EXCEPTION(부모)) { --OTHERS
        실행구문
    }
    
* 시스템 예외(오라클에서 미리 정의해둔 예외)
- NO_DATA_FOUND: SELECT한 결과가 하나도 없는 경우
- TOO_MANY_ROWS: SELECT한 결과 여러행일 경우
- ZERO_DIVIDE: 0으로 나눌 때
- DUP_VAL_ON_INDEX: UNIQUE 제약조건에 위배됐을 때
...

-- 0으로 나눌 때
DECLARE
    RESULT NUMBER;
BEGIN
    RESULT := 10/&숫자;
    DBMS_OUTPUT.PUT_LINE('결과: ' || RESULT);
EXCEPTION
    --WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('0으로 나눌 수 없습니다');
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('0으로 나눌 수 없습니다');
END;
/

--UNIQUE 제약조건 위배
BEGIN 
    UPDATE EMPLOYEE
    SET EMP_ID = '&변경할사번' -- EMP_ID가 문자열이면 수정값도 문자열로 해야됨
    WHERE EMP_NAME = '홍정보';
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다');
END;
/

-- NO_DATA_FOUND와 TOO_MANY_ROWS 예외처리
--사수 201번 1명, 200번 여러명, 202번 없음
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME
    INTO EID, ENAME
    FROM EMPLOYEE
    WHERE MANAGER_ID = &사수사번;
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
EXCEPTION
    --WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('많은 행이 조회됩니다');
    --WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('조회 결과가 없습니다');
    WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('많은 행이 조회되거나 조회 결과가 없습니다');
END;
/

*연습문제*

-- 짝수 구구단 출력

--1) FOR LOOP
BEGIN
    FOR I IN 2..9
    LOOP
        IF MOD(I,2) = 0 THEN -- 여기선 MOD함수 사용
            FOR J IN 1..9
            LOOP
                DBMS_OUTPUT.PUT_LINE(I || '*' || J  || '=' || I*J);
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('');
        END IF;
    END LOOP;
END;
/
--2) WHILE LOOP
/*DECLARE
    I NUMBER := 2;
    J NUMBER;
BEGIN
    WHILE I <= 9
    LOOP
        J := 1;
        IF MOD(I,2) = 0 THEN
            WHILE J <= 9
            LOOP
                DBMS_OUTPUT.PUT_LINE(I || '*' || J  || '=' || I*J);
                J := J+1;
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('');
        END IF; 
        I := I+1;      
    END LOOP;
END;
/*/

DECLARE
    I NUMBER := 2;
    J NUMBER;   --여기서 초기화를 시켜주면 J가 10일 때 WHILE 조건에 해당이 안 돼서 반복 못 함
BEGIN
    WHILE I <= 9
    LOOP
        J := 1; -- LOOP안에 초기화를 시켜줘야 WHILE 조건문에 해당해서 8단까지 나올 수 있음
            WHILE J <= 9
            LOOP
                DBMS_OUTPUT.PUT_LINE(I || '*' || J  || '=' || I*J);
                J := J+1;
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('');
        I := I+2;      
    END LOOP;
END;
/

TRIGGER:

내가 지정한 테이블에 DML문에 의해 변경사항이 생겼을 때
자동으로 매번 실행할 내용을 미리 정의해 둘 수 있는 객체

EX) 회원탈퇴시 기존의 회원 테이블의 데이터 DELETE 후 곧바로 탈퇴된 회원만 따로 보관하는 테이블에 자동으로 INSERT처리
신고횟수가 일정 수를 넘었을 때 묵시적으로 해당 회원을 블랙리스트로 자동 처리
입출고에 대한 데이터가 입고, 출고가 되었을 때 자동으로 재고수량을 수정 처리

• 트리거 종류
  -SQL문의 실행시기에 따른 분류

      > BEFORE TRIGGER: 명시한 테이블에 이벤트가 발생되기 전에 트리거 실행
      > AFTER TRIGGER: 명시한 테이블에 이벤트가 발생한 후에 트리거 실행
      

  -SQL문에 의해 영향을 받는 각 행에 따른 분류

      > STATEMENT TRIGGER(문장트리거): 이벤트가 발생한 SQL문에 대해 딱 한번만 트리거 실행
      > ROW TRIGGER(행 트리거): 해당 SQL문 실행할 때마다 매번 트리거 실행
                              (FOR EACH ROW 옵션 기술해야됨)
                              > : OLD -기존컬럼에 들어있던 데이터
                              > : NEW - 새로 들어온 데이터
                              

• 트리거 생성 구문
  [표현식]

  CREATE [OR REPLACE] TRIGGER 트리거명
  BEFORE|AFTER INSERT|UPDATE|DELETE ON 테이블명
  [FOR EACH ROW][DECLARE 변수선언;]
  BEGIN
  실행내용
  [EXCEPTION 예외처리구문;]
  END;
  /

• 트리거 삭제

  DROP TRIGGER 트리거명;

-- EMPLOYEE 테이블에 새로운 행이 INSERT 될 때마다 자동으로 메시지 출력하는 트리거 정의
CREATE OR REPLACE TRIGGER TRG_01
AFTER INSERT ON EMPLOYEE 
BEGIN
    DBMS_OUTPUT.PUT_LINE('신입사원님 환영합니다');
END;
/

SET SERVEROUT ON;

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE)
    VALUES(700,'오상진','971120-1234567','J2',SYSDATE);
    
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE)
    VALUES(701,'황민현','951223-1234567','J3',SYSDATE);
    
-- 상품 입고 및 출고가 되면 재고수량이 변경되도록 하는 예
--1. 상품에 대한 데이터를 보관할 테이블(TB_PRODUCT) 
CREATE TABLE TB_PRODUCT(
    PCODE NUMBER PRIMARY KEY, --상품번호
    PNAME VARCHAR2(30) NOT NULL, --상품명
    BRAND VARCHAR2(30) NOT NULL, --브랜드명
    STOCK_QUANT NUMBER DEFAULT 0 -- 재고수량
);

-- 상품번호에 넣을 시퀀스(SEQ_PCODE)
CREATE SEQUENCE SEQ_PCODE
START WITH 200
INCREMENT BY 5;

--샘플 데이터
INSERT INTO TB_PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '갤럭시20','삼성', DEFAULT);
INSERT INTO TB_PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '아이폰13','애플', 10);
INSERT INTO TB_PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '갤럭시워치','삼성', 20);

COMMIT;

--2. 입고 테이블(TB_PROSTOCK)
CREATE TABLE TB_PROSTOCK(
    TCODE NUMBER PRIMARY KEY,    --입고번호
    PCODE NUMBER REFERENCES TB_PRODUCT,  --상품번호
    TDATE DATE,                        --상품입고일
    STOCK_COUNT NUMBER NOT NULL,        --입고수량
    STOCK_PRICE NUMBER NOT NULL         --입고단가
);

--입고번호에 넣을 시퀀스(SEQ_TCODE)
CREATE SEQUENCE SEQ_TCODE;

-- 3. 판매 테이블(TB_PROSALE)
CREATE TABLE TB_PROSALE(
    SCODE NUMBER PRIMARY KEY,    --판매번호
    PCODE NUMBER REFERENCES TB_PRODUCT, --상품번호
    SDATE DATE,                          --판매일
    SALE_COUNT NUMBER NOT NULL,         --판매수량
    SALE_PRICE NUMBER NOT NULL          --판매단가
);

-- 판매번호에 넣을 시퀀스(SEQ_SCODE)
CREATE SEQUENCE SEQ_SCODE;

-- 200번 상품 입고 10개
INSERT INTO TB_PROSTOCK VALUES(SEQ_TCODE.NEXTVAL, 200, SYSDATE, 10, 900000);

-- TB_PRODUCT 테이블의 200번 상품의 재고수량을 10 증가
UPDATE TB_PRODUCT
SET STOCK_QUANT = STOCK_QUANT + 10
WHERE PCODE = 200;

COMMIT;

-- 210번 상품이 오늘날짜로 5개 출고
INSERT INTO TB_PROSALE VALUES(SEQ_SCODE.NEXTVAL, 210, SYSDATE, 5, 500000);

UPDATE TB_PRODUCT
SET STOCK_QUANT = STOCK_QUANT - 5
WHERE PCODE = 210;
COMMIT;

트리거 정의

-- TB_PROSTOCK 테이블에 입고(INSERT) 이벤트 발생시
/*
    - 해당 상품을 찾아서 재고수량 증가 UPDATE
    UPDATE TB_PRODUCT
    SET STOCK_QUANT = STOCK_QUANT + 현재 입고된 수량(INSERT시 STOCK_COUNT값)
    WHERE PCODE = 입고된 상품의 번호(INSERT시 PCODE값);
*/
CREATE OR REPLACE TRIGGER TRG_STOCK
AFTER INSERT ON TB_PROSTOCK
FOR EACH ROW
BEGIN 
    UPDATE TB_PRODUCT
    SET STOCK_QUANT = STOCK_QUANT + :NEW.STOCK_COUNT
    WHERE PCODE = :NEW.PCODE;
END;
/

--205번 상품이 오늘날짜로 5개 입고
INSERT INTO TB_PROSTOCK
    VALUES(SEQ_TCODE.NEXTVAL, 205, SYSDATE, 5, 1000000);
    
--210번 상품이 오늘날짜로 100개 입고
INSERT INTO TB_PROSTOCK
    VALUES (SEQ_TCODE.NEXTVAL, 210, SYSDATE, 100, 1000000);
    
-- TB_PROSALE테이블에 출고 INSERT 이벤트 발생시 트리거 
/*
    해당 상품을 찾아서 재고수량 감소 UPDATE
    UPDATE TB_PRODUCT
    SET STOCK_QUANT = STOCK_QUANT - 현재출고된수량(INSERT시 SALE_COUNT값)
    WHERE PCODE = 출고된 상품번호(INSERT시 PCODE값);
*/
CREATE OR REPLACE TRIGGER TRG_SALE
AFTER INSERT ON TB_PROSALE
FOR EACH ROW
BEGIN 
    UPDATE TB_PRODUCT
    SET STOCK_QUANT = STOCK_QUANT - :NEW.SALE_COUNT
    WHERE PCODE = :NEW.PCODE;
END;
/

-- 출고
INSERT INTO TB_PROSALE VALUES(SEQ_SCODE.NEXTVAL, 200, SYSDATE, 5, 1100000);
INSERT INTO TB_PROSALE VALUES(SEQ_SCODE.NEXTVAL, 210, SYSDATE, 50, 500000);

--출고시 재고수량이 부족할 경우 출고가 안 되게하는 트리거
/*
    * 사용자 함수 예외처리
    RAISE_APPLICATION_ERROR([에러코드],[에러메시지])
    -에러코드: -20000 ~ -20999 사이의 코드
*/
CREATE OR REPLACE TRIGGER TRG_SALE
BEFORE INSERT ON TB_PROSALE     --재고수량이 있나 없나 확인해야해서 BEFORE
FOR EACH ROW
DECLARE
    SCOUNT NUMBER;
BEGIN 
    SELECT STOCK_QUANT
    INTO SCOUNT
    FROM TB_PRODUCT
    WHERE PCODE = :NEW.PCODE;
    
    IF(SCOUNT >= :NEW.SALE_COUNT)
        THEN 
            UPDATE TB_PRODUCT
            SET STOCK_QUANT = STOCK_QUANT - :NEW.SALE_COUNT
            WHERE PCODE = :NEW.PCODE;
    ELSE
        RAISE_APPLICATION_ERROR(-20001, '재고수량 부족으로 판매할 수 없음');
    END IF;
END;
/

-- 재고수량 부족으로 판매할 수 없음(20001)의 오류 발생
INSERT INTO TB_PROSALE VALUES(SEQ_SCODE.NEXTVAL, 200, SYSDATE, 10, 1100000);

-- 입고수량을 수정하는 트리거
CREATE OR REPLACE TRIGGER TRG_UPDATE
AFTER UPDATE ON TB_PROSTOCK
FOR EACH ROW
BEGIN
    UPDATE TB_PRODUCT
    SET STOCK_QUANT = STOCK_QUANT - :OLD.STOCK_COUNT + :NEW.STOCK_COUNT
    --처음 입고 10개 추가(기존 재고량 10개인 상태) => 입고량 수정 5개로 => 총 15개
    -- 현재 재고 20개 - 잘못 추가한 10개 + 수정한 입고량 5개
    WHERE PCODE = :NEW.PCODE;
END;
/

UPDATE TB_PROSTOCK
SET STOCK_COUNT = 50
WHERE TCODE = 3; -- PCODE는 계속해서 행이 증가되는 데이터이기 때문에 중복이므로 TCODE로 해야함

-- TB_PROSTOCK테이블에서 삭제하는 트리거
CREATE OR REPLACE TRIGGER TRG_DELETE
AFTER DELETE ON TB_PROSTOCK
FOR EACH ROW
BEGIN
    UPDATE TB_PRODUCT
    SET STOCK_QUANT = STOCK_QUANT - :OLD.STOCK_COUNT
    WHERE PCODE = :NEW.PCODE;
END;
/

입출력을 한 테이블에 저장

-- 하나의 트리거로 입/출력 발생시 모두 할 수 있도록

-- 4. 상품의 입출고 테이블(TB_PRODETAIL)
CREATE TABLE TB_PRODETAIL(
    DECODE NUMBER PRIMARY KEY,
    PCODE NUMBER REFERENCES TB_PRODUCT,
    DDATE DATE,
    AMOUNT NUMBER NOT NULL, --입출고 수량
    STATUS CHAR(6) CHECK(STATUS IN('입고','출고'))
);
--시퀀스
CREATE SEQUENCE SEQ_DCODE;

-- 입출력시 트리거 생성
CREATE OR REPLACE TRIGGER TRG_PRO
AFTER INSERT ON TB_PRODETAIL
FOR EACH ROW
BEGIN
    --상품이 '입고'이면 재고수량 증가
    IF(:NEW.STATUS = '입고')
        THEN 
            UPDATE TB_PRODUCT
            SET STOCK_QUANT = STOCK_QUANT + :NEW.AMOUNT
            WHERE PCODE = :NEW.PCODE;
    END IF;
    --상품이 '출고'이면 재고수량 감소
    IF(:NEW.STATUS = '출고')
        THEN 
            UPDATE TB_PRODUCT
            SET STOCK_QUANT = STOCK_QUANT - :NEW.AMOUNT
            WHERE PCODE = :NEW.PCODE;
    END IF;
END;
/
INSERT INTO TB_PRODETAIL VALUES(SEQ_DCODE.NEXTVAL, 210, SYSDATE, 10, '입고');
INSERT INTO TB_PRODETAIL VALUES(SEQ_DCODE.NEXTVAL, 210, SYSDATE, 10, '출고');

-- 수정시 트리거
CREATE OR REPLACE TRIGGER TRG_ADD
AFTER UPDATE ON TB_PRODETAIL
FOR EACH ROW
BEGIN
    --입고
    IF(:NEW.STATUS = '입고')
        THEN
            UPDATE TB_PRODUCT
            SET STOCK_QUANT = STOCK_QUANT - :OLD.STOCK_COUNT + :NEW.STOCK_COUNT
            WHERE PCODE = :NEW.PCODE;
    END IF;
    --출고
    IF(:NEW.STATUS = '출고')
        THEN
            UPDATE TB_PRODUCT
            SET STOCK_QUANT = STOCK_QUANT - :OLD.SALE_COUNT + :NEW.SALE_COUNT
            WHERE PCODE = :NEW.PCODE;
    END IF;        
END;
/

--삭제시 트리거
CREATE OR REPLACE TRIGGER TRG_DEL
AFTER DELETE ON TB_PRODETAIL
FOR EACH ROW
BEGIN
    UPDATE TB_PRODUCT
    SET STOCK_QUANT = STOCK_QUANT - :OLD.STOCK_COUNT
    WHERE PCODE = :NEW.PCODE;
END;
/