오라클 PL/SQL 트리거(Trigger)와 함수(User Defined Function) 특징 및 예제

사용자 정의 함수(User defined Function, Function) 특징

프로시저(Procedure)처럼 SQL과 로직을 묶은 명령문이다. 다만 다른 점은 RETURN을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 특징이 있다. 보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용한다. 즉, Function은 특정 작업 수행 후 반드시 결과값을 RETURN하는 PL/SQL 블럭(BLOCK).

생성방법은 프로시저와 동일하다. 단지 Procedure가 아니라 function으로만 바꿔주면 된다.

CREATE [OR REPLACE] FUNCTION "함수 이름"(){ ... ...

트리거(Trigger) 특징

1) 트리거(Trigger)란 특정 테이블에 DML이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램. 즉, 사용자가 직접 호출하는 것이 아니라 데이터베이스에서 자동으로 수행하는 프로시저(Procedure)이다.

2) 지속적으로 조회해야 하는 집계데이터들은 트리거를 통해 미리 계산해서 테이블에 보관하도록 하면 좋음. 또한 SQL의 제약조건 방법을 통해 명시할 수 없는 무결성 제약조건을 구현하고, 관련 테이블의 데이터를 일치시킬 때도 자주 사용된다.

예) ‘입고’ 테이블에 새로운 제품이 들어왔을 때, 그 수량을 "재고" 테이블에 자동으로 반영되게 하는 경우.

3) 뷰(View) 대해서가 아니라 TABLE에 관해서만 정의될 수 있다.

4) 트랜잭션 제어문(COMMIT, ROLLBACK, SAVEPOINT)을 사용할 수 없습니다.

트리거(Trigger) 문법 예시

CREATE [OR REPLACE] TRIGGER '트리거 이름'()

AFTER INSERT

ON 'trigger를 설정할 테이블'

FOR EACH ROW

DECLARE ← 변수를 선언할 때는 DECLARE문을 사용해야 합니다. 'PL/SQL 블록 기본 문법' 을 확인하려면 여기를 클릭!!

변수1 데이블명.칼럼명%TYPE;

변수2 데이블명.칼럼명%TYPE;

BEGIN

변수1 := :NEW.칼럼명;

변수2 := :OLD.변수명;

UPDATE 테이블명

SET

Where

If SQL%NOTFOUND then

…쿼리문…

end if;

END;

/

1) FOR EACH ROW : 테이블의 각 ROW마다 Trigger를 적용하겠다는 의미.

2) :NEW : 신규로 입력된 레코드의 정보를 가지고 있는 구조체를 의미.

3) :OLD : 수정, 삭제되기 전의 레코드를 가지고 있는 구조체를 의미.

4) SQL%NOTFOUND : 해당 if문에서는 SQL처리 결과가 NULL이 아니면. then 아래 작성한 Query문을 실행.

트리거(Trigger) 실행 예제

SQL>

CREATE OR REPLACE TRIGGER sum_trigger

BEFORE

INSERT OR UPDATE ON emp

FOR EACH ROW

DECLARE

avg_sal NUMBER;

BEGIN

SELECT ROUND(AVG(sal),3)

INTO avg_sal

FROM emp;

DBMS_OUTPUT.PUT_LINE('급여 평균 : ' || avg_sal);

END;

/

-- DBMS_OUTPUT.PUT_LINE을 출력.

SQL> SET SERVEROUTPUT ON ;

-- INSERT문을 실행. 1 개의 행을 추가.

SQL> INSERT INTO EMP(EMPNO, ENAME, JOB, HIREDATE, SAL) VALUES(1000, 'LION', 'SALES', SYSDATE, 5000);

-- INSERT문을 실행되기 전까지의 급여 평균이 출력된다.

급여 평균 : 2073.214

1 개의 행이 만들어졌습니다.