014. [PL/SQL] PROCEDURE(프로시저),EXCEPTION,TRANSACTION

2023.04.10 (월)

SCOTT 계정 실습

• PROCEDURE(프로시저) 실습 (지난주에 이어서...)

--○ TBL_SUNGJUK 테이블에서
--   특정 학생의 점수(학번, 국어점수, 영어점수, 수학점수)
--   데이터 수정 시 총점, 평균, 등급까지 수정되는 프로시저를 작성한다.
--   프로시저 명 : PRC_SUNGJUK_UPDATE
/*
실행 예)
EXEC PRC_SUNGJUK_UPDATE('2312120',100,100,100);

→ 프로시저 호출로 처리된 결과)
학번        이름    국어점수 영어점수 수학점수 총점 평균 등급
2312120   	연수양	100	     100	  100	   300	100	 A
*/

CREATE OR REPLACE PROCEDURE PRC_SUNGJUK_UPDATE
( V_HAKBUN  IN TBL_SUNGJUK.HAKBUN%TYPE
, V_KOR     IN TBL_SUNGJUK.KOR%TYPE
, V_ENG     IN TBL_SUNGJUK.ENG%TYPE
, V_MAT     IN TBL_SUNGJUK.MAT%TYPE
)
IS
    -- UPDATE 쿼리문을 수행하는데 필요한 주요 변수 선언
    V_TOT   TBL_SUNGJUK.TOT%TYPE;
    V_AVG   TBL_SUNGJUK.AVG%TYPE;
    V_GRADE TBL_SUNGJUK.GRADE%TYPE;
BEGIN
    -- 아래의 쿼리문 실행을 위해서는
    -- 선언한 변수들에 값을 담아내야 한다. (V_TOT, V_AVG, V_GRADE)
    V_TOT := V_KOR + V_ENG + V_MAT;
    V_AVG := V_TOT/3 ;
    
    IF (V_AVG >=90)
        THEN V_GRADE := 'A';
    ELSIF (V_AVG >=80)
        THEN V_GRADE := 'B';
    ELSIF (V_AVG >=70)
        THEN V_GRADE := 'C';
    ELSIF (V_AVG >=60)
        THEN V_GRADE := 'D';
    ELSE
        V_GRADE := 'F';
    END IF;
    
    -- UPDATE 쿼리문 구성
    UPDATE TBL_SUNGJUK
    SET KOR = V_KOR, ENG = V_ENG, MAT = V_MAT
      , TOT = V_TOT , AVG = V_AVG , GRADE = V_GRADE 
    WHERE HAKBUN = V_HAKBUN;
    
    -- 커밋
    COMMIT;
END;
--==>> Procedure PRC_SUNGJUK_UPDATE이(가) 컴파일되었습니다.

--○ 생성한 프로사저(→PRC_SUNGJUK_UPDATE)가 제대로 작동하는지의 여부 확인
--   → 프로시저 호출 

EXEC PRC_SUNGJUK_UPDATE ('2312120',100,100,100);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_SUNGJUK;
--==>> 2312120   	연수양	100	100	100	300	100	A

---

--○ TBL_STUDENTS 테이블에서
--   전화번호와 주소 데이터를 수정하는(변경하는) 프로시저를 작성한다
--   단, ID 와 PW 가 일치하는 경우에만 수정을 진행할 수 있도록 한다.
--   프로시저 명 : PRC_STUDENTS_UPDATE
/*
실행 예)
EXEC PRC_STUDENTS_UPDATE('superman','java002$','010-9999-8888','경기 일산');

→ 프로시저 호출로 처리된 결과)
superman    	재혁킴	010-9999-888	경기 일산
*/

SELECT *
FROM
(
    SELECT I.ID, I.PW, S.TEL, S.ADDR
    FROM TBL_IDPW I JOIN TBL_STUDENTS S
    ON I.ID = S.ID
) T
WHERE T.ID = 입력받은아이디
  AND T.PW = 입력받은패스워드;
  
SELECT *
FROM
(
    SELECT I.ID, I.PW, S.TEL, S.ADDR
    FROM TBL_IDPW I JOIN TBL_STUDENTS S
    ON I.ID = S.ID
) T
WHERE T.ID = 'superman'
  AND T.PW = 'java002$';
--==>> superman	java002$	010-1111-1111	제주도 서귀포시

※ JOIN 의 결과도 UPDATE 가능 
---------------------------------------------------------------------------------------
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 쿼리문 구성
    /*
    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이(가) 컴파일되었습니다.

-- ID 가 다를 때
EXEC PRC_STUDENTS_UPDATE('batman','java002$','010-9999-8888','경기 일산');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_STUDENTS;
--==>> 변화 없음

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

SELECT *
FROM TBL_STUDENTS;
--==>> 변화 없음

-- ID, PW 가 일치할 때
EXEC PRC_STUDENTS_UPDATE('superman','java002$','010-9999-8888','경기 일산');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_STUDENTS;
--==>>
/*
superman	    재혁킴	010-9999-8888	경기 일산
happyday	서영유	010-2222-2222	서울 마포구
*/

---

--○ TBL_INSA 테이블을 대상으로 신규 데이터 입력 프로시저를 작성한다.
--   NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG
--   구조를 갖고 있는 대상 테이블에 데이터 입력 시
--   NUM 항목(사원번호)의 값은
--   기존 부여된 사원번호 마지막 번호의 그 다음 번호를
--   자동으로 입력 처리할 수 있는 프로시저로 구성한다.
--   프로시저 명 : PRC_INSA_INSERT()
/*
실행 예)
EXEC PRC_INSA_INSERT('희주윤', '990215-2234567', SYSDATE, '서울', '010-7670-8649', '영업부', '대리', 5000000, 500000);

→ 프로시저 호출로 처리된 결과
1061    희주윤 990215-2234567  2023-04-10 서울 010-7670-8649 영업부 대리 5000000 500000
*/

※ 쿼리문의 값을 대입할 경우 INTO 를 통해서 대입

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
BEGIN
    -- INSERT 쿼리문 작성
    INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
    VALUES ((SELECT MAX(NUM)
            FROM TBL_INSA) + 1, V_NAME, V_SSN, TO_DATE(TO_CHAR(V_IBSADATE,'YYYY-MM-DD')), V_CITY, V_TEL, V_BUSEO, V_JIKWI, V_BASICPAY, V_SUDANG);
END;
--==>> Procedure PRC_INSA_INSERT이(가) 컴파일되었습니다.
----------------------------------------------------------------------------------------------------------------------------------------------------
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   TBL_INSA.NUM%TYPE;
BEGIN

    --NUM 
    SELECT MAX(NUM) + 1 INTO V_NUM
    FROM TBL_INSA;
    
    -- INSERT 쿼리문 작성
    INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
    VALUES (V_NUM, V_NAME, V_SSN, TO_DATE(TO_CHAR(V_IBSADATE,'YYYY-MM-DD')), V_CITY, V_TEL, V_BUSEO, V_JIKWI, V_BASICPAY, V_SUDANG);
END;
--==>> Procedure PRC_INSA_INSERT이(가) 컴파일되었습니다.
----------------------------------------------------------------------------------------------------------------------------------------------------
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
    VINSA   TBL_INSA%ROWTYPE;
BEGIN

    --NUM 
    SELECT MAX(NUM) + 1 INTO VINSA.NUM
    FROM TBL_INSA;
    
    -- INSERT 쿼리문 작성
    INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
    VALUES (VINSA.NUM, V_NAME, V_SSN, TO_DATE(TO_CHAR(V_IBSADATE,'YYYY-MM-DD')), V_CITY, V_TEL, V_BUSEO, V_JIKWI, V_BASICPAY, V_SUDANG);
END;

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
    -- INSERT 쿼리문 수행에 필요한 변수 추가 선언
    -- → V_NUM
    V_NUM   TBL_INSA.NUM%TYPE;
BEGIN
    -- 아래 쿼리문을 수행하기 위해
    -- 선언한 변수 (→ V_NUM)  에 값 담아내기 
    SELECT MAX(NUM)+1 INTO V_NUM
    FROM TBL_INSA;
    
    -- INSERT 쿼리문 구성
    INSERT INTO TBL_INSA (NUM, NAME, SSN, IBSADATE, CITY, TEL, BUSEO, JIKWI, BASICPAY, SUDANG)
    VALUES ( V_NUM , V_NAME, V_SSN, TO_DATE(TO_CHAR(V_IBSADATE,'YYYY-MM-DD')), V_CITY, V_TEL, V_BUSEO, V_JIKWI, V_BASICPAY, V_SUDANG);
    
    COMMIT;
END;
--==>> Procedure PRC_INSA_INSERT이(가) 컴파일되었습니다.

--○ 생성한 프로사저(→PRC_INSA_INSERT)가 제대로 작동하는지의 여부 확인
--   → 프로시저 호출 
EXEC PRC_INSA_INSERT('희주윤', '990215-2234567', SYSDATE, '서울', '010-7670-8649', '영업부', '대리', 5000000, 500000);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_INSA;
--==>>
/*
                            :
1061	희주윤	990215-2234567	2023-04-10	서울	010-7670-8649	영업부	대리	5000000	500000
                            :
*/

---

• 실습 테이블 생성 및 데이터 입력

 

•  

--○ TBL_상품, TBL_입고 테이블을 대상으로
--   TBL_입고 테이블에 데이터 입력 시 (즉, 입고 이벤트 발생 시)
--   TBL_ 상품 테이블의 재고수량이 함께 변동될 수 있는 기능을 가진
--   프로시저를 작성한다.
--   단, 이 과정에서 입고 번호는 자동 증가 처리한다. (시퀀스 사용 X)
--   TBL_입고 테이블 구성 컬럼
--   → 입고번호, 상품코드, 입고일자, 입고수량, 입고단가
--  프로시저 명 : PRC_입고_INSERT()
/*
EXEC PRC_입고_INSERT('H001',30,1300);

→ 프로시저 호출로 처리된 결과
① TBL_입고
    1   H001    2023-04-10  30  1300

② TBL_상품
        :
H001	홈런볼	1500	30
        :
*/

CREATE OR REPLACE PROCEDURE PRC_입고_INSERT
( V_상품코드      IN TBL_입고.상품코드%TYPE
, V_입고수량      IN TBL_입고.입고수량%TYPE
, V_입고단가      IN TBL_입고.입고단가%TYPE
)
IS
    -- 입고테이블에 사용할 변수
    -- → V_입고번호
    V_입고번호  TBL_입고.입고번호%TYPE;
BEGIN
    -- 입고번호 순차 증가
    SELECT COUNT(*) + 1 INTO V_입고번호
    FROM TBL_입고;

    -- 입고테이블 INSERT 구문
    INSERT INTO TBL_입고(입고번호, 상품코드, 입고수량, 입고단가) VALUES(V_입고번호, V_상품코드, V_입고수량, V_입고단가);
    
    -- 상품테이블 UPDATE 구문
    -- 입고수량 UPDATE
    
    UPDATE TBL_상품
    SET 재고수량 = 재고수량 + V_입고수량
    WHERE 상품코드 = V_상품코드;
    
    --○ 커밋
    COMMIT;
END;
--==>> Procedure PRC_입고_INSERT이(가) 컴파일되었습니다.
--------------------------------------------------------------------------------------------------------------------------------
EXEC PRC_입고_INSERT('H001',30,1300);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_입고;
--==>> 1	H001	2023-04-10	30	1300

SELECT *
FROM TBL_상품;
--==>> 
/*
    :
H001	홈런볼	1500	30
    :
*/

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

SELECT *
FROM TBL_입고;
--==>>
/*
1	H001	2023-04-10	30	1300
2	H001	2023-04-10	30	1300
*/
SELECT *
FROM TBL_상품;
--==>>
/*
        :
H001	홈런볼	1500	60
        :
*/

CREATE OR REPLACE PROCEDURE PRC_입고_INSERT
( V_상품코드    IN TBL_상품.상품코드%TYPE
, V_입고수량    IN TBL_입고.입고수량%TYPE
, V_입고단가    IN TBL_입고.입고단가%TYPE
)
IS
    -- 아래 쿼리문을 수행하기 위해 필요한 변수 선언
    V_입고번호  TBL_입고.입고번호%TYPE;
BEGIN
    -- 선언한 변수(V_입고번호)에 값 담아내기
    SELECT NVL(MAX(입고번호),0) INTO V_입고번호
    FROM TBL_입고;
    
    -- 쿼리문 구성
    -- ① INSERT → TBL_입고
    INSERT INTO TBL_입고(입고번호, 상품코드, 입고수량, 입고단가)
    VALUES((V_입고번호+1),V_상품코드,V_입고수량,V_입고단가);
    
    -- ② UPDATE → TBL_상품
    UPDATE TBL_상품
    SET 재고수량 = 재고수량 + V_입고수량
    WHERE 상품코드=V_상품코드;

    -- 커밋
    COMMIT;

--// 프로시저 내에 DML 구문이 2개 이상 -> 절차대로 진행
--// 첫번째 DML 구문을 수행할 때 오류가 나게 되면, 두번째 DML 구문이 실행이 안되게 되면?
--// ▶ 따라서 두가지 구문이 같이 정상적으로 수행되지 않으면 ROLL BACK 되고,
--// 두가지 구문이 정상적으로 모두 수행된다면 COMMIT 을 하게 하는 것을 트랜젝션이라고 하며
--// TCL구문(COMMIT, ROLLBACK)의 T가 TRANSACTION(트랜잭션)

    -- 예외처리
    EXCEPTION
        WHEN OTHERS THEN ROLLBACK;
--// 위의 코드에서 다른 상황이 발생한다면(즉, 문제가 생긴다면) ROLLBACK 해라    
END;

 

--○ 생성한 프로시저(→ PRC_입고_INSERT) 가 제대로 작동하는지의 여부 확인
--   → 프로시저 호출

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

SELECT *
FROM TBL_입고;
--==>> 1	H001	2023-04-10	30	1300

SELECT *
FROM TBL_상품;
--==>> 
/*
H001	홈런볼	1500	30
    :
*/

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

SELECT *
FROM TBL_입고;
--==>>
/*
1	H001	2023-04-10	30	1300
2	H001	2023-04-10	30	1300
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	60
        :
*/

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

SELECT *
FROM TBL_입고;
--==>>
/*
1	H001	2023-04-10	30	1300
2	H001	2023-04-10	30	1300
3	H002	2023-04-10	20	1000
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	60
H002	새우깡	1200	20
        :
*/

---

• 프로시저 내에서의 예외 처리

• 실습 테이블 생성

--○ 실습 테이블 생성(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이(가) 생성되었습니다.

--■■■ 프로시저 내에서의 예외 처리 ■■■--

--○ 실습 테이블 생성(TBL_MEMBER) → 20230410_02_scott.sql 파일 참조

--○ TBL_MEMBER 테이블에 데이터를 입력하는 프로시저를 생성
--   단, 이 프로시저를 통해 데이터를 입력할 경우
--   CITY(지역) 항목에 '서울', '인천', '경기' 만 입력이 가능하도록 구성한다.
--   이 지역 외의 지역을 프로시저 호출을 통해 입력하려는 경우
--   예외 처리를 하려고 한다.
--   프로시저 명 : PRC_MEMBER_INSERT(이름, 전화번호, 지역)

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;
    
    -- 사용자 정의 예외에 대한 변수 선언!!! CHECK!!!
    USER_DEFINE_ERROR   EXCEPTION;  --// 변수이름   EXCEPTION타입
BEGIN
    -- 프로시저를 통해 입력 처리를 정상적으로 진행해야 할 데이터인지
    -- 아닌지의 여부를 가장 먼저 확인할 수 있도록 코드 구성
    IF (V_CITY NOT IN('서울','인천','경기'))  -- '서울','인천','경기' 중 하나가 들어있지 않다면...
        -- 예외 발생!!! CHECK!!!
        --// 우리가 직접 발생시키는 예외이기 때문에 사용자 정의 예외라고 볼 수 있으며 -- 예외도 변수 필요
        THEN RAISE  USER_DEFINE_ERROR;  --// 에러를 일으키겠다(발생시키겠다) 『RAISE』
    END IF;

    -- 선언한 변수에 값 담아내기
    SELECT NVL(MAX(NUM),0)+1 INTO V_NUM
    FROM TBL_MEMBER;

    -- 쿼리문 구성(INSERT)
    INSERT INTO TBL_MEMBER(NUM,NAME,TEL,CITY)
    VALUES(V_NUM , V_NAME, V_TEL, V_CITY);
    
    -- 커밋
    COMMIT;
    
    -- 예외 처리 
    --// RAISE 한 예외(자바의 THROW 와 같기 때문에 잡아주는 것이 필요)
    --// 예외도 여러개며 이를 여러가지 방식으로 처리를 할 수 있다
    /*
    EXCEPTION
        WHEN   이런 예외라면...
            THEN    이렇게 처리하고
        WHEN    저런 예외라면...
            THEN    저렇게 처리해라...
        ↓
    EXCEPTION
        WHEN   USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-에러코드, 에러내용기술);  --// 함수
        WHEN    저런 예외라면...
            THEN    저렇게 처리해라...;
        ↓
    EXCEPTION
        WHEN   USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-에러코드, 에러내용기술);  
        WHEN   OTHERS           --// 사용자가 규정하지 않은 예외일 때
            THEN    ROLLBACK;   --// 롤백해라~
        ↓
    EXCEPTION
        WHEN   USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-에러코드, 에러내용기술);
                    ROLLBACK;   --// 롤백도 같이 할 수 있음
        WHEN   OTHERS           
            THEN    ROLLBACK;   
    */
    EXCEPTION
        WHEN   USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-20001, '서울,인천,경기만 입력 가능합니다.'); 
                                        --// 2만번까지는 오라클 내부에서 사용하는 에러코드
                    ROLLBACK;
        WHEN   OTHERS          
            THEN    ROLLBACK;   
END;
--==>> Procedure PRC_MEMBER_INSERT이(가) 컴파일되었습니다.

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

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

EXEC PRC_MEMBER_INSERT('준복이','010-2222-2222','인천');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

EXEC PRC_MEMBER_INSERT('히정권','010-3333-3333','경기');
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_MEMBER;
--==>>
/*
1	수현양	010-1111-1111	서울
2	준복이	010-2222-2222	인천
3	히정권	010-3333-3333	경기
*/

EXEC PRC_MEMBER_INSERT('하경이','010-4444-4444','부산');
--==>> 에러 발생
/*
ORA-20001: 서울,인천,경기만 입력 가능합니다.
*/

EXEC PRC_MEMBER_INSERT('수인주','010-5555-5555','대구');
--==>> 에러 발생
/*
ORA-20001: 서울,인천,경기만 입력 가능합니다.
*/

SELECT *
FROM TBL_MEMBER;
--==>>
/*
1	수현양	010-1111-1111	서울
2	준복이	010-2222-2222	인천
3	히정권	010-3333-3333	경기
*/

---

• 실습 테이블 생성

--○ 실습 테이블 생성(TBL_출고)
CREATE TABLE TBL_출고
( 출고번호      NUMBER
, 상품코드      VARCHAR2(20)
, 출고일자      DATE    DEFAULT SYSDATE
, 출고수량      NUMBER
, 출고단가      NUMBER
);
--==>> Table TBL_출고이(가) 생성되었습니다.

--○ TBL_출고 테이블의 상품코드 TBL_상품 테이블의 상품코드를
--   참조할 수 있도록 외래키(FK) 제약조건 지정
ALTER TABLE TBL_출고
ADD (CONSTRAINT 출고_상품코드_FK FOREIGN KEY(상품코드)
                REFERENCES TBL_상품(상품코드));
--==>> Table TBL_출고이(가) 변경되었습니다.

--○ TBL_출고 테이블의 출고번호에 PK 제약조건 지정
ALTER TABLE TBL_출고
ADD CONSTRAINT 출고_출고번호_PK PRIMARY KEY(출고번호);
--==>> Table TBL_출고이(가) 변경되었습니다.

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

CREATE OR REPLACE PROCEDURE PRC_출고_INSERT
( V_상품코드    IN  TBL_상품.상품코드%TYPE
, V_출고수량    IN  TBL_출고.출고수량%TYPE
, V_출고단가    IN  TBL_출고.출고수량%TYPE
)
IS
    -- INSERT 쿼리문 작성에 필요한 변수
    V_출고번호  TBL_출고.출고번호%TYPE;
    -- 재고수량 변수
    V_재고수량  TBL_상품.재고수량%TYPE;
    -- 사용자정의 에러 변수 선언
    USER_DEFINE_ERROR   EXCEPTION;
BEGIN
    -- 재고수량 파악
    SELECT 재고수량 INTO V_재고수량
    FROM TBL_상품
    WHERE 상품코드 = V_상품코드;

    -- 출고수량이 재고수량보다 많은지 확인 먼저 필요
    IF (V_출고수량 > V_재고수량)
        THEN RAISE USER_DEFINE_ERROR;
    END IF;

    -- 작성한 변수 V_출고번호에 변수 대입
    SELECT NVL(MAX(출고번호),0) +1 INTO V_출고번호
    FROM TBL_출고;

    -- INSERT 쿼리문 작성
    INSERT INTO TBL_출고(출고번호, 상품코드, 출고수량, 출고단가)
    VALUES (V_출고번호, V_상품코드, V_출고수량, V_출고단가);
    
    -- UPDATE 쿼리문 작성
    UPDATE TBL_상품
    SET 재고수량 = 재고수량 - V_출고수량
    WHERE 상품코드 = V_상품코드;
    
    -- 커밋
    COMMIT;
    
    -- 던진 예외 잡아주기
    EXCEPTION
        WHEN    USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-20002,'재고수량이 부족합니다.');
                    ROLLBACK;
        WHEN    OTHERS
            THEN    ROLLBACK;
            
END;
--==>> Procedure PRC_출고_INSERT이(가) 컴파일되었습니다.
----------------------------------------------------------------------------------------------
--○ 생성한 프로시저(→ PRC_출고_INSERT) 가 제대로 작동하는지의 여부 확인
--   → 프로시저 호출

EXEC PRC_출고_INSERT('H002', 10, 1800);

SELECT *
FROM TBL_출고;
--==>>
/*
1	H002	2023-04-10	10	1800
*/

EXEC PRC_출고_INSERT('H001', 800, 2000);
--==>> 에러 발생
/*
ORA-20002: 재고수량이 부족합니다
*/

CREATE OR REPLACE PROCEDURE PRC_출고_INSERT
( V_상품코드    IN  TBL_상품.상품코드%TYPE
, V_출고수량    IN  TBL_출고.출고수량%TYPE
, V_출고단가    IN  TBL_출고.출고수량%TYPE
)
IS
    -- 주요 변수 선언
    V_출고번호 TBL_출고.출고번호%TYPE;
    V_재고수량 TBL_상품.재고수량%TYPE;
    
    USER_DEFINE_ERROR   EXCEPTION;  -- 사용자 정의 예외
    
BEGIN
    -- 쿼리문 수행 이전에 수행 여부 확인 → 기존 재고 확인 → 출고 수량과 비교
    SELECT 재고수량 INTO V_재고수량
    FROM TBL_상품
    WHERE 상품코드 = V_상품코드 ;
    
    -- 출고를 정상적으로 진행해 줄 것인지 아닌지에 대한 여부 확인
    -- (해당 상품에 대해 파악한 재고수량보다 출고수량이 많으면... 예외 발생)
    IF (V_출고수량 > V_재고수량)
        THEN RAISE USER_DEFINE_ERROR;
    END IF;

    -- 선언한 변수에 값 담아내기
    SELECT NVL(MAX(출고번호),0)   INTO V_출고번호
    FROM TBL_출고;

    -- 쿼리문 구성(INSERT → TBL_출고)
    INSERT INTO TBL_출고(출고번호, 상품코드, 출고수량, 출고단가)
    VALUES ((V_출고번호+1), V_상품코드, V_출고수량, V_출고단가);
    
    -- 쿼리문 구성(UPDATE → TBL_상품)
    UPDATE TBL_상품
    SET 재고수량 = 재고수량 - V_출고수량
    WHERE 상품코드 = V_상품코드;
    
    -- 커밋
    COMMIT;

    -- 예외 처리
    EXCEPTION
        WHEN    USER_DEFINE_ERROR   --//CHECKED EXCEPTION 으로 생각
            THEN    RAISE_APPLICATION_ERROR(-20002,'재고가 부족합니다.');
                    ROLLBACK;
        WHEN    OTHERS              --//UNCHECKED EXCEPTION
            THEN    ROLLBACK;
   
END;
--==>> Procedure PRC_출고_INSERT이(가) 컴파일되었습니다.

--○ 생성한 프로시저(→ PRC_출고_INSERT) 가 제대로 작동하는지의 여부 확인
--   → 프로시저 호출

EXEC PRC_출고_INSERT('H001', 30, 1500);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	10
        :
*/
SELECT *
FROM TBL_출고;
--==>>
/*
1	H001	2023-04-10	30	1500
*/

EXEC PRC_출고_INSERT('H001', 30, 1500);
--==>> 에러 발생
/*
ORA-20002: 재고가 부족합니다.
*/