Notice
Recent Posts
Recent Comments
Link
Everything has an expiration date
[Oracle - PL/SQL] 20231108 [프로그램 소스] 본문
- 출고 테이블의 데이터 입력, 수정, 삭제시 재고수량 변동 트리거 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일
*/
'[Oracle] > PL·SQL (Program source)' 카테고리의 다른 글
[Oracle - PL/SQL] 20231107 [프로그램 소스] (2) | 2023.11.07 |
---|---|
[Oracle - PL/SQL] 20231106 [프로그램 소스] (0) | 2023.11.06 |
[Oracle - PL/SQL] 20231103 [프로그램 소스] (0) | 2023.11.03 |
[Oracle - PL/SQL] 20231102 [프로그램 소스] (0) | 2023.11.03 |