albert's blog: Oracle/PLSQL: Case Statement

2011/12/22

Oracle/PLSQL: Case Statement

Requirement
到NIGT001與NIGT005，根據前端條件輸入結果產出報表

報表內容包含違章編號、統一編號、議程類別等欄位，其中有幾個欄位是經過if-else判斷是得來的，如作業代號、案況、會次等

AS-IS
現有VB/Cobol在NIG335的作法是：
1. 在VB裡頭到NIGT001撈資料出來
2. 根據找出來的資料往後拋到Cobol，在根據撈出來的值來判斷，以下是Cobol程式片段

EVALUATE WK-PRCD-STUS  
 WHEN "33"  
   IF WK-UPD-JDGF-STUS = "50"  
     MOVE "4"     TO RPT-CASE-STUS(RECORD-CNT)  
     MOVE "50"    TO RPT-PRCD-STUS(RECORD-CNT)  
   ELSE   
     MOVE "1"     TO RPT-CASE-STUS(RECORD-CNT)  
     MOVE "33"    TO RPT-PRCD-STUS(RECORD-CNT)  
   END-IF   
    MOVE WK-PRCD-MK(1:4)  
      TO RPT-MTGNO(RECORD-CNT)  
  WHEN "36"  
    IF WK-UPD-JDGF-STUS = "50"  
      MOVE "5"     TO RPT-CASE-STUS(RECORD-CNT)  
      MOVE "50"    TO RPT-PRCD-STUS(RECORD-CNT)  
    ELSE  
      MOVE "2"     TO RPT-CASE-STUS(RECORD-CNT)  
      MOVE "36"    TO RPT-PRCD-STUS(RECORD-CNT)  
    END-IF   
    MOVE WK-PRCD-MK(1:4)  
      TO RPT-MTGNO(RECORD-CNT)      
  WHEN "50"  
    MOVE "6" TO RPT-CASE-STUS(RECORD-CNT)  
    MOVE "50" TO RPT-PRCD-STUS(RECORD-CNT)  
    MOVE WK-PRCD-MK(8:4)  
      TO RPT-MTGNO(RECORD-CNT)      
  WHEN OTHER  
    MOVE "3" TO RPT-CASE-STUS(RECORD-CNT)  
    MOVE "18" TO RPT-PRCD-STUS(RECORD-CNT)  
    MOVE SPACES     TO RPT-MTGNO(RECORD-CNT)      
END-EVALUATE.

TO-BE
以過去的作法來說，挺沒效率的，他需要每筆資料for-loop出來，在按照條件，給予不同的值。以此段程式來說，可以用Oracle內建的CASE statement，如下：

SELECT....,  
CASE WHEN NIGT005.PRCD_STUS = '33' and NIGT001.UPD_JDGF_STUS = '50' THEN '4'       
   WHEN NIGT005.PRCD_STUS = '33' and NIGT001.UPD_JDGF_STUS <> '50' THEN '1'       
   WHEN NIGT005.PRCD_STUS = '36' and NIGT001.UPD_JDGF_STUS = '50' THEN '5'       
   WHEN NIGT005.PRCD_STUS = '36' and NIGT001.UPD_JDGF_STUS <> '50' THEN '2'       
   WHEN NIGT005.PRCD_STUS = '50' THEN '6'       
   ELSE '3'     END as CASE_STUS,   --案況  
CASE WHEN NIGT005.PRCD_STUS = '33' and NIGT001.UPD_JDGF_STUS = '50' THEN '50'       
   WHEN NIGT005.PRCD_STUS = '33' and NIGT001.UPD_JDGF_STUS <> '50' THEN '33'       
    WHEN NIGT005.PRCD_STUS = '36' and NIGT001.UPD_JDGF_STUS = '50' THEN '50'       
    WHEN NIGT005.PRCD_STUS = '36' and NIGT001.UPD_JDGF_STUS <> '50' THEN '36'       
    WHEN NIGT005.PRCD_STUS = '50' THEN '50'       
    ELSE '18'     END as PRCD_STUS,   --作業代號  
 CASE WHEN NIGT005.PRCD_STUS = '33' THEN SUBSTR(NIGT005.PRCD_MK, 1, 4)       
    WHEN NIGT005.PRCD_STUS = '36' THEN SUBSTR(NIGT005.PRCD_MK, 1, 4)       
    WHEN NIGT005.PRCD_STUS = '50' THEN SUBSTR(NIGT005.PRCD_MK, 8, 4)       
    ELSE ''     END AS MTG_NO --會次  
 FROM NIGT001, NIGT005  
 WHERE ....

Benefits
這樣的作法有幾個好處
1. 將這些if-else statement放到SQL中去處理，降低程式複雜度，也減少處理出錯的風險
2. 將這些if-else statement放到SQL中去處理，會得到最好的處理速度與效能(相較於用Java程式處理)

Oracle/PLSQL: Case Statement - http://www.techonthenet.com/oracle/functions/case.php

albert's blog

Total Pageviews

2011/12/22

Oracle/PLSQL: Case Statement

No comments: