015. [PL/SQL] UPDATE / DELETE PROCEDURE(프로시저), CURSOR (커서), TRIGGER(트리거)

2023.04.11 (화)

 SCOTT 계정 실습

•  UPDATE / DELETE PROCEDURE 프로시저

--○ TBL_출고 테이블에서 출고 수량을 변경(수정)하는 프로시저를 작성한다.
--   프로시저 명 : PRC_출고_UPDATE(출고번호,변경할수량);

- 내 풀이

CREATE OR REPLACE PROCEDURE PRC_출고_UPDATE
( V_출고번호     IN TBL_출고.출고번호%TYPE  
, V_변경할수량   IN TBL_상품.재고수량%TYPE
)
IS
    V_최대수량      TBL_상품.재고수량%TYPE;
    V_기존출고수량  TBL_출고.출고수량%TYPE;
    V_기존재고수량  TBL_상품.재고수량%TYPE;
    
    USER_DEFINE_ERROR   EXCEPTION;
BEGIN
    -- 기존출고수량 담기
    SELECT 출고수량 INTO V_기존출고수량
    FROM TBL_출고
    WHERE 출고번호 = V_출고번호;

    -- 기존재고수량 담기
    SELECT 재고수량 INTO V_기존재고수량
    FROM TBL_상품
    WHERE 상품코드 = (SELECT 상품코드
                      FROM TBL_출고
                      WHERE 출고번호 = V_출고번호);

    -- 변경가능한 최대 수량 확인
    V_최대수량 := (V_기존출고수량) + (V_기존재고수량);

    -- 변경하는 수량이 최대 수량을 넘는지 확인
    IF  (V_변경할수량 > V_최대수량)
        THEN RAISE USER_DEFINE_ERROR;
    END IF;

    -- UPDATE 1
    UPDATE TBL_출고
    SET 출고수량 = V_변경할수량
    WHERE 출고번호 = V_출고번호;
    
    --UPDATE 2
    UPDATE TBL_상품
    SET 재고수량 = V_최대수량 - (V_변경할수량)
    WHERE 상품코드 = (SELECT 상품코드
                      FROM TBL_출고
                      WHERE 출고번호 = V_출고번호);
    
    --커밋       
    COMMIT;
                      
    -- 예외처리
    EXCEPTION
        WHEN    USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-20003, '변경 가능한 수량이 아닙니다.');
                    ROLLBACK;
        WHEN    OTHERS
            THEN    ROLLBACK;
END;
--==>> Procedure PRC_출고_UPDATE이(가) 컴파일되었습니다.
---------------------------------------------------------------------------------------------
EXEC PRC_출고_UPDATE(1,40);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_상품;
SELECT *
FROM TBL_출고;
--==>>
/*
H001	홈런볼	1500	0
1	H001	2023-04-10	40	1500
*/
---------------------------------------------------------------------------------------------
EXEC PRC_출고_UPDATE(1,20);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_상품;
SELECT *
FROM TBL_출고;
--==>>
/*
H001	홈런볼	1500	20
1	H001	2023-04-10	20	1500
*/
---------------------------------------------------------------------------------------------
EXEC PRC_출고_UPDATE(1,50);
--==>> ORA-20003: 변경 가능한 수량이 아닙니다.

CREATE OR REPLACE PROCEDURE PRC_출고_UPDATE
(
    --① 매개변수 구성
  V_출고번호   IN  TBL_출고.출고번호%TYPE
, V_출고수량   IN  TBL_출고.출고수량%TYPE
)
IS
    --③ 쿼리문을 수행하는데 필요한 변수 선언(주요 변수 선언) → 상품코드
    V_상품코드        TBL_상품.상품코드%TYPE;
    
    --⑥ 쿼리문을 수행하는데 필요한 변수 선언(주요 변수 선언) → 이전출고수량
    V_이전출고수량    TBL_출고.출고수량%TYPE;
    
    --⑩ 쿼리문을 수행하는데 필요한 변수 선언(주요 변수 선언) → 재고수량
    V_재고수량        TBL_상품.재고수량%TYPE;
    
    --⑫ 쿼리문을 수행하는데 필요한 변수 선언(주요 변수 선언) → 사용자 정의 예외
    USER_DEFINE_ERROR   EXCEPTION;
    
BEGIN
    --④ 상품코드 파악 / ⑦ 이전출고수량 파악
    SELECT 상품코드, 출고수량 INTO V_상품코드, V_이전출고수량
    FROM TBL_출고
    WHERE 출고번호 = V_출고번호;
    /*
    SELECT 상품코드 
    FROM TBL_출고
    WHERE 출고번호 = 1;
    --==>> H001
    */
    --⑪ ※ 출고를 정상적으로 수행해야 하는지의 여부 판단 필요
    --      변경 이전의 출고수량 및 현재의 재고수량 파악
    SELECT 재고수량 INTO V_재고수량
    FROM TBL_상품
    WHERE 상품코드 = V_상품코드;
    
    --⑬ ※ 파악한 재고수량에 따라 데이터 변경 실시 여부 판단
    --      (『재고수량+이전출고수량 < 현재출고수량』 인 상황이라면... 사용자 정의 예외 발생)
    IF ((V_재고수량+V_이전출고수량) < V_출고수량)
        -- THEN 예외 발생
        THEN RAISE USER_DEFINE_ERROR;
    END IF;
    
    --② 수행해야 할 쿼리문 구성(UPDATE→TBL_출고 / UPDATE→TBL_상품)
    --⑤ TBL_상품 UPDATE 쿼리문의 WHERE 절 추가구성
    --⑧ TBL_상품 UPDATE 쿼리문의 SET 완성
    UPDATE TBL_출고
    SET 출고수량 = V_출고수량
    WHERE 출고번호 = V_출고번호;
    
    UPDATE TBL_상품
    SET 재고수량 = (재고수량 + V_이전출고수량) - V_출고수량
    WHERE 상품코드 = V_상품코드;
    
    --⑨ 커밋
    COMMIT;
    
    --⑭ 예외 처리 (// 절대 중간에 끼워넣지 않고 마지막에 처리한다.)
    EXCEPTION
        WHEN    USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-20002,'재고가 부족합니다');
                    ROLLBACK;
        WHEN    OTHERS
            THEN    ROLLBACK;
    
END;
--==>> Procedure PRC_출고_UPDATE이(가) 컴파일되었습니다.

--○ 생성한 프로시저(→ PRC_출고_UPDATE(출고번호, 변경할수량)) 정상 작동여부 확인
--   → 프로시저 호출
--       『재고10/출고10』 인 새우깡 출고내역 변경 → 『출고30』 
EXEC PRC_출고_UPDATE(2,30);
--==>> 에러 발생
/*
ORA-20002: 재고가 부족합니다
*/

SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	10
H002	새우깡	1200	10
            :
*/

SELECT *
FROM TBL_출고
ORDER BY 1;
--==>>
/*
1	H002	2023-04-11	60	1600
2	H002	2023-04-11	10	1600
3	H001	2023-04-11	40	1600
*/
-----------------------------------------------------------------------------------------
--       『재고10/출고10』 인 새우깡 출고내역 변경 → 『출고20』 
EXEC PRC_출고_UPDATE(2,20);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.

SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	10
H002	새우깡	1200	0
            :
*/

SELECT *
FROM TBL_출고
ORDER BY 1;
--==>>
/*
1	H002	2023-04-11	60	1600
2	H002	2023-04-11	20	1600    ←
3	H001	2023-04-11	40	1600
*/

---

--○ TBL_입고 테이블에서 입고수량을 수정(변경)하는 프로시저를 작성한다.
--   프로시저 명 : PRC_입고_UPDATE(입고번호, 변경할입고수량)

--○ TBL_출고 테이블에서 출고수량을 삭제하는 프로시저를 작성한다.
--   프로시저 명 : PRC_출고_DELETE(출고번호)

--○ TBL_입고 테이블에서 입고수량을 삭제하는 프로시저를 작성한다.
--   프로시저 명 : PRC_입고_DELETE(입고번호)

• PRC_입고_UPDATE(입고번호, 변경할입고수량)

CREATE OR REPLACE PROCEDURE PRC_입고_UPDATE
( V_입고번호        IN  TBL_입고.입고번호%TYPE
, V_변경할입고수량  IN  TBL_입고.입고수량%TYPE
)
IS
    V_상품코드          TBL_상품.상품코드%TYPE;
    V_기존입고수량      TBL_입고.입고수량%TYPE;
    V_기존재고수량      TBL_상품.재고수량%TYPE;
    
    USER_DEFINE_ERROR   EXCEPTION;
BEGIN
    -- V_상품코드 / V_기존입고수량 
    SELECT 상품코드, 입고수량 INTO V_상품코드, V_기존입고수량
    FROM TBL_입고
    WHERE 입고번호 = V_입고번호;
    
    -- V_기존재고수량
    SELECT 재고수량 INTO V_기존재고수량
    FROM TBL_상품
    WHERE 상품코드 = V_상품코드;
    
    -- 기존입고수량 - 변경할입고수량 > 기존재고수량 → 변경불가
    IF (V_기존입고수량 - V_변경할입고수량 > V_기존재고수량)
        THEN RAISE USER_DEFINE_ERROR;
    END IF;

    -- 입고 UPDATE / 재고 UPDATE
    UPDATE TBL_입고
    SET 입고수량 = V_변경할입고수량
    WHERE 입고번호 = V_입고번호;
    
    UPDATE TBL_상품
    SET 재고수량 = (재고수량 - V_기존입고수량) + V_변경할입고수량
    WHERE 상품코드 = V_상품코드;
    
    -- 커밋
    COMMIT;
    
    -- 예외 처리
    EXCEPTION
        WHEN    USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-20002, '재고수량이 부족하여 변경할 수 없습니다.');
                    ROLLBACK;
        WHEN    OTHERS
            THEN    ROLLBACK;
END;
--==>> Procedure PRC_입고_UPDATE이(가) 컴파일되었습니다.

--○ 생성한 프로시저(→ PRC_입고_UPDATE(입고번호, 변경할입고수량)) 정상 작동여부 확인
--   → 프로시저 호출
SELECT *
FROM TBL_입고
ORDER BY 1;
--==>>
/*
1	H001	2023-04-11	30	1500
2	H002	2023-04-11	30	1500
3	H002	2023-04-11	20	1500
4	H002	2023-04-11	30	1500
5	H001	2023-04-11	20	1500
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	10
H002	새우깡	1200	0
            :
*/
--  『입고30/재고10』 인 홈런볼 입고내역 변경 → 『입고10』  
EXEC PRC_입고_UPDATE(1,10);
--==> 에러 발생
/*
ORA-20002: 재고수량이 부족하여 변경할 수 없습니다.
*/
SELECT *
FROM TBL_입고
ORDER BY 1;
--==>>
/*
1	H001	2023-04-11	30	1500
2	H002	2023-04-11	30	1500
3	H002	2023-04-11	20	1500
4	H002	2023-04-11	30	1500
5	H001	2023-04-11	20	1500
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	10
H002	새우깡	1200	0
            :
*/
----------------------------------------------------------------------------------------
--  『입고30/재고10』 인 홈런볼 입고내역 변경 → 『입고20』  
EXEC PRC_입고_UPDATE(1,20);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_입고
ORDER BY 1;
--==>>
/*
1	H001	2023-04-11	20	1500    ←
2	H002	2023-04-11	30	1500
3	H002	2023-04-11	20	1500
4	H002	2023-04-11	30	1500
5	H001	2023-04-11	20	1500
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	0    ← 
H002	새우깡	1200	0
            :
*/

• PRC_출고_DELETE(출고번호)

CREATE OR REPLACE PROCEDURE PRC_출고_DELETE
( V_출고번호    IN  TBL_출고.출고번호%TYPE
)
IS
    V_상품코드      TBL_상품.상품코드%TYPE;
    V_기존출고수량  TBL_출고.출고수량%TYPE;
    
    V_출고번호존재  TBL_출고.출고번호%TYPE;
    
    USER_DEFINE_ERROR   EXCEPTION;
BEGIN
    -- V_출고번호존재 (COUNT 가 0)
    SELECT COUNT(*) INTO V_출고번호존재
    FROM TBL_출고
    WHERE 출고번호 = V_출고번호;
              
    -- 해당 출고번호가 없다면 사용자 에러 발생
    IF (V_출고번호존재 = 0)
        THEN RAISE USER_DEFINE_ERROR;
    END IF;

    --V_상품코드 / V_기존출고수량
    SELECT 상품코드, 출고수량 INTO V_상품코드, V_기존출고수량
    FROM TBL_출고
    WHERE 출고번호 = V_출고번호;
    
    -- DELETE 출고 / UPDATE 상품-재고수량
    DELETE
    FROM TBL_출고
    WHERE 출고번호 = V_출고번호;
    
    UPDATE TBL_상품
    SET 재고수량 = 재고수량 + V_기존출고수량
    WHERE 상품코드 = V_상품코드;
    
    -- 예외처리
    EXCEPTION
        WHEN    USER_DEFINE_ERROR
            THEN    RAISE_APPLICATION_ERROR(-20003,'해당 번호는 존재하지 않습니다.');
                    ROLLBACK;
        WHEN    OTHERS
            THEN    ROLLBACK;
END;

--○ 생성한 프로시저(→ PRC_출고_DELETE(출고번호)) 정상 작동여부 확인
--   → 프로시저 호출
SELECT *
FROM TBL_출고
ORDER BY 1;
--==>>
/*
1	H002	2023-04-11	60	1600
2	H002	2023-04-11	20	1600
3	H001	2023-04-11	40	1600
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	0
H002	새우깡	1200	0
            :
*/
-- 출고번호 2번 새우깡 20개 출고 삭제
EXEC PRC_출고_DELETE(2);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_출고
ORDER BY 1;
--==>>
/*
1	H002	2023-04-11	60	1600
3	H001	2023-04-11	40	1600
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	0
H002	새우깡	1200	20
            :
*/
-----------------------------------------------------------------------------
-- 출고번호 7번 아직 없는디 출고 삭제
EXEC PRC_출고_DELETE(7);
--==>> 에러 발생
/*
ORA-20003: 해당 번호는 존재하지 않습니다.
*/

• PRC_입고_DELETE(입고번호)

--○ TBL_입고 테이블에서 입고수량을 삭제하는 프로시저를 작성한다.
--   프로시저 명 : PRC_입고_DELETE(입고번호)

CREATE OR REPLACE PROCEDURE PRC_입고_DELETE
( V_입고번호    IN  TBL_입고.입고번호%TYPE
)
IS
    V_상품코드      TBL_상품.상품코드%TYPE;
    V_입고수량      TBL_입고.입고수량%TYPE;
    V_재고수량      TBL_상품.재고수량%TYPE;
    
    USER_DEFINE_ERROR   EXCEPTION;
BEGIN
    --V_상품코드 / V_입고수량
    SELECT 상품코드, 입고수량 INTO V_상품코드, V_입고수량
    FROM TBL_입고
    WHERE 입고번호 = V_입고번호;
    
    --V_재고수량
    SELECT 재고수량 INTO V_재고수량
    FROM TBL_상품
    WHERE 상품코드 = V_상품코드;
    
    -- 재고수량이 삭제할 수량보다 많이 남아 있는지 확인
    IF (V_재고수량 < V_입고수량)
        THEN RAISE USER_DEFINE_ERROR;
    END IF;
    
    -- 입고 DELETE / 상품-재고 UPDATE 
    DELETE
    FROM TBL_입고
    WHERE 입고번호 = V_입고번호;
    
    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_입고_DELETE이(가) 컴파일되었습니다.

--○ 생성한 프로시저(→ PRC_입고_DELETE(입고번호)) 정상 작동여부 확인
--   → 프로시저 호출
SELECT *
FROM TBL_입고
ORDER BY 1;
--==>>
/*
1	H001	2023-04-11	20	1500
2	H002	2023-04-11	30	1500
3	H002	2023-04-11	20	1500
4	H002	2023-04-11	30	1500
5	H001	2023-04-11	20	1500*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	0
H002	새우깡	1200	20
            :
*/
-- 입고번호 3번 새우깡 20개 입고 삭제
EXEC PRC_입고_DELETE(3);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.
SELECT *
FROM TBL_입고
ORDER BY 1;
--==>>
/*
1	H001	2023-04-11	20	1500
2	H002	2023-04-11	30	1500
4	H002	2023-04-11	30	1500
5	H001	2023-04-11	20	1500
*/
SELECT *
FROM TBL_상품;
--==>>
/*
H001	홈런볼	1500	0
H002	새우깡	1200	0
            :
*/
------------------------------------------------------------------------------------
-- 입고번호 2번 새우깡 30개 입고 삭제
EXEC PRC_입고_DELETE(2);
--==>> 에러 발생
/*
ORA-20002: 재고 수량이 부족합니다.
*/

---

• CURSOR (커서)

--■■■ CURSOR(커서) ■■■--

-- 1. 오라클에서 하나의 레코드가 아닌 여러 레코드로 구성된
--    작업 영역에서 SQL 문을 실행하고 그 과정에서 발생한 정보를
--    저장하기 위하여 커서(CURSOR)를 사용하며.
--    커서에는 암시적 커서와 명시적 커서가 있다.

-- 2. 암시적 커서는 모든 SQL 문에 존재하며,
--    SQL 실행 후 오직 하나의 행(ROW)만 출력하게 된다.
--    그러나 SQL 문을 실행한 결과물(RESULT SET)이
--    여러 행으로 구성된 경우
--    커서(CURSOR)를 명시적으로 선언해야 여러 행을 다룰 수 있다.

• 관찰

- 커서 이용 전

--○ 커서 이용 전 상황(단일 행 접근 시)
SET SERVEROUTPUT ON;

DECLARE
    V_NAME  TBL_INSA.NAME%TYPE;
    V_TEL   TBL_INSA.TEL%TYPE;
BEGIN
    SELECT NAME, TEL INTO V_NAME, V_TEL
    FROM TBL_INSA
    WHERE NUM=1001;
    
    DBMS_OUTPUT.PUT_LINE(V_NAME|| ' : ' || V_TEL);
END;
--==>> 
/*
홍길동 : 011-2356-4528


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

--○ 커서 이용 전 상황(다중 행 접근 시)
DECLARE
    V_NAME  TBL_INSA.NAME%TYPE;
    V_TEL   TBL_INSA.TEL%TYPE;
BEGIN
    SELECT NAME, TEL    INTO    V_NAME, V_TEL
    FROM TBL_INSA;
    
    DBMS_OUTPUT.PUT_LINE(V_NAME|| ' : ' ||V_TEL);
END;
--==>> 에러 발생
/*
ORA-01422: exact fetch returns more than requested number of rows
*/

--○ 커서 이용 전 상황(다중 행 접근 시 - 반복문을 활용)
DECLARE
    V_NAME  TBL_INSA.NAME%TYPE;
    V_TEL   TBL_INSA.TEL%TYPE;
    V_NUM   TBL_INSA.NUM%TYPE :=1001;
BEGIN
    LOOP
        SELECT NAME, TEL    INTO    V_NAME, V_TEL
        FROM TBL_INSA
        WHERE NUM = V_NUM;
    
        DBMS_OUTPUT.PUT_LINE(V_NAME|| ' : ' ||V_TEL);
        V_NUM := V_NUM +1;              -- V_NUM++ / V_NUM+=1
        
        EXIT WHEN V_NUM >= 1061;
        
    END LOOP;
    
END;
--==>>
/*
홍길동 : 011-2356-4528
이순신 : 010-4758-6532
이순애 : 010-4231-1236
김정훈 : 019-5236-4221
한석봉 : 018-5211-3542
이기자 : 010-3214-5357
장인철 : 011-2345-2525
김영년 : 016-2222-4444
나윤균 : 019-1111-2222
김종서 : 011-3214-5555
유관순 : 010-8888-4422
정한국 : 018-2222-4242
조미숙 : 019-6666-4444
황진이 : 010-3214-5467
이현숙 : 016-2548-3365
이상헌 : 010-4526-1234
엄용수 : 010-3254-2542
이성길 : 018-1333-3333
박문수 : 017-4747-4848
유영희 : 011-9595-8585
홍길남 : 011-9999-7575
이영숙 : 017-5214-5282
김인수 : 
김말자 : 011-5248-7789
우재옥 : 010-4563-2587
김숙남 : 010-2112-5225
김영길 : 019-8523-1478
이남신 : 016-1818-4848
김말숙 : 016-3535-3636
정정해 : 019-6564-6752
지재환 : 019-5552-7511
심심해 : 016-8888-7474
김미나 : 011-2444-4444
이정석 : 011-3697-7412
정영희 : 
이재영 : 011-9999-9999
최석규 : 011-7777-7777
손인수 : 010-6542-7412
고순정 : 010-2587-7895
박세열 : 016-4444-7777
문길수 : 016-4444-5555
채정희 : 011-5125-5511
양미옥 : 016-8548-6547
지수환 : 011-5555-7548
홍원신 : 011-7777-7777
허경운 : 017-3333-3333
산마루 : 018-0505-0505
이기상 : 
이미성 : 010-6654-8854
이미인 : 011-8585-5252
권영미 : 011-5555-7548
권옥경 : 010-3644-5577
김싱식 : 011-7585-7474
정상호 : 016-1919-4242
정한나 : 016-2424-4242
전용재 : 010-7549-8654
이미경 : 016-6542-7546
김신제 : 010-2415-5444
임수봉 : 011-4151-4154
김신애 : 011-4151-4444


PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/

--○ 커서 이용 후 상황(다중 행 접근 시)
DECLARE
    V_NAME  TBL_INSA.NAME%TYPE;
    V_TEL   TBL_INSA.TEL%TYPE;
    
    --// 예외처럼 커서도 선언부에 선언(정의)하여 사용하게 된다
    -- 커서 이용을 위한 커서변수 선언(→ 커서 정의)
    --// ex) CREATE TABLE 테이블명 (TABLE이 타입인것처럼 CURSOR도 동일하게)(커서도 타입이 크다!)
    --// ★1★
    CURSOR CUR_INSA_SELECT
    IS
    SELECT NAME,TEL
    FROM TBL_INSA;
    --// 커서(깜짝상자)에 우겨넣은 상태인것 
BEGIN
    -- 커서 오픈
    --//와아ㅏㅏ아ㅏㅏ아아아아아아ㅏ아 하고 튀어나옴
    OPEN CUR_INSA_SELECT;    
    -- 커서 오픈 시 쏟아져 나오는 데이터들 처리(잡아내기)
    LOOP
        -- 한 행 한 행 끄집어내어 가져오는 행위 → 『FETCH』
        FETCH CUR_INSA_SELECT INTO V_NAME, V_TEL;
    --//--------------------- 
    --// └IS 뒤에 넣어둔 「SELECT NAME,TEL FROM TBL_INSA; 」임
        
        --// 상자 안에 아무 것도 없을 때 반복문을 빠져나가라
        EXIT WHEN CUR_INSA_SELECT%NOTFOUND;        
        -- 출력
        DBMS_OUTPUT.PUT_LINE(V_NAME|| ' : ' ||V_TEL);       
    END LOOP;
    
    -- 커서 클로즈
    CLOSE CUR_INSA_SELECT;
    --// 다시 사용하기 위해서는 상자를 닫아놔야함.
    
    --// ★오라에서 레퍼런스하는 커서의 경우 (JDBC하면서..?)
    --// (커서는 실제로 데이터를 담아두는 형태는 아닙니다.(레이저)포인터처럼 멀리서 가리키는 느낌)
    --// 닫아버리면 다시 접근이 불가능하기 때문에 상황에 따라서 닫지 말아야하는 경우도 있다.
END;

---

• 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이(가) 변경되었습니다.

• 관찰

- 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
WHERE ID=1;
--==>> 1 행 이(가) 삭제되었습니다.

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

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

---

- BEFORE STATEMENT TRIGGER (내일 이어서...)