Tag Archives: 오라클

[ORACLE] 트리거 사용 예제

오늘 오라클에 대해 잠깐 볼 기회가 생겨 보던중에 트리거라는 녀석과 마주치게 되었습니다.

그래도 공부한 내용인데 까먹지 않기 위해 기록해 둬야 겠습니다.

트리거를 이용해 특정 테이블의 데이터에 생기는 작업을 감시 하여 추가적인 작업을 수행할 수 있습니다.

마치 스프링 AOP의 Advice같은 느낌입니다. 하지만 DB에서 이런것을 마구 쓰다보면 퍼포먼스 저하는 자명한 결과일것 같네요.

[code]SQL> DESC RENTAL_LIST;
 이름                                      널?      유형
 —————————————– ——– —————————-
 NO                                        NOT NULL NUMBER(4)
 RENTAL_DATE                               NOT NULL DATE
 TO_RETURN_DATE                                     DATE
 RETURN_DATE                                        DATE
 CUSTOMER_NO                               NOT NULL NUMBER(4)
 DVD_NO                                    NOT NULL NUMBER(4)



SQL> DESC DVD_TITLE;
 이름                                      널?      유형
 —————————————– ——– —————————-
 NO                                        NOT NULL NUMBER(4)
 TITLE                                     NOT NULL VARCHAR2(20)
 GENRE                                              VARCHAR2(20)
 DIRECTOR                                           VARCHAR2(20)
 MAIN_ACTOR                                         VARCHAR2(20)
 PURCHASE_DATE                                      DATE
 QUANTITY                                  NOT NULL NUMBER(2)[/code]

위와 같은 스키마 구조일 때 DVD의 렌탈이 일어나면 DVD_TITLE의 QUANTITY를 1개 줄이고, 반납 일자인 RETURN_DATE를 RENTAL_DATE의 3일 후로 자동 입력하는 코드를 작성하여 봅시다.

BEFORE와 AFTER를 사용하면 됩니다.

[code]CREATE OR REPLACE TRIGGER CHANGE_QUANTITY
AFTER INSERT ON RENTAL_LIST FOR EACH ROW
BEGIN
  UPDATE DVD_TITLE SET QUANTITY = QUANTITY – 1 WHERE NO = :NEW.DVD_NO;
END;

INSERT INTO RENTAL_LIST(NO, RENTAL_DATE, TO_RETURN_DATE, RETURN_DATE, CUSTOMET_NO, DVD_NO)
VALUES(RENTAL_SEQ.NEXTVAL, ‘2007-11-02’, ‘2007-11-05’, ”, 50, 1) [/code]

위와 같은 쿼리문 실행시 :NEW.DVD_NO에는 방금 입력된 DVD_NO의 값이 대입됩니다. DVD_NO로 DVD_TITLE 테이블에서 값을 찾아 QUANTITY를 1 감소시키게 됩니다.

AFTER INSERT ON RENTAL_LIST 에서 알 수 있듯이 RENTAL_LIST에 INSERT가 일어난 후에 이 트리거가 실행됩니다.

FOR EACH ROW 는 여러 ROW가 영향을 받았을 경우 각각을 실행한다는 의미입니다.

이번에는 INSERT 직전에 RETURN_DATE에 RENTAL_DATE의 3일 후 날짜를 자동으로 입력하는 트리거를 작성해 보죠.

[code]CREATE OR REPLACE TRIGGER AUTO_ADDED_RETURN_DATE
BEFORE INSERT ON RENTAL_LIST FOR EACH ROW
BEGIN
  :NEW.RETURN_DATE := :NEW.RENTAL_DATE + 3
END;[/code]

BEFORE INSERT ON RENTAL_LIST 에서 볼 수 있듯이 INSERT가 일어나기 전에 실행됩니다.

값이 입력되지 않은 :NEW.RETURN_DATE 에 :NEW.RENTAL_DATE를 3증가시켜 입력합니다. +3을 하면 3일이 증가됩니다.

이로써 RETURN_DATE에도 값이 자동으로 입력됩니다.

오라클 공부중-_-;

아 정신없다. 한밤중에 포스팅 하는건 무리다. 일단 공부한거 올려만 놓겠다.

[CODE]SELECT A.EMP_ID, A.EMP_NAME,A.DEPT_CODE, B.DEPT_NAME
FROM TEMP A, TDEPT B
WHERE B.DEPT_CODE = A.DEPT_CODE

SELECT A.EMP_ID, A.EMP_NAME, A.SALARY + B.COMM
FROM TEMP A, TCOM B
WHERE A.EMP_ID = B.EMP_ID
  AND B.WORK_YEAR = ‘2001’

SELECT A.EMP_ID, A.EMP_NAME, A.LEV, A.SALARY
FROM TEMP A, EMP_LEVEL B
WHERE A.SALARY BETWEEN B.FROM_SAL AND B.TO_SAL
  AND B.LEV = ‘과장’
 
SELECT A.EMP_ID, B.EMP_ID
FROM TEMP A, TCOM B
WHERE B.WORK_YEAR(+) = ‘2001’
AND B.EMP_ID(+) = A.EMP_ID

SELECT A.EMP_ID, A.EMP_NAME, A.SALARY, B.FROM_SAL, B.TO_SAL
FROM TEMP A, EMP_LEVEL B
WHERE A.LEV = B.LEV(+)

SELECT A.DEPT_CODE “부서코드”,
A.DEPT_NAME “부서명”,
B.DEPT_CODE “상위부서코드”,
B.DEPT_NAME “상위부서명”
FROM TDEPT A, TDEPT B
WHERE B.DEPT_CODE = A.PARENT_DEPT

SELECT A.EMP_ID, A.EMP_NAME, A.BIRTH_DATE, COUNT(A.EMP_ID) “COUNT”
FROM TEMP A, TEMP B
WHERE A.BIRTH_DATE > B.BIRTH_DATE(+)
GROUP BY A.EMP_ID, A.EMP_NAME, A.BIRTH_DATE
ORDER BY COUNT DESC

SELECT AVG(SALARY) FROM TEMP

SELECT EMP_ID, EMP_NAME, SALARY
FROM TEMP
WHERE SALARY > (SELECT AVG(SALARY) FROM TEMP)

SELECT DEPT_CODE, COUNT(*)
FROM TEMP
WHERE EMP_ID IN (SELECT EMP_ID FROM TCOM)
GROUP BY DEPT_CODE

SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE (DEPT_CODE, EMP_ID) IN (SELECT DEPT_CODE, BOSS_ID FROM TDEPT);

SELECT DEPT_CODE, MAX(SALARY) “MAX_SALARY”
FROM TEMP
GROUP BY DEPT_CODE

SELECT EMP_ID, EMP_NAME, SALARY
FROM TEMP
WHERE (DEPT_CODE, SALARY) IN
  (SELECT DEPT_CODE, MAX(SALARY)
  FROM TEMP
  GROUP BY DEPT_CODE)[/CODE]