2) 트리거

서브 프로그램 단위의 하나인 TRIGGER는 테이블, 뷰, 스키마, 또는 데이터베이스에 관련된 PL/SQL 블록(또는 프로시저)으로 관련된 특정 사건(Event)이 발생될 때마다 묵시적(자동)으로 해당PL/SQL 블록이 실행된다. TRIGGER는 데이터베이스 내에 오브젝트로서 저장되어 관리되고, TRIGGER 자체는 사용자가 지정해서 실행할 수 없으며, 오직 TRIGGER 생성시 정의한 특정 사건(Event)에 의해서만 묵시적으로 자동실행(Fire)이 이루어진다. TRIGGER를 이루는 TRIGGER 몸체(실행부)에 TCL 명령, 즉
COMMIT, ROLLBACK, SAVEPOINT 명령이 포함될 수 없다.

트리거의 사전적 의미는 방아쇠를 쏘다,발사하다, 사건을 유발시키다 라는 의미가 있다. PL/SQL에서의 트리거 역시 방아쇠가 당겨지면 자동으로 총알이 발사되듯이 어떠한 이벤트가 발생하면 그에 따라 다른 작업이 자동으로 처리되는 것을 의미한다.

트리거란 특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는 저장 프로시저 라고 할 수 있다.
앞서 배운 저장 프로시저는 필요할 때마다 사용자가 직접 EXECUTE 명령어로 호출해야 했다.
하지만 트리거는 이와 달리 테이블의 데이터가 INSERT,UPDATE,DELETE 문에 의해 변경 될 때 자동으로 수행되므로 이 기능을 이용하여 여러가지 작업을 할 수 있다. 이런 이유로 트리거를 사용자가 직접 실행 시킬수는 없다.

CREATE TRIGGER trigger_name
TIming [BEFORE|AFTER]
EVENT [INSERT|UPDATE|DELETE]
ON table_name
[FOR EACH ROW][WHEN condeitions]
BEGIN
statement
END

1)트리거의 타이밍(트리거의 동작 시점을 설정)

[BEFORE]타이밍은 어떤 테이블에 INSERT,UPDATE,DELETE 문이 실행될 때 해당 문장이 실행되기 전에 트리거가 가지고 있는 BEGIN~END 사이의 문장을 실행한다.
[AFTER] 타이밍은 INSERT,UPDATE,DELETE 문이 실행되고 난 후에 트리거가 가지고 있는 BEGIN~END 사이의 문장을 실행한다.

2) 트리거의 이벤트(트리거 동작을 위한 이벤트 종류를 정의하는 단계)

트리거의 이벤트는 사용자가 어떤 DML(INSERT,UPDATE,DELETE)문을 실행했을 때 트리거를 발생시킬 것인지를 결정한다. ON 테이블명은 트리거가 주목하는 대상 테이블을 정의한다.

3)트리거의 유형

트리거의 유형은 FOR EACH ROW에 의해 문장 레벨 트리거와 행 레벨 트리거로 나눈다.
FOR EACH ROW가 생략되면 문장 레벨 트리거이고, 행 레벨 트리거를 정의하고자 할 때에는 반드시 FOR EACH ROW를 기술해야만 한다. 이때 행 레벨 트리거는 추가되는 행의 수만큼 트리거가 동작하여 행 내에서 이벤트 발생되는걸 기준으로 트리거의 감시,보안,범위를 정해두는 내용이다.

문장 레벨 트리거는 영향을 받는 행이 전혀 없더라도 TRIGGER가 한 번은 실행된다. 문장 TRIGGER 는 TRIGGER 작업이 영향을 받는 행의 데이터 또는 TRIGGER 이벤트 자체에서 제공하는 데이터에 종속되지 않은 경우에 유용하다.
행 레벨 트리거는 테이블이 TRIGGER 이벤트의 영향을 받을때 마다 실행되고, TRIGGER 이벤트의 영향을 받는 행이 없을 경우에는 실행되지 않는다. 행 TRIGGER는 영향을 받은 행의 데이터나 TRIGEER 이벤트 자체에서 제공하는 데이터 TRIGGER 작업이 종속될 경우에 유용하다. 행 TRIGGER로 생성하려면 FOR EACH ROW라는 구절을 사용하면 된다.

4):NEW & OLD

행 레벨 트리거에서 컬럼의 실제 데이터 값을 제어하는데 사용되는 연산자로 INSERT문의 경우 입력된 컬럼의 값은 :NEW, DELETE문의 경우 삭제되는 컬럼값안 :OLD,UPDATE문의 경우 변경 전 칼럼 데이터 값은 :OLD로 수정할 새로운 데이터 값은 :NEW로 가진다.

5) 트리거의 몸체(BEGIN~END)

트리거의 몸체는 해당 타이밍에 해당 이벤트가 발생하게 되면 실행될 기본 로직이 포함되는 부분으로
BEGIN~END에 기술한다.

-트리거의 사용 목적

ㆍ데이터베이스 테이블 새엇ㅇ하는 과정에서 참조 무결성과 데이터 무결성 등의 복잡한 제약 조건을 생성하는 경우
ㆍ데이터베이스 테이블의 데이터에 생기는 작업을 감시,보완
ㆍ데이터베이스 테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행하는 경우
ㆍ불필요한 트랙잭션을 금지하기 위해
ㆍ컬럼의 값을 자동으로 생성되도록 하는 경우

CREATE TABLE EMP03(
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(20),
    JOB VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER EMP_TRG01
AFTER INSERT
ON EMP03
BEGIN
    DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.');
END;
/
INSERT INTO EMP03
VALUES(1,'홍길동','무인');

위 코드를 입력하였을 때 인서트 후 출력창에 '신입사원이 입사했습니다' 라는 문구가 나타나는것을 확인할 수 있다.

-사원 테이블에 새로운 데이터 즉 신입 사원이 들어오면 급여 테이블에 새로운 
--데이터를 자동으로 생성하고 싶을 경우, 사원 테이블에 트리거를 설정하여 구현할 수 있다.
CREATE TABLE SAL03(
    SALNO NUMBER(4),
    SAL NUMBER,
    EMPNO NUMBER(4),
    CONSTRAINT SAL03_PK PRIMARY KEY(SALNO),
    CONSTRAINT SAL03_FK FOREIGN KEY(EMPNO) REFERENCES EMP03(EMPNO)
);

CREATE SEQUENCE SAL03_SEQ
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000000
NOCYCLE
CACHE 2;

--삭제 후 커밋 실행.
DELETE FROM EMP03 WHERE EMPNO = 1;
SELECT * FROM EMP03;

CREATE OR REPLACE TRIGGER EMP_TRG02
AFTER INSERT
ON EMP03
FOR EACH ROW
BEGIN
    INSERT INTO SAL03(SALNO,SAL,EMPNO)
    VALUES(SAL03_SEQ.NEXTVAL,200000,:NEW.EMPNO);
END;
/
DELETE FROM EMP03 WHERE EMPNO = 1;
INSERT INTO EMP03(EMPNO, ENAME, JOB) VALUES(1,'김철수','경영지원직(구매/자재)');

SELECT * FROM EMP03;

SELECT * FROM SAL03;

--사원의 정보가 제거될 때 그 사원의 급여 정보도 함께 삭제하는 내용을 트리거로 작성
INSERT INTO EMP03(EMPNO,ENAME,JOB) VALUES (2,'이영희','경영지원직(인사)');

CREATE OR REPLACE TRIGGER EMP_TRG03
AFTER DELETE ON EMP03
FOR EACH ROW
BEGIN
    DELETE FROM SAL03 WHERE EMPNO=:old.EMPNO;
END;
/

DELETE FROM EMP03 WHERE EMPNO=2;

SELECT * FROM EMP03;
SELECT * FROM SAL03;

결과를 보면 삭제된 것을 확인할 수 있다.

--입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거 작성해 보자.
--1. 상품 테이블을 생성
CREATE TABLE PRODUCT(
    PCODE CHAR(6),                  --상품코드
    PNAME VARCHAR2(12) NOT NULL,   --상품명
    PCOMPANY VARCHAR2(12),          --제조사
    PPRICE NUMBER(8),                --가격
    STOCK NUMBER DEFAULT 0,         --재고수량
    CONSTRAINT PRODUCT_PK PRIMARY KEY(PCODE)
);

--2. 입고 테이블을 생성
CREATE TABLE RECEIVING(
    RNO NUMBER(6),                  --입고번호
    PCODE CHAR(6),                  --상품코드
    RDATE DATE DEFAULT SYSDATE,     --입고날짜
    RQTY NUMBER(6),                 --입고 수량
    RPRICE NUMBER(8),               --입고가격
    RAMOUNT NUMBER(8),              --입고단가
    CONSTRAINT RECEIVING_PK PRIMARY KEY(RNO),
    CONSTRAINT RECEIVING_FK FOREIGN KEY(PCODE) REFERENCES PRODUCT(PCODE)
);

--3. 상품테이블의 재고수량 컬럼을 통해서 실질적인 트리거의 적용 예를 살펴보도록 하겠다.
--우선 상품 테이블에 다음과 같은 샘플 데이터를 입력해보자.
INSERT INTO PRODUCT(PCODE,PNAME,PCOMPANY,PPRICE)
VALUES('A00001','세탁기','LG',15000000);
INSERT INTO PRODUCT(PCODE,PNAME,PCOMPANY,PPRICE)
VALUES('A00002','컴퓨터','LG',10000000);
INSERT INTO PRODUCT(PCODE,PNAME,PCOMPANY,PPRICE)
VALUES('A00003','냉장고','삼성',45000000);

SELECT * FROM PRODUCT;
--4. 입고 테이블에 상품이 입력되면 입구 수량을 상품 테이블의 재고 수량에 추가하는 트리거 작성
CREATE OR REPLACE TRIGGER TRG_IN
AFTER INSERT ON RECEIVING
FOR EACH ROW
BEGIN
    UPDATE PRODUCT
    SET STOCK = STOCK + :NEW.RQTY     --재고수량= 재고수량 + 입고수량
    WHERE PCODE = :NEW.PCODE;
END;
/

--5.트리거를 실행시킨 후 입고 테이블에 행을 추가한다. 입고 테이블에는 물론 상품 테이블의 재고 수량이 변경됨을 
--확인할 수 있다.
INSERT INTO RECEIVING(RNO,PCODE,RQTY,RPRICE,RAMOUNT)
VALUES(1,'A00001',5,850000,950000);

SELECT * FROM RECEIVING;

SELECT * FROM PRODUCT;

--6.입고 테이블에 상품이 입력되면 자동으로 상품 테이블의 재고 수량이 증가하게 된다.
--입고 테이블에 또 다른 상품을 입력한다.
INSERT INTO RECEIVING(RNO,PCODE,RQTY,RPRICE,RAMOUNT)
VALUES(2,'A00002',10,680000,780000);

INSERT INTO RECEIVING(RNO,PCODE,RQTY,RPRICE,RAMOUNT)
VALUES(3,'A00003',10,250000,300000);

SELECT * FROM PRODUCT;

SELECT * FROM RECEIVING;

--<실습하기> 갱신 트리거 작성하기
-- 이미 입고된 상품에 대해서 입고 수량이 변경되면 상품 테이블의 재고수량 역시 변경되어야한다.
--이를 위한 갱신 트리거를 작성
--1.갱신 트리거 작성

CREATE OR REPLACE TRIGGER TRG_UP
AFTER UPDATE ON RECEIVING
FOR EACH ROW
BEGIN
    UPDATE PRODUCT
    SET STOCK = STOCK +(-:old.RQTY + :new.RQTY)
    WHERE PCODE =:new.PCODE;
END;
/

--2.입고 번호 3번은 냉장고가 입고된 정보를 기록한 것으로서 입고 번호 3번의 입고수량을 8번으로
--변경하였더니 냉장고의 재고 수량 역시 8로 변경되었다.
UPDATE RECEIVING SET RQTY=8, RAMOUNT=280000 --입고수량과 입고금액
WHERE RNO = 3;

SELECT * FROM RECEIVING;

3) FUNCTION 이란?

값을 반환하는 명명된 PL/SQL BLOCK으로 오라클 내장 함수와 같이 SQL 표현식의 일부로 사용하여 복잡한 SQL문을 간단한 형태로 사용할 수 있다. 값을 반환하는 RETURN이 반드시 포함되며 반드시 하나의 값을 반환한다. 그래서 내장 함수와 프로시저는 문법이나 특징이 거의 비슷하지만 차이점은 프로시저는 정해진 작업을 수행한 후 결과를 반환할 수도 있고(OUT,IN OUT모드 사용시)반환하지 않고 그냥 종료할 수도 있지만 함수는 정해진 작업을 수행한 후 결과를 돌려준다(RETURN)는 부분이다. 이 떄 RETURN의 데이터타입은 오직 CHAR, DATE, NUMBER이어야한다.

CREATE [OR REPLACE] FUNCTION FUNCTION명
(매개변수1 데이터타입, 매개변수2, 데이터타입...)
RETURN 데이터타입 --함수가 반환할 데이터 타입 지정. 크기는 지정할 수 없다.
IS
지역변수
BEGIN
실행부
RETURN 반환값; -- 매개변수를 받아 특정 연산을 수행한 후 반환할 값을 명시
[EXCEPTION 예외 처리부]
END[함수이름];
/

--첫번째 방법
--부서 번호를 매개변수로 부서의 이름을 반환하는 함수를 생성하여 봅시다
CREATE OR REPLACE FUNCTION GETDNAME(vdepartment_id IN departments.department_id%type)
RETURN VARCHAR2
IS
 vdepartment_name departments.department_name%type;
 vcount NUMBER := 0;
 BEGIN
  SELECT COUNT(*) INTO vcount FROM departments
  WHERE department_id = vdepartment_id;
  
  IF vcount = 0 THEN
  vdepartment_name := '해당 부서 없음';
  ELSE
   SELECT department_name INTO vdepartment_name FROM departments
   WHERE department_ID = vdepartment_id;
  END IF;
  RETURN vdepartment_name;
END;
/

select first_name, job_id, nvl(commission_pct,0) commission_pct, salary,
GETDNAME(department_id) DNAME
FROM employees
WHERE first_name = 'Lisa';

select getdname(500) from dual;

-부서 번호를 매개변수로 부서의 이름을 반환하는 함수를 생성하여 봅시다(두번째 방법)
CREATE OR REPLACE FUNCTION GETDNAME(vdepartment_id IN departments.department_id%type)
RETURN VARCHAR2
IS
  vdepartment_name departments.department_name%type;
BEGIN
    SELECT department_name INTO vdepartment_name FROM departments
    WHERE department_id = vdepartment_id;
    RETURN vdepartment_name;

 EXCEPTION
    WHEN NO_DATA_FOUND THEN
        vdepartment_name := '해당 부서 없음';
RETURN vdepartment_name;
END;
/

SELECT employee_id,first_name,TO_CHAR(hire_date,'YYYY-MM-DD') HIREDATE,
    GETDNAME(department_id) department_name
FROM employees;

select getdname(400) from dual;

--<예제> 부서번호를 매개변수로 해당 부서의 급여의 합을 반환하는 함수를 생성하여 봅시다.
--(함수명:GETSUMDEPT)

CREATE OR REPLACE FUNCTION GETSUMDEPT(vdepartment_id in departments.department_id%type)
RETURN VARCHAR2
IS
    vsumsalary VARCHAR2(50);
BEGIN
    select NVL(TO_CHAR(ROUND(SUM(salary)),'9999999'),'해당 부서 없음') into vsumsalary from employees
    where department_id = vdepartment_id;
    return vsumsalary;
    
        RETURN vsumsalary;
end;
/
--사원명 , 급여, 부서번호, 부서명, 부서의 급여합을 출력. (사원번호가 100인, 178인)
SELECT first_name, salary, department_id, GEtdname(department_id) department_name,
        getsumdept(department_id) sumsalary
from employees
where employee_id = 100;

4) PL/SQL 예외란?

PL/SQL 블록이 PARSE 되는 동안에 오타 등으로 인하여 발생되는 에러를 컴파일 에러(Compilation Error) 라고 부르며, PL/SQL 블록이 실행되는 동안에 발생되는 에러를 런타임 에러(Run-Time Error)라고 부르는데, 이 런타임 에러를 오라클에서는 예외(ORACLE Exception)와 다른 하나는 사용자에 의해 정의되는 사용자 정의 예외(User-defined Exception)가 있다.

-미리 정의되어 있는 오라클 예외들

...
EXCEPTION
WHEN exception1 [OR exception2 ...] THEN
statement1;
statement2;
...
WHEN exception3 [OR exception4 ...] THEN
statement3;
statement4;
...
...

DECLARE
    vfirst_name employees.first_name% type;
BEGIN
    SELECT first_name INTO vfirst_name
  FROM employees
    WHERE first_name Like 'O%';
    DBMS_OUTPUT.PUT_LINE('사원명은 '||vfirst_name||'입니다');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 존재하지 않습니다.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('현재 단어를 포함한 사원이 두명 이상 존재합니다.');
END;
/

WHERE first_name Like 'Y%';

WHERE first_name Like 'A%'

-RAISE를 사용하여 예외 처리하기
예제를 수행하기 위해서는 EMP04테이블을 삭제하고(존재한다면) 다시 생성한다.

DROP TABLE EMP04;
CREATE TABLE EMP04
AS
SELECT EMPLOYEE_ID,FIRST_NAME FROM EMPLOYEES;

SELECT EMPLOYEE_ID,FIRST_NAME FROM EMP04 ORDER BY EMPLOYEE_ID;


CREATE OR REPLACE PROCEDURE NOEMPNO_EXCEPTION
(vemployee_id emp04.employee_id%type)
IS
    exempid EXCEPTION;
BEGIN
    DELETE FROM emp04 WHERE employee_id = vemployee_id;
    IF SQL%NOTFOUND THEN
        RAISE exempid;
    ELSE
        DBMS_OUTPUT.PUT_LINE(CONCAT(vemployee_id,'사원이 삭제되었습니다.'));
      END IF;
EXCEPTION
    WHEN exempid THEN
        DBMS_OUTPUT.PUT_LINE('입력하신 번호는 없는 사원입니다.');
END;
/

EXEC NOEMPNO_EXCEPTION(800);

EXEC NOEMPNO_EXCEPTION(100);

5) 패키지

관련 있는 프로시저를 보다 효율적으로 관리하기 위해서 패키지 단위로 배포할 때 유용하게 사용된다.

-패키지는 연관성이 높은 함수나 프로시저를 하나의 그룹으로 묶어두는 개념.
-패키지 선언부(Spec)와 몸체부(body)로 구성.
-패키지 선언부의 역할은 해당 패키지에 사용될 함수나 프로시저,변수등에 대한 정의를 선언하는 부분
-패키지 몸체부에서는 선언부에서 선언된 함수나 프로시저들이 실제 구현되는 부분임.

패키지는 패키지선언(명세부)과 패키지 몸체선언(몸체부) 두 가지 모두를 정의해야한다.

패키지 선언부(Specification)
Create [OR REPLACE] PACKAGE 패키지명
IS
TYPE구문;
상수명 CONSTANT 상수타입;
변수명 변수타입;
커서구문;
FUNCTION 함수명(매개변수1 IN 매개변수1타입, 매개변수2 IN 매개변수2타입,...)
RETURN 반환타입;
PROCEDURE 프로시저명 (매개변수1 [IN,OUT,INOUT] 매개변수1타입,
매개변수2[IN,OUT,INOUT] 매개변수2_타입,...);
...
END 패키지명;

패키지 본문(BODY)
CREATE OR REPLACE PACKAGE BODY 패키지명IS
상수명 CONSTANT 상수타입;
변수명 변수타입;
커서정의구문;

FUNCTION 함수명 ( 매개변수1 IN 매개변수1타입, 매개변수2 IN 매개변수2타입,...)
RETURN 반환타입
IS
...,
BEGIN
...,
RETURN 값
END 함수명;

PROCEDURE 프로시저명(매개변수 1[ IN,OUT,INOUT] 매개변수1타입,
매개변수2[IN,OUT,INOUT] 매개변수2타입,...)
IS
...,
BEGIN
...,
END 프로시저명;
...
END 패키지명;

패키지 내의 정의된 프로시저를 호출하는 방식["패키지명.서브_프로그램명" 형태로 사용]
EXECUTE 패키지명.프로시저명;