Notice
Recent Posts
Recent Comments
Link
«   2025/07   »
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
Archives
Today
Total
관리 메뉴

Everything has an expiration date

[Oracle - PL/SQL] 20231108 [프로그램 소스] 본문

[Oracle]/PL·SQL (Program source)

[Oracle - PL/SQL] 20231108 [프로그램 소스]

Jelly-fish 2023. 11. 9. 15:03
  • 출고 테이블의 데이터 입력, 수정, 삭제시 재고수량 변동 트리거 TBL_CHULGO
  • 패키지 (PACKAGE)

 

20231108_01_scott(plsql).sql
SELECT USER
FROM DUAL;
--==>> SCOTT

-- 현재 시트에서 만들어서 제대로 돌아가면 제출!!


--○ TBL_출고 테이블의 데이터 입력, 수정, 삭제 시
--   TBL_상품 테이블의 해당 상품에 대한 재고수량 변동 트리거 작성
--   트리거명 : TRG_CHULGO
--   제출 파일명 : 오라클_트리거_홍길동.sql
--   재고 부족 관련 예외 처리



DESC TBL_출고;

SELECT *
FROM TBL_상품;

CREATE OR REPLACE TRIGGER TBL_CHULGO
        AFTER
        INSERT OR UPDATE OR DELETE ON TBL_출고
        FOR EACH ROW
        
DECLARE
    
    USER_DEFINE_ERROR   EXCEPTION;
    V_재고수량          TBL_상품.재고수량%TYPE;
    
-- 10 LINE
BEGIN
    
    IF (INSERTING)
        THEN
        UPDATE TBL_상품
        SET 재고수량 = 재고수량 - :NEW.출고수량
        WHERE 상품코드 = :NEW.상품코드;
        
        SELECT 재고수량 INTO V_재고수량
        FROM TBL_상품
        WHERE 상품코드 = :NEW.상품코드;
        
        
        IF (V_재고수량 < 0)
            THEN RAISE USER_DEFINE_ERROR;
        END IF;
        
        
    ELSIF (UPDATING)
-- 20 LINE
        THEN
        UPDATE TBL_상품
        SET 재고수량 = 재고수량 + :OLD.출고수량 - :NEW.출고수량
        WHERE 상품코드 = :NEW.상품코드;
        
        
        SELECT 재고수량 INTO V_재고수량
        FROM TBL_상품
        WHERE 상품코드 = :NEW.상품코드;
        
        IF (V_재고수량 < 0)
            THEN RAISE USER_DEFINE_ERROR;
        END IF;
    
    
    ELSIF (DELETING)
        THEN
        UPDATE TBL_상품
        SET 재고수량 = 재고수량 + :OLD.출고수량
        WHERE 상품코드 = :OLD.상품코드;
        

    END IF;



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



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

/*
패키지 : 논리적인 묶음
어떤 파티션, 어떤 테이블 스페이스... 논리적으로 접근해서 꺼낼 수 있도록 구성한것.
편의성을 위해 구성한 것이다.

SUBPROGRAM : 프로시저, 함수

패키지 명세서와 몸체로 한 쌍으로 이루어져있다.
통 안에 담아놓고 뚜껑 덮으면 뭔지 알 수 없다.
명세부라는 것은 상자 밖에다가 안에 뭐가 들어있는지 작성하는 부분이다.

상자 바깥에 라벨링 => 명세부이다.

상자 따로 명세부 따로인데, 서로 페어여야 한다.
라벨을 만들어서 상자에 붙인다고 생각하면 된다.

라벨을 만들었는데... 라벨이 상자에 붙어야 되므로
PACKAGE 패키지명
PACKAGE BODY 패키지명 의 패키지명은 서로 동일해야 한다.

상자 안에 있는 물건들의 리스트를 작성해서 써 놓으면 상자가 매우 많아지더라도
필요한 걸 꺼내쓸 때 확인할 수 있다. -> 이것이 명세부, 상자가 곧 몸체부(패키지)

*/

--■■■ PACKAGE(패키지) ■■■--

-- 1. PL/SQL 의 패키지는 관계되는 타입, 프로그램 객체,
--    서브 프로그램(PROCEDURE, FUNCTION 등)을
--    논리적으로 묶어놓은 것이다.
--   오라클에서 제공하는 패키지 중 하나가 바로 『DBMS_OUTPUT』이다.

-- 2. 패키지는 서로 유사한 업무에 사용되는 여러 개의 프로시저와 함수를
--    하나의 패키지로 만들어 관리함으로써 향후 유지보수가 편리하고
--    전체 프로그램을 모듈화 할 수 있다는 장점이 있다.


-- 3. 패키지는 명세부(PACKAGE SPECIFICATION)와
--    몸체부(PACKAGE BODY)로 구성되어 있으며
--    명세 부분에는 TYPE, CONSTRAINT, VARIABLE, EXCEPTION, CURSOR, SUBPROGRAM 이 선언되고
--    몸체 부분에는 이들의 실제 내용이 존재한다.
--    그리고, 호출할 때에는 『패키지명.프로시저명』과 같은 형식의 참조를 이용해야 한다.


-- 4. 형식 및 구조(명세부)
/*
CREATE [OR REPLACE] PACKAGE 패키지명
IS
    전역변수 선언;
    커서 선언;
    예외 선언;
    함수 선언;
    프로시저 선언;
        :
END 패키지명;

*/


-- 5. 형식 및 구조(몸체부)
/*
CREATE [OR REPLACE] PACKAGE BODY 패키지명
IS
    FUNCTION 함수명[(인수, ...)]
    RETURN 자료형
    IS
        변수 선언;
    BEGIN
        함수 몸체 구성 코드;
        RETURN 값;
    END;
    
    
    PROCEDURE 프로시저명[(인수, ...)]
    IS
        변수 선언;
    BEGIN
        프로시저 몸체 구성 코드;
    END;
   
END 패키지명;

*/


/*
[추가 필기]

# DBMS_OUTPUT : 패키지

# PUT_LINE() : 함수



-->> 『패키지명.함수()』
-->> DBMS_OUTPUT.PUT_LINE() 과 같이 쓰는 이유이다.

*/


-- 패키지 등록 실습

-- ① 명세부 작성

-- 명세부 표시 -> PACKAGE INSA_PACK
CREATE OR REPLACE PACKAGE INSA_PACK
IS
    FUNCTION FN_GENDER(V_SSN VARCHAR2)
    RETURN VARCHAR2;
    
--    PROCEDURE 프로시저명(매개변수);
    --==>> 명세부의 IS는 뭐가 들어있는지만 적는 란이다!!
    
    
END INSA_PACK; --> * [이 패키지는, 이것이 뼈대이다. (IF ~ END IF;와 같은 형태)]

--==>> Package INSA_PACK이(가) 컴파일되었습니다.




-- ② 몸체부 작성
-- 몸체부는 PACKAGE 'BODY'!!!
-- [BODY] 키워드 하나로 명세부와 몸체부가 달라진다.
-- END 패키지명은 명세부나 몸체부나 똑같다~!!

-- 패키지 내부에서 함수를 선언할 때는
-- CREATE OR REPLACE FUNCTION이 아니라
-- 바로 FUNCTION으로 작성하여 함수 생성 가능하다. 이외에는 모두 동일

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이(가) 컴파일되었습니다.

 


20231108_02_scott.sql
SELECT USER
FROM DUAL;
--==>> SCOTT

SELECT *
FROM TBL_상품;

SELECT *
FROM TBL_입고;

SELECT *
FROM TBL_출고;

DESC TBL_상품;
DESC TBL_입고;
DESC TBL_출고;



INSERT INTO TBL_입고(입고번호, 상품코드, 입고수량, 입고단가)
VALUES (4, 'H003', 100, 800);



/*
H001	    바밤바	600	30
H002    	죠스바	500	50
H003    	보석바	500	100
H004    	누가바	600	0
H005	    쌍쌍바	700	100
*/  

INSERT INTO TBL_출고(출고번호, 상품코드, 출고수량, 출고단가)
VALUES (1, 'H001', 10, 800);

/*
H001	    바밤바	600	20
H002    	죠스바	500	50
H003    	보석바	500	100
H004	    누가바	600	0
H005	    쌍쌍바	700	100
*/

INSERT INTO TBL_출고(출고번호, 상품코드, 출고수량, 출고단가)
VALUES (2, 'H002', 20, 800);


DELETE
FROM TBL_출고
WHERE 출고번호 = 1;

SELECT *
FROM TBL_상품;
/*
H001	바밤바	600	20
H002	죠스바	500	30
H003	보석바	500	100
H004	누가바	600	0
H005	쌍쌍바	700	100
*/


SELECT *
FROM TBL_출고;

UPDATE TBL_출고
SET 출고수량 = 30
WHERE 출고번호 = 1;












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



--○ 패키지 활용 실습

SELECT INSA_PACK.FN_GENDER('801007-1544236') "함수호출결과"
FROM DUAL;
--==>> 남자


SELECT NAME, SSN, INSA_PACK.FN_GENDER(SSN) "함수호출결과"
FROM TBL_INSA;
--==>>
/*
NAME	    SSN           	함수호출결과
최혜인	970812-2234567	여자
홍길동	771212-1022432	남자
이순신	801007-1544236	남자
이순애	770922-2312547	여자
김정훈	790304-1788896	남자
한석봉	811112-1566789	남자
이기자	780505-2978541	여자
장인철	780506-1625148	남자
김영년	821011-2362514	여자
나윤균	810810-1552147	남자
김종서	751010-1122233	남자
유관순	801010-2987897	여자
정한국	760909-1333333	남자
조미숙	790102-2777777	여자
황진이	810707-2574812	여자
이현숙	800606-2954687	여자
이상헌	781010-1666678	남자
엄용수	820507-1452365	남자
이성길	801028-1849534	남자
박문수	780710-1985632	남자
유영희	800304-2741258	여자
홍길남	801010-1111111	남자
이영숙	800501-2312456	여자
김인수	731211-1214576	남자
김말자	830225-2633334	여자
우재옥	801103-1654442	남자
김숙남	810907-2015457	여자
김영길	801216-1898752	남자
이남신	810101-1010101	남자
김말숙	800301-2020202	여자
정정해	790210-2101010	여자
지재환	771115-1687988	남자
심심해	810206-2222222	여자
김미나	780505-2999999	여자
이정석	820505-1325468	남자
정영희	831010-2153252	여자
이재영	701126-2852147	여자
최석규	770129-1456987	남자
손인수	791009-2321456	여자
고순정	800504-2000032	여자
박세열	790509-1635214	남자
문길수	721217-1951357	남자
채정희	810709-2000054	여자
양미옥	830504-2471523	여자
지수환	820305-1475286	남자
홍원신	690906-1985214	남자
허경운	760105-1458752	남자
산마루	780505-1234567	남자
이기상	790604-1415141	남자
이미성	830908-2456548	여자
이미인	810403-2828287	여자
권영미	790303-2155554	여자
권옥경	820406-2000456	여자
김싱식	800715-1313131	남자
정상호	810705-1212141	남자
정한나	820506-2425153	여자
전용재	800605-1456987	남자
이미경	780406-2003214	여자
김신제	800709-1321456	남자
임수봉	810809-2121244	여자
김신애	810809-2111111	여자
*/



/*
         ^---^
        ('ㅅ' )
  @>----  ㅜ
          ㅅ∫
           

암호화 , 첨부ㅍㅏ일 
기간 7일~14일



*/