FUNCTION & TRIGGER : 함수와 트리 거

함수 FUNCTION

리턴 값이 존재함 (C와 비교 >> 프로시저는 VOID 함수, 함수는 RETURN값이 있는 함수)
OUT변수를 사용하지 않고 결과값을 돌려주기 위한 용도 (매개변수를 사용하지 않는 게 아니다)

 

함수를 만들 때에는 FUNCTION이라고 씀

 

형식

CREATE OR REPLACE FUNCTION [함수 명](
    [매개변수 명] [MODE] [데이터 타입])
    RETURN [데이터 타입] --리턴 타입을 여기서 정함
IS 
    [리턴하는 매개변수 명][데이터 타입]
BEGIN
     RETURN [리턴 매개변수 명];
END;
/

함수 호출

EXECUTE : [변수 명] := 함수명(매개변수 명);

 

예제

CREATE OR REPLACE FUNCTION GET_NAME(
    V_DNO DEPARTMENT.DNO%TYPE)
    RETURN DEPARTMENT.DNAME%TYPE --리턴 타입을 여기서 정함
IS 
    V_DNAME DEPARTMENT.DNAME%TYPE;  
BEGIN
    SELECT DNAME INTO V_DNAME FROM department WHERE DNO =V_DNO; --SELECT 결과를 v_dname 변수에 담는 문장
    RETURN V_DNAME;
    EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line(SQLCODE||SQLERRM);
        RETURN 0;
END;
/

SELECT DNO 부서번호,ENO 사번 ,ENAME 사원명, GET_NAME(DNO) 부서명 
FROM EMPLOYEE;

 

트리거 TRIGGER

어떤 이벤트가 발생했을때 내부적으로 실행되도록 데이터베이스에 저장된 프로시저
어떤 이벤트 :  데이터 추가, 수정, 삭제 등

[BEFORE] 해당문장이 실행되기전 체크
[AFTER] 문장이 실행되고 난 후에 트리거가 가지고 있는 BEGIN END문장 실행

 

형식

CREATE OR REPLACE TRIGGER [트리거 명]
     [INSERT, UPDATE, DELETE]
     [FOR EACH ROW ]
     --FOR EACH ROW : 각 행에 변화가 있을 경우 
     --새로운 데이터가 들어가는 순간
     --수정되는 순간
     --삭제되는 순간
BEGIN
...
END;
/

예제1

CREATE TABLE DEPT_HIST AS SELECT * FROM DEPARTMENT WHERE 0 =1;

CREATE OR REPLACE TRIGGER DEPT_TR BEFORE
     INSERT ON DEPARTMENT FOR EACH ROW
BEGIN
     IF INSERTING THEN
        INSERT INTO DEPT_HIST(DNO,DNAME,LOC) VALUES(:NEW.DNO,:NEW.DNAME,:NEW.LOC); 
        --NEW : 새로운데이터, OLD : 이전 데이터
    END IF;
END;
/

INSERT INTO DEPARTMENT VALUES (39,'개발5팀','대구');
SELECT * FROM dept_hist

예제2

CREATE OR REPLACE TRIGGER EMP_TR 
    BEFORE
        INSERT OR UPDATE OF ENAME,SALARY OR DELETE ON EMPLOYEE
BEGIN
CASE 
    WHEN INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('INSERT 트리거');
    WHEN UPDATING('SALARY') THEN
        DBMS_OUTPUT.PUT_LINE('UPDATE SALARY 트리거');   
    WHEN UPDATING('ENAME') THEN
        DBMS_OUTPUT.PUT_LINE('UPDATE ENAME 트리거');   
    WHEN DELETING THEN
       DBMS_OUTPUT.PUT_LINE('DELETE 트리거'); 
    END CASE;
END;
/

INSERT INTO EMPLOYEE (ENO,ENAME,JOB) VALUES (999,'홍길동','감사팀');
UPDATE EMPLOYEE SET ENAME = '김길동' WHERE ENO =999;
DELETE FROM EMPLOYEE WHERE ENO =999;