016. [PL/SQL] TRIGGER(트리거)_(:OLD/:NEW), PACKAGE(패키지)

2023.04.12 (수)

SCOTT 계정 실습

• TRIGGER (트리거)

--■■■ TRIGGER(트리거) ■■■--

-- 사전적인 의미 : 방아쇠, 촉발시키다, 야기하다, 유발하다...
--// 부비트랩 연상

-- 1. TRIGGER(트리거)란 DML 작업 즉, INSERT, UPDATE, DELETE 와 같은 작업이 일어날 때
--    자동적으로 실행되는(유발되는, 촉발되는) 객체로
--    이와 같은 특징을 강조하여(부각시켜) DML TRIGGER 라고 부르기도 한다.
--    TRIGGER(트리거)는 데이터 무결성 뿐 아니라
--    다음과 같은 작업에도 널리 사용된다.

--  ·자동으로 파생된 열 값 생성    
      --// 어떤 테이블 A에 INSERT 쿼리문을 입력 시켰는데 TRIGGER로 자동으로 B라는 테이블에도 INSERT가 될 수 있다. 
--  ·잘못된 트랜잭션 방지
      --// 프로시저를 통해서 입고 하지 않고 직접 입고 테이블에 INSERT 시키면 상품테이블이 UPDATE 되지 않을 수 있는데 이를 방지 가능
--  ·복잡한 보안 권한 강제 수행
      --// 회사에서 일과시간 동안은 주식사이트 막아두는 느낌
--  ·분산 데이터베이스 노드 상에서 참조 무결성 강제 수행
      --// 참조무결성 : EMP 테이블의 DEPT 번호는 DEPT 테이블의 부서번호에 있는 번호만 있어야 하는데 
      --// 분산 DB : EMP 테이블은 서울에 DEPT 는 대전에
      --// 즉, 대전에 있는 DEPT 컬럼을 데려와서 EMP 테이블에서 사용하게 되어야하는데
      --// 이를 TRIGGER 로 적용하여 사용할 수 있게 함
--  ·복잡한 업무 규칙 강제 적용
      --// 복잡한 보안 권한 ~ 과 동일한 말
--  ·투명한 이벤트 로깅 제공
      --// 파생된 열 값 생성과 동일
--  ·복잡한 감사 제공
      --// 너 이때 뭐 했지 ~ 하고 감사하는 것
--  ·동기 테이블 복제 유지 관리
      --// 동기화 : A테이블에 변환된 데이터를 B,C 테이블에도 같이 업데이트해주는것. 1,2번 말과 유사함
--  ·테이블 액세스 통계 수집
      --// 투명한 이벤트~ 와 동일한 말

-- 2. TRIGGER 내에서는 COMMIT, ROLLBACK 구문을 사용할 수 없다.

-- 3. 특징 및 종류
/*
--// 크게 BEFORE-AFTER로 나눔
--// 증권사 홈페이지 예시 -- 접근하면 안되는데 접근 한 후에 처리? 안됨 --> BEFORE
--// 떠든사람 이름 -- 떠들기 전에 알 수 없는(즉, 파생된) 경우          --> AFTER

--// STATEMENT(문장)와 ROW(행)처리로 나눔
--// 상품 테이블 특정한 상품 INSERT 시 어떤 상품인지 열을 모두 확인해야하는지 --> ROW
--// 떠는 사람 이름 -- 한번에 한줄로 처리 가능                                --> STATEMENT
*/

--  ·BEFORE STATEMEMT TRIGGER
--    SQL 구문이 실행되기 전에(DML 작업을 수행하기 전에) 그 문장에 대해 한 번 실행
--  ·BEFORE ROW TRIGGER
--    SQL 구문이 실행되기 전에(DML 작업을 수행하기 전에) 각 행(ROW)에 대해 한 번 실행
--  ·AFTER STATEMENT TRIGGER
--    SQL 구문이 실행된 후에(DML 작업을 수행한 후에) 그 문장에 대해 한 번 실행
--  ·AFTER ROW TRIGGER
--    SQL 구문이 실행된 후에(DML 작업을 수행한 후에) 각 행(ROW)에 대해 한 번 실행

-- 4. 형식 및 구조
/*
CREATE [OR REPLACE] TRIGGER 트리거명
    [BEFORE] | [AFTER]
    이벤트1 [OR 이벤트2 [OR 이벤트3]] ON 테이블명
    [FOR EACH ROW [WHEN TRIGGER 조건]]
[DECLARE]
    -- 선언 구문;
BEGIN
    -- 실행 구문;
END;
*/

• 관찰 전 세팅

--※ 트리거 실습을 위한 준비

--○ 트리거 실습 관련 테이블 생성(TBL_TEST1)
CREATE TABLE TBL_TEST1
( ID    NUMBER
, NAME  VARCHAR2(30)
, TEL   VARCHAR2(60)
);
--==>> Table TBL_TEST1이(가) 생성되었습니다.

--○ 생성된 테이블에 제약조건 추가
--  ID 컬럼에 PK 제약조건 지정
ALTER TABLE TBL_TEST1
ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY(ID);
--==>> Table TBL_TEST1이(가) 변경되었습니다.

--○ 트리거 실습 관련 테이블 생성(TBL_EVENTLOG)
CREATE TABLE TBL_EVENTLOG
( MEMO  VARCHAR2(200)
, ILJA  DATE    DEFAULT SYSDATE
);
--==>> Table TBL_EVENTLOG이(가) 생성되었습니다.

--○ 확인
SELECT *
FROM TBL_TEST1;
--=>> 조회 결과 없음
SELECT *
FROM TBL_EVENTLOG;
--==>> 조회 결과 없음

--○ 날짜에 대한 세션 정보 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.

• 관찰

1. AFTER STATEMENT TRIGGER
- 트리거 생성

--■■■ AFTER STATEMENT TRIGGER 에 대한 상황 실습 ■■■--
--※ DML 작업에 대한 이벤트 기록

--○ TRIGGER(트리거) 생성 → TRG_EVENTLOG
CREATE OR REPLACE TRIGGER TRG_EVENTLOG
        AFTER
        INSERT OR UPDATE OR DELETE ON TBL_TEST1 --//이벤트가 발생할 대상(사용자가 보는 곳)
DECLARE
BEGIN
    -- 이벤트 종류 구분(조건문을 통한 분기)
    -- 각 구문에 대한 키워드 CHECK~!!!
    IF (INSERTING)  --// INSERT 관련 이벤트 키워드
        THEN INSERT INTO TBL_EVENTLOG(MEMO)
             VALUES('INSERT 쿼리문이 수행되었습니다.');
    ELSIF  (UPDATING)   --// UPDATE 관련 이벤트 키워드
        THEN INSERT INTO TBL_EVENTLOG(MEMO)
             VALUES('UPDATE 쿼리문이 수행되었습니다');
    ELSIF (DELETING)    --// DELETE 관련 이벤트 키워드
        THEN INSERT INTO TBL_EVENTLOG(MEMO)
             VALUES('DELETE 쿼리문이 수행되었습니다');
    END IF;
    
    --COMMIT;
    --TRIGGER 내에서 는 COMMIT 구문 사용 금지~!!!

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

- 데이터 입력, 수정, 삭제 후 확인

--○ TBL_TEST1 테이블에 데이터 입력
INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(1,'재혁킴','010-1111-1111');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(2,'서영유','010-1111-1111');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(3,'수인주','010-3333-3333');
--==>> 1 행 이(가) 삽입되었습니다.
-------------------------------------------------------------------
--○ TBL_TEST1 테이블의 데이터 수정
UPDATE TBL_TEST1
SET NAME = '이기배'
WHERE ID = 3;
--==>> 1 행 이(가) 업데이트되었습니다.
UPDATE TBL_TEST1
SET NAME = '별희캉'
WHERE ID = 3;
--==>> 1 행 이(가) 업데이트되었습니다.
-------------------------------------------------------------------
--○ TBL_TEST1 테이블의 데이터 삭제
DELETE
FROM TBL_TEST1
WHERE ID=3;
--==>> 1 행 이(가) 삭제되었습니다.
-------------------------------------------------------------------
DELETE
FROM TBL_TEST1
SELECT *
FROM TBL_TEST1;
--==>> 조회 결과 없음
-------------------------------------------------------------------
--○ 확인
--// 사용자는 모르는 EVENTLOG 의 존재... 
SELECT *
FROM TBL_EVENTLOG;
--==>>
/*
INSERT 쿼리문이 수행되었습니다.	2023-04-11 17:31:34
INSERT 쿼리문이 수행되었습니다.	2023-04-11 17:31:43
INSERT 쿼리문이 수행되었습니다.	2023-04-11 17:32:13
UPDATE 쿼리문이 수행되었습니다	2023-04-11 17:32:55
UPDATE 쿼리문이 수행되었습니다	2023-04-11 17:33:58
DELETE 쿼리문이 수행되었습니다	2023-04-11 17:34:47
DELETE 쿼리문이 수행되었습니다	2023-04-11 17:35:11
DELETE 쿼리문이 수행되었습니다	2023-04-11 17:35:33
*/

---

2. BEFORE STATEMENT TRIGGER
- 트리거 생성

--■■■ BRFORE STATEMENT TRIGGER 에 대한 상황 실습 ■■■--
--※ DML 작업 수행 전에 작업 가능여부 확인 및 강제 적용
--  (보안 정책 적용 / 업무 규칙 적용)

--○ TRIGGER(트리거) 생성 → TRG_TEST1_DML
CREATE OR REPLACE TRIGGER TRG_TEST1_DML
        BEFORE
        INSERT OR UPDATE OR DELETE ON TBL_TEST1
BEGIN
    IF (서버 시간이 오전 8시 이전이거나... 오후 6시 이후라면...)
        THEN 작업을 하지 못하도록 처리하겠다.
    END IF;
END;


CREATE OR REPLACE TRIGGER TRG_TEST1_DML
        BEFORE
        INSERT OR UPDATE OR DELETE ON TBL_TEST1
BEGIN
    IF (서버 시간이 오전 8시 이전이거나... 오후 6시 이후라면...)
        THEN 예외를 발생시키도록 하겠다.
    END IF;
END;

/*
-- IF 구문 작성 생각해보기
CREATE OR REPLACE TRIGGER TRG_TEST1_DML
        BEFORE
        INSERT OR UPDATE OR DELETE ON TBL_TEST1
BEGIN
    IF ( TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) < 8 ) OR ( TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) > 18 )
        THEN 예외를 발생시키도록 하겠다.
    END IF;
END;
*/

CREATE OR REPLACE TRIGGER TRG_TEST1_DML
        BEFORE
        INSERT OR UPDATE OR DELETE ON TBL_TEST1
BEGIN
    IF ( TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) < 8 
      OR TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) >= 18 ) --// 18 을 포함하는 것을 확인!
        THEN 예외를 발생시키도록 하겠다.
    END IF;
    /*
    IF ( TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) < 8 
      OR TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) > 17 )
        THEN 예외를 발생시키도록 하겠다.
    END IF;
    */
END;


CREATE OR REPLACE TRIGGER TRG_TEST1_DML
        BEFORE
        INSERT OR UPDATE OR DELETE ON TBL_TEST1
BEGIN
    IF ( TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) < 8 
      OR TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) >= 18 )
        THEN RAISE_APPLICATION_ERROR(-20003, '작업은 08:00 ~ 18:00 까지만 가능합니다');
          --// 예외발생-예외처리 하지 않고 한 번에 처리 할 수 도 있다.
          --// 이런 구문을 수행할 때, 행동 이전부터 처리하기 때문에 COMMIT, ROLLBACK 도 필요없는 것.
    END IF;
END;
--==>> Trigger TRG_TEST1_DML이(가) 컴파일되었습니다.

- TRIGGER 확인

--※ 생성한 트리거(→ TRG_TEST1_DML) 정상 작동여부 확인
-- 오라클 서버의 시간이 09:22 인 상태로 테스트
INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(1,'상훈문','010-1111-1111');
--==>> 1 행 이(가) 삽입되었습니다.

SELECT *
FROM TBL_TEST1;
--==>> 1	상훈문	010-1111-1111

UPDATE TBL_TEST1
SET NAME='리서최', TEL='010-2222-2222'
WHERE ID=1;
--==>> 1 행 이(가) 업데이트되었습니다.

SELECT *
FROM TBL_TEST1;
--==>> 1	리서최	010-2222-2222

DELETE
FROM TBL_TEST1
WHERE ID=1;
--==>> 1 행 이(가) 삭제되었습니다.

SELECT *
FROM TBL_TEST1;
--==>> 조회 결과 없음 

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

--// 즉, INSERT,UPDATE,DELETE 구문이 정상적으로 작동하고 있음을 확인할 수 있음
-- 오라클 서버의 시간이 07:27 인 상태로 테스트 (→ 시스템 시간 변경 후 실습)

INSERT INTO TBL_TEST1(ID,NAME,TEL)
VALUES(1,'상훈문','010-1111-1111');
--==>> 에러 발생
/*
ORA-20003: 작업은 08:00 ~ 18:00 까지만 가능합니다
*/

SELECT *
FROM TBL_TEST1;
--==>> 조회 결과 없음

UPDATE TBL_TEST1
SET NAME='리서최', TEL='010-2222-2222'
WHERE ID=1;
--==>> 에러 발생
/*
ORA-20003: 작업은 08:00 ~ 18:00 까지만 가능합니다
*/

SELECT *
FROM TBL_TEST1;
--==>> 조회 결과 없음

DELETE
FROM TBL_TEST1
WHERE ID=1;
--==>> 에러 발생
/*
ORA-20003: 작업은 08:00 ~ 18:00 까지만 가능합니다
*/

SELECT *
FROM TBL_TEST1;
--==>> 조회 결과 없음

---

3. BEFORE ROW TRIGGER 
- 실습 테이블 설정

--○ BEFORE ROW TRIGGER 실습 진행을 위한 테이블 생성
--   (TBL_TEST2) → 부모 테이블 활용
CREATE TABLE TBL_TEST2
( CODE  NUMBER
, NAME  VARCHAR2(40)
, CONSTRAINT TEST2_CODE_PK PRIMARY KEY(CODE)
);
--==>> Table TBL_TEST2이(가) 생성되었습니다.
-----------------------------------------------------------------------------------
--○ BEFORE ROW TRIGGER 실습 진행을 위한 테이블 생성
--   (TBL_TEST3) → 자식 테이블 활용
CREATE TABLE TBL_TEST3
( SID   NUMBER
, CODE  NUMBER
, SU    NUMBER
, CONSTRAINT TEST3_SID_PK PRIMARY KEY(SID)
, CONSTRAINT TEST3_CODE_FK FOREIGN KEY(CODE)
             REFERENCES TBL_TEST2(CODE)
);
--==>> Table TBL_TEST3이(가) 생성되었습니다.
-----------------------------------------------------------------------------------
--○ 부모 테이블(→ TBL_TEST2) 에 데이터 입력
INSERT INTO TBL_TEST2(CODE,NAME) VALUES (1,'세탁기');
INSERT INTO TBL_TEST2(CODE,NAME) VALUES (2,'냉장고');
INSERT INTO TBL_TEST2(CODE,NAME) VALUES (3,'건조기');
--==>> 1 행 이(가) 삽입되었습니다. * 3
SELECT *
FROM TBL_TEST2;
--==>>
/*
1	세탁기
2	냉장고
3	건조기
*/
COMMIT;
--==>> 커밋 완료.
-----------------------------------------------------------------------------------
--○ 자식 테이블(→ TBL_TEST3) 에 데이터 입력
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (1,1,20);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (2,2,30);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (3,3,40);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (4,1,50);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (5,1,60);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (6,1,70);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (7,2,80);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (8,2,90);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (9,1,20);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (10,3,30);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (11,3,40);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (12,2,50);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (13,1,60);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (14,1,70);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (15,2,80);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (16,2,90);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (17,1,20);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (18,3,30);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (19,2,40);
INSERT INTO TBL_TEST3(SID,CODE,SU) VALUES (20,1,50);
--==>> 1 행 이(가) 삽입되었습니다. * 20
SELECT *
FROM TBL_TEST3;
--==>>
/*
1	1	20
2	2	30
3	3	40
4	1	50
5	1	60
6	1	70
7	2	80
8	2	90
9	1	20
10	3	30
11	3	40
12	2	50
13	1	60
14	1	70
15	2	80
16	2	90
17	1	20
18	3	30
19	2	40
20	1	50
*/
COMMIT;
--==>> 커밋 완료.
-----------------------------------------------------------------------------------
SELECT C.SID, P.CODE, P.NAME, C.SU
FROM TBL_TEST2 P JOIN TBL_TEST3 C
ON P.CODE = C.CODE;
--==>>
/*
1	1	세탁기	20
2	2	냉장고	30
3	3	건조기	40
4	1	세탁기	50
5	1	세탁기	60
6	1	세탁기	70
7	2	냉장고	80
8	2	냉장고	90
9	1	세탁기	20
10	3	건조기	30
11	3	건조기	40
12	2	냉장고	50
13	1	세탁기	60
14	1	세탁기	70
15	2	냉장고	80
16	2	냉장고	90
17	1	세탁기	20
18	3	건조기	30
19	2	냉장고	40
20	1	세탁기	50
*/
-----------------------------------------------------------------------------------
SELECT *
FROM TBL_TEST2
WHERE CODE =1;
--==>> 1	세탁기
DELETE
FROM TBL_TEST2
WHERE CODE =1;
--==>> 에러 발생
/*
ORA-02292: integrity constraint (SCOTT.TEST3_CODE_FK) violated - child record found
*/
DELETE
FROM TBL_TEST2
WHERE CODE =2;
--==>> 에러 발생
/*
ORA-02292: integrity constraint (SCOTT.TEST3_CODE_FK) violated - child record found
*/
DELETE
FROM TBL_TEST2
WHERE CODE =3;
--==>> 에러 발생
/*
ORA-02292: integrity constraint (SCOTT.TEST3_CODE_FK) violated - child record found
*/
-----------------------------------------------------------------------------------
SELECT *
FROM TBL_TEST3
WHERE CODE = 3;
--==>>
/*
3	3	40
10	3	30
11	3	40
18	3	30
*/
--○ 자식 테이블(→ TBL_TEST3)의 모든 건조기(→ CODE=3) 삭제 
DELETE
FROM TBL_TEST3
WHERE CODE = 3;
--==>> 4개 행 이(가) 삭제되었습니다.
--○ 부모 테이블(→ TBL_TEST2)의 모든 건조기(→ CODE=3) 삭제 
DELETE
FROM TBL_TEST2
WHERE CODE=3;
--==>> 1 행 이(가) 삭제되었습니다.
-----------------------------------------------------------------------------------
--○ 트리거 구성 전 최종 확인
SELECT *
FROM TBL_TEST2;
--==>>
/*
1	세탁기
2	냉장고
*/
SELECT *
FROM TBL_TEST3;
--==>>
/*
1	1	20
2	2	30
4	1	50
5	1	60
6	1	70
7	2	80
8	2	90
9	1	20
12	2	50
13	1	60
14	1	70
15	2	80
16	2	90
17	1	20
19	2	40
20	1	50
*/
SELECT COUNT(*) AS COUNT
FROM TBL_TEST3;
--==>> 16

- 트리거 생성

--■■■ BRFORE ROW TRIGGER 에 대한 상황 실습 ■■■--
--※ 참조 관계가 설정된 데이터(자식) 삭제를
--   사전에 먼저 수행할 수 있도록 처리하는 모델
--// ex) DEPT 부서번호 20 번 삭제 하라고 명령하면
--//     오라클이 삭제 명령 전에 해당번호를 참조하는 모든 행(ROW)를 삭제 하게 됨

-- 10:16:10
DELETE
FROM TBL_TEST2
WHERE CODE = 1;

-- 10:16:09
--○ TRIGGER(트리거) 작성 (→ TRG_TEST2_DELETE)
CREATE OR REPLACE TRIGGER TRG_TEST2_DELETE
        BEFORE
        DELETE ON TBL_TEST2 --// TRIGGER 가 건드려지는 곳!(DELETE 가 ~에서 일어났을 때)
        FOR EACH ROW        --// ★행(ROW)트리거는 FOR 추가 옵션이 붙는다
DECLARE
BEGIN
    --// 지금 만드는 트리거는 호출트리거가 아니기 때문에 프로시저처럼 변수를 넘겨받을 수 없음 
    DELETE
    FROM TBL_TEST3
    WHERE CODE = :OLD.CODE;  --//★ :OLD // 오라클이 만든 변수라고 생각하면 됨
                             --// 이미 지워질거라서 :OLD 에 담아놓고 시간을 멈추는 느낌
                             --// 새로 만들어질거는 :NEW 에 오라클이 담아둔다
                             --// .CODE 는 :OLD 박스 안에 있는 값을 가져오는 것
END;
--==>> Trigger TRG_TEST2_DELETE이(가) 컴파일되었습니다.

--※ 『:OLD』
--   참조 전 열의 값
--   (INSERT : 입력하기 이전 자료, DELETE : 삭제하기 이전자료 즉, 삭제할 (대상)자료)

--※ UPDATE → 내부적으로는 DELETE 그리고 INSERT 가 결합된 상태
--             이 과정에서 UPDATE 하기 이전의 자료는 :OLD
--             이 과정에서 UPDATE 한   이후의 자료는 :NEW

- 트리거 확인

--○ TRIGGER(트리거) 작성 이후 다시 테스트
SELECT *
FROM TBL_TEST2
WHERE CODE=1;
--==>> 1	세탁기

DELETE
FROM TBL_TEST2
WHERE CODE=1;
--==>> 1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_TEST3;
--==>>
/*
2	2	30
7	2	80
8	2	90
12	2	50
15	2	80
16	2	90
19	2	40
*/
--// CASCADE 옵션이 없음에도 불구하고 잘 삭제된다.
--// 위험하다. -> 1행 삭제 되었다고 오라클에서 알려주지만 실제로는 자식테이블에서 다수의 행이 삭제됨

---

4. AFTER ROW TRIGGER
- 실습 전 환경 확인

--○ AFTER ROW TRIGGER 실습을 위한 환경 확인
UPDATE TBL_상품
SET 재고수량=0;
--==>> 21개 행 이(가) 업데이트되었습니다.
TRUNCATE TABLE TBL_입고;
--==>> Table TBL_입고이(가) 잘렸습니다.
TRUNCATE TABLE TBL_출고;
--==>> Table TBL_출고이(가) 잘렸습니다.
--// TRUNCATE TABLE 테이블명 :  틀만 두고 데이터 삭제 한 것
----------------------------------------------------------------------------------
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	0
H002	새우깡	1200	0
H003	자갈치	1000	0
H004	감자깡	1100	0
H005	꼬깔콘	1600	0
H006	오감자	1800	0
H007	치토스	1500	0
C001	다이제	2000	0
C002	에이스	1400	0
C003	오레오	1800	0
C004	아이비	1300	0
C005	버터링	1700	0
C006	고소미	1900	0
C007	로투스	1200	0
E001	비틀즈	1100	0
E002	마이쮸	1500	0
E003	아이셔	1700	0
E004	하리보	1000	0
E005	아이써	1900	0
E006	아달아	2200	0
E007	엠앤엠	1000	0
*/
SELECT *
FROM TBL_입고;
--==>> 조회 결과 없음
SELECT *
FROM TBL_출고;
--==>> 조회 결과 없음
COMMIT;
--=>> 커밋 완료.

- 트리거 작성 (UPDATE 만)

--■■■ AFTER ROW TRIGGER 에 대한 상황 실습 ■■■--
--※ 참조 테이블 관련 트랜젝션 처리
--// 프로시저와 비슷
--// 그러나 1. 프로시저는 특정 형태로 호출해와야함.
--//        2. 프로시저는 내용을 추가/업데이트/삭제 시에 재고가 마이너스가 되지않는 처리 가능
--//           하지만 트리거는 이러한 처리가 불가능하여 재고가 마이너스가 될 수 있음

-- TBL_상품, TBL_입고, TBL_출고

--○ TBL_입고 테이블의 데이터 입력 시(입고 이벤트 발생 시)
--   TBL_상품 테이블의 재고수량 변동 트리거 작성
--   (→ TRG_IBGO)
CREATE OR REPLACE TRIGGER TRG_IBGO
        AFTER
        INSERT ON TBL_입고
        FOR EACH ROW
BEGIN
    IF (INSERTING)
        THEN UPDATE TBL_상품
             SET 재고수량 = 재고수량 + 새로입고되는 입고수량
             WHERE 상품코드 = 새로입고되는 상품코드;
    END IF;
END;

/*
INSERT INTO TBL_입고(...., 상품코드, ..., 입고수량)
VALUES (.....,'H001', ... , 10);
*/

CREATE OR REPLACE TRIGGER TRG_IBGO
        AFTER
        INSERT ON TBL_입고
        FOR EACH ROW
BEGIN
    IF (INSERTING)
        THEN UPDATE TBL_상품
             SET 재고수량 = 재고수량 + :NEW.입고수량
             WHERE 상품코드 = :NEW.상품코드;
    END IF;
END;
--==>> Trigger TRG_IBGO이(가) 컴파일되었습니다.

- 트리거 확인

--○ 트리거(TRG_IBGO) 작성 이후 입고 테이블에 입고 이벤트 발생
INSERT INTO TBL_입고(입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
VALUES(1,'H001',SYSDATE, 100,1000);
--==>> 1 행 이(가) 삽입되었습니다.
--확인
SELECT *
FROM TBL_입고;
--==>> 1	H001	2023-04-12	100	1000
--커밋
COMMIT;
--==>> 커밋 완료.
-----------------------------------------------------------------------
SELECT *
FROM TBL_상품;
--==>> 
/*
H001	홈런볼	1500	100
          :
*/

---

--○ TBL_상품, TBL_입고, TBL_출고 의 관계에서
--   입고수량, 재고수량의 트랜잭션 처리가 이루어질 수 있도록
--   TRG_IBGO 트리거를 REPLACE(수정)한다.

CREATE OR REPLACE TRIGGER TRG_IBGO
        AFTER
        INSERT OR UPDATE OR DELETE  ON TBL_입고
        FOR EACH ROW
BEGIN
    IF (INSERTING)
        THEN UPDATE TBL_상품
             SET 재고수량 = 재고수량 + :NEW.입고수량
             WHERE 상품코드 = :NEW.상품코드;
    ELSIF (UPDATING)
        THEN UPDATE TBL_상품
             SET 재고수량 = 재고수량 - :OLD.입고수량 + :NEW.입고수량 
             WHERE 상품코드 = :OLD.상품코드;
    ELSIF (DELETING)
        THEN UPDATE TBL_상품
             SET 재고수량 = 재고수량 - :OLD.입고수량
             WHERE 상품코드 = :OLD.상품코드;
    END IF;
END;
--==>> Trigger TRG_IBGO이(가) 컴파일되었습니다.

---

INSERT INTO TBL_입고(입고번호, 상품코드, 입고수량, 입고단가)
VALUES(2,'H002', 100,1000);
--==>> 1 행 이(가) 삽입되었습니다.
SELECT *
FROM TBL_입고;
--==>>
/*
1	H001	2023-04-12	100	1000
2	H002	2023-04-12	100	1000
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	100
H002	새우깡	1200	100
            :
*/
-------------------------------------------------------------------------------
UPDATE TBL_입고
SET 입고수량 = 50
WHERE 입고번호 = 2;
--==>> 1 행 이(가) 업데이트되었습니다.
SELECT *
FROM TBL_입고;
--==>>
/*
1	H001	2023-04-12	100	1000
2	H002	2023-04-12	50	1000
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	100
H002	새우깡	1200	50
            :
*/
-------------------------------------------------------------------------------
DELETE
FROM TBL_입고
WHERE 입고번호 =2;
--==>> 1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_입고;
--==>>
/*
1	H001	2023-04-12	100	1000
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	100
H002	새우깡	1200	0
            :
*/

---

--○ TBL_상품, TBL_입고, TBL_출고 의 관계에서
--   출고수량, 재고수량의 트랜잭션 처리가 이루어질 수 있도록
--   TRG_CHULGO 트리거를 작성한다.

CREATE OR REPLACE TRIGGER TRG_CHULGO
        AFTER
        INSERT OR UPDATE OR DELETE ON TBL_출고
        FOR EACH ROW
DECLARE
BEGIN
        IF (INSERTING)
            THEN    UPDATE TBL_상품
                    SET 재고수량 = 재고수량 - :NEW.출고수량
                    WHERE 상품코드 = :NEW.상품코드;
        ELSIF (UPDATING)
            THEN    UPDATE TBL_상품
                    SET 재고수량 = 재고수량 + :OLD.출고수량 - :NEW.출고수량
                    WHERE 상품코드 = :OLD.상품코드;
        ELSIF (DELETING)
            THEN    UPDATE TBL_상품
                    SET 재고수량 = 재고수량 + :OLD.출고수량
                    WHERE 상품코드 = :OLD.상품코드;
        END IF;
END;
--==>> Trigger TRG_CHULGO이(가) 컴파일되었습니다.

---

INSERT INTO TBL_출고(출고번호, 상품코드, 출고수량, 출고단가)
VALUES(1,'H001', 50,2000);
--==>> 1 행 이(가) 삽입되었습니다.
SELECT *
FROM TBL_출고;
--==>>
/*
1	H001	2023-04-12	50	2000
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	50
            :
*/
--------------------------------------------------------------------------
UPDATE TBL_출고
SET 출고수량 = 150
WHERE 출고번호 = 1;
--==>> 1 행 이(가) 업데이트되었습니다.
SELECT *
FROM TBL_출고;
--==>>
/*
1	H001	2023-04-12	150	2000
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	-50
            :
*/
--------------------------------------------------------------------------
DELETE
FROM TBL_출고
WHERE 출고번호 =1;
--==>> 1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_출고;
--==>> 조회 결과 없음
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	100
H002	새우깡	1200	0
            :
*/

---

• TRIGGER 조회

--※ TRIGGER 의 정보 조회
SELECT *
FROM USER_TRIGGERS;
--==>>
--컬럼의 내용 확인 → DESCRIPTION
/*
"TRG_IBGO
        AFTER
        INSERT OR UPDATE OR DELETE  ON TBL_입고
        FOR EACH ROW
"
*/
--컬럼의 내용 확인 → TRIGGER_BODY
/*
"BEGIN
    IF (INSERTING)
        THEN UPDATE TBL_상품
             SET 재고수량 = 재고수량 + :NEW.입고수량
             WHERE 상품코드 = :NEW.상품코드;
    ELSIF (UPDATING)
        THEN UPDATE TBL_상품
             SET 재고수량 = 재고수량 - :OLD.입고수량 + :NEW.입고수량 
             WHERE 상품코드 = :OLD.상품코드;
    ELSIF (DELETING)
        THEN UPDATE TBL_상품
             SET 재고수량 = 재고수량 - :OLD.입고수량
             WHERE 상품코드 = :OLD.상품코드;
    END IF;
END;"
*/

---

• PACKAGE (패키지)

--■■■ 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;
*/

---

• 함수 준비

--○ 주민번호 입력 시 성별을 반환하는 함수
--   이 함수를 구성요소로 포함하는 패키지 작성

-- 함수 준비
CREATE OR REPLACE 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;
--==>> Function FN_GENDER이(가) 컴파일되었습니다.

• 패키지 등록

-- 패키지 등록

-- 1. 명세부 작성(패키지명 : INSA_PACK)
CREATE OR REPLACE PACKAGE INSA_PACK
IS
    FUNCTION FN_GENDER(V_SSN VARCHAR2)
    RETURN VARCHAR2;   
    --// 별도로 추가하는 문법은 없고, 추가 작성 후 REPLACE 하면 됨.
END INSA_PACK;
--==>> Package INSA_PACK이(가) 컴파일되었습니다.
--------------------------------------------------------------------------------------------
-- 2. 몸체부 작성(패키지명 : INSA_PACK)
CREATE OR REPLACE PACKAGE BODY INSA_PACK    --// 명세서에 적어둔 패키지명과 정확히 일치할 것
IS
    --// 함수 만든 구문에서 CREATE OR REPLACE 만 제거
    --// 명세부에 작성한 틀과 몸체부에 작성한 함수의 틀이 정확히 일치할 것
    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이(가) 컴파일되었습니다.

• 패키지 사용

--○ 구성한 패키지가 유효한지 확인(테스트)
--   → 패키지에 논리적으로 구성된 함수 호출 확인
SELECT INSA_PACK.FN_GENDER('960405-2234567') "호출결과 확인"
FROM DUAL;
--==>> 여자
SELECT NAME, SSN, INSA_PACK.FN_GENDER(SSN) "호출결과 확인"
FROM TBL_INSA;
--==>>
/*
희주윤	990215-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	여자
*/