(29)[ORACLE] 12일차 : PL/SQL - 5 (트리거 및 패키지)

 

 

트리거란?
스키마 객체의 일종으로, 데이터베이스가 미리 정해 놓은 특정 조건이 만족되거나
어떤 동작이 수행되면 자동으로 실행되도록 정의한 동작.
ex. 테이블에 DML 문장이 실행 되거나, 사용자가 설정한 DDL 문장이 수행될 때 
트리거가 실행될 수 있다. 


트리거(TRIGGER)유형

DML 트리거	INSERT, DELETE, UPDATE 작업이 일어날 때 자동으로 실행되는 객체로 이런 TRIGGER를 DML TRIGGER라 한다.
INSTEAD OF 트리거	테이블이 아닌 뷰에 정의된 트리거
시스템 트리거	스키마 또는 데이터베이스에 정의된 트리거

 

 

트리거 기본 구문

트리거를 생성하거나 삭제하기 위해서는 사용자는 CREATE TRIGGER 권한을 가져야 한다. 

-- 관리자 계정 : sky 계정에 트리거를 만들 수 있는 권한 부여
GRANT CREATE TRIGGER TO sky;

-- sky 계정 : 시스템 권한 확인
SELECT * FROM user_sys_privs;

-- 트리거 확인
SELECT * FROM user_triggers; 

-- 트리거 소스 확인    
SELECT * FROM user_source; 

-- 의존성 확인
SELECT * FROM user_dependencies;

-- 트리거 삭제
DROP TRIGGER testtrigger;

 

 

문장 트리거 

트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오직 한번만 트리거를 발생 시킨다. 

-- test 테이블에 지정된 시간에만 dml이 가능한 트리거 만들기
CREATE OR REPLACE TRIGGER testtrigger
BEFORE INSERT OR UPDATE OR DELETE ON TEST
BEGIN
    if TO_CHAR(SYSDATE,'d') in (1,7) OR
    --(to_char(sysdate,'hh24') < 9 or to_char(sysdate,'hh24') > 18) then
    (TO_CHAR(SYSDATE,'hh24') >= 11 AND TO_CHAR(SYSDATE,'24') <= 12) THEN
    RAISE_APPLICATION_ERROR(-20001,'지정된 시간에만 데이터를 수정할 수 있다');
    End If;
End;

 

 

행 트리거 

조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 수행하는 방법으로 'FOR EACH ROW [WHEN 조건]'절을 정의된다. 트리거가 설정된 테이블에 DELETE 이벤트가 등록된 경우, 해당 테이블에서 DELETE 작업이 10번 일어나면 트리거도 10번 실행된다. 트리거 이벤트가 행에 영향을 주지 않으면 행 트리거는 한번도 실행하지 않는다. 

-- 행 트리거
-- : DML 문에서 조건만족하는 모든 행에 대하여 트리거나 일어난다.
-- : 예를 들어 "DELETE FROM 테이블;" 문장으로 5개의 레코드가 삭제된 경우 트리거는 5번 실행 된다.
-- : OLD 와 NEW 레코드
  -- 행 트리거에서만 사용 가능
  -- :OLD 
    UPDATE 에서는 수정전 레코드, DELETE 에서는 삭제할 레코드
  -- :NEW
    INSERT에서는 추가할 레코드, UPDATE 에서는 수정할 레코드


-- score1 테이블에 데이터가 추가되면 score2 테이블에 데이터가 추가되는 트리거 작성
-- insert 트리거

CREATE OR REPLACE TRIGGER scoreinserttrigger
AFTER INSERT ON score1
FOR EACH ROW -- 행 트리거
DECLARE
-- 필요한 변수 선언
BEGIN 
INSERT INTO score2(hak, kor, eng, mat) VALUES(:NEW.hak, 
            fngrade(:NEW.kor), fngrade(:NEW.eng), fngrade(:NEW.mat)); 
            -- :new 트리거에서 사용할 수 있는 예약어
END;
/

 

 

트리거 생성

• WHEN TRIGGER조건 : 트리거 이벤트 중에 조건에 만족하는 데이터만 트리거 한다.
• OLD와 NEW의사 레코드 
  • OLD와 NEW는 문장 트리거에서는 사용할 수 없으면 행 트리거에서만 사용한다. OLD와 NEW는 WHEN절에서는 OLD,NEW처럼 앞에 콜론(:)을 붙이지 않지만 트리거 본문에서는 콜론(:)을 붙여야 한다. OLD는 값을 변경할 수 없지만 NEW는 가능하다. 
  • :OLD = UPDATE: 수정전 자료, DELETE : 삭제할 자료
  • :NEW = INSERT : 입력할 자료, UPDATE: 수정할 자료 

--트리거 생성

CREATE[OR ROPLACE] TRIGGER 트리거이름 {BEFORE|AFTER}
이벤트 ON 테이블이름
[FOR EACH ROW [WHEN TRIGGER조건]]
DECLARE
변수,상수등 선언
BEGIN
실행구문
END;

-- 트리거 실행 시기
-- BEFORE :테이블의 DML 트리거 이벤트 전에 트리거 본문이 실행
-- AFTER : 테이블의 DML 트리거 이벤트 전에 트리거 본문이 실행
-- 이벤트 : INSERT,UPDATE,DELETE

 

 

 

---

 

 

패키지란?
오라클에 저장되어 있는 서로 관련 있는 PL/SQL 프로시저, 함수, 타입등을 논리적으로 묶어 놓은 집합
패키지는 선언부와 본문으로 나눈다.
패키지 내에서는 프로시저, 함수는 오버로딩(중복 정의)이 가능하다.


오버로딩
매개변수의 개수나 타입이 다르면 동일한 이름으로 프로시저나 함수를 여러개 정의 할 수 있다.
오버로딩은 패키지 내에서만 가능하다. 

 

 

패키지 

 

패키지 선언

선언절은 패키지에 포함될 PL/SQL 프로시저나, 함수, 커서, 변수, 예외 절을 선언한다. 패키지 선언부에서 선언한 모든 요소들은 패키지 전체에 적용된다. 

 

CREATE OR REPLACE PACKAGE pEmp IS
        FUNCTION fnTax(pay IN NUMBER) RETURN NUMBER;
        PROCEDURE empList(pName VARCHAR2);
        PROCEDURE empList;
END pEmp;
/
--함수와 프로시저 생성 
--프로시저 이름이 같다,오버로딩 가능(매개변수가 다르면 다른 프로시저로 처리함)

 

 

패키지 생성

패키지 본문은 패키지에서 선언된 프로시저나 함수의 몸체를 구현한다.

 

--패키지 몸체 구현


CREATE OR REPLACE PACKAGE BODY PEMP IS
    FUNCTION FNTAX(PAY IN NUMBER) --함수
    RETURN NUMBER
    IS
         T NUMBER := 0;
    BEGIN
         IF PAY >= 3000000 THEN T := TRUNC(PAY*0.03,-1);
         ELSIF PAY >= 2000000 THEN T := TRUNC(PAY*0.02,-1);
         ELSE T :=0;
         END IF;
         RETURN T;
    END;
    
    PROCEDURE EMPLIST(PNAME VARCHAR2) --프로시저
    IS
        VNAME VARCHAR2(30);
        VSAL  NUMBER;
        CURSOR CUR_EMP IS
            SELECT NAME,SAL  
            FROM EMP WHERE INSTR(NAME,PNAME)>=1;
    BEGIN
        OPEN CUR_EMP;
        LOOP
            FETCH CUR_EMP INTO VNAME,VSAL;
            EXIT WHEN CUR_EMP%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(VNAME||':'||VSAL);
        END LOOP;
        CLOSE CUR_EMP;
    END;
    
    PROCEDURE EMPLIST --프로시저 
    IS
    BEGIN
        FOR REC IN (SELECT NAME,SAL+BONUS PAY,FNTAX(SAL+BONUS)TAX FROM EMP)LOOP
            DBMS_OUTPUT.PUT_LINE(REC.NAME||':'||REC.PAY||':'||REC.TAX);
        END LOOP;
    END;
END PEMP;

 

패키지내의 함수나 프로시저 실행은 패키지 명 다음에 점(.)을 찍고 프로시저나 함수명을 적어준다.

EXEC pEmp.empList('김');
EXEC pEmp.empList;

 

패키지 삭제 

--선언부와 몸체 삭제
DROP PACKAGE 패키지명;

--몸체 삭제
DROP PACKAGE BOOY 패키지명;