Oracle 11일차(1)-패키지,트리거

PACKAGE : PROCEDURE의 묶음

○PACKAGE + BODY

▶PACKAGE

CREATE OR REPLACE PACKAGE SAWON_PACK

IS

PROCEDURE SANO_OUT;

PROCEDURE SANAME_OUT;

PROCEDURE BUNO_OUT;

PROCEDURE JIK_OUT;

PROCEDURE PAY_OUT;

END;

▶BODY

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 SW_REC IN SA_CUR LOOP

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

END LOOP;

END;

PROCEDURE SANAME_OUT

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('사원이름');

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

FOR SW_REC IN SA_CUR LOOP

DBMS_OUTPUT.PUT_LINE(SW_REC.사원명);

END LOOP;

END;

PROCEDURE BUNO_OUT

IS

BEGIN

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

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

FOR SW_REC IN SA_CUR LOOP

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

END LOOP;

END;

PROCEDURE JIK_OUT

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('직급');

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

FOR SW_REC IN SA_CUR LOOP

DBMS_OUTPUT.PUT_LINE(SW_REC.직급);

END LOOP;

END;

PROCEDURE PAY_OUT

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('급여');

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

FOR SW_REC IN SA_CUR LOOP

DBMS_OUTPUT.PUT_LINE(SW_REC.급여);

END LOOP;

END;

END 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;

사원번호 ------------ 2001 2002 2003 2004 2005 2006 2007 2008

사원이름 ------------ 이순신 홍길동 성유리 옥주현 길건 한지혜 박솔미 이효리

부서번호 ------------ 10 10 20 30 10 20 30 40

직급 ------ 부장 대리 사원 과장 대리 사원 대리 사원

급여 ------ 5000 4000 2500 5000 4000 2000 3000 2000

---

TRIGGER : 연쇄작용

○INSERT, UPDATE, DELETE문을 실행할 때 수행되는 PROCEDURE

○AFTER TRIGGER

  SQL문이 실행된 다음에 TRIGGER 실행

○BEFORE TRIGGER

 SQL문이 실행되기 전에 TRIGGER 실행

1. AFTER TRIGGER

▶TABLE 생성

CREATE TABLE HAKSANG

(HAKBUN VARCHAR2(10),

NAME VARCHAR2(10));

CREATE TABLE MEMO

(NO NUMBER,

BIGO VARCHAR2(200));

▶SEQUENCE 생성

CREATE SEQUENCE SE99

START WITH 1

INCREMENT BY 1

NOMAXVALUE

NOCYCLE

NOCACHE;

▶ INSERT TRIGGER 생성

○ ON 스키마.테이블명

CREATE OR REPLACE TRIGGER TRI_HAKSANG_IN

AFTER INSERT

ON LEE.HAKSANG

BEGIN

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

END;

▶결과 확인

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

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

SELECT * FROM HAKSANG;

SELECT * FROM MEMO;

            

▲HAJSANG 테이블                             ▲ MEMO 테이블         

+ NEW.테이블의 컬럼명 / OLD.테이블의 컬럼명

CREATE OR REPLACE TRIGGER TRI_HAKSANG_IN

AFTER INSERT

ON LEE.HAKSANG

FOR EACH ROW

BEGIN

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

END;

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

 결과

▶ UPDATE TRIGGER 생성

CREATE OR REPLACE TRIGGER TRI_HAKSANG_UP

AFTER UPDATE OF NAME

ON LEE.HAKSANG --ON 스키마.테이블

FOR EACH ROW

BEGIN

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

END;

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

SELECT * FROM MEMO;

 결과

▶ DELETE TRIGGER 생성

CREATE OR REPLACE TRIGGER TRI_HAKSANG_DEL

AFTER DELETE

ON LEE.HAKSANG

FOR EACH ROW

BEGIN

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

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

END;

DELETE HAKSANG WHERE NAME='AAA';

SELECT * FROM MEMO;

SELECT * FROM COPY_HAKSANG;

결과

                    

▲MEMO 테이블                                                                   ▲COPY_HAJSANG 테이블 

2. BEFORE TRIGGER

▶TABLE 생성

CREATE TABLE INSA

(EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JIK VARCHAR2(10),

SAL NUMBER,

CONSTRAINT PK_INSA_EMPNO PRIMARY KEY(EMPNO));

▶TRIGGER 생성

CREATE OR REPLACE TRIGGER TRI_INSA

BEFORE INSERT OR DELETE OR UPDATE

ON LEE.INSA

DECLARE

IMPOSSIBLE_TIME EXCEPTION;

BEGIN

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

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

RAISE IMPOSSIBLE_TIME;

END IF;

EXCEPTION

WHEN IMPOSSIBLE_TIME THEN

RAISE_APPLICATION_ERROR(-20007,'주중 12:00부터 17:00까지만 DML문장이 가능합니다.');

END;

▶결과 확인

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

▲에러발생

▲INSERT성공. INSA 테이블

3.주민번호 확인 트리거

▶테이블 생성

CREATE TABLE MEMBER

(ID NUMBER(3) PRIMARY KEY,

NAME VARCHAR2(10),

JUBUN VARCHAR2(13));

▶TRIGGER 생성

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);

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;

▲INSERT성공. MEMBER 테이블

▲에러발생