8/22 KK 영업매출현황 패키지(KK_COLLECTION_PKG) 제작

-- PACKAGE 선언부(Header) (Interface)
CREATE OR REPLACE PACKAGE KK_COLLECTION_PKG AS
    g_in_sawonid VARCHAR2(4) := 'S003';         -- 손예진 (임의 입력 사원 지정), 글로벌함수
    g_prod_cnt    NUMBER(9) := 0;
    
    --행동강령
    -- 1. 당월기초 입고 수량을 생성한다.
    PROCEDURE KK_COLLECTION_PRC1(p_sum_yymm IN VARCHAR2);
    
    --2. 일자별 거래처 제품별 판매현황(SMCP10)정보를 생성 PGM    
    PROCEDURE KK_COLLECTION_PRC2(p_sum_yymm IN VARCHAR2);
    
     --3. 일자별 제품별 판매현황(SMProd10)정보를 생성 PGM                        
    PROCEDURE KK_COLLECTION_PRC3(p_sum_yymm IN VARCHAR2);    
    
    --4. 전체수불 처리 시 전체 Program 조율하는 Main Procedure
    PROCEDURE KK_COLLECTION_MAIN(p_sum_yymm in VARCHAR2);
    
    --5. 전체수불 마감 후 MMSUMM30을 SMSALE에 따라 STCK_QTY 차감 및 마감 처리작업
    PROCEDURE KK_COLLECTION_END(p_sum_yymm in VARCHAR2);

END KK_COLLECTION_PKG;


-- PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY KK_COLLECTION_PKG  AS
-- 5. 전체수불 처리시 전체 PGM 조율 Main Procedure
    PROCEDURE   KK_COLLECTION_MAIN(  p_sum_yymm in	VARCHAR2)

   IS  
    
   BEGIN
        DELETE   MMSUM30
        WHERE    SUM_YYMM = p_sum_yymm;
        -- 일자별 거래처 제품별 판매현황(smcp10)정보 해당월 삭제
        DELETE   SMCP10
        WHERE    SUBSTR(YYMMDD,1,6) = p_sum_yymm;
        --일자별 제품별 판매현황(SMProd10)정보 해당월 삭제
        DELETE   SMProd10
        WHERE    SUBSTR(YYMMDD,1,6) = p_sum_yymm;
        --일자별 판매실적 오류현황(smsale_err)정보 해당월 삭제
        DELETE   SMSALE_ERR
        WHERE    YYMM = p_sum_yymm;
        
         DBMS_OUTPUT.ENABLE; 
         DBMS_OUTPUT.PUT_LINE(' KK_COLLECTION_MAIN  p_sum_yymm => ' ||p_sum_yymm );
      
         -- 1. 당월기초 입고 수량을 생성한다.
         KK_COLLECTION_PRC1(p_sum_yymm);
    
         --2. 일자별 거래처 제품별 판매현황(SMCP10)정보를 생성 PGM    
         -- 파라미터=>파라미터 는 왼쪽의 파라미터를 오른쪽의 파라미터로 값을 넣는다는 뜻
         --(p_sum_yymm 만 쓰는 것과 기능은 같음)
         KK_COLLECTION_PRC2(p_sum_yymm => p_sum_yymm);
        
         --3. 일자별 제품별 판매현황(SMProd10)정보를 생성 PGM                        
         KK_COLLECTION_PRC3(p_sum_yymm => p_sum_yymm);       
        
         --5. 전체수불 마감 후 MMSUMM30을 SMSALE에 따라 STCK_QTY 차감 및 마감 처리작업
         KK_COLLECTION_END(p_sum_yymm => p_sum_yymm);                       
    
    END KK_COLLECTION_MAIN;
    
    
    
     /**************************************************************************************
    Procedure Name : KK_COLLECTION_PRC1
    Description    : 당월기초 입고 수량을 생성한다.
    *************************************************************************************/   
    PROCEDURE KK_COLLECTION_PRC1(p_sum_yymm in VARCHAR2)
    IS
    
    BEGIN
        DBMS_OUTPUT.ENABLE;
        DBMS_OUTPUT.PUT_LINE('KK_COLLECTION_PRC1 p_sum_yymm=> ' || p_sum_yymm);
        -- 1) 당월 기초 입고 수량을 생성한다.
        -- 전 월 말(기말)의 재고를 이번 달 초(기초)의 재고로 가져온다.
        INSERT INTO mmsum30
            ( sum_yymm
            , item_code
            , item_gubn     --0이면 기초, 1이면 기말 재고를 뜻함. 
            , stck_qty
            , sawonID
            , RegiDate
            )
        ( SELECT p_sum_yymm
                        , item_code
                        , '0'   -- 기초
                        , stck_qty
                        , SawonID
                        , SYSDATE
            FROM mmsum30
            -- yyyymm(년월) string을 to_date로 하면  date형식으로 바뀐 후 일은 1일로 잡아준다. (*데이터 컨버팅)
            --그리고 ADD_MONTHS로 월에 1을 빼준다.
            --그리고 sum_yymm 형식이 varchar이기 때문에 다시 varchar 형식으로 바꾼다.
            -- *mmsum30테이블의 sum_yymm칼럼은 데이터는 날짜이지만 형식은 varchar이다. 
            -- 그 이유는 sum_yymm이 pk인데, 
            -- pk를 날짜형식으로 지정하게 되면 데이터에 시간데이터까지 들어가서 문제가 생길 수 있기 때문이다.
            WHERE sum_yymm = TO_CHAR(ADD_MONTHS ( TO_DATE(p_sum_yymm, 'YYYYMM'), -1), 'YYYYMM')
            AND item_gubn = '1' --기말
            );
    END KK_COLLECTION_PRC1;

     PROCEDURE KK_COLLECTION_PRC2(p_sum_yymm in VARCHAR2)
        /**************************************************************************************
       Project        : KK 영업매출현황
       Module         : 수불관리
       Procedure Name : KK_COLLECTION_PRC2 
       Description    : 일자별 거래처 제품별 판매현황(SMCP10)정보를 생성한다.
                       - 일별 판매실적 현황(SMSALE)을 읽어 일자별 거래처 제품별 판매현황(SMCP10)정보를 생성
                       - 일별 판매실적 현황, 제품(Product) 테이블 JOIN
                       - 사원은 global 변수인 g_in_sawonid 으로 입력
       Program History
       --------------------------------------------------------------------------
       Date       In Charge        Version   Description
       --------------------------------------------------------------------------
       2022.02.23 강태광            1.0      최초작성
      *************************************************************************************/
    IS
        --일별 판매실적 현황(SMSALE)을 읽음
        CURSOR csr_smsale IS
            SELECT s.yymmdd yymmdd
                        ,  s.CustomID CustomID
                        , s.item_code item_code
                        , s.stck_qty stck_qty
                        , p.danga danga
            FROM smsale s, product p    --일별 판매실적 현황, 제품(product 테이블)
            WHERE s.item_code = p.item_code
            AND substr(s.yymmdd, 1, 6) = p_sum_yymm;     --그 달의 년, 월까지이므로 6바이트를 가져옴
            
    -- smcp 10 완성
    BEGIN
         DBMS_OUTPUT.ENABLE;
        FOR rec_smsale IN csr_smsale LOOP
            INSERT INTO smcp10
                    (yymmdd
                    , CustomID
                    , item_code
                    , stck_qty
                    , danga
                    , sawonid
                    , regidate)
                VALUES (
                     rec_smsale.yymmdd
                    , rec_smsale.customid
                    , rec_smsale.item_code
                    , rec_smsale.stck_qty
                    , rec_smsale.danga
                    , g_in_sawonid   --Default (손예진)
                    , SYSDATE);
        END LOOP;
        
        EXCEPTION
        WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE(SQLERRM || '에러 발생');
    END KK_COLLECTION_PRC2;


     PROCEDURE KK_COLLECTION_PRC3(p_sum_yymm in VARCHAR2)
/**************************************************************************************************
   Project        : KK 영업매출현황
   Module         : 수불관리
   Procedure Name : KK_COLLECTION_PRC3 
   Description    :  일자별 제품별 판매현황(SMProd10)정보를 생성한다.
                   - 일별 판매실적 현황(SMSALE)을 읽어 일자별 제품별 판매현황(SMProd10)정보를 생성
                   - STCK_QTY -> SUM (수량), Danga -> AVG (단가는 2개 이상 발생 가능)
                   - CURSOR, FOR문 사용
                 
   Program History
   --------------------------------------------------------------------------
   Date       In Charge        Version   Description
   --------------------------------------------------------------------------
   2022.02.23 강태광            1.0      최초작성
  ************************************************************************************************/

     IS
        CURSOR cur_smsale IS
        SELECT s.yymmdd yymmdd
                        , s.item_code item_code
                        , SUM(s.stck_qty) stck_qty
                        , AVG(p.danga) danga

        FROM smsale s, product p
        WHERE s.item_code = p.item_code
        AND substr(s.yymmdd, 1, 6) = p_sum_yymm
        GROUP BY s.yymmdd, s.item_code;     
        
     BEGIN
     DBMS_OUTPUT.ENABLE;
     FOR rec_smsale IN cur_smsale LOOP
     -- 초기값 있으면 여기에 작성
            INSERT INTO SMProd10
                ( yymmdd
                , item_code
                , stck_qty
                , danga
                , sawonid
                , regidate )
            VALUES (
            rec_smsale.yymmdd,
            rec_smsale.item_code,
            rec_smsale.stck_qty,
            rec_smsale.danga,
            g_in_sawonid,
            SYSDATE);
        END LOOP;
        
        EXCEPTION
        WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE(SQLERRM || '에러 발생');
    END KK_COLLECTION_PRC3;
    
    
    PROCEDURE KK_COLLECTION_END(p_sum_yymm in VARCHAR2)
/****************************************************************************************
   Project        : KK 영업매출현황
   Module         : 수불관리
   Procedure Name : KK_COLLECTION_END 
   Description    : 전체 수불마감후 MMSUMM30을 SMSALE에 따라 STCK_QTY 
                        차감 및 마감 처리작업   
    1.  만약 창고 기초재고(mmsum30 테이블)가 판매량보다 크다면(정상이라면) 기말재고 입력       
    (판매량을 빼고 item_gybn기말재고를 1로 바꾸고)
    2.  만약 창고 기초재고가 판매량보다 작다면 (기초재고가 잘못된 것이므로) SMSALE_ERR 테이블 입력
    3. CURSOR명 CSR_Store_Remain
   Program History
   --------------------------------------------------------------------------
   Date       In Charge        Version   Description
   --------------------------------------------------------------------------
   2022.02.23 강태광            1.0      최초작성
  ****************************************************************************************/
    IS
    -- mmsum30을 smsale에 대하여 yymmdd, item_code별 수량 합계 차감
   CURSOR   CSR_Store_Remain  IS
            SELECT   SUBSTR(S.YYMMDD,1,6)               YYMM
                         , S.ITEM_CODE                              ITEM_CODE
                         , SUM(S.STCK_QTY)                      S_STCK_QTY  
                         , AVG(M.STCK_QTY)                      M_STCK_QTY 
            FROM    ( SELECT * FROM SMSALE
                            WHERE  SUBSTR(YYMMDD,1,6) = p_sum_yymm
                         )   S ,   -- 월별 판매실적현황
                         ( SELECT * FROM MMSUM30
                           WHERE    SUM_YYMM  = p_sum_yymm
                           AND          ITEM_GUBN = '0'    -- 기초 재고에 한해   
                        )  M      -- 창고 재고(MMSUM30)테이블
            WHERE     S.ITEM_CODE  = M.ITEM_CODE 
            GROUP BY  SUBSTR(S.YYMMDD,1,6) , S.ITEM_CODE;     
        
   BEGIN
        DBMS_OUTPUT.ENABLE;
      FOR 	rec_Store_Remain  IN CSR_Store_Remain  LOOP
        ------------------------------------------------------------------
        --    만약 창고 기초재고가 판매량보다 크다면 기말재고 입력 
        ------------------------------------------------------------------
        IF  rec_Store_Remain.M_STCK_QTY >  rec_Store_Remain.S_STCK_QTY  THEN  
            INSERT INTO MMSUM30       
                (    SUM_YYMM        
                 ,   ITEM_CODE       
                 ,   ITEM_GUBN
                 ,   STCK_QTY
                 ,   SawonID
                 ,   RegiDate
                 )
           VALUES(  rec_Store_Remain.YYMM 
                        , rec_Store_Remain.ITEM_CODE   
                        , '1'                              -- 기말재고    
                        , rec_Store_Remain.M_STCK_QTY - rec_Store_Remain.S_STCK_QTY   
                        , g_in_sawonid                               
                        ,  SYSDATE
           );
        ELSE   -- rec_Store_Remain.M_STCK_QTY <  rec_Store_Remain.S_STCK_QTY 
             INSERT INTO SMSALE_ERR       
                  (    YYMM        
                   ,   ITEM_CODE       
                   ,   MMSUM30_QTY
                   ,   SMSALE_QTY
                   ,   SawonID
                   ,   RegiDate
                   )
             VALUES(    rec_Store_Remain.YYMM 
                            , rec_Store_Remain.ITEM_CODE   
                            , rec_Store_Remain.M_STCK_QTY   -- 기초재고    
                            , rec_Store_Remain.S_STCK_QTY   -- 판매수량
                            , g_in_sawonid                               
                            , SYSDATE
             );

             g_prod_cnt  :=   rec_Store_Remain.M_STCK_QTY - rec_Store_Remain.S_STCK_QTY;
             dbms_output.put_line(rec_Store_Remain.YYMM||' 년월에 ' || rec_Store_Remain.ITEM_CODE  ||
               ' 재고부족 양=>' || g_prod_cnt  );
             dbms_output.put_line('재고부족 양=>'||  g_prod_cnt);
	      END IF;
       
     END LOOP;
     EXCEPTION
        WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');     
              
   END  KK_COLLECTION_END;
        

END KK_COLLECTION_PKG;

 

[KK_COLLECTION_PRC2 실행]

실행 결과 smcp에 추가된 데이터

 

 

[KK_COLLECTION_PRC3 실행]

smsale 테이블에서 20220212의 B002 합이  150+700이라서

smprod10 테이블의 결과가 850임을 확인할 수 있다.

 

[procedure_end 실행]

smsale에서

2022년 2월에 B001은 200, 100개가 팔림

그렇기 때문에 mmsum30의 B001에 9700가 나옴

 

원래 mmsum30 테이블 모습

실행

변경 후 테이블 모습

 

 

 

[패키지 실행 예약하는 방법, KK_COLLECTION_MAIN 실행]

- KK_COLLECTION_MAIN 을 만들었기 때문에 이 프로시저를 통해 값을 수작업으로 입력하지 않아도 된다. 이렇게 값을 입력하는 것도 프로그램화 해야 한다.

- 이 MAIN 프로시저를 통해 한꺼번에 프로그램을 돌릴 수 있어서 이것이 패키지를 사용하는 이유이다.

- 스케줄러 우클릭 > 새작업 에서 돌릴 프로그램을 시간예약할 수 있다.

보통 작업하지 않는 새벽에 자동으로 프로그램을 돌린다. (컴퓨터가 켜져있는 동안 해당. 회사에서는 서버를 24시간 돌리기 때문에 상관이 없다.)

smsale 값에 10500으로 10000이상 설정 하고 실행시키면

 

어느 사원이 부정행위로 판매내역을 입력했는 지 smsale_err 테이블에서 확인할 수 있다.

 

 

지금까지 배운 조인(하단)이 시스템적(DBMS)적으로 내부에서 실행될 때는 nested loop, sort-merge, hash 조인으로 변한다. 성능을 더 좋게 하기 위해선 이런 것도 알아두면 좋다. *면접에 나오지는 않음.