[오라클]패키지, 트리거(1)

 

- 여러 개의 프로시저를 하나로 묶을 수 있다.

 

PACKAGE : 프로시저의 묶음

- package와 body로 구성되어 있음

- FOR EACH ROW : 이걸 사용안하면 테이블 레벨 트리거에서 NEW 혹은 OLD 참조는 허용되지 않습니다. 오류

- NEW 사용자가 지정한 작업의 실행 후 데이터

- OLD 사용자가 지정한 작업이 실행 전 데이터

패키지 생성(5개의 프로시저를 하나로 묶음)

CREATE OR REPLACE PACKAGE SAWON_PACK

IS

PROCEDURE SANO_OUT;

PROCEDURE SANAME_OUT;

PROCEDURE BUNO_OUT;

PROCEDURE JIK_OUT;

PROCEDURE PAY_OUT;

END;

 

패키지 바디 생성(각각의 프로시저를 안에 정의함)

CREATE OR REPLACE PACKAGE BODY SAWON_PACK

IS

CURSOR SA_CUR

IS

SELECT * FROM 사원;

 

PROCEDURE SANO_OUT

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('사원번호');

DBMS_OUTPUT.PUT_LINE('--------');

FOR SA_REC IN SA_CUR LOOP

DBMS_OUTPUT.PUT_LINE(SA_REC.사원번호);

END LOOP;

END;

PROCEDURE SANAME_OUT

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('사원명');

DBMS_OUTPUT.PUT_LINE('--------');

FOR SA_REC IN SA_CUR LOOP

DBMS_OUTPUT.PUT_LINE(SA_REC.사원명);

END LOOP;

END;

PROCEDURE BUNO_OUT

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('부서번호');

DBMS_OUTPUT.PUT_LINE('--------');

FOR SA_REC IN SA_CUR LOOP

DBMS_OUTPUT.PUT_LINE(SA_REC.부서번호);

END LOOP;

END;

PROCEDURE JIK_OUT

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('직급');

DBMS_OUTPUT.PUT_LINE('--------');

FOR SA_REC IN SA_CUR LOOP

DBMS_OUTPUT.PUT_LINE(SA_REC.직급);

END LOOP;

END;

PROCEDURE PAY_OUT

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('급여');

DBMS_OUTPUT.PUT_LINE('--------');

FOR SA_REC IN SA_CUR LOOP

DBMS_OUTPUT.PUT_LINE(SA_REC.급여);

END LOOP;

END;

END SAWON_PACK;

 

SAWON_PACK 패키지에 해당하는 프로시저를 하나씩 실행시킬 수 있음

EXECUTE SAWON_PACK.SANO_OUT;

EXECUTE SAWON_PACK.SANAME_OUT;

EXECUTE SAWON_PACK.BUNO_OUT;

EXECUTE SAWON_PACK.JIK_OUT;

EXECUTE SAWON_PACK.PAY_OUT;

 

 

---

 

- TRIGGER: 방아쇠. 연쇄작용

- AFTER TRIGGER : 사용자의 작업을 끝낸 뒤 실행되는 트리거(INSERT, UPDATE, DELETE)

- BEFORE TRIGGER : 사용자의 작업을 하기전에 먼저 진행되는 트리거

 

테이블 생성

CREATE TABLE HAKSANG

(HAKBUN VARCHAR2(10),

NAME VARCHAR2(10));

 

CREATE TABLE MEMO

(NO NUMBER,

BIGO VARCHAR2(200));

 

시퀀스 생성

CREATE SEQUENCE SE99

START WITH 1

INCREMENT BY 1

NOMAXVALUE

NOCYCLE

NOCACHE;

 

트리거 생성(NEW, OLD 개념 기억하기. )

트리거의 이름: TRI_HAKSANG_IN. 자동으로 실행되므로 길어도 상관없음

CREATE OR REPLACE TRIGGER TRI_HAKSANG_IN

AFTER INSERT --INSERT문이 실행되고 난 뒤 자동으로 트리거가 실행

ON LEE.HAKSANG

FOR EACH ROW --이걸 사용안하면 테이블 레벨 트리거에서 NEW 혹은 OLD 참조는 허용되지 않습니다. 오류발생 (:NEW.NAME 쓸수없음)

BEGIN

INSERT INTO LEE.MEMO VALUES(LEE.SE99.NEXTVAL,:NEW.HAKBUN||'행이 INSERT되었습니다.'); --NAME은 컬럼명

END;

 

데이터 삽입

INSERT INTO HAKSANG VALUES(111,'SUZI');

INSERT INTO HAKSANG VALUES(222,'PARK');

INSERT INTO HAKSANG VALUES(333,'SHIN');

INSERT INTO HAKSANG VALUES(444,'LEE');

INSERT INTO HAKSANG VALUES(777,'CHO');

 

입력 후 트리거 진행 여부 확인

SELECT * FROM HAKSANG;

SELECT * FROM MEMO;

DELETE FROM HAKSANG WHERE HAKBUN = 111;

 

카피 테이블 생성

CREATE TABLE COPY_HAKSANG

(HAKBUN VARCHAR2(10),

NAME VARCHAR2(10));

 

트리거를 이용해서 하나의 데이터 저장시 그대로 복사해서 다른테이블에 백업도 가능

CREATE OR REPLACE TRIGGER TRI_COPY_HAKSANG_IN

AFTER INSERT

ON LEE.HAKSANG

FOR EACH ROW

BEGIN

INSERT INTO LEE.COPY_HAKSANG VALUES(:NEW.HAKBUN,:NEW.NAME);

END;

 

데이터 삽입

INSERT INTO HAKSANG VALUES(777,'LOO');

 

데이터 삽입 후 조회해보면 COPY_HAKSANG 에도 똑같은 데이터가 들어간 것을 볼 수 있음

SELECT * FROM HAKSANG;

SELECT * FROM MEMO;

SELECT * FROM COPY_HAKSANG;

---

 

UPDATE TRIGGER

CREATE OR REPLACE TRIGGER TRI_HAKSANG_UP

AFTER UPDATE OF NAME --업데이트는 어느 컬럼이 변경되었을때 진행될 건지 작성해야함

ON LEE.HAKSANG

FOR EACH ROW

BEGIN

INSERT INTO HAKSANG VALUES(LEE.SE99.NEXTVAL,:OLD.HAKBUN||'행이 UPDATE되었습니다.');

END;

 

업데이트 진행 후 테이블 조회

UPDATE HAKSANG SET NAME='AAA' WHERE HAKBUN=333;

 

 

 

---

 

DELETE TRIGGER

CREATE OR REPLACE TRIGGER TRI_HAKSANG_DEL

AFTER DELETE

ON LEE.HAKSANG

FOR EACH ROW

BEGIN

INSERT INTO MEMO VALUES(LEE.SE99.NEXTVAL,:OLD.NAME|| '행이 DELETE 되었습니다.');

INSERT INTO LEE.COPY_HAKSANG VALUES(:OLD.HAKBUN,:OLD.NAME); --삭제된 데이터를 COPY_HAKSANG테이블에 저장

END;

 

삭제 진행 후 테이블 조회

DELETE HAKSANG WHERE NAME = 'AAA';

 

 

---

 

BEFORE TRIGGER

- 두 트리거가 하는 일이 똑같다면 먼저 만들어진 트리거만 실행됨

 

테이블 생성

CREATE TABLE INSA

(EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JIK VARCHAR2(10),

SAL NUMBER,

CONSTRAINT PK_INSA_EMPNO PRIMARY KEY(EMPNO));

 

트리거 생성

CREATE OR REPLACE TRIGGER TRI_INSA

BEFORE INSERT OR DELETE OR UPDATE

--'UPDATE OF 컬럼명' 을 쓸 경우 해당 컬럼명 수정시에만 실행. 안쓸경우 하나라도 수정되면 진행

-- INSERT OR DELETE OR UPDATE 세개 동시에 작성 가능

ON LEE.INSA

DECLARE

IMPOSSIBLE_TIME EXCEPTION;--사용자 정의 에러

BEGIN  --'DAY'는 요일의 풀네임

IF TO_CHAR(SYSDATE, 'DAY') IN ('화요일','토요일','일요일') OR

TO_CHAR(SYSDATE,'HH24')<12 OR TO_CHAR(SYSDATE,'HH24')>17 THEN

RAISE IMPOSSIBLE_TIME;

END IF;

EXCEPTION

WHEN IMPOSSIBLE_TIME THEN

RAISE_APPLICATION_ERROR(-20007,'월,수,목,금요일 중 정오 12:00부터 오후 5:00까지만 DML문장이 가능합니다.');

END;

 

데이터 삽입

INSERT INTO INSA VALUES(1,'배수지','과장',50000);

 

 

 

---

 

트리거 예시

 

테이블 생성

CREATE TABLE MEMBER

(ID NUMBER(3) PRIMARY KEY,

NAME VARCHAR2(10),

JUBUN VARCHAR2(13));

 

 

트리거 생성

CREATE OR REPLACE TRIGGER TRI_MEMBER_JUBUN

BEFORE INSERT OR UPDATE OF JUBUN

ON LEE.MEMBER

FOR EACH ROW

DECLARE

A NUMBER; B NUMBER; C NUMBER; D NUMBER; E NUMBER;

F NUMBER; G NUMBER; H NUMBER; I NUMBER; J NUMBER;

K NUMBER; L NUMBER; M NUMBER;

CK NUMBER;

JUBUNFAIL EXCEPTION;

BEGIN

A:=TO_CHAR(SUBSTR(:NEW.JUBUN,1,1));

B:=TO_CHAR(SUBSTR(:NEW.JUBUN,2,1));

C:=TO_CHAR(SUBSTR(:NEW.JUBUN,3,1));

D:=TO_CHAR(SUBSTR(:NEW.JUBUN,4,1));

E:=TO_CHAR(SUBSTR(:NEW.JUBUN,5,1));

F:=TO_CHAR(SUBSTR(:NEW.JUBUN,6,1));

G:=TO_CHAR(SUBSTR(:NEW.JUBUN,7,1));

H:=TO_CHAR(SUBSTR(:NEW.JUBUN,8,1));

I:=TO_CHAR(SUBSTR(:NEW.JUBUN,9,1));

J:=TO_CHAR(SUBSTR(:NEW.JUBUN,10,1));

K:=TO_CHAR(SUBSTR(:NEW.JUBUN,11,1));

L:=TO_CHAR(SUBSTR(:NEW.JUBUN,12,1));

M:=TO_CHAR(SUBSTR(:NEW.JUBUN,13,1));

CK:=(A*2)+(B*3)+(C*4)+(D*5)+(E*6)+(F*7)+(G*8)+(H*9)+(I*2)+(J*3)+(K*4)+(L*5);

-- 11-(합%11)

CK:=MOD(CK,11);

CK:=11-CK;

CK:=MOD(CK,10);

IF M!=CK THEN

RAISE JUBUNFAIL;

END IF;

EXCEPTION

WHEN JUBUNFAIL THEN

RAISE_APPLICATION_ERROR(-20001,'주민번호가 틀립니다!');

END;

데이터 삽입

INSERT INTO MEMBER VALUES(111,'SUZI','1111111111118');

 

 

INSERT INTO MEMBER VALUES(112,'SUZY','1111111111111');

 

 

조회

SELECT * FROM MEMBER;