User Defined Function
Functiondms Procedure가 갖는 장점은 동일
- 반환값 존재
: 일반 오라클 내장함수처럼 사용할 수 있다는 것
자주 반복되는 subquery, 복잡한 계산식을 사용자가 만들어서 일반 함수처럼 사용 가능 - 반환할 데이터 타입을 RETURN으로 선언
- 실행영역에서 RETURN문이 있어야 함
CREATE OR REPLACE FUNCTION fn_memName
(p_mem_id IN VARCHAR2)
RETURN VARCHAR2
IS
r_name VARCHAR2(30);
BEGIN
SELECT mem_name INTO r_name FROM member
WHERE mem_id =p_mem_id;
RETURN r_name;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('예외 발생: ' || SQLERRM);
RETURN NULL;
END;
VAR m_name VARCHAR2
EXEC :m_name := fn_memName('a001');
PRINT m_name;
SELECT cart_no, cart_prod, cart_member, fn_memName(cart_member)
FROM cart
WHERE cart_no >= '2020040100001';
CREATE OR REPLACE FUNCTION fn_prodAvgQty
(p_year IN NUMBER DEFAULT (EXTRACT(YEAR FROM SYSDATE)),
p_prod_id IN VARCHAR2)
RETURN NUMBER
IS
r_qty NUMBER(10);
v_year VARCHAR2(5) := TO_CHAR(p_year) || '%';
BEGIN
SELECT NVL(AVG(cart_qty), 0) INTO r_qty FROM cart
WHERE cart_prod = p_prod_id AND cart_no LIKE v_year;
RETURN r_qty;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('예외 발생: ' || SQLERRM);
RETURN 0;
END;
/트리거
: 특정 동작을 이벤트로, 그로 인해서만 실행되는 프로시저의 일종
이벤트 발생 : 특정 테이블 변경 => 다른 테이블 자동으로(연쇄적으로) 변경
--CREATE TRIGGER 문 예시
CREATE OR REPLACE TRIGGER TG_LPROD_IN
AFTER INSERT
ON LPROD
BEGIN
DBMS_OUTPUT.PUT_LINE('상품분류가 추가되었습니다');
END;타이밍
- [BEFORE] 타이밍
: 어떤 테이블에 INSERT, UPDATE, DELETE 문이 실행될 때 해당 문장이 실행되기 전에 트리거가 가지고 있는 BEGIN ~ END 사이의 문장을 실행 - [AFTER] 타이밍은 INSERT, UPDATE, DELETE 문이 실행되고 난 후에 트리거가 가지고 있는 BEGIN ~ END 사이의 문장을 실행
이벤트
- 사용자가 어떤 DML(INSERT, UPDATE, DELETE)문을 실행했을 때 트리거를 발생시킬 것인지를 결정
몸체
- 해당 타이밍에 해당 이벤트가 발생하게 되면 실행될 기본 로직이 포함되는 부분으로 BEGIN ~ END에 기술
유형
문장 레벨 트리거
: FOR EACH ROW가 생략
: 어떤 사용자가 트리거가 설정되어 있는 테이블에 대해 DML(INSERT, UPDATE, DELETE)문을 실행할 때 단 한번만 트리거를 발생시킬 때 사용
행 레벨 트리거
: 반드시 FOR EACH ROW를 기술
: DML(INSERT, UPDATE, DELETE)문에 의해서 여러 개의 행이 변경된다면 각 행이 변경될 때마다 트리거를 발생시키는 방법 - 만약 5개의 행이 변경되면 5번 트리거가 발생
조건
- 행 레벨 트리거에서만 설정 가능
- 트리거 이벤트에 정의된 테이블에 이벤트가 발생할 때 보다 구체적인 데이터 검색 조건을 부여할 때 사용 됨
CREATE OR REPLACE TRIGGER TG_LPROD_IN
AFTER INSERT
ON LPROD
BEGIN
DBMS_OUTPUT.PUT_LINE('상품분류가 추가되었습니다');
END;
/
--만들어진 트리거 확인
SELECT TRIGGER_NAME FROM USER_TRIGGERS;
--상품분류 테이블에 로우 추가
SET SERVEROUTPUT ON;
INSERT INTO LPROD(LPROD_ID, LPROD_GU, LPROD_NM)
VALUES((SELECT MAX(LPROD_ID) + 1 FROM LPROD),
(SELECT 'P' || (SUBSTR(MAX(LPROD_GU), 2) +1) FROM LPROD), '트리거추가값1');
SELECT * FROM LPROD;--트리거 실습
--직원 저장 테이블
CREATE TABLE EMP01(
EMPNO NUMBER(4) PRIMARY KEY,
EMPNAME VARCHAR2(45),
EMPJOB VARCHAR2(60)
);
--급여 저장 테이블
CREATE TABLE SAL01(
SALNO NUMBER(4) PRIMARY KEY,
SAL NUMBER(7, 2),
EMPNO NUMBER(4) REFERENCES EMP01(EMPNO) --참조키
);
--급여번호를 자동 생성하는 시퀀스를 정의하고 이 시퀀스로부터 일련번호를 얻어 급여번호에 부여
CREATE SEQUENCE SAL01_SALNO_SEQ;
CREATE OR REPLACE TRIGGER TRG_02
AFTER INSERT
ON EMP01
FOR EACH ROW
BEGIN
INSERT INTO SAL01 VALUES(
SAL01_SALNO_SEQ.NEXTVAL, 100, :NEW.EMPNO); --:NEW => 인식될 때 새로 받는 값, :OLD => 삭제 시
END;
/
INSERT INTO EMP01 VALUES(2, '전수빈', '프로그래머');
SELECT * FROM EMP01;
SELECT * FROM SAL01;
--트리거 삭제(급여 테이블에서 참조하고 있기때문에 오류)
DELETE FROM EMP01 WHERE EMPNO = 2;
--트리거 자동 삭제(사원 정보 제거될 때 그 사원 급여 정보도 함께 삭제하는 트리거)
CREATE OR REPLACE TRIGGER TRG_03
AFTER DELETE ON EMP01
FOR EACH ROW
BEGIN
DELETE FROM SAL01 WHERE EMPNO =:OLD.EMPNO; --!!!:OLD!!!
END;
/
--사원 테이블 로우 삭제
DELETE FROM EMP01 WHERE EMPNO = 2;
SELECT * FROM EMP01;
SELECT * FROM SAL01;
--트리거 삭제
DROP TRIGGER TRG_02;
--입고 트리거 작성
--상품 테이블 생성
CREATE TABLE PRODUCT(
PROD_ID VARCHAR2(6) PRIMARY KEY,
PROD_NAME VARCHAR2(12) NOT NULL,
PROD_JEJO VARCHAR2(12),
PROD_SALE NUMBER(8),
PROD_JAEGO NUMBER DEFAULT 0
);
--입고테이블 생성
CREATE TABLE IPGO(
IPGO_ID NUMBER(6) PRIMARY KEY,
PROD_ID VARCHAR2(6) REFERENCES PRODUCT(PROD_ID),
IPGO_DAY DATE DEFAULT SYSDATE,
IPGO_QTY NUMBER(6),
IPGO_COST NUMBER(8),
IPGO_AMOUNT NUMBER(8)
);
--샘플데이터
INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
VALUES('A00001','세탁기', 'LG', 500);
INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
VALUES('A00002','컴퓨터', 'LG', 700);
INSERT INTO PRODUCT(PROD_ID, PROD_NAME, PROD_JEJO, PROD_SALE)
VALUES('A00003','냉장고', '삼성', 600);
--입고테이블에 상품 입력되면 입고 수량을 상품 테이블 재고 수량에 추가하는 트리거 작성
CREATE OR REPLACE TRIGGER TRG_04
AFTER INSERT
ON IPGO
FOR EACH ROW
BEGIN
UPDATE PRODUCT
SET PROD_JAEGO = PROD_JAEGO + :NEW.IPGO_QTY
WHERE PROD_ID = :NEW.PROD_ID;
END;
/
--트리거 실행 후, 테이블에 행 추가
INSERT INTO IPGO(IPGO_ID, PROD_ID, IPGO_QTY, IPGO_COST, IPGO_AMOUNT)
VALUES(1, 'A00001', 5, 320, 1600);
INSERT INTO IPGO(IPGO_ID, PROD_ID, IPGO_QTY, IPGO_COST, IPGO_AMOUNT)
VALUES(3, 'A00003', 50, 700, 3500);
기록