eXERD

(사용예)

회원이 상품을 구매할 경우 구매상품(ORDER_GOOD)테이블에 구매정보가 기록되어야 한다. 이 때 구매테이블의 내용(AMOUNT)이 자동으로 갱신될 수 있는 트리거를 작성하시오.

SELECT절이나 WHERE절에서 쓰여질 것은 함수로 만들어야함 -> 반환값이 있기 때문
외부에서 프로시저는를 호출 할 때 반환값이 없기 때문에 독립실행되어져야함.
다른 블록에서 프로시저를 호출할 때는 EXECUTE 없이 그냥 호출해야함

1) 주문번호 생성 함수

CREATE OR REPLACE FUNCTION FN_CREATE_ORDER_NUMBER
     RETURN NUMBER
     IS
       V_ONUM TBL_ORDER.ORDERNUM%TYPE;
       V_FLAG NUMBER:=0;
    BEGIN
      SELECT COUNT(*) INTO V_FLAG
        FROM TBL_ORDER
       WHERE TRUNC(ODATE) = TRUNC(SYSDATE);
      IF V_FLAG=0 THEN
         V_ONUM:=TO_CHAR(SYSDATE, 'YYYYMMDD')||TRIM('001');
      ELSE
        SELECT MAX(ORDERNUM)+1 INTO V_ONUM
          FROM TBL_ORDER
         WHERE TRUNC(ODATE) = TRUNC(SYSDATE);
      END IF;
      RETURN V_ONUM;
    END;

• (INSERT문에 함수 사용)

 INSERT INTO TBL_ORDER 
      VALUES(FN_CREATE_ORDER_NUMBER, SYSDATE, 0,'2205');

2) 트리거 생성

• 주문상품 테이블(ORDER_GOOD)에 데이터가 INSERT되면
  구매 테이블(TBL_GOOD)의 금액이 UPDATE됨

    CREATE OR REPLACE TRIGGER TG_UPDATE_ORDER
      AFTER INSERT ON ORDER_GOOD
      FOR EACH ROW
    DECLARE
      V_GID TBL_GOOD.GOOD_ID%TYPE;
      V_PRICE NUMBER:=0;
      V_AMT NUMBER:=0;
    BEGIN
      V_GID:=(:NEW.GOOD_ID);
        SELECT GOOD_PRICE INTO V_PRICE
          FROM TBL_GOOD
         WHERE GOOD_ID = V_GID;
       V_AMT:= V_PRICE * (:NEW.ORDER_QTY);
         UPDATE TBL_ORDER
            SET AMOUNT = AMOUNT + V_AMT
          WHERE ORDERNUM = (:NEW.ORDERNUM);
    END;

• :NEW -> 테이블에 새로 삽입(INSERT, UPDATE)된 데이터 (의사(pseudo)레코드)
• :OLD -> 테이블에 이미 저장되어있던 데이터 (DELETE,UPDATE일 때 유효)
• 트리거에는 DCL명령(Rollback, COMMIT)을 쓸 수 없다.

'but' 트리거가 실행될 때 ORDER_GOOD에서 주문번호(ORDERNUM)을 가져오려면 (추가예정)

3) 프로시저 생성

    CREATE OR REPLACE PROCEDURE PROC_INSERT_ORDER_GOOD(
      P_CID IN CUSTOMER.CID%TYPE,
      P_GID IN TBL_GOOD.GOOD_ID%TYPE,
      P_SU IN NUMBER)
    IS
      V_ORDER_NUM TBL_ORDER.ORDERNUM%TYPE;
    BEGIN
      SELECT ORDERNUM INTO V_ORDER_NUM
        FROM TBL_ORDER
       WHERE TRUNC(ODATE)=TRUNC(SYSDATE) AND CID = P_CID;       
      INSERT INTO ORDER_GOOD
        VALUES(P_GID, V_ORDER_NUM, P_SU);
     COMMIT;
    END;

• TBL_ORDER 에서 CID(고객번호)에 부여된
  ORDERNUM(주문번호)을 찾아오기 위해 생성한 프로시져
• 같은 회원이 오전에 한번 구매하고 오후에 다시 접속하면 새로운 주문번호가 부여되는데 프로시져에서 그 회원의 주문번호를 불러오게 되면 그 날 부여된 모든 주문번호가 다 불러오게 된다. 따라서 SELECT절에 서브쿼리를 사용하여 부여된 주문번호 중에 가장 큰 값 하나만 불러오도록 수정해보자.

3-1) 프로시저 수정