[ ORACLE ] PROCEDURE 내에서 예외처리

SELECT *
FROM TBL_IDPW;
/*
ID	        PW
superman	    java006$
happyday	java006$
*/

SELECT *
FROM TBL_STUDENTS;
/*
ID	        NAME	TEL	ADDR
superman	    박정준	010-9999-8888	경기 김포
happyday	김서현	010-2222-2222	서울 마포구
*/


SELECT I.ID, I.PW, S.TEL, S.ADDR
FROM TBL_IDPW I JOIN TBL_STUDENTS S
ON I.ID = S.ID;

/*
superman    	java006$	010-9999-9999	경기 일산
happyday	java006$	010-2222-2222	서울 마포구
*/

UPDATE(SELECT I.ID, I.PW, S.TEL, S.ADDR
        FROM TBL_IDPW I JOIN TBL_STUDENTS S
        ON I.ID = S.ID)T
SET T.TEL=입력받은 전화번호, T.ADDR = 입력받은주소
WHERE T.ID = 입력받은아이디 AND T.PW = 입력받은패스워드;

데이터 줄을 맞춰도 복붙만 하면 왜이렇게 되는지 ㅠ

어쨌든 이렇게 tbl_idpw 와 tbl_students 를 조인했다.

 

 

CREATE OR REPLACE PROCEDURE PRC_STUDENTS_UPDATE
( V_ID      IN TBL_IDPW.ID%TYPE 
, V_PW      IN TBL_IDPW.PW%TYPE
, V_TEL     IN TBL_STUDENTS.TEL%TYPE
, V_ADDR    IN TBL_STUDENTS.ADDR%TYPE
)

IS
BEGIN
    /*
    UPDATE(SELECT I.ID, I.PW, S.TEL, S.ADDR
        FROM TBL_IDPW I JOIN TBL_STUDENTS S
        ON I.ID = S.ID)T
        SET T.TEL=입력받은 전화번호, T.ADDR = 입력받은주소
        WHERE T.ID = 입력받은아이디 AND T.PW = 입력받은패스워드;*/
        UPDATE(SELECT I.ID, I.PW, S.TEL, S.ADDR
--            FROM TBL_IDPW I JOIN TBL_STUDENTS S
            ON I.ID = S.ID)T
    SET T.TEL=V_TEL, T.ADDR = V_ADDR
    WHERE T.ID = V_ID AND T.PW = V_PW;
    
    COMMIT;
END;
--==>> Procedure PRC_STUDENTS_UPDATE이(가) 컴파일되었습니다.

아이디, 패스워드, 전화번호, 주소를 받아서

입력받은 아이디, 입력받은 패스워드가 기존의 조인된 테이블의 그것들과 일치하면

전화번호와 주소를 업데이트 해주는 프로시저이다. 

 

-- 생성한 PRC_STUDENTS_UPDATE 가 제대로 작동하는지의 여부 확인

EXEC PRC_STUDENTS_UPDATE('superman','net007$','010-9999-1111','경기 김포');
--==>>PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_STUDENTS;

/*
superman	박정준	010-9999-9999	경기 일산
happyday	김서현	010-2222-2222	서울 마포구
*/



EXEC PRC_STUDENTS_UPDATE('superman','java006$','010-9999-8888','경기 김포');
--==>>PL/SQL 프로시저가 성공적으로 완료되었습니다.

/*
ID	       		 NAME	TEL	            ADDR
superman		박정준	010-9999-8888	경기 김포
happyday		김서현	010-2222-2222	서울 마포구
*/

*/
--===>> 정확한 ID/PW 를 입력한 결과 데이터가 변경되었다.

위와 같이 아이디는 맞지만 비밀번호가 틀린 경우에는 프로시저가 성공적으로 완료되었다고는 하지만

업데이트 하고자 하는 내용이 반영되지 않는다.

정확한 비밀번호를 입력했을 때는 전화번호와 주소 모두 정상적으로 업데이트 되었다.

 

 

 

-- ● TBL_INSA 테이블을 대상으로 신규 데이터 입력 프로시저를 작성한다.
/*
NUM	NAME	SSN	IBSADATE	CITY	TEL	BUSEO	JIKWI	BASICPAY	SUDANG
구조를 갖고 있는 대상 테이블에 데이터 입력 시 NUM 항목(사원번호) 의 값은
기존 부여된 사원번호 마지막 번호의 그 다음 번호를 자동으로 입력 처리할 수 있는
프로시저로 구성한다.
프로시저명 : PRC_INSA_INSERT(NAME,SSN,IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)

실행 예)
EXEC PRC_INSA_INSERT('한혜림','971006-2222222',SYSDATE, '서울','010-5555-5555','영업부','대리',5000000,500000);

프로시저로 호출된 결과 NUM이 1061부터 하나씩 입력될 수 있도록 한다. (지금 테이블에서는 1061부터 들어가도록)
시퀀스 만드는 것 아님*/

SELECT *
FROM TBL_INSA;



CREATE OR REPLACE PROCEDURE PRC_INSA_INSERT
( V_NAME        IN TBL_INSA.NAME%TYPE 
, V_SSN         IN TBL_INSA.SSN%TYPE
, V_IBSADATE    IN TBL_INSA.IBSADATE%TYPE
, V_CITY        IN TBL_INSA.CITY%TYPE
, V_TEL         IN TBL_INSA.TEL%TYPE
, V_BUSEO       IN TBL_INSA.BUSEO%TYPE
, V_JIKWI       IN TBL_INSA.JIKWI%TYPE
, V_BASICPAY    IN TBL_INSA.BASICPAY%TYPE
, V_SUDANG      IN TBL_INSA.SUDANG%TYPE
)

IS
    V_NUM     NUMBER;
BEGIN
    -- NUM 의 최대값 V_NUM에 담기
    SELECT MAX(NUM) INTO V_NUM
    FROM TBL_INSA;

    -- INSERT 구문
    INSERT INTO TBL_INSA(NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
    VALUES (V_NUM+1,V_NAME, V_SSN, V_IBSADATE, V_CITY, V_TEL, V_BUSEO, V_JIKWI, V_BASICPAY, V_SUDANG);

    --COMMIT
    COMMIT;


END;
--==>>Procedure PRC_INSA_INSERT이(가) 컴파일되었습니다.

 

TBL_INSA 에는 현재 NUM이 1060인 레코드가 마지막이다. 여기에 새로운 직원 데이터를 넣는다고 했을 때

 NUM=1061, 그 다음에는 또 NUM=1062 ... 이렇게 들어가도록 하는 프로시저를 작성한 것이다. (NUM을 매개변수로 넘겨주지 않아도 자동으로)

여기서 핵심은 MAX(NUM) 의 값을 새롭게 선언한 지역변수처럼 자체선언한 V_NUM 에 초기화시키고

그 값에 +1 해준 값을 NUM의 자리에 넣어 데이터를 INSERT 해주는 것이다. 

 

실행한 결과는 아래와 같다. 

----------------------------------------------------------
EXEC PRC_INSA_INSERT('한혜림','971006-2222222',SYSDATE, '서울','010-5555-5555','영업부','대리',5000000,500000);
--==>>PL/SQL 프로시저가 성공적으로 완료되었습니다.
EXEC PRC_INSA_INSERT('박민민','999999-2222222',SYSDATE, '서울','010-5555-5555','영업부','대리',5000000,500000);
--==>>PL/SQL 프로시저가 성공적으로 완료되었습니다.


SELECT *
FROM TBL_INSA;
/*
NUM	    NAME	SSN	            IBSADATE	CITY	TEL	        BUSEO	JIKWI	BASICPAY	SUDANG
1060	김신애	810809-2111111	2001-10-10	서울	011-4151-4444	개발부	사원	900000	    102000
1061	한혜림	971006-2222222	2021-04-09	서울	010-5555-5555	영업부	대리	5000000	    500000
1062	박민민	999999-2222222	2021-04-09	서울	010-5555-5555	영업부	대리	5000000	    500000
*/

 

 

 

===========================

-- ● 실습 테이블 생성(TBL_상품) --한글로 만들지만 실무에서 절대 이렇게 하지말자 실습을 위한 테이블

CREATE TABLE TBL_상품
( 상품코드      VARCHAR2(20)
, 상품명        VARCHAR2(100)
, 소비자가격    NUMBER
, 재고수량      NUMBER  DEFAULT 0 --(NULL이면 아무리 더해져도 NULL 이므로)
, CONSTRAINT 상품_상품코드_PK PRIMARY KEY(상품코드)
);
--==>>Table TBL_상품이(가) 생성되었습니다.



-- -- ● 실습 테이블 생성(TBL_입고)
CREATE TABLE TBL_입고
( 입고번호  NUMBER
, 상품코드  VARCHAR2(20)
, 입고일자  DATE DEFAULT SYSDATE
, 입고수량  NUMBER
, 입고단가  NUMBER
, CONSTRAINT 입고_입고번호_PK PRIMARY KEY(입고번호)
, CONSTRAINT 입고_상품코드_FK FOREIGN KEY(상품코드) REFERENCES TBL_상품(상품코드)
);
--==>>Table TBL_입고이(가) 생성되었습니다.

-- TBL_입고 테이블의 입고번호를 기본키(PK) 제약조건 설정
-- TBL_입고 테이블의 상품코드는 TBL_상품 테이블의 상품코드를 참조할 수 
-- 있도록 외래키(FK) 제약조건 설정

실습을 위해서 두 개의 테이블을 생성(TBL_상품, TBL_입고)

여기서 주의할 점은 우선 실무에서는 TABLE명에 한글을 쓰지 않으니 이것은 실습만을 위한 테이블이라는 점,

그리고 재고수량의 경우 실습을 진행하면서 계속 값을 더하거나 뺄 것이므로 DEFAULT 값으로 NULL이 아닌 0을 준 점이다. 

 

 

----------------------------------------------------------------------------------------
-- ● TBL_상품, TBL_입고 테이블을 대상으로...
-- TBL_입고 테이블에 데이터 입력 시 (즉, 입고 이벤트 발생 시)
-- TBL_상품 테이블의 재고수량이 함께 변동될 수 있는 기능을 가진
-- 프로시저를 작성한다.
-- 단, 이 과정에서 입고번호는 자동 증가 처리한다. (시퀀스 사용 X)
-- 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; 

BEGIN

    -- TBL_입고 업데이트하기
    SELECT NVL(MAX(입고번호),0)+1 INTO V_입고번호 FROM TBL_입고;
    INSERT INTO TBL_입고(입고번호, 상품코드, 입고수량, 입고단가) 
    VALUES (V_입고번호, V_상품코드,V_입고수량, V_입고단가);
    -- TBL_상품 재고 업데이트하기
    UPDATE TBL_상품
    SET 재고수량 = 재고수량 + V_입고수량
    WHERE 상품코드 = V_상품코드;
    
    
    -- 커밋
    COMMIT;
    
    -- 예외처리
    /*EXCEPTION
        WHEN OTHERS THEN ROLLBACK;*/
    EXCEPTION
        WHEN OTHERS THEN ROLLBACK;
END;

--Procedure PRC_입고_INSERT이(가) 컴파일되었습니다.

입고번호는 위 실습과 마찬가지로 MAX(입고번호)+1 로 마지막 입고번호에서 하나 증가한 값으로 데이터가 입력될 수 있도록 하였다. 다만 지금 입고테이블에 데이터가 없는 상태이므로 이 상태에서 MAX(입고번호)를 하게 되면 NULL이 된다.

SELECT MAX(NULL)
FROM DUAL;
--==>>(null)

NULL 에 1을 더하면 결국 또 NULL 이 되므로 NVL(MAX(입고번호),0) 을 통해 NULL값이라면 0으로 바꿔줘야 한다.

 

SELECT *
FROM TBL_상품;
/*
상품코드	상품명	소비자가격	재고수량
H001	홈런볼	1500	        0
H002	새우깡	1200	        0
H003	자갈치	1000	        0
H004	감자깡	900	            0
H005	꼬깔콘	1100	        0
H006	꼬북칩	2000	        0
H007	맛동산	1700	        0
C001	다이제	2000	        0
C002	사브레	1800	        0
C003	에이스	1700	        0
C004	버터링	1900	        0
C005	아이비	1600	        0
C006	웨하스	1200	        0
C007	오레오	1900	        0
E001	엠엔엠	600	            0
E002	아폴로	500	            0
E003	쫀드기	300	            0
E004	비틀즈	600	            0
E005	마이쮸	800	            0
E006	에그몽	900	            0
E007	차카니	900	            0
*/


COMMIT;
--==>>커밋 완료.



-- 생성한 PROCEDURE 가 제대로 작동하는지의 여부 확인

EXEC PRC_입고_INSERT('H001',20,900);

EXEC PRC_입고_INSERT('H001',20,900);

EXEC PRC_입고_INSERT('H001',40,900);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT * 
FROM TBL_입고;
/*
입고번호	상품코드	입고일자	입고수량	입고단가
1		H001	21/04/09		20  	    900
2		H001	21/04/09		20  	    900
3		H001	21/04/09		40	        900
*/

SELECT *
FROM TBL_상품;
/*
상품코드	상품명소비자가격재고수량
H001	홈런볼	1500	80
H002	새우깡	1200	0
H003	자갈치	1000	0
H004	감자깡	900	    0
H005	꼬깔콘	1100	0
H006	꼬북칩	2000	0
H007	맛동산	1700	0
C001	다이제	2000	0
C002	사브레	1800	0
C003	에이스	1700	0
C004	버터링	1900	0
C005	아이비	1600	0
C006	웨하스	1200	0
C007	오레오	1900	0
E001	엠엔엠	600	    0
E002	아폴로	500	    0
E003	쫀드기	300	    0
E004	비틀즈	600	    0
E005	마이쮸	800	    0
E006	에그몽	900	    0
E007	차카니	900	    0
*/

생성한 프로시저를 통해서 H001 상품을 20,20,40 입고수량을 증가시켰을 때

TBL_상품의 재고수량도 80으로 증가한 것을 확인할 수 있다.

 

 

-- ■■■  프로시저 내에서의 예외 처리  ■■■
-- 실습 테이블 생성 TBL_MEMBER

-- ●TBL_MEMBER 테이블에 데이터를 입력하는 프로시저를 생성
-- 단, 이 프로시저를 통해 데이터를 입력할 경우
-- CITY(지역) 항목에 '서울','경기','인천'만 입력이 가능하도록 구성한다.


-- 이 지역 외의 지역을 프로시저 호출을 통해 입력하려는 경우
-- 예외처리를 하려고 한다.
-- 프로시저명 : PRC_MEMBER_INSERT(이름, 전화번호, 지역)

 

-- ● 테이블 생성(TBL_MEMBER)
CREATE TABLE TBL_MEMBER
( NUM   NUMBER
, NAME  VARCHAR2(30)
, TEL   VARCHAR2(60)
, CITY  VARCHAR2(60)
, CONSTRAINT MEMBER_NUM_PK PRIMARY KEY(NUM)
);

--==>>Table TBL_MEMBER이(가) 생성되었습니다.

실습을 위해 테이블을 생성했다. 번호, 이름, 전화번호, 지역(번호가 PK) 으로 구성된 TBL_MEMBER

 

CREATE OR REPLACE PROCEDURE PRC_MEMBER_INSERT
( V_NAME    IN TBL_MEMBER.NAME%TYPE
, V_TEL     IN TBL_MEMBER.TEL%TYPE
, V_CITY    IN TBL_MEMBER.CITY%TYPE
)
IS
    V_NUM TBL_MEMBER.NUM%TYPE;
    -- 사용자 정의 예외에 대한 변수 선언★★★★
    USER_DEFINE_ERROR/*←예외이름*/ EXCEPTION;  
    
BEGIN
    -- 프로시저를 통해 입력 처리를 정상적으로 진행해야 할 데이터인지 아닌지
    -- 여부를 가장 먼저 확인할 수 있도록 코드 구성
    
    IF(V_CITY NOT IN ('서울','경기','인천'))  --서울인천경기 중 하나가 들어있지 않다면
        -- 예외 발생 ★★★
        THEN RAISE USER_DEFINE_ERROR;
        
    END IF;
    
    
    -- 선언한 변수에 값 담아내기
    SELECT NVL(MAX(NUM),0)+1 INTO V_NUM -- 0이 담기거나 최대값이 담길 것
    FROM TBL_MEMBER;
    
    -- 쿼리문 구성(INSERT)
    
    
    INSERT INTO TBL_MEMBER(NUM, NAME, TEL,CITY)
    VALUES(V_NUM, V_NAME, V_TEL,V_CITY);
    

    EXCEPTION
        --WHEN 이런 예외라면 THEN 이렇게 처리하고 WHEN 저런예외라면 THEN 저렇게 처리해라
        /*WHEN USER_DEFINE_ERROR 
            THEN RAISE_APPLICATION_ERROR(-에러코드, 에러내용기술); -- 에러를띄워주는함수 에러코드를 부여하고 에러내용을기술하게된다*/
        WHEN USER_DEFINE_ERROR
            THEN RAISE_APPLICATION_ERROR(-20001, '서울,인천,경기만 입력 가능합니다.');
            ROLLBACK;
        WHEN OTHERS --(예를 들어 코드 실행 도중 PC전원이 꺼지거나, 다른 수많은 예외사항들...)
            THEN ROLLBACK;
    
END;
--==>>Procedure PRC_MEMBER_INSERT이(가) 컴파일되었습니다.

 

USER_DEFINE_ERROR(임의의 예외이름) EXCEPTION; 으로 사용자 정의 예외 변수 선언해주고

IF-END IF; 문 안에서 조건 맞는다면 RAISE USER_DEFINE_ERROR 한다.

만약 에러가 RAISE 됐다면 EXCEPTION 예외처리영역으로 간다 (JAVA의 CATCH와 비슷)

 

사용자 정의 에러가 발생했다면 THEN RAISE_APPLICATION_ERROR() 함수로 에러코드를 부여하고

에러의 내용을 기술하게 된다.

그리고 ROLLBACK 해준다. 여기서 사실 왜 ROLLBACK을 하게 되는지 이해가 잘 안된다.

 

 

-- 생성한 프로시저(PRC_MEMBER_INSERT)가 제대로 작동하는지의 여부 확인

EXEC PRC_MEMBER_INSERT(이름, 전화번호, 지역)

EXEC PRC_MEMBER_INSERT('정주희','010-1111-1111','서울');
--==>>PL/SQL 프로시저가 성공적으로 완료되었습니다.

EXEC PRC_MEMBER_INSERT('소서서','010-1212-1212','경기');
EXEC PRC_MEMBER_INSERT('김아아','010-1331-1331','인천');

--ROLLBACK;

SELECT *
FROM TBL_MEMBER;
/*
NUM	NAME	TEL	            CITY
1	정주희	010-1111-1111	서울
2	소서서	010-1212-1212	경기
3	김아아	010-1331-1331	인천
*/




EXEC PRC_MEMBER_INSERT('이라라','010-1234-1234','부산');
/*
BEGIN PRC_MEMBER_INSERT('이라라','010-1234-1234','부산'); END;
오류 보고 -
ORA-20001: 서울,인천,경기만 입력 가능합니다.
ORA-06512: at "SCOTT.PRC_MEMBER_INSERT", line 39
ORA-06512: at line 1
*/
EXEC PRC_MEMBER_INSERT('박밍밍','010-1235-1235','대전');
--==>> 대전도 위와 같이 에러가 발생한다. 

위에서 작성한 프로시저를 EXECUTE 해보면 서울, 경기, 인천을 입력한 경우에는 TBL_MEMBER 에 INSERT 가 되지만

그렇지 않고 부산이나 대전을 입력한 경우에는 RAISE_APPLICATION_ERROR에서 지정한 에러코드와 에러메세지가 출력된다. 

 

-- ● 실습 테이블 생성(TBL_출고)

CREATE TABLE TBL_출고
( 출고번호  NUMBER
, 상품코드  VARCHAR2(20)
, 출고일자  DATE DEFAULT SYSDATE
, 출고수량  NUMBER
, 출고단가  NUMBER
);

--==>>Table TBL_출고이(가) 생성되었습니다.


-- ● TBL_출고 테이블의 출고번호에 PK 제약조건 지정

ALTER TABLE TBL_출고
ADD CONSTRAINT 출고_출고번호_PK PRIMARY KEY(출고번호);
--==>>Table TBL_출고이(가) 변경되었습니다.


-- ● TBL_출고 테이블의 상품코드는 TBL_상품테이블의 상품코드를 
-- 참조할 수 있도록 외래키(FK) 제약조건 지정

ALTER TABLE TBL_출고
ADD CONSTRAINT 출고_상품코드_FK FOREIGN KEY(상품코드)
               REFERENCES TBL_상품(상품코드);
--==>> Table TBL_출고이(가) 변경되었습니다.

실습을 위해 TBL_상품, TBL_입고 에 이어서 TBL_출고 테이블도 생성했다. 

 

-- ● 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;
    USER_DEFINE_ERROR EXCEPTION;
    TEMP_재고수량   TBL_상품.재고수량%TYPE;
    
BEGIN

    -- 에러 있는 지 없는 지 확인부터 우선
    SELECT 재고수량 INTO TEMP_재고수량 FROM TBL_상품 WHERE 상품코드=V_상품코드;
    
    
    IF -- 출고수량이 재고수량보다 크다면
    V_출고수량 > TEMP_재고수량
        THEN RAISE USER_DEFINE_ERROR;
    END IF;


    -- 출고번호
    SELECT NVL(MAX(출고번호),0)+1 INTO V_출고번호
    FROM TBL_출고;
    
    -- TBL_출고에 데이터업데이트
    INSERT INTO TBL_출고 (출고번호, 상품코드, 출고수량, 출고단가)
    VALUES (V_출고번호,V_상품코드, V_출고수량, V_출고단가);
    -- TBL_상품에 데이터 업데이트
    UPDATE TBL_상품
    SET 재고수량 = 재고수량 -  V_출고수량
    WHERE 상품코드 = V_상품코드;  
    

    
    EXCEPTION
        WHEN USER_DEFINE_ERROR
            THEN RAISE_APPLICATION_ERROR(-20002, '재고수량이 부족합니다');
            ROLLBACK;
        WHEN OTHERS
            THEN ROLLBACK;
    
    
END;


---------------------------------------------------------------------------------------------

상품코드로 TBL_상품의 현재 재고수량을 받아오고 그것을 출고할 수량이랑 비교한다. 만약 재고량이 50개인데 출고를 60개 하려고 한다면 예외를 발생시켜야 하므로 예외처리를 해준다. 

 

 

-- 프로시저 PRC_출고_INSERT() 제대로 작동하는지 여부 확인
--프로시저명 : PRC_출고_INSERT(상품코드, 출고수량, 출고단가)



EXEC PRC_출고_INSERT('H001',1000,800);
/*
오류 보고 -
ORA-20002: 재고수량이 부족합니다
*/
EXEC PRC_출고_INSERT('H001',10,1000);
--PL/SQL 프로시저가 성공적으로 완료되었습니다.



/*
상품코드	상품명		소비자가격	재고수량
H001		홈런볼		1500	        70  ← 기존 80 개에서 줄어들었다.
*/

현재 홈런볼의 재고수량이 80 개인데 1000개를 출고시킨다고 했으면 재고수량이 부족하다는 에러가 출력되고

만약 10개를 출고시킨다고 한다면 80개에서 10개가 감소된 70개의 재고수량이 TBL_상품에 업데이트된다. 

 

 

 

--● TBL_출고 테이블에서 출고 수량을 변경(수정)하는 프로시저를 작성한다.

-- 프로시저 명 : PRC_출고_UPDATE(출고번호, 변경할 수량);

-- 만약 출고를 10개에서 5로 하면 재고수량이 10개라면 15개가될것이고 
-- 만약 출고를 10에서 30로 하려는데 재고가 10개이니까 안될 것이고
-- 만약 출고를 10에서 12로 하려면 재고가 10에서 8개가 될 것이다 .




CREATE OR REPLACE PROCEDURE PRC_출고_UPDATE
(V_출고번호     IN TBL_출고.출고번호%TYPE
, V_변경수량    IN TBL_상품.재고수량%TYPE
)
IS 
    USER_DEFINE_ERROR EXCEPTION;
    V_상품코드 TBL_출고.상품코드%TYPE;
    V_변경전재고 TBL_상품.재고수량%TYPE;
    V_변경전출고 TBL_출고.출고수량%TYPE;

BEGIN
    -- 출고번호와 일치하는 상품코드
    SELECT 상품코드 INTO V_상품코드
    FROM TBL_출고
    WHERE 출고번호=V_출고번호;

    -- 변경전 재고수량
    SELECT 재고수량 INTO V_변경전재고
    FROM TBL_상품
    WHERE 상품코드 = V_상품코드;
    
    -- 변경 전 출고수량
    SELECT 출고수량 INTO V_변경전출고
    FROM TBL_출고
    WHERE 출고번호=V_출고번호;
    
    -- 만약 변경수량-원래출고수량 > 재고수량이라면 재고가부족한 상황이다.
    IF V_변경수량 - V_변경전출고 > V_변경전재고
        THEN RAISE USER_DEFINE_ERROR;
    END IF;
    


    -- TBL_출고 TBL_상품 업데이트

        UPDATE TBL_출고
        SET 출고수량=V_변경수량
        WHERE 출고번호=V_출고번호;
        
        UPDATE TBL_상품
        SET 재고수량 = 재고수량 + (V_변경전출고 - V_변경수량)
        WHERE 상품코드 = V_상품코드;
    
    
    
    
    EXCEPTION
    WHEN USER_DEFINE_ERROR
        THEN RAISE_APPLICATION_ERROR(-20003, '재고수량이 부족합니다');
        ROLLBACK;
    WHEN OTHERS
        THEN ROLLBACK;
        
    -- 커밋
    COMMIT;    
    
END;

 

위의 예제와 비슷하며

 

    -- 만약 변경수량-원래출고수량 > 재고수량이라면 재고가부족한 상황이다.

    IF V_변경수량 - V_변경전출고 > V_변경전재고
        THEN RAISE USER_DEFINE_ERROR;
    END IF;

 

이 부분만 주의한다면 똑같다. 변경하려는 수량과 출고수량의 차이가 재고수량보다 크다면

(원래출고수량+재고수량 < 변경수량 이라면) RAISE USER_DEFINE_ERROR 한다.

 

 

아래는 프로시저 확인

상품 테이블에서는 지금 오레오 재고가 82개 있고 

출고 테이블에서는 출고번호 14번에서 8개를 출고하고 있는 상황이다.

창고에는 82개의 오레오가 있고, 지금 8 개는 출고하려고 따로 빼두고 준비하고 있는 상황..

(즉, 82+8 = 90개 가 출고변경이 가능한 범위이다.)

-- 프로시저 확인

--- C007  오레오가 지금 재고가 82개 있고 출고번호 14 에서는 출고 8개 하고있는상황



-- 1. 만약 재고수량보다 더 많이 출고를 하려고한다면? 8개출고에서 100개 출고한다고해보자
--PRC_출고_UPDATE(출고번호, 변경할 수량);
EXEC PRC_출고_UPDATE(14, 100);
/*
BEGIN PRC_출고_UPDATE(14, 100); END;
오류 보고 -
ORA-20003: 재고수량이 부족합니다
ORA-06512: at "SCOTT.PRC_출고_UPDATE", line 49
ORA-06512: at line 1
*/

-- 2. 출고수량을 8개에서 6개로 바꾼다면???? 재고량이 +2 되어야함 
EXEC PRC_출고_UPDATE(14, 6);

SELECT *
FROM TBL_출고;

SELECT *
FROM TBL_상품; --==>> 그 결과 출고량은 6 개가 되고 재고량은 84개가 된다.
-- 3. 출고수량을 현재 6 개에서 10개로 바꾼다면??
EXEC PRC_출고_UPDATE(14, 10);
SELECT *
FROM TBL_출고;

SELECT *
FROM TBL_상품; --==>> 그 결과 출고량은 10개가 되고 재고량은 80개가 된다.

만약에 90개 이상으로 출고수량을 바꾸려고 한다면 에러가 나고, 기존에 빼둔 8개에서 6개만 출고된다고 했으면 창고에 있는 82개에 2개가 추가된 84개가 되고 , 여기서 출고량을 또 10개로 변경한다고 해서 창고에 있는 4개를 빼오면 재고는 80이 된다. 

이렇게 4월 9일의 수업은 끝이나고,,,,