[oracle] Function / Trigger(트리거)

DB FUNCTION ?

현업에서 반복되는 값 계산 또는 반복되는 select문을 간편하게 사용하기 위해서 함수를 필수적으로 사용을 합니다.

대부분의 구성은 프로시저와 비슷합니다. 그러나 프로시저는 반환값이 없지만 함수는 반환값이 있습니다.

 

 

또한, 반드시 반환 될 값의 데이터 타입을 return문에 선언해야 한다.

그러면 반드시 return문을 통해서 반환되는 값이 있어야겠죠?

 

 

문법

CREATE OR REPLACE FUNCTION
[(argument)]

RETURN datatype --반환되는 값의 datatype

IS

	[변수 선언 부분]
    
BEGIN

    [SQL BLOCK]
    RETURN 변수;
    
END;

 

사용중인 FUNCTION

CREATE OR REPLACE FUNCTION FN_GET_COMPANY_NM(comp_cd in varchar2 ) --함수이름 및 파라메터
RETURN VARCHAR2 --리턴타입 

IS 
    tmpVar varchar2(40); --변수 선언
    
BEGIN
   tmpVar := ''; --변수 선언
   
   SELECT COUNT(1) into tmpVar  --COUNT(1)의 값을 tmpVar에 복사
   FROM RN_COMPANY WHERE COMP_NO = comp_cd; --RN_COMPANY테이블의 COMP_NO와 파라메터 comp_cd 비교
   
   IF tmpVar = '0' THEN
     RETURN NULL; --일치하는게 없다면 종료
   END IF;
   
   SELECT COMP_NM into tmpVar
   FROM RN_COMPANY WHERE COMP_NO = comp_cd;
   RETURN tmpVar;
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       RAISE;
END FN_GET_COMPANY_NM;

 

IF문

조건이 1개	조건이 2개	조건이 n개
IF 조건 THEN    조건 처리; END IF;	IF 조건 THEN    조건 처리 1; ELSE 조건 처리2; END IF;	IF 조건1 THEN    조건 처리1; ELSIF 조건2 THEN    조건 처리2;    ... ELSE    조건 처리n; END IF;

 

 

 

 

 

---

 

 

트리거

테이블에 어떤 행동이(CRUD) 발생했을 때, 자동으로 사용자가 정의한  트리거(PL/SQL문)를 실행 할수 있는 구문이다.

 

 

문법

CREATE or REPLACE TRIGGER 트리거명
BEFORE or AFTER 

[INSERT,DELETE,UPDATE] ON table_name
[Referencing OLD AS {변경전 값을 참조하는 변수명} NEW AS {변경후 값을 참조하는 변수명}]

[ FOR EACH ROW ]

DECLARE
-- 변수선언

BEGIN
-- 트리거 코드(PL/SQL 코드)

EXCEPTION
WHEN
--익셉션

END;

 

실무에서는 트리거는 굉장히 많이 사용되며 보통 접속 이력을 남기는 테이블으로 인해 만들어진다.

 

문법중 5번째줄에

Referencing OLD AS OLD NEW AS NEW

라는 문장이 있는데

 

변경전 값을 참조하는 변수명이라고 적어놨다. 대부분 이말의 뜻이 이해가겠지만.. 나는 뭔소린지 잘 몰라 계속해서 찾아본 결과

 

만약에 내가 A라는 테이블에 update를 했다고 치자. 그러면 A라는 테이블에 update를 하면 발동하는 트리거를 만들어 놨고

update 쿼리를 날리기전의 값을 OLD를 통해서 가져올수 있고 변경된 후의 값을 NEW를 통해서 값을 가져올수 있다.

 

 

그러면 내가 사용하는 실제 트리거를 살펴보자