(52)[oracle]17일차: ((PL/SQL)) PACKAGE

1. 23.11.08(수)

1. 20231108_01_scott(qlsql).sql

SELECT USER
FROM DUAL;
--==>> SCOTT

/* 1-과제&문제 & 함께 푼 내용 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
--○ TBL_출고 테이블의 데이터 입력, 수정, 삭제 시
--   TBL_상품 테이블의 해당 상품에 대한 재고 수량 변동 트리거 작성
--   트리거명 : TRG_CHULGO
--   제출 파일명 : 오라클_트리거_오수경.sql
--   재고 부족 관련 예외 처리
CREATE OR REPLACE TRIGGER TRG_CHULGO
    AFTER
    INSERT OR UPDATE OR DELETE ON TBL_출고
    FOR EACH ROW
DECLARE
    -- 선언구문
    V_재고수량      TBL_상품.재고수량%TYPE;

    USER_DEFINE_ERROR EXCEPTION;
BEGIN
    -- 실행구문
    IF (INSERTING)
        THEN
            -- 기존 재고수량
            SELECT 재고수량 INTO V_재고수량
            FROM TBL_상품 
            WHERE 상품코드 = :NEW.상품코드;
            
            -- 예외처리 조건
            IF(V_재고수량 - :NEW.출고수량 < 0)
                THEN RAISE USER_DEFINE_ERROR;
            END IF;
            
            -- 업데이트 쿼리
            UPDATE TBL_상품
            SET 재고수량 = 재고수량 - :NEW.출고수량
            WHERE 상품코드 = :NEW.상품코드;
            
    ELSIF (UPDATING)
        THEN
            -- 기존 재고수량
            SELECT 재고수량 INTO V_재고수량
            FROM TBL_상품 
            WHERE 상품코드 = :OLD.상품코드;
            
            -- 예외처리 조건
            IF(V_재고수량 + :OLD.출고수량 - :NEW.출고수량 < 0)
                THEN RAISE USER_DEFINE_ERROR;
            END IF;
            
            -- 업데이트 쿼리
            UPDATE TBL_상품
            SET 재고수량 = 재고수량 + :OLD.출고수량 - :NEW.출고수량
            WHERE 상품코드 = :NEW.상품코드;
            
    ELSIF (DELETING)
        THEN
            -- 업데이트 쿼리
            UPDATE TBL_상품
            SET 재고수량 = 재고수량 + :OLD.출고수량
            WHERE 상품코드 = :OLD.상품코드;
    END IF;
    
    -- 예외처리
    EXCEPTION
        WHEN USER_DEFINE_ERROR
            THEN RAISE_APPLICATION_ERROR(-20002, '재고부족~!!!');
END;


--■■■ PACKAGE(패키지) ■■■--
/*
1. PL/SQL 의 패키지는 관계되는 타입, 프로그램 객체, 서브 프로그램(PROCEDURE, FUNCTION 등)을 논리적으로 묶어놓은 것이다.  
    오라클에서 제공하는 패키지 중 하나가 바로 <DBMS_OUTPUT>이다.  
    
2. 패키지는 서로 유사한 업무에 사용되는 여러 개의 프로시저와 함수를 하나의 패키지로 만들어 관리함으로써  
    향후 유지보수가 편리하고 전체 프로그램을 모듈화 할 수 있다는 장점이 있다.  

3. 패키지는 명세부(PACKAGE SPECIFICATION)와 몸체부(PACKAGE BODY)로 구성되어 있으며  
    명세 부분에는 TYPE, CONSTRAINT, VARIABLE, EXCEPTION, XURSOR, SUBPROGRAM 이 선언되고  
    몸체 부분에는 이들의 실제 내용이 존재한다.  
    그리고, 호출할 때에는 <패키지명. 프로시저명>과 같은 형식의 참조를 이용해야 한다.  
    
4. 형식 및 구조(명세부)  
*/
/*
-- *상자안에 어떤 내용이 있는지 라벨을 붙이는 것*  
-- *따라서 명세부-몸체부의 패키지명은 같아야 함*  
CRAEATE [OR REPLACE] PACKAGE 패키지명
IS
    전역변수  선언;
    커서 선언;
    예외 선언;
    함수 선언;
    프로시저 선언;
        :
END 패키지명;
*/
/*
5. 형식 및 구조(몸체부)
*/
/*
CREATE [OR REPLACE] PACKAGE BODY 패키지명
IS
    FUNCTION 함수명[(인수,...)]
    RETURN 자료형
    IS
        변수 선언;
    BEGIN
        함수 몸체 구성 코드;
        RETURN 값;
    END;
    
    PROCEDURE 프로시저명[(인수, ...)]
    IS
        변수 선언;
    BEGIN
        프로시저 몸체 구성 코드;
    END;
END 패키지명;
    
*/

-- 패키지 등록 실습
-- (1) 명세부 작성
CREATE OR REPLACE PACKAGE INSA_PACK     -- *명세부라는 특정 단어X*  
IS
    FUNCTION FN_GENDER(V_SSN VARCHAR2)
    RETURN VARCHAR2;
    
END INSA_PACK;
--==>> Package INSA_PACK이(가) 컴파일되었습니다.


-- (2) 몸체부 작성  
-- *뼈대...나머지는 함수 정의와 같음~*  
CREATE OR REPLACE PACKAGE BODY INSA_PACK     -- *BODY: 몸체부라는 특정 단어O* 
IS
    FUNCTION FN_GENDER(V_SSN VARCHAR2)      
    RETURN VARCHAR2
    IS
    BEGIN
    END;
    
END INSA_PACK;                               -- *END BODY 아님!*  


CREATE OR REPLACE PACKAGE BODY INSA_PACK
IS
    FUNCTION FN_GENDER(V_SSN VARCHAR2)      
    RETURN VARCHAR2
    IS
        V_RESULT VARCHAR2(20);
    BEGIN
        IF (SUBSTR(V_SSN,8,1) IN ('1','3'))
            THEN V_RESULT := '남자';
        ELSIF (SUBSTR(V_SSN,8,1) IN ('2','4'))
            THEN V_RESULT := '여자';
        ELSE
            V_RESULT := '확인불가';
        END IF;
        
        RETURN V_RESULT;
    END;
    
END INSA_PACK;
--==>> Package Body INSA_PACK이(가) 컴파일되었습니다.

 

2. 20231108_02_scott.sql

SELECT USER
FROM DUAL;
--==>> SCOTT

-- ■■■ AFTER ROW TRIGGER 상황 실습 ■■■--
SELECT *
FROM TBL_입고;

SELECT *
FROM TBL_출고;

SELECT *
FROM TBL_상품;
/*
H001	바밤바	600	60
H002	죠스바	500	0
H003	보석바	500	0
H004	누가바	600	0
H005	쌍쌍바	700	0
H006	수박바	500	0
H007	알껌바	500	0
C001	빵빠레	1600	0
C002	월드콘	1500	0
C003	메타콘	1500	0
C004	구구콘	1600	0
C005	슈퍼콘	1700	0
E001	빵또아	2600	0
E002	투게더	2500	0
E003	팥빙수	2500	0
E004	셀렉션	2600	0
E005	설레임	2700	0
*/

INSERT INTO TBL_출고(출고번호, 상품코드, 출고일자, 출고수량, 출고단가)
VALUES(1, 'H001',SYSDATE, 60, 1000);

SELECT *
FROM TBL_출고;
/*
1	H001	2023-11-08	60	1000
*/

SELECT *
FROM TBL_상품;
/*
H001	바밤바	600	0
*/

INSERT INTO TBL_출고(출고번호, 상품코드, 출고일자, 출고수량, 출고단가)
VALUES(2, 'H001',SYSDATE, 50, 1000);
--==>> 에러 발생 (ORA-20002: 재고부족~!!!)

UPDATE TBL_출고
SET 출고수량 = 30
WHERE 출고번호 = 1;
--==>> 1 행 이(가) 업데이트되었습니다.

SELECT *
FROM TBL_출고;
/*
1	H001	2023-11-08	30	1000
*/

SELECT *
FROM TBL_상품;
/*
H001	바밤바	600	30
*/

DELETE 
FROM TBL_출고
WHERE 출고번호 = 1;
--==>> 1 행 이(가) 삭제되었습니다.

SELECT *
FROM TBL_상품;
/*
H001	바밤바	600	60
*/

INSERT INTO TBL_출고(출고번호, 상품코드, 출고일자, 출고수량, 출고단가)
VALUES(1, 'H001',SYSDATE, 20, 1000);


--■■■ PACKAGE(패키지) ■■■--
--○ 패키지 활용 실습
SELECT INSA_PACK.FN_GENDER('801007-1544236') "함수호출결과"
FROM DUAL;
--==>> 남자

SELECT NAME, SSN, INSA_PACK.FN_GENDER(SSN) "함수호출결과"
FROM TBL_INSA;
--==>>
/*
홍길동	771212-1022432	남자
이순신	801007-1544236	남자
이순애	770922-2312547	여자
            :
김신애	810809-2111111	여자
최혜인	970812-2234567	여자
*/

---

오라클 공식 수업 종료~!!!!