Oracle - 프로시저(4)

-- 문제 
-- ○ TBL_출고 테이블에 데이터 입력 시(즉, 출고 이벤트 발생 시)
--    TBL_상품 테이블의 해당 상품의 재고수량이 변동될 수 있는 프로시저를 작성한다.
--    단, 출고번호는 입고번호와 마찬가지로 자동증가.
--    또한, 출고수량이 재고수량보다 많은 경우...
--    출고 액션이 처리되지 않도록 구성한다.(출고가 이루어지지 않도록...)
--    프로시저명 : PRC_출고_INSERT(상품코드, 출고수량, 출고단가) 

CREATE OR REPLACE PROCEDURE PRC_출고_INSERT
( V_상품코드    IN TBL_상품.상품코드%TYPE
, V_출고수량    IN TBL_출고.출고수량%TYPE
, V_출고단가    IN TBL_출고.출고단가%TYPE
)
IS
    V_출고번호  TBL_출고.출고번호%TYPE; 
     -- 출고를 끝낸 후 재고수량 8번 수행
    V_재고수량  TBL_상품.재고수량%TYPE; 
    
    -- 사용자 정의 예외 선언
    USER_DEFINE_ERROR   EXCEPTION; -- 사용자 정의 예외 9
    
BEGIN
    
    SELECT 재고수량 INTO V_재고수량
    FROM TBL_상품
    WHERE 상품코드 = V_상품코드;
    
    -- 출고를 정상적으로 진행해 줄 것인지에 대한 여부 확인 7
    -- → 파악한 재고수량보다 출고수량이 많으면 ... 예외발생
    IF (V_출고수량 > V_재고수량)
        -- 예외 발생
        THEN RAISE USER_DEFINE_ERROR;
    END IF;
    
    SELECT NVL(MAX(출고번호), 0) + 1 INTO V_출고번호 
    FROM TBL_출고;
    
    -- 쿼리문 구성 → INSERT  → TBL_출고컬럼 인서트 (출고일자는 SYSDATE라 제외) 2
    INSERT INTO TBL_출고(출고번호, 상품코드, 출고수량, 출고단가)
    VALUES(V_출고번호, V_상품코드, V_출고수량, V_출고단가);
    
    -- 쿼리문 구성 → UPDATE  → TBL_상품  5 파라미터(매개변수)로 넘겨받은 V_출고수량 
    UPDATE TBL_상품
    SET 재고수량 = 재고수량 - V_출고수량
    WHERE 상품코드 = V_상품코드;
  
    EXCEPTION
        WHEN USER_DEFINE_ERROR
            THEN RAISE_APPLICATION_ERROR(-20002, '재고 부족~!!!');
                 ROLLBACK;
        WHEN OTHERS
            THEN ROLLBACK;
 
    -- 커밋 6 
    COMMIT;

END;

-- ○ TBL_출고 테이블에서 출고수량을 변경(수정)하는 프로시저를 작성한다.
--   프로시저명: PRC_출고_UPDATE(출고번호, 변경할수량) 

CREATE OR REPLACE PROCEDURE PRC_출고_UPDATE
( V_출고번호        IN TBL_출고.출고번호%TYPE
, V_변경할수량      IN TBL_출고.출고수량%TYPE
)
IS
   
    V_상품코드      TBL_상품.상품코드%TYPE;
    
    V_이전출고수량   TBL_출고.출고수량%TYPE;  
    
    V_재고수량       TBL_상품.재고수량%TYPE;
    
   
     USER_DEFINE_ERROR EXCEPTION;
BEGIN
 
    SELECT 상품코드, 출고수량 INTO V_상품코드, V_이전출고수량
    FROM TBL_출고
    WHERE 출고번호 = V_출고번호;
    
    SET 재고수량 INTO V_재고수량
    FROM TBL_상품
    WHERE 상품코드 = V_상품코드; 
    
    IF (V_재고수량 > V_이전출고수량) < V_출고수량)
        -- 예외발생
        THEN RAISE USER_DEFINE_ERROR3;
    END IF;

    -- UPDATE -> TBL_출고 
    UPDATE TBL_출고
    SET 출고수량 = V_출고수량
    WHERE 출고번호 =  V_출고번호;
    
    UPDATE TBL_상품
  
    SET 재고수량 = 재고수량+ V_이전출고수량 - V_출고수량
    WHERE 상품코드 = V_상품코드;
    
    COMMIT;
   
    EXCEPTION
        WHEN USER_DEFINE_ERROR
            THEN RAISE_APPLICATION_ERROR(-20003, '재고 부족~!!!');
                 ROLLBACK;
        
        WHEN OTHERS
            THEN ROLLBACK;             
END;