SsY/Class
015. [PL/SQL] UPDATE / DELETE PROCEDURE(프로시저), CURSOR (커서), TRIGGER(트리거)
planet-si
2023. 4. 11. 17:49
728x90
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 (내일 이어서...)
728x90