Total Pageviews

2015/01/08

How to do Conditional Control in Stored Procedure

There are three input parameters for this stored procedure
  • INPUT_YEAR: ex. 103 (ROC era system)
  • INPUT_QUARTER: ex. 4 means quarter 4
  • INPUT_USER_ID: login user id
This procedure will execute different part based on different quarter.
We can use IF-THEN-ELSIF to fulfill this requirement.

 The syntax for IF-THEN-ELSIF is :
IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}

ELSIF condition2 THEN
   {...statements to execute when condition2 is TRUE...}

ELSE
   {...statements to execute when both condition1 and condition2 are FALSE...}

END IF;

 Example:
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
create or replace PROCEDURE PROC_FMS406R_TAB6_FCST_VALUES 
(
  INPUT_YEAR IN VARCHAR2 
, INPUT_QUARTER IN VARCHAR2 
, INPUT_USER_ID IN VARCHAR2 
) AS 
BEGIN
  NULL;
  IF INPUT_QUARTER = '1' THEN
    dbms_output.put_line('Q1');
    --1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
    --1.2.9.1 當前端選擇第一季(本年度1~3月都是預估數)
    MERGE INTO FMS406FI T1
    USING
    (
      SELECT YYY_MM, ALC1, ALC2, ALC3
      FROM
      (
        SELECT *
        FROM FMS406FG
        WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'03')
        ORDER BY YYY_MM DESC
      )
      WHERE ROWNUM = 1
    ) T2
    ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
    WHEN MATCHED THEN UPDATE 
    SET T1.AMT1 = T2.ALC1, T1.AMT2 = T2.ALC2, T1.AMT3 = T2.ALC3
    ;
    dbms_output.put_line('execute 1.2.9 撈出本年度第一季收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
   
  ELSIF  INPUT_QUARTER = '2' THEN
    dbms_output.put_line('Q2');
    --1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
    --1.2.9.2 當前端選擇第二季 (本年度4~6月都是預估數)
    MERGE INTO FMS406FI T1
    USING
    (
      SELECT YYY_MM, ALC4, ALC5, ALC6
      FROM
      (
        SELECT *
        FROM FMS406FG
        WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'06')
        ORDER BY YYY_MM DESC
      )
      WHERE ROWNUM = 1
    ) T2
    ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
    WHEN MATCHED THEN UPDATE 
    SET T1.AMT4 = T2.ALC4, T1.AMT5 = T2.ALC5, T1.AMT6 = T2.ALC6
    ;
    dbms_output.put_line('execute 1.2.9 撈出本年度第二季收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
        
  ELSIF  INPUT_QUARTER = '3' THEN
    dbms_output.put_line('Q3');
    --1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
    --1.2.9.3 當前端選擇第三季 (本年度7~9月都是預估數)
    MERGE INTO FMS406FI T1
    USING
    (
      SELECT YYY_MM, ALC7, ALC8, ALC9
      FROM
      (
        SELECT *
        FROM FMS406FG
        WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'09')
        ORDER BY YYY_MM DESC
      )
      WHERE ROWNUM = 1
    ) T2
    ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
    WHEN MATCHED THEN UPDATE 
    SET T1.AMT7 = T2.ALC7, T1.AMT8 = T2.ALC8, T1.AMT9 = T2.ALC9
    ;
    dbms_output.put_line('execute 1.2.9 撈出本年度第三季收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
        
  ELSIF  INPUT_QUARTER = '4' THEN
    dbms_output.put_line('Q4');
    --1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
    --1.2.9.4 當前端選擇第四季 (本年度10~12月都是預估數)
    MERGE INTO FMS406FI T1
    USING
    (
      SELECT YYY_MM, ALC10, ALC11, ALC12
      FROM
      (
        SELECT *
        FROM FMS406FG
        WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'12')
        ORDER BY YYY_MM DESC
        )
        WHERE ROWNUM = 1
    ) T2
    ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
    WHEN MATCHED THEN UPDATE 
    SET T1.AMT10 = T2.ALC10, T1.AMT11 = T2.ALC11, T1.AMT12 = T2.ALC12
    ;
    dbms_output.put_line('execute 1.2.9 撈出本年度第四季 收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
    
  END IF;
  
END PROC_FMS406R_TAB6_FCST_VALUES;

Assume I assign these values into three parameters:
  • INPUT_YEAR: 103
  • INPUT_QUARTER: 4
  • INPUT_USER_ID: ALBERT
The stored procedure will execute this section
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  ELSIF  INPUT_QUARTER = '4' THEN
    dbms_output.put_line('Q4');
    --1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
    --1.2.9.4 當前端選擇第四季 (本年度10~12月都是預估數)
    MERGE INTO FMS406FI T1
    USING
    (
      SELECT YYY_MM, ALC10, ALC11, ALC12
      FROM
      (
        SELECT *
        FROM FMS406FG
        WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'12')
        ORDER BY YYY_MM DESC
        )
        WHERE ROWNUM = 1
    ) T2
    ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
    WHEN MATCHED THEN UPDATE 
    SET T1.AMT10 = T2.ALC10, T1.AMT11 = T2.ALC11, T1.AMT12 = T2.ALC12
    ;
    dbms_output.put_line('execute 1.2.9 撈出本年度第四季 收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
    
  END IF;


Execution result:
1
2
Q4
execute 1.2.9 撈出本年度第四季 收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表



Reference
[1] http://www.techonthenet.com/oracle/loops/if_then.php
[2] http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/controlstatements.htm#LNPLS386
Posted by Albert Kuo at 4:25 PM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)