Everything has an expiration date
[Oracle - PL/SQL] 20231107 [프로그램 소스] 본문
- PROCEDURE(출고 업데이트, 입고수량 업데이트, 입고수량 삭제, 출고수량 삭제)
- CURSOR
- TRIGGER
[처음 배운 개념] - `:OLD` , `:NEW`
Ⅰ [UPDATE의 의미]
--※ UPDATE
내부적으로 DELETE 그리고 INSERT 가 결합된 형태
UPDATE 하기 이전의 데이터는 『:OLD』
UPDATE 수행한 이후의 데이터는 『:NEW』
Ⅱ [`:OLD`, `:NEW`]
20231107_01_SCOTT(PLSQL).SQL
SELECT USER
FROM DUAL;
--==>> SCOTT
--○ TBL_출고 테이블에서 출고수량을 수정(변경)하는 프로시저를 작성한다.
-- 프로시저명 : PRC_출고_UPDATE()
/*
실행 예)
EXEC PRC_출고_UPDATE(출고번호, 변경할수량);
*/
-- 잘못 들어갔던 출고수량을 다시 더해주고, 그 결과값에서 변경할 수량을 빼 준다.
-- 20 -> 50 가능
-- 20 -> 60 불가능(재고수량초과)
--********************************************************************
-- ① 잘못 들어갔던 출고 수량을 상품의 재고 수량에 더해준다.
-- ② 그 수량이 현재의 변경할 수량보다 작거나 같으면 : 변경 가능하다.
-- 크면 : 변경이 불가능하다!!!!
--********************************************************************
--[선생님 프로시저~!!!]
CREATE OR REPLACE PROCEDURE PRC_출고_UPDATE2
(
-- ① 매개변수 구성
V_출고번호 IN TBL_출고.출고번호%TYPE
, V_출고수량 IN TBL_출고.출고수량%TYPE
)
IS
-- ③ 필요한 변수 선언
V_상품코드 TBL_상품.상품코드%TYPE;
V_이전출고수량 TBL_출고.출고수량%TYPE;
V_재고수량 TBL_상품.재고수량%TYPE;
USER_DEFINE_ERROR EXCEPTION;
BEGIN
-- ④ 선언한 변수에 값 담아내기
-- ★ [INTO] : 한꺼번에 두 개의 변수에 값을 담을 수 있다는것을 항상 기억하자! ★
SELECT 상품코드, 출고수량 INTO V_상품코드, V_이전출고수량
FROM TBL_출고
WHERE 출고번호 = V_출고번호;
-- ⑧ 출고 정상수행여부 판단 필요
-- 변경 이전의 출고수량 및 현재의 재고수량 확인
SELECT 재고수량 INTO V_재고수량
FROM TBL_상품
WHERE 상품코드 = V_상품코드;
-- ⑨ 파악한 재고수량에 따라 데이터 변경 실시 여부 판단
-- (『재고수량 + 이전출고수량 < 현재출고수량』인 상황이라면... 사용자정의 예외 발생)
IF (V_재고수량 + V_이전출고수량 < V_출고수량)
THEN RAISE USER_DEFINE_ERROR;
END IF;
-- ② 수행될 쿼리문 체크(UPDATE→TBL_출고 / UPDATE→TBL_상품)
UPDATE TBL_출고
SET 출고수량 = V_출고수량
WHERE 출고번호 = V_출고번호;
-- ⑤
-- 『상품 재고수량 : 재고수량 + 이전 출고수량 - 변경할 출고수량』
UPDATE TBL_상품
SET 재고수량 = 재고수량 + V_이전출고수량 - V_출고수량
WHERE 상품코드 = V_상품코드;
-- ⑩ 예외 처리
EXCEPTION
WHEN USER_DEFINE_ERROR
THEN RAISE_APPLICATION_ERROR(-20002, '재고 부족~!!!');
ROLLBACK;
WHEN OTHERS
THEN ROLLBACK;
-- ⑦ 커밋
COMMIT;
END;
--==>> Procedure PRC_출고_UPDATE이(가) 컴파일되었습니다.
-- [변수선언]
-- USER_DEFINE_ERROR EXCEPTION
-- IF (재고수량 + V_이전출고수량 - V_출고수량)
-- THEN
--
-- END IF;
/*
EXCEPTION
WHEN USER_DEFINE_ERROR
THEN RAISE_APPLICATION_ERROR(20002, '재고부족!!');
ROLLBACK;
WHEN OTHERS
THEN ROLLBACK;
*/
/*
1. PRC_입고_UPDATE(입고번호, 입고수량)
2. PRC_입고_DELETE(입고번호)
3. PRC_출고_DELETE(출고번호)
*/
-- 원래들어있는 상품의 입고내역이 10개이다
-- 그것으로 인해서 현재 재고수량이 0이다.
-- 입고가 10개 됐는데 어떻게 0?
-- 10개였던 수량을 5개로 바꾼다? 불가능!
-- 출고가 10개라면... 불가!
-- 1. 입고를 업데이트 한다는 건 어떤 의미일까...
-- #1 : 입고 번호에 해당하는 상품코드의 상품 재고 수량을 증가시키는 것이다.
-- #2 : 입고 수량이 출고 테이블에 있는 레코드의 총 수량만큼은 커야한다.
-- #3 : 변경된 입고 수량으로 인해 재고 수량이 출고수량보다 크거나 같다면 (○)
-- : 변경된 입고 수량으로 인해 재고 수량이 출고수량보다 작다면 (Ⅹ)
SELECT *
FROM TBL_입고;
SELECT *
FROM TBL_상품;
SELECT *
FROM TBL_출고;
-- 1. 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;
V_총출고수량 TBL_출고.출고수량%TYPE;
V_총입고수량 TBL_입고.입고수량%TYPE;
V_최종재고수량 TBL_상품.재고수량%TYPE;
USER_DEFINE_ERROR EXCEPTION;
BEGIN
-- ⓐ 입고 번호에 해당하는 상품 알아내기 [V_상품코드]
SELECT 상품코드 INTO V_상품코드
FROM TBL_입고
WHERE 입고번호 = V_입고번호;
-- ⓑ 상품의 재고수량 확인하기 (TBL_상품) [V_재고수량]
SELECT 재고수량 INTO V_재고수량
FROM TBL_상품
WHERE 상품코드 = V_상품코드;
-- ⓒ 상품의 총 출고량 확인하기 (TBL_출고) [V_총출고수량]
SELECT SUM(출고수량) INTO V_총출고수량
FROM TBL_출고
WHERE 상품코드 = V_상품코드
GROUP BY 상품코드;
-- ⓓ 상품코드에 맞는 입고 총 입고수량 확인하기 [V_총입고수량]
SELECT SUM(입고수량) INTO V_총입고수량
FROM TBL_입고
WHERE 상품코드 = V_상품코드
GROUP BY 상품코드;
-- ⓔ 입고번호에 맞는 이전 입고수량 확인하기 [V_이전입고수량]
SELECT 입고수량 INTO V_이전입고수량
FROM TBL_입고
WHERE 입고번호 = V_입고번호;
--[V_최종재고수량]
-- 이전 입고수량을 초기화 하고
-- 변경된 입고수량을 더한 최종 재고수량 구하기 (이전입고수량 취소)
-- [V_총입고수량 - V_이전입고수량 + V_입고수량(변경후 입고수량) - V_총출고수량]
V_최종재고수량 := V_총입고수량 - V_이전입고수량 + V_입고수량 - V_총출고수량;
-- ⓔ 변경 가능한지 확인.
-- #3 : 변경된 입고 수량으로 인해 재고 수량이 출고수량보다 크거나 같다면 (○)
-- : 변경된 입고 수량으로 인해 재고 수량이 출고수량보다 작다면 (Ⅹ)
-- 최종재고수량이 음수가 됐다면, 변경이 불가능한 입고 업데이트 수량이므로
-- 사용자 정의 예외를 발생시켜야 한다.
IF (V_최종재고수량 < 0)
THEN RAISE USER_DEFINE_ERROR;
END IF;
-- 업데이트 실행
-- (TBL_입고 테이블의 입고번호에 맞는 입고 수량을 V_입고수량으로 변경)
UPDATE TBL_입고
SET 입고수량 = V_입고수량
WHERE 입고번호 = V_입고번호;
-- (TBL_상품 테이블의 상품코드에 맞는 상품의 재고를 최종재고수량으로 변경)
UPDATE TBL_상품
SET 재고수량 = V_최종재고수량
WHERE 상품코드 = V_상품코드;
-- 예외가 발생했을 때 처리
EXCEPTION
WHEN USER_DEFINE_ERROR
THEN RAISE_APPLICATION_ERROR(-20004, '입고수량 잘못됨!');
ROLLBACK;
WHEN OTHERS
THEN ROLLBACK;
COMMIT;
END;
--==>> Procedure PRC_입고_UPDATE이(가) 컴파일되었습니다.
/*
입고
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10
10 10
11 10
12 10
모든 시간대의 입고 재고 출고를 고려해야한다.
입고 테이블 업데이트 시점, 호출시점
입고가 일어난 시간 출고가 일어난시간 재고가 변경된 시간을 다 따져야 한다.
그렇게까지 접근하진 않는다.
현재 재고상황하고 트랜 잭션 처리를 해라.
미래의 것, 이전의 과거것 까지 모두 고려해서 만드는 것은 상당히 어렵고 힘들다.
결과적으로 재고가 '-'가 되지 않게끔만 구성하면 된다.
*/
/*
1. PRC_입고_UPDATE(입고번호, 입고수량)
2. PRC_입고_DELETE(입고번호)
3. PRC_출고_DELETE(출고번호)
*/
--2. PRC_입고_DELETE(입고번호)
CREATE OR REPLACE PROCEDURE PRC_입고_DELETE
( V_입고번호 IN TBL_입고.입고번호%TYPE
)
IS
V_상품코드 TBL_상품.상품코드%TYPE;
V_재고수량 TBL_상품.재고수량%TYPE;
V_최종재고수량 TBL_상품.재고수량%TYPE;
V_이전입고수량 TBL_입고.입고수량%TYPE;
USER_DEFINE_ERROR EXCEPTION;
BEGIN
-- #1 입고번호에 해당하는 상품코드, 이전입고수량 확인
SELECT 상품코드, 입고수량 INTO V_상품코드, V_이전입고수량
FROM TBL_입고
WHERE 입고번호 = V_입고번호;
-- #2 상품코드의 재고 수량 확인
SELECT 재고수량 INTO V_재고수량
FROM TBL_상품
WHERE 상품코드 = V_상품코드;
-- #3 최종 재고수량 계산 (재고수량 - 이전입고수량)
V_최종재고수량 := V_재고수량 - V_이전입고수량;
-- #4 재고수량 - 이전입고수량 < 0 이 되선 안 된다.
IF (V_최종재고수량 < 0)
THEN RAISE USER_DEFINE_ERROR;
END IF;
-- #5 DELETE, UPDATE
-- 1 (TBL_입고 = 입고번호에 맞는 레코드 제거)
-- 2 (TBL_상품 = 재고수량 - 이전입고수량)
DELETE
FROM TBL_입고
WHERE 입고번호 = V_입고번호;
UPDATE TBL_상품
SET 재고수량 = 재고수량 - V_이전입고수량
WHERE 상품코드 = V_상품코드;
-- #6 예외처리
EXCEPTION
WHEN USER_DEFINE_ERROR
THEN RAISE_APPLICATION_ERROR(-20005, '입고취소불가능!');
ROLLBACK;
WHEN OTHERS
THEN ROLLBACK;
-- #7 커밋
COMMIT;
END;
--3. PRC_출고_DELETE(출고번호)
-- 출고번호에 맞는 상품코드 확인
-- 상품코드에 해당하는 상품의 재고수량 + 취소된 출고수량
CREATE OR REPLACE PROCEDURE PRC_출고_DELETE
( V_출고번호 IN TBL_출고.출고번호%TYPE
)
IS
V_상품코드 TBL_상품.상품코드%TYPE;
V_출고수량 TBL_출고.출고수량%TYPE;
V_재고수량 TBL_상품.재고수량%TYPE;
V_최종재고수량 TBL_상품.재고수량%TYPE;
BEGIN
-- #1 출고번호에 맞는 상품코드 확인, 출고수량 확인
SELECT 상품코드, 출고수량 INTO V_상품코드, V_출고수량
FROM TBL_출고
WHERE 출고번호 = V_출고번호;
-- #2 상품코드에 맞는 재고수량 확인
SELECT 재고수량 INTO V_재고수량
FROM TBL_상품
WHERE 상품코드 = V_상품코드;
-- #3 최종 재고수량 구하기
V_최종재고수량 := V_재고수량 + V_출고수량;
-- 최종 재고수량으로 업데이트
-- ⓐ TBL_출고 출고번호에 해당하는 레코드 제거
DELETE
FROM TBL_출고
WHERE 출고번호 = V_출고번호;
-- ⓑ TBL_상품 재고수량 = V_최종재고수량으로 변경.
UPDATE TBL_상품
SET 재고수량 = V_최종재고수량
WHERE 상품코드 = V_상품코드;
-- 예외가 발생할 경우, ROLLBACK을 처리하도록 구성.
-- 출고수량 NULL로 처리했을 때, 한번 지우고 나서 다시 실행했을 경우
-- 지웠던 항목이 다시 생겨나는 오류가 발생했었음...
-- 이유는 뭔지 모르겠음...ㅠㅠ
EXCEPTION
WHEN OTHERS
THEN ROLLBACK;
-- 커밋
COMMIT;
END;
--------------------------------------------------------------------------------
--■■■ CURSOR(커서) ■■■--
--1. 오라클에서는 하나의 레코드가 아닌 여러 레코드로 구성된
-- 작업 영역에서 SQL 문을 시행하고 그 과정에서 발생한 정보를
-- 저장하기 위해 커서(CURSOR)를 사용하며
-- 커서에는 암시적인 커서와 명시적인 커서가 있다.
--2. 암시적 커서는 모든 SQL 구문에 존재하며
-- SQL 문 실행 후 오직 하나의 행(ROW)만 출력하게 된다.
-- 그러나 SQL 문을 실행한 결과물(RESULT SET)이
-- 여러 행(ROW)으로 구성된 경우
-- 커서(CURSOR)를 명시적으로 선언해야 여러 행(ROW)을 다룰 수 있다.
--커서 - [깜짝 상자]와도 같은 느낌...
--커서 = 모든 SQL 구문에 존재...
--커서 = 상자처럼 생김... 내용물을압축해서 그 상자 안에 넣고 뚜껑을 닫으면...
--뚜껑을 여는 순간 튀어나온다... 한꺼번에 왁 하고 쏟아져 나온다...?
--○ 커서 이용 전 상황(단일 행 접근 시)
-- 서버의 아웃풋을 ON한다. 출력을 할 수 있도록. 세션변경.
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 프로시저가 성공적으로 완료되었습니다.
*/
--○ 커서 이용 전 상황(다중 행 접근 시)
-- 다중행을 처리하려면, WHERE 조건절(입사번호 비교)만 삭제하면 된다.
-- 그러면 전체 사원을 조회하므로...
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)
-- 결과물이 하나의 행이 아니므로 오류가 발생한다.
SELECT *
FROM TBL_INSA;
-- WHILE LOOP를 할 때, 괄호를 씌우면 오류가 발생한다.
-- JAVA할 때 습관 때문에...
DECLARE
V_NAME TBL_INSA.NAME%TYPE;
V_TEL TBL_INSA.TEL%TYPE;
N NUMBER;
BEGIN
N := 1001;
WHILE (N <= 1061) LOOP
SELECT NAME, TEL INTO V_NAME, V_TEL
FROM TBL_INSA
WHERE NUM = N;
DBMS_OUTPUT.PUT_LINE(V_NAME || ' -- ' || V_TEL);
N := N + 1;
END LOOP;
END;
--[선생님 풀이!!!]
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;
EXIT WHEN V_NUM >= 1062;
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
최혜인 -- 010-2509-1783
PL/SQL 프로시저가 성공적으로 완료되었습니다.
*/
/*
일반변수 → 선언
예외변수 → 선언
변수명 자료형;
V_NUM NUMBER;
U_ERR EXCEPTION;
커서명 CURSOR; --(Ⅹ)
*/
/*
커서 → 정의
(선언과는 달리)
(자료형 변수명 으로 구성이 된다.)
TABLE 테이블명
INDEX 인덱스명
USER 유저명
CURSOR 커서명 --(○)
*/
--[전체적인 커서 사용의 흐름 재정리]==============================================
-- 커서를 정의한다고 한 이유 : 데이터타입을 먼저 적은 후 변수명(커서명)을 적으므로
-- 커서 정의 : VIEW를 정의하는 것과 유사하다.
-- 만들어진 커서는 뚜껑을 열어서 오픈해야 쓸 수 있다.
-- 커서를 오픈해야 한다. -> 오픈 커서
-- 커서를 오픈하면 데이터가 와~하고 쏟아져 나오기 때문에
-- 배추, 연탄을 나르듯이 한 행 한 행 커서를 가져오는 형태로 담는다.
-- 한 행 한 행 변수에 담았으면 그것을 출력한다.
-- 담고 쓰고 담고 쓰고... 이런 반복을 멈추는 조건은
-- 커서를 참조할 때 그 상태가 NOTFOUND 상태이면 반복을 멈추면 된다.
-- 모든 데이터를 출력하고 나면 커서를 닫아준다.
-- 클로즈 커서.
-- 오라클에서 자동으로 닫아주는 커서들은 오히려 클로즈 구문 때문에 에러가 나는 것도 있다.
-- 그런 커서의 경우에는 클로즈 커서 구문을 작성하지 않으면 오류가 해결된다.
--==============================================================================
--○ 커서 이용 후 상황
DECLARE
-- 주요 변수 선언
V_NAME TBL_INSA.NAME%TYPE;
V_TEL TBL_INSA.TEL%TYPE;
-- 커서 이용을 위한 커서 정의 CHECK~!!!
--【 앞 TYPE, 뒤 변수명 】
-- 이 커서는, TBL_INSA 테이블의 NAME 컬럼과 TEL 컬럼을 조회하는 커서이다.
-- 깜짝 상자 안에, TBL_INSA의 NAME, TEL 정보를 꾹 눌러담아서 뚜껑을 덮어둔 상태!!
CURSOR CUR_INSA_SELECT
IS
SELECT NAME, TEL
FROM TBL_INSA;
BEGIN
-- *(커서는 열어야 쓸 수 있다!)
-- 오픈 커서
OPEN CUR_INSA_SELECT;
-- 안에 꾹 눌러담아 놨던, TBL_INSA의 NAME, TEL 정보가 와악!! 하고 나온다...
-- 커서 뚜껑을 연 상황 → 데이터가 하나씩 계속 날라오는 상황!!
-- 연탄을 하나씩 넘겨넘겨 넘겨서 받어 넘겨받어!!!
-- 커서 오픈 시 (와~!!!) 쏟아져나오는 데이터를 처리(반복문을 활용하여 처리)
LOOP
--# [FETCH] : 가져오다.
-- 한 행 한 행 받아다가 처리하는 행위 → 가져오다(데려오다) → 『FETCH』
-- 패치하겠다. 커서명...
-- CUR_INSA_SELECT 내부에... 아래의 쿼리문이 있는 것.
-- [SELECT NAME, TEL
-- FROM TBL_INSA;]
-- 이것을 변수에 저장하듯이 담아낼 수 있다 하나씩...
FETCH CUR_INSA_SELECT INTO V_NAME, V_TEL;
-- 언제까지??? (EXIT WHEN)
-- → 커서에서 더 이상 데이터가 쏟아져 나오지 않는 상태... → 『NOTFOUND』
-- [%] : 참조하기 위한 기호
-- 참조하려고 봤더니... NOTFOUND야...
-- 더 이상 쏟아져 나올 데이터가 없어. 바닥이 보여. 이때까지 반복해라...
EXIT WHEN CUR_INSA_SELECT%NOTFOUND;
-- 출력
DBMS_OUTPUT.PUT_LINE(V_NAME || ' -- ' || V_TEL);
END LOOP;
-- 커서를 사용했으면 닫아 주어야 한다.
-- 클로즈 커서
CLOSE CUR_INSA_SELECT;
END;
--------------------------------------------------------------------------------
--■■■ TRIGGER(트리거) ■■■--
-- 부비트랩 : 계속 저걸 지켜보고 있다가... 내가 그 자리에 없더라도
-- 뭔가를 설치해놓고 자리를 비웠어. 그렇더라도... 내가 원하던 그 일이
-- 벌어지게끔 만든다!!
-- ▶ 설치만 해두고... 나는 그 자리를 떠난다!
-- 거기를 지나가는 당사자는 자기가 그랬다는 걸 모르고... 폭탄에 당해버려...
-- 사전적인 의미 : 방아쇠, 촉발시키다, 야기하다, 유발하다.
-- 1. TRIGGER(트리거)란 DML 작업 즉, INSERT, UPDATE, DELETE 작업이 일어날 때
-- 자동적으로 실행되는(유발되는, 촉발되는) 객체로
-- 이와 같은 특징을 강조하여 DML TRIGGER 라고 부르기도 한다.
-- TRIGGER 는 무결성 뿐 아니라
-- 다음과 같은 작업에도 널리 사용된다.
-- 자동으로 파생된 열 값 생성
--==>> 내가 자리를 비웠을 떄 떠들면... 떠든 사람 : 김뫄뫄 반장이나 부반장이 작성...
-- 노트를 열어두고, 자동으로 이름을 적게끔 만든다.
-- 잘못된 트랜잭션 방지
--==>> 입고가 하나 했으면, 재고가 하나 늘고... 출고가 하나 되면 재고가 하나 줄어들도록.
-- 모두 성공했을 때만 커밋. 하나라도 실패하면 ROLLBACK
-- 복잡한 보안 권한 강제 수행
--==>> 복잡한 업무 규칙 강요. 업무 시간에는 주식 사이트에 접속 못하도록 막아놨어.
-- 업무시간에는 메시지 시스템이 안 돌도록 그룹 웨어 안에다가 그런 기능을 삽입해놨어.
-- 그렇게 되면 업무시간이 지난 후에만 사용가능한 것. 이것과 비슷한 개념.
-- 분산 데이터베이스 노드 상에서 참조 무결성 강제 수행
-- [참조 무결성] : 저 쪽에 있는걸. 사원을 등록하는 과정에서 부서번호 10, 20 30만 가능하도록 하는것이 참조무결성
-- 서버가 나눠져있는 것. 서로 다른 테이블들끼리 제약조건을 둬야하는데 그럴 수 없는 상황이다. (분산되어 있으므로)
-- DEPT → EMP 같은 테이블스페이스이면 제약조건 설정 가능
-- 10 20 30 번 외에 40번 부서번호를 갖는 사원을 EMP 테이블에 입력 못하게끔 하는것이 참조무결성.
-- 가시거리 안에 있기 때문에 제약조건 설정이 가능한 것이다.
-- EMP (하나의 테이블스페이스(서버)) : [서울]에 있어
-- DEPT : [대전]에 있어
-- 같은 서버에 존재하지 않으므로 하나의 테이블스페이스 안에 테이블이 존재하지 않으므로(DEPT가 서울에 없음)
-- 제약조건을 설정하기가 힘들지 않을까... → 가능하게끔 하려면?
-- 40번 입력이 불가능하도록 할 때 트리거를 사용할 수 있다는 의미이다.
-- 복잡한 업무 규칙 강제 적용
-- 투명한 이벤트 로깅 제공
-- 누가 무언가를 INSERT했다...와 같은 LOG들을..
-- LOG (요새로 접근하는 나무다리. 성 안에 들어가는 역할.)
-- 그렇기 때문에 LOG IN(성안에 들어감)
-- 다리 위에 있는상태 LOG ON!!
-- 다리를 통과해서 들어오는 사람마다 기록을 남긴다 -> 로그기록
-- 그런 로그를 남기는 행위 : 로깅.
-- 이벤트가 발생했다는 기록을 남기는 것을 로그기록.
-- 복잡한 감사 제공
-- 지켜보고 감시한다.
-- 동기 테이블 복제 유지관리
-- 동기 / 비동기... 나중에 쌤이 정리해 줄게요
-- 동기화 : 한 쪽에서 어떤 일이 일어났을 때, 다른 쪽에서도 그 일이 발생하도록 처리하는 것.
-- INSERT를 프로시저를 통하지 않고 한 쪽 테이블에서의 인서트 밸류가 다른 쪽으로도 자동으로 저장되는 것.
-- 테이블 엑세스 통계 수집
-- 이 테이블에 누가 접근했는지의 통계.
-- 경태가 이 테이블에 3번 INSERT했네, 민지가 3번 DELETE했네 그것을 적어놔야
-- 통계를 낼 수 있으니까. 그걸 도와주는 것이 트리거.
-- 사람이 그걸 직접 일일이 감시하면서 진행하기에는 어려움이 있다는 특징이 있다.
-- 점심시간 아니면 웹 브라우저 금지 : 사람이 일일이 점검하기에는 매우 어렵다.
-- 이런걸 도와주는 것이 트리거.
--(프로시저 내부에서는 가능했지만... 트리거는...?)
-- 호출하는형식으로 쓰는것이 아니라, 사용자가 트리거가 있는지도 모르고 쓰게 된다는 것이다.
-- INSERT나 DELETE를 했다면 사용자가 COMMIT을 해야한다. 그렇기에 트리거 내부에 적지 않는 것이다.
-- 2. TRIGGER(트리거) 내에서는 COMMIT, ROLLBACK 문을 사용할 수 없다.
-- 일과시간에만 이용할 수 있는 테이블 (이 시간 동안에는 못하거나, 이 시간에만 하게끔 조건.)
-- 접근 못하게끔 막는다 : 사후에 막으면 돼요? 안 돼요!
-- 맞고나서 막으면... 아파..... BEFORE에 막아야 돼...
-- 술취한 사람에게 잡히지 말자...
-- 3. 특징 및 종류
-- - BEFORE STATEMENT
-- - BEFORE ROW
-- (앞에서 동작하는 트리거)
-- - AFTER STATEMENT
-- - AFTER ROW
-- (사후에 동작해야 하는 트리거)
-- 떠든사람 노트에 이름을 적는다. ...주형이가 5분 뒤에 떠들 것 같은데? 적어...안돼!
-- 떠들것 같다고해서 적으면 안 되고, 진짜 떠들었을 때만 이름을 적어야지!
-- AFTER TRIGGER!!
--[단일값]
-- STATE : 문장 - 하나의 케이스에 대해서 동작
-- (떠든 사람의 이름 하나만 적어야 한다. 이 레코드가 있는지 없는지 전부확인할 필요X)
--[전체 행 모두!]
-- ROW : 모든 행 - 행을 전부 스캔해서 확인해 보면서 동작해야 돼
-- (바밤바 하나 입고 - 어떤 레코드가 바밤바 레코드인지 확인해야 한다. 각각의 행들을 살펴봐야 하는것!)
-- 이벤트 : INSERTING, UPDATING, ...
-- 모든 이벤트 : 전부 기술
-- ON [테이블명] : 부비트랩을 어디에다가 장착할래?? 어떤 테이블에다가 장착해서
-- 누가 건드리면 폭발하게끔 할래??
-- 트리거가 각각의 행 모두 스캔해야 하는 ROW타입 트리거 일 때는 FOR EACH ROW 조건을 작성한다.
-- STATE의 경우에는 하나의 행에 대해서만 처리하므로 이 조건이 필요 없다.
-- FOR EACH ROW : FOR (무엇을 위해)
-- EACH (각각의 행에 대해서)
--
-- 4. 형식 및 구조
/*
CREATE [OR REPLACE] TRIGGER 트리거명
[BEFORE | AFTER]
이벤트 1 [OR 이벤트 2 [OR 이벤트3]] ON 테이블명
[FOR EACH ROW [WHEN TRIGGER 조건]]
[DECLARE]
-- 선언 구문;
BEGIN
-- 실행 구문;
END;
*/
--■■■ AFTER STATEMENT TRIGGER 상황 실습 ■■■--
-- ※ DML 작업에 대한 이벤트 기록 (DATA MANIPULATION LANGUAGE)
--○ TRIGGER(트리거) 생성
CREATE OR REPLACE TRIGGER TRG_EVENTLOG
AFTER --> 입력되거나 삭제되거나 업데이트된 '후'에 진행하므로 AFTER.
INSERT OR UPDATE OR DELETE ON TBL_TEST1 --> 어느 테이블을 기준으로 감시하는지를 작성.
-- 부비트랩을 설치한 테이블
-- 아무런 제약조건을 걸지 않을 경우 ON TBL_TEST1
-- 업데이트만 처리 UPDATE ON TBL_TEST1
BEGIN
-- 이벤트 종류 구분(조건문을 통한 분기)
IF (INSERTING) -- ★ ING~잉
THEN INSERT INTO TBL_EVENTLOG(MEMO)
VALUES('INSERT 쿼리가 실행되었습니다.');
ELSIF (UPDATING)-- ★ ING~잉
THEN INSERT INTO TBL_EVENTLOG(MEMO)
VALUES('UPDATE 쿼리가 실행되었습니다.');
ELSIF (DELETING)-- ★ ING~잉
THEN INSERT INTO TBL_EVENTLOG(MEMO)
VALUES('DELETE 쿼리가 실행되었습니다.');
END IF;
--COMMIT;
-- ※ TRIGGER 내에서는 COMMIT / ROLLBACK 구문 사용 불가~!!! CHECK~!!!
END;
--==>> Trigger TRG_EVENTLOG이(가) 컴파일되었습니다.
-- (DML : DATA MANIPULATION LANGUAGE : INSERT, UPDATE, DELETE, ...)
--■■■ BEFORE STATEMENT TRIGGER 상황 실습 ■■■--
-- ※ DML 작업 수행 전에 작업에 대한 가능여부 확인
-- 오전 9시 이전이거나 오후 6시 이후이면 작업 수행 불가능하도록.
-- 트리거에서는 웬만하면 DECLARE로 내부 변수를 선언하지 않는다.
-- 트리거는 DECLARE로 구성하는데... 이 마저도 일반적으로 생략한다.
-- 프로시저는 사용자가 쓰고 싶을 때 쓰지만
-- 트리거는 사용자 의지로 사용할 수 있는 것이 아니므로
-- 트리거 내부에서는 변수를 선언하고 그 변수를 통한 처리를 하지 않는다.
-- 따라서, 예외를 나타내는 변수 또한 생성하지 않는다.
--○ TRIGGER(트리거) 생성 → TRG_TEST1_DML
CREATE OR REPLACE TRIGGER TRG_TEST1_DML
BEFORE
INSERT OR UPDATE OR DELETE ON TBL_TEST1
BEGIN
--IF (작업시간이 오전 9시 이전이거나... 오후 6시 이후라면...)
-- THEN 작업을 수행하지 못하도록 처리하겠다
-- IF (TO_CHAR(SYSDATE, 'HH24') NOT IN (BETWEEN 9 AND 18))
IF (TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) < 9 OR TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) > 17)
--THEN 예외를 발생시키도록 처리하겠다.
THEN RAISE_APPLICATION_ERROR(-20007, '작업 시간은 09:00 ~ 18:00 까지만 가능합니다.');
END IF;
END;
--==>> Trigger TRG_TEST1_DML이(가) 컴파일되었습니다.
-- 자식의 테이블을 삭제하기 위해 삭제하려는 시점 NOW 이전에
-- 과거 시점으로 돌아가서 부모의 PRIMARY KEY를 삭제하려고 할 때 이 트리거를 많이 한다.
--■■■ BEFORE ROW TRIGGER 상황 실습 ■■■--
-- ※ 참조 관계가 설정된 데이터(자식) 삭제를 먼저 수행하는 모델
-- 부모 테이블의 텔레비전 데이터를 제거하려했더니 오류가 나면서 삭제가 안됐다.
-- 자식 테이블에 코드 1(텔레비전)을 참고하는 데이터가 있어서 제거가 안 됐던 것.
-- TBL_TEST3에 있는 텔레비전을 참고하고 있는 모든 데이터들을 삭제하면
-- 부모테이블의 텔레비전 항목이 지워진다.
-- 즉, 부모 테이블에서 텔레비전을 지우려고 할 때 지워지도록 처리하는것.
-- 제거시점(NOW)보다 이전(과거 : BEFORE)으로 돌아가서 자식 테이블의 데이터 제거.
-- 사용자가 부모 테이블의 데이터를 제거하려고 할 때 움직이는 것.
-- 사용자가 건드리는 테이블은 부모 테이블인 TBL_TEST2이므로 ON TBL_TEST2이다!!!!!!
-- 사용자가 삭제하려는 TBL_TEST2의 데이터를 지칭하는 쿼리문 내의 코드.
--==>> :OLD.CODE;
-- :OLD ← → :NEW
-----------------|------------------->
-- 과거의 코드를 삭제한다는 의미로(자식 테이블 내에 존재하는 코드...) [:OLD.CODE]...
--『UPDATE』 : 오라클 내부적으로 들어가면 존재하지 않는다.
-- --==>> DELETE + INSERT 이다.
-- 내가 수정하려는 데이터를 삭제한 다음에 바꾸려는 데이터로 INSERT되는 것이다.
-- UPDATE : DELETE + INSERT (더 비효율적) -- 한 번 실행할 때마다 두 개의 명령 실행.
-- INSERT : INSERT (효율적 : 명령 단일.)
--○ TRIGGER(트리거) 생성 → TRG_TEST2_DELETE
-- 행 트리거. 부모가 하나 없어질 떄, 자식을 하나씩 찾아봐야 하는 트리거.
-- FOR EACH ROW 구문이 빠졌다!★
-- 확인을 하려면, 저장 공간이 필요하다. 그것이 바로 :OLD!!
CREATE OR REPLACE TRIGGER TRG_TEST2_DELETE
BEFORE
DELETE ON TBL_TEST2
FOR EACH ROW
BEGIN
DELETE
FROM TBL_TEST3
WHERE CODE = :OLD.CODE;
END;
--==>> Trigger TRG_TEST2_DELETE이(가) 컴파일되었습니다.
--※ 『:OLD』
-- 참조 전 열의 값
-- - INSERT : 입력하기 이전 자료 즉, 입력할 자료
-- - DELETE : 삭제하기 이전 자료 즉, 삭제할 자료
--※ UPDATE
-- 내부적으로 DELETE 그리고 INSERT 가 결합된 형태
-- UPDATE 하기 이전의 데이터는 『:OLD』
-- UPDATE 수행한 이후의 데이터는 『:NEW』
-- 입고, 상품, 출고 : 프로시저를 통해서 했지만...
-- 이를 트리거로 처리해 주면... INSERT만 하더라도 관련 재고가 늘어난다!!!
-- 출고 테이블에 한 건을 입력했을 뿐인데 재고가 줄어든다!!! 와우
-- ROW 트리거일 경우에는... 여러 개가 하나를 참조하는 경우
-- 하나의 파장이 자식에 반영되어야 하는 경우는 대부분 ROW TRIGGER이다.
--■■■ AFTER ROW TRIGGER 상황 실습 ■■■--
--※ 참조 테이블 관련 트랜잭션 처리
20231107_02_scott.sql
SELECT USER
FROM DUAL;
--==>> SCOTT
--------------------------------------------------------------------------------
SELECT *
FROM TBL_상품;
--==>>
/*
상품코드 상품명 소비자가격 재고수량
H001 바밤바 600 30
H002 죠스바 500 30
H003 보석바 500 20 (35 → -15 → 20)
H004 누가바 600 70
H005 쌍쌍바 700 0
H006 수박바 500 0
H007 알껌바 500 0
C001 빵빠레 1600 20
C002 월드콘 1500 20
C003 메타콘 1500 20
C004 구구콘 1600 20
C005 슈퍼콘 1700 0
E001 빵또아 2600 0
E002 투게더 2500 0
E003 팥빙수 2500 0
E004 셀렉션 2600 0
E005 설레임 2700 0
*/
SELECT *
FROM TBL_입고;
--==>>
/*
입고번호 상품코드 입고일자 입고수량 입고단가
3 H001 2023-11-06 50 500
4 H002 2023-11-06 30 400
1 H001 2023-11-06 30 500
2 H002 2023-11-06 20 400
5 H003 2023-11-06 35 400 -- [H003 - 보석바]
6 H004 2023-11-06 70 500
7 C001 2023-11-06 26 1400
8 C001 2023-11-06 24 1400
9 C001 2023-11-06 20 1500
10 C002 2023-11-06 20 1000
11 C003 2023-11-06 20 1000
12 C004 2023-11-06 20 1000
*/
SELECT *
FROM TBL_출고;
--==>>
/*
출고번호 상품코드 출고일자 출고수량 출고단가
2 H001 2023-11-06 10 600
1 H001 2023-11-06 10 600
3 C001 2023-11-06 20 1500
4 C001 2023-11-06 20 1500
5 C001 2023-11-06 10 1500
6 H001 2023-11-06 10 500
7 H001 2023-11-06 10 500
8 H001 2023-11-06 10 500
9 H002 2023-11-06 20 500
10 H003 2023-11-06 15 500 -->[보석바 15개 출고] (40개 업데이트 불가)
*/
--○ 생성한 프로시저가 제대로 작동하는지의 여부 확인 → 프로시저 호출
EXEC PRC_출고_UPDATE2(10, 40);
--==>> 에러 발생
-- (ORA-2002: 재고 부족~!!!)
EXEC PRC_출고_UPDATE2(10, 25);
--==>> PL/SQL 프로시저가 성공적으로 완료되었습니다.
--○ 프로시저 호출 이후 테이블 조회(확인)
SELECT *
FROM TBL_출고;
--==>>
/*
출고번호 상품코드 출고일자 출고수량 출고단가
2 H001 2023-11-06 10 600
1 H001 2023-11-06 15 600
3 C001 2023-11-06 20 1500
4 C001 2023-11-06 20 1500
5 C001 2023-11-06 10 1500
6 H001 2023-11-06 10 500
7 H001 2023-11-06 10 500
8 H001 2023-11-06 10 500
9 H003 2023-11-06 25 500
*/
SELECT *
FROM TBL_상품;
--==>>
/*
상품코드 상품명 소비자가격 재고수량
H001 바밤바 600 25
H002 죠스바 500 30
H003 보석바 500 10
H004 누가바 600 70
H005 쌍쌍바 700 0
H006 수박바 500 0
H007 알껌바 500 0
C001 빵빠레 1600 20
C002 월드콘 1500 20
C003 메타콘 1500 20
C004 구구콘 1600 20
C005 슈퍼콘 1700 0
E001 빵또아 2600 0
E002 투게더 2500 0
E003 팥빙수 2500 0
E004 셀렉션 2600 0
E005 설레임 2700 0
*/
--김지민 어디가쏘~~~ 빨리와 8282
/*
현재 재고수량 = 총입고수량 - 총출고수량
재고수량 = 입고수량 - 출고수량
총 입고수량 = 입고수량 1 + 입고수량 2 + 입고수량 3
변경할 입고수량 = 입고수량 2 → 입고수량 4로 변경하고 싶다
총 입고수량 - 입고수량 2 = 입고수량 1 + 입고수량 3
위의 결과값 + 입고수량 4 를 했을 때의 결과 = 최종 입고 수량(변경후)
최종 입고 수량(변경후) - 총 출고수량 < 0 이 되면 안된다!!
기존 재고가 3이야(출고까지 반영됨)
입고번호가 1인 레코드를 바꿀거야
1 10
1 5
3 - 5 < 0
*/
SELECT *
FROM TBL_상품;
SELECT *
FROM TBL_입고;
SELECT *
FROM TBL_출고;
EXECUTE PRC_입고_UPDATE(3, 50);
EXECUTE PRC_입고_DELETE(6);
EXECUTE PRC_출고_DELETE(1);
ROLLBACK;
--------------------------------------------------------------------------------
--■■■ AFTER STATEMENT TRIGGER 상황 실습 ■■■--
-- ※ DML 작업에 대한 이벤트 기록 (DATA MANIPULATION LANGUAGE)
--○ 실습을 위한 준비 → 테이블 생성(TBL_TEST1)
CREATE TABLE TBL_TEST1
( ID NUMBER
, NAME VARCHAR2(30)
, TEL VARCHAR2(60)
, 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이(가) 생성되었습니다.
--○ 날짜 관련 세션 설정
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
--○ 확인
SELECT *
FROM TBL_TEST1;
--==>> 조회 결과 없음
SELECT *
FROM TBL_EVENTLOG;
--==>> 조회 결과 없음
-- 졸리거나 수업이 재미없거나 집에 가고싶거나 하면...
-- 테이블 1에(TBL_TEST1) 이름 전화번호를 적으세요.
--○ 생성한 TRIGGER 작동여부 확인
-- → TBL_TEST1 테이블을 대상으로 INSERT, UPDATE, DELETE 수행
INSERT INTO TBL_TEST1(ID, NAME, TEL)
VALUES(1, '이윤수', '010-1111-1111');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(ID, NAME, TEL)
VALUES(2, '강혜성', '010-2222-2222');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(ID, NAME, TEL)
VALUES(3, '문정환', '010-3333-3333');
--==>> 1 행 이(가) 삽입되었습니다.
UPDATE TBL_TEST1
SET NAME = '박가영', TEL = '010-4444-4444'
WHERE ID = 1;
SELECT *
FROM TBL_TEST1;
-- 1 이윤수 010-1111-1111
-- 2 강혜성 010-2222-2222
-- 3 문정환 010-4444-4444
DELETE
FROM TBL_TEST1
WHERE ID IN (2, 3);
--==>> 2개 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_TEST1;
--==>>
/*
ID NAME TEL
1 박가영 010-4444-4444
*/
DELETE
FROM TBL_TEST1
WHERE ID = 1;
--==>> 1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_TEST1;
--==>> 조회 결과 없음.
SELECT *
FROM TBL_EVENTLOG;
--==>>
/*
MEMO ILJA
INSERT 쿼리가 실행되었습니다. 2023-11-07 16:14:22
INSERT 쿼리가 실행되었습니다. 2023-11-07 16:14:53
INSERT 쿼리가 실행되었습니다. 2023-11-07 16:17:24
UPDATE 쿼리가 실행되었습니다. 2023-11-07 16:18:32
DELETE 쿼리가 실행되었습니다. 2023-11-07 16:19:25
DELETE 쿼리가 실행되었습니다. 2023-11-07 16:20:20
*/
-- 쓰는 사람 입장에서는 순수하게 INSERT, DELETE, UPDATE를 하는 것일 뿐인데
-- 미리 사전에 걸어둔 트리거 때문에 이런 기능이 실행된다.
--
--------------------------------------------------------------------------------
--■■■ BEFORE STATEMENT TRIGGER 상황 실습 ■■■--
-- ※ DML 작업 수행 전에 작업에 대한 가능여부 확인
SELECT SYSDATE
FROM DUAL;
--==>> 2023-11-07 18:46:49
INSERT INTO TBL_TEST1(ID, NAME, TEL)
VALUES(1, '김다슬', '010-1111-1111');
--==>> 에러 발생
-- (ORA-20007: 작업 시간은 09:00 ~ 18:00 까지만 가능합니다.) ← 사용자 정의 에러
SELECT SYSDATE
FROM DUAL;
--==>> ORA-20007: 작업 시간은 09:00 ~ 18:00 까지만 가능합니다.
INSERT INTO TBL_TEST1(ID, NAME, TEL)
VALUES(1, '김다슬', '010-1111-1111');
--==>> 1 행 이(가) 삽입되었습니다.
SELECT *
FROM TBL_TEST1;
--==>>
/*
ID NAME TEL
1 김다슬 010-1111-1111
*/
COMMIT;
--==>> 커밋 완료.
UPDATE TBL_TEST1
SET NAME = '오수경', TEL = '010-2222-2222'
WHERE ID = 1;
--==>> 1 행 이(가) 업데이트되었습니다.
INSERT INTO TBL_TEST1(ID, NAME, TEL)
VALUES(2, '김경태', '010-3333-3333');
--==>> 1 행 이(가) 삽입되었습니다.
SELECT *
FROM TBL_TEST1;
--==>>
/*
ID NAME TEL
2 김경태 010-3333-3333
1 오수경 010-2222-2222
*/
COMMIT;
--==>> 커밋 완료.
SELECT SYSDATE
FROM DUAL;
--==>> 2023-11-07 06:02:05
UPDATE TBL_TEST1
SET NAME = '박범구', TEL = '010-4444-4444'
WHERE ID = 1;
--==>> 에러 발생
-- (ORA-20007: 작업 시간은 09:00 ~ 18:00 까지만 가능합니다.)
DELETE
FROM TBL_TEST1
WHERE ID = 2;
--==>> 에러 발생
-- (ORA-20007: 작업 시간은 09:00 ~ 18:00 까지만 가능합니다.)
--------------------------------------------------------------------------------
--■■■ BEFORE ROW TRIGGER 상황 실습 ■■■--
-- ※ 참조 관계가 설정된 데이터(자식) 삭제를 먼저 수행하는 모델
--○ 실습 환경 구성을 위한 테이블 생성 → TBL_TEST2
CREATE TABLE TBL_TEST2
( CODE NUMBER
, NAME VARCHAR2(40)
, CONSTRAINT TEST2_CODE_PK PRIMARY KEY(CODE)
);
--==>> Table TBL_TEST2이(가) 생성되었습니다.
--○ 실습 환경 구성을 위한 테이블 생성 → 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이(가) 생성되었습니다.
--○ 실습 관련 데이터 입력
-- 부모테이블에 입력.
INSERT INTO TBL_TEST2(CODE, NAME) VALUES(1, '텔레비전');
INSERT INTO TBL_TEST2(CODE, NAME) VALUES(2, '냉장고');
INSERT INTO TBL_TEST2(CODE, NAME) VALUES(3, '세탁기');
INSERT INTO TBL_TEST2(CODE, NAME) VALUES(4, '건조기');
--==>> 1 행 이(가) 삽입되었습니다. * 4
SELECT *
FROM TBL_TEST2;
--==>>
/*
CODE NAME
1 텔레비전
2 냉장고
3 세탁기
4 건조기
*/
COMMIT;
--==>> 커밋 완료.
--○ 실습 관련 데이터 입력
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(1, 1, 30); -- 텔레비전
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(2, 1, 50); -- 텔레비전
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(3, 1, 60); -- 텔레비전
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(4, 2, 20); -- 냉장고
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(5, 2, 20); -- 냉장고
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(6, 3, 40); -- 세탁기
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(7, 1, 30); -- 텔레비전
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(8, 4, 30); -- 건조기
INSERT INTO TBL_TEST3(SID, CODE, SU) VALUES(9, 3, 10); -- 세탁기
--==>> 1 행 이(가) 삽입되었습니다. * 9
SELECT *
FROM TBL_TEST3;
--==>>
/*
==================
SID CODE SU
==================
1 1 30
2 1 50
3 1 60
4 2 20
5 2 20
6 3 40
7 1 30
8 4 30
9 3 10
==================
*/
COMMIT;
--==>> 커밋 완료.
--○ 부모 테이블(TBL_TEST2)의 데이터 삭제 시도
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)
--○ TRIGGER 생성 이후 확인
DELETE
FROM TBL_TEST2
WHERE CODE=1;
--==>> 1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_TEST2;
--==>>
/*
CODE NAME
2 냉장고
3 세탁기
4 건조기
*/
-- 부모 테이블의 텔레비전이 날라갔다!!
SELECT *
FROM TBL_TEST3;
--==>>
/*
SID CODE SU
4 2 20
5 2 20
6 3 40
8 4 30
9 3 10
*/
-- 자식 테이블에서 부모 테이블의 텔레비전을 참조하는 데이터들이
-- 모두 삭제되었음을 확인!
DELETE
FROM TBL_TEST2
WHERE CODE=2;
--==>> 1 행 이(가) 삭제되었습니다.
SELECT *
FROM TBL_TEST2;
--==>>
/*
CODE NAME
3 세탁기
4 건조기
*/
SELECT *
FROM TBL_TEST3;
--==>>
/*
SID CODE SU
6 3 40
8 4 30
9 3 10
*/
--------------------------------------------------------------------------------
--■■■ AFTER ROW TRIGGER 상황 실습 ■■■--
--※ 참조 테이블 관련 트랜잭션 처리
--※ 실습을 위한 준비
-- 모든 재고수량 0으로 초기화.
UPDATE TBL_상품
SET 재고수량 = 0;
--==>> 17개 행 이(가) 업데이트되었습니다.
-- 테이블을 초기 상태로 되돌리고, 로그조차 남기지 않는 것.
TRUNCATE TABLE TBL_입고;
--==>> Table TBL_입고이(가) 잘렸습니다.
TRUNCATE TABLE TBL_출고;
--==>> Table TBL_출고이(가) 잘렸습니다.
SELECT *
FROM TBL_상품;
--==>> 모든 상품 재고 0
ROLLBACK;
SELECT *
FROM TBL_입고;
--==>> 조회결과 없음.
SELECT *
FROM TBL_출고;
--==>> 조회결과 없음.
--******************************************************************************
--★ [DDL : DATA DEFINE LANGUAGE 데이터 정의어 이므로, 자동 커밋된다.]
--★ [TRUNCATE] : 테이블을 오려내는 것이다. (잘린 것을 다시 붙여넣을 수 없다.)
-- ROLLBACK 되지 않는다. AUTO-COMMIT!!!!
--******************************************************************************
SELECT *
FROM TBL_상품;
--==>>
/*
상품코드 상품명 소비자가격 재고수량
H001 바밤바 600 0
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
*/'[Oracle] > PL·SQL (Program source)' 카테고리의 다른 글
| [Oracle - PL/SQL] 20231108 [프로그램 소스] (0) | 2023.11.09 |
|---|---|
| [Oracle - PL/SQL] 20231106 [프로그램 소스] (0) | 2023.11.06 |
| [Oracle - PL/SQL] 20231103 [프로그램 소스] (0) | 2023.11.03 |
| [Oracle - PL/SQL] 20231102 [프로그램 소스] (0) | 2023.11.03 |