230808 SQL 강의

---

--분류테이블에 추가되거나, 변경될 때 분류코드를 항상 대문자로 처리하는 트리거 예제

CREATE or REPLACE TRIGGER tg_lprod_upper BEFORE INSERT or UPDATE 
    ON  lprod
   FOR EACH ROW
BEGIN
    :NEW.lprod_gu := UPPER(:NEW.lprod_gu);
END;
/

INSERT INTO lprod VALUES ((SELECT MAX(LPROD_ID) + 1 FROM LPROD), 'tt07', '트리거 테스트 ');
SELECT * FROM lprod;

---

SET SERVEROUTPUT ON;
--장바구니 테이블에 입력이 발생할 때 재고 수불 테이블에 출고, 현재고를 변경하는 트리거 예제(2023년으로 예제 작성)
CREATE or REPLACE TRIGGER tg_cart_qty_change
      AFTER insert or update or delete ON cart
      FOR EACH ROW
   DECLARE 
      v_qty NUMBER;
      v_prod VARCHAR2(20);
 BEGIN
    IF INSERTING THEN
         v_qty := NVL(:NEW.cart_qty,0);
         v_prod := :NEW.cart_prod;
    ELSIF UPDATING THEN
         v_qty :=  NVL(:NEW.cart_qty,0) - NVL(:OLD.cart_qty,0);
         v_prod := :NEW.cart_prod;
    ELSIF DELETING THEN
         v_qty :=  -(NVL(:OLD.cart_qty,0));
         v_prod := :OLD.cart_prod;
    END IF;
    
--장바구니 테이블에 입력이 발생할 때 재고 수불 테이블에 출고, 현재고를 변경하는 트리거 예제 (2020년으로 예제 작성) (Cont.)

    UPDATE remain 
      SET remain_o = remain_o + v_qty
        , remain_j_99 = remain_j_99 - v_qty
     WHERE remain_year = '2023' AND remain_prod = v_prod;
    
    DBMS_OUTPUT.PUT_LINE('수량 :' || v_qty);

EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('예외 발생:' || SQLERRM);     
END;
/

INSERT INTO cart VALUES ('a001','2023040100001','P101000002',7);

SELECT * 
  FROM remain 
 WHERE remain_year='2020'
   AND remain_prod = 'P101000002';

SELECT * 
  FROM remain 
 WHERE remain_year in ('2002', '2003')
   AND remain_prod = 'P101000002';

---

-- 재고현황 테이블 만들기

CREATE TABLE REMAIN_TEST
(  
    REMAIN_YEAR      CHAR(04)      NOT NULL,  -- 해당 년도   
    REMAIN_PROD      VARCHAR2(10)  NOT NULL,  -- 상품 코드   
    REMAIN_J_00      NUMBER(5),               -- 전년 재고   
    REMAIN_I         NUMBER(5),               -- 입고
    REMAIN_O         NUMBER(5),               -- 출고
    REMAIN_J_99      NUMBER(5),               -- 현재고  
    REMAIN_DATE      DATE,                    -- 처리일자
    CONSTRAINT PK_REMAIN_TEST      PRIMARY KEY(REMAIN_YEAR, REMAIN_PROD),
    CONSTRAINT FR_REMAIN_TEST_PROD FOREIGN KEY(REMAIN_PROD) 
                                   REFERENCES PROD(PROD_ID)
);

-- 재고현황 테이블에 데이터 삽입하기
INSERT INTO REMAIN_TEST(REMAIN_YEAR, REMAIN_PROD, remain_j_00,REMAIN_I, REMAIN_J_99, REMAIN_DATE)
VALUES('2020', 'P102000001', 30, 30, 10, '2020-12-31');
INSERT INTO REMAIN_TEST(REMAIN_YEAR,REMAIN_PROD,remain_j_00,REMAIN_I,remain_o,REMAIN_J_99,REMAIN_DATE)
VALUES('2021', 'P102000002', 50, 40, NULL, 30, '2021-10-11');
INSERT INTO REMAIN_TEST(REMAIN_YEAR,REMAIN_PROD,remain_j_00,REMAIN_I,remain_o,REMAIN_J_99,REMAIN_DATE)
VALUES('2022', 'P102000003', 80, 20, 10, 40,  '2022-09-10');
INSERT INTO REMAIN_TEST(REMAIN_YEAR,REMAIN_PROD,remain_j_00,REMAIN_I,remain_o,REMAIN_J_99,REMAIN_DATE)
VALUES('2023', 'P101000001', 100, 20, 10, 50,  '2023-04-08');

SELECT * FROM REMAIN_TEST;

--위 작성된 트리거를  해당 회원의 마일리지도 변경되는 트리거로 변경하시오 ?   
--단, 마일리지는 (수량  * 상품판매가) 의 1%로 한다.
CREATE or REPLACE TRIGGER tg_mem_mileage_change
      AFTER  insert or update or delete ON cart
      FOR EACH ROW
DECLARE 
       v_qty NUMBER(9);
       v_prod VARCHAR2(10);
       v_member VARCHAR2(15);
       v_prod_sale prod.prod_sale%type;
       v_sum NUMBER := 10;
 BEGIN
    IF INSERTING THEN
        v_qty := NVL(:NEW.cart_qty,0); 
        v_prod := :NEW.cart_prod;        
        v_member := :NEW.cart_member;
    ELSIF UPDATING THEN
         v_qty :=  NVL(:NEW.cart_qty,0) - NVL(:OLD.cart_qty,0);
         v_prod := :NEW.cart_prod;
         v_member := :NEW.cart_member;
    ELSIF DELETING THEN
         v_qty :=  -(NVL(:OLD.cart_qty,0));
         v_prod := :OLD.cart_prod;
         v_member := :OLD.cart_member;
    END IF;
    SELECT prod_sale INTO v_prod_sale from prod  WHERE prod_id = v_prod;
    v_sum := (v_prod_sale * v_qty) * 0.01;

    UPDATE member SET mem_mileage = mem_mileage + v_sum
           WHERE mem_id = v_member;
    DBMS_OUTPUT.PUT_LINE('변경마일리지 ==> ' || v_sum);
EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('예외 발생:' || SQLERRM);     
END;
/

---

--prod_mgr 팩키지의 본문
CREATE OR REPLACE PACKAGE BODY prod_mgr 
IS
CURSOR prod_cur (v_lgu VARCHAR2) IS
     SELECT prod_id, prod_name, to_char(prod_sale,'L999,999,999') prod_sale
     FROM prod
     WHERE prod_lgu = v_lgu; 
     
PROCEDURE prod_list IS     
BEGIN
  IF g_prod_lgu is null Then 
       RAISE exp_no_prod_lgu;  
  END IF;
  FOR prod_rec  IN prod_cur (p_prod_lgu)  LOOP
      DBMS_OUTPUT.PUT_LINE(  prod_rec.prod_id || ', ' || prod_rec.prod_name || ', ' || prod_rec.prod_sale );
  END LOOP;
  EXCEPTION
    WHEN exp_no_prod_lgu THEN
          DBMS_OUTPUT.PUT_LINE ( '상품 분류가 없습니다.'); 
    WHEN  OTHERS  THEN  
         DBMS_OUTPUT.PUT_LINE ( '기타 에러 :' || SQLERRM  ); 
  END prod_list;
END;
/

--prod_mgr 팩키지의 본문(Cont.)
PROCEDURE  prod_list (p_prod_lgu IN prod.prod_lgu%TYPE)
 IS     
BEGIN
  FOR prod_rec  IN prod_cur (p_prod_lgu)  LOOP
      DBMS_OUTPUT.PUT_LINE(  prod_rec.prod_id || ', '
                         || prod_rec.prod_name || ', ' || prod_rec.prod_sale );
  END LOOP;
  EXCEPTION
     WHEN OTHERS THEN  
     DBMS_OUTPUT.PUT_LINE ( '기타 에러 :' || SQLERRM  ); 
  END prod_list;  

  FUNCTION prod_count   
     RETURN NUMBER    
     IS  
       v_cnt NUMBER;
     BEGIN
        SELECT COUNT(*) INTO v_cnt FROM prod WHERE prod_lgu = g_prod_lgu;
        return v_cnt;
  END prod_count;  
END prod_mgr;
/