-
015. [PL/SQL] UPDATE / DELETE PROCEDURE(프로시저), CURSOR (커서), TRIGGER(트리거)SsY/Class 2023. 4. 11. 17:49728x90
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'SsY > Class' 카테고리의 다른 글
001. Eclipse 설치 및 JDBC 개념 (2) 2023.04.20 016. [PL/SQL] TRIGGER(트리거)_(:OLD/:NEW), PACKAGE(패키지) (0) 2023.04.12 014. [PL/SQL] PROCEDURE(프로시저),EXCEPTION,TRANSACTION (0) 2023.04.10 013. [PL/SQL] %TYPE / %ROWTYPE (자료형 참조 데이터타입), FUNCTION(함수), PROCEDURE (0) 2023.04.07 012. UPDATE 예제, DELETE, 뷰(VIEW), [PL/SQL ] IF 문, ACCEPT(&외부변수), 기본 반복문(LOOP), WHILE LOOP , FOR LOOP (0) 2023.04.06