十分瀑布
十分靜安吊橋
十分老街
台北孔廟
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;
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; |
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; |
1 2 | Q4 execute 1.2.9 撈出本年度第四季 收入數之預估數,寫入﹝FMS406FI﹞國庫現金收支估測分析表 |
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 | package gov.nta.dbm.enumeration; // TODO: Auto-generated Javadoc /** * The Enum DebtTypeEnum. */ public enum DebtTypeEnum { /** The a. */ A("A", "公債"), /** The b. */ B("B", "國庫券"), /** The c. */ C("C", "中長借"), /** The d. */ D("D", "短借"); private String code; private String name; private DebtTypeEnum(String code, String name) { this.code = code; this.name = name; } /** * Gets the code. * * @return the code */ public String getCode() { return code; } /** * Gets the name. * * @return the name */ public String getName() { return name; } } |
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 | package gov.nta.dbm.enumeration; // TODO: Auto-generated Javadoc /** * The Enum DebtTypeEnum. */ public enum DebtTypeEnum { /** The a. */ A("A", "公債"), /** The b. */ B("B", ""), /** The c. */ C("C", ""), /** The d. */ D("D", ""); private String code; private String name; private DebtTypeEnum(String code, String name) { this.code = code; this.name = name; } /** * Gets the code. * * @return the code */ public String getCode() { return code; } /** * Gets the name. * * @return the name */ public String getName() { return name; } } |
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 | //create a value object and add into list1 UnpaymentDataVo vo1 = new UnpaymentDataVo(); vo1.setDebtType("B"); vo1.setBudgetCode(new BigDecimal(1)); vo1.setDebitName("總預算"); vo1.setDamt(new BigDecimal(5156340000000L)); vo1.setCamt(new BigDecimal(3489209150690L)); vo1.setIamt(new BigDecimal(69360745739L)); vo1.setRamt(new BigDecimal(1597620103571L)); List<UnpaymentDataVo> list1 = new ArrayList<UnpaymentDataVo>(); list1.add(vo1); //create 2 value objects and add into list2 UnpaymentDataVo vo2 = new UnpaymentDataVo(); vo2.setDebtType("A"); vo2.setBudgetCode(new BigDecimal(2)); vo2.setDebitName("易淹水地區水患治理計劃第2期特別預算"); vo2.setDamt(new BigDecimal(7500000000L)); vo2.setCamt(new BigDecimal(0)); vo2.setIamt(new BigDecimal(637500000L)); vo2.setRamt(new BigDecimal(7500000000L)); UnpaymentDataVo vo3 = new UnpaymentDataVo(); vo3.setDebtType("A"); vo3.setBudgetCode(new BigDecimal(1)); vo3.setDebitName("振興經濟擴大公共建設特別預算"); vo3.setDamt(new BigDecimal(204400000000L)); vo3.setCamt(new BigDecimal(0)); vo3.setIamt(new BigDecimal(14465750000L)); vo3.setRamt(new BigDecimal(204400000000L)); List<UnpaymentDataVo> list2 = new ArrayList<UnpaymentDataVo>(); list2.add(vo2); list2.add(vo3); //print list1 and list2 log.debug("list1=" + list1.toString()); log.debug("list2=" + list2.toString()); |
1 2 3 | - list1=[UnpaymentDataVo(debtType=B, budgetCode=1, debitName=總預算, damt=5156340000000, camt=3489209150690, iamt=69360745739, ramt=1597620103571)] - list2=[UnpaymentDataVo(debtType=A, budgetCode=2, debitName=易淹水地區水患治理計劃第2期特別預算, damt=7500000000, camt=0, iamt=637500000, ramt=7500000000), UnpaymentDataVo(debtType=A, budgetCode=1, debitName=振興經濟擴大公共建設特別預算, damt=204400000000, camt=0, iamt=14465750000, ramt=204400000000)] |
1 2 3 4 5 6 7 8 9 10 11 12 | // find out the data from list2 which condition is debt_type='A' and budget_code=2 List<UnpaymentDataVo> searchResult = new ArrayList<UnpaymentDataVo>(); CollectionUtils.select(list2, new Predicate() { @Override public boolean evaluate(Object object) { UnpaymentDataVo vo = (UnpaymentDataVo) object; return "A".equals(vo.getDebtType()) && vo.getBudgetCode().compareTo(new BigDecimal(2)) == 0; } }, searchResult); log.debug("searchResult = " + searchResult.toString()); |
1 2 | - searchResult = [UnpaymentDataVo(debtType=A, budgetCode=2, debitName=易淹水地區水患治理計劃第2期特別預算, damt=7500000000, camt=0, iamt=637500000, ramt=7500000000), ] |
1 2 3 4 | // Returns a Collection containing the union of the given Collections List<UnpaymentDataVo> unionResult = (List<UnpaymentDataVo>) CollectionUtils.union(list1, list2); log.debug("unionResult = " + unionResult.toString()); |
1 2 3 | - unionResult = [UnpaymentDataVo(debtType=A, budgetCode=2, debitName=易淹水地區水患治理計劃第2期特別預算, damt=7500000000, camt=0, iamt=637500000, ramt=7500000000), UnpaymentDataVo(debtType=A, budgetCode=1, debitName=振興經濟擴大公共建設特別預算, damt=204400000000, camt=0, iamt=14465750000, ramt=204400000000), UnpaymentDataVo(debtType=B, budgetCode=1, debitName=總預算, damt=5156340000000, camt=3489209150690, iamt=69360745739, ramt=1597620103571)] |
1 2 3 4 | // Returns a new Collection containing a - b. List<UnpaymentDataVo> subtractResult = (List<UnpaymentDataVo>) CollectionUtils.subtract( unionResult, list2); log.debug("subtractResult = " + subtractResult.toString()); |
1 | - subtractResult = [UnpaymentDataVo(debtType=B, budgetCode=1, debitName=總預算, damt=5156340000000, camt=3489209150690, iamt=69360745739, ramt=1597620103571)] |