Total Pageviews

2014/03/25

How do I insert a record if it does not exist in Oracle?

Requirement
Here has two report items as following:
Each year has its own report items, ex. 102年度--未滿一年之短期融資, 103年度--未滿一年之短期融資, 102年度--公債及賒借收入, 103年度--公債及賒借收入, etc.

This sort of information had been stored in this table.

Our requirement is to generate the two report items automatically next year.

Solution
We can use merge statement to accomplish this requirement.
Step1: Find record(s) in FMS405FA based on our defined search criteria
Step2: Result
2.1: If does not find any record, then insert data into FMS405FA
2.2: If found record(s), then skip it.
MERGE INTO FMS405FA T1 
USING DUAL ON(T1.ACCYR =
     (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))  FROM DUAL)
      AND T1.RPT_TYPE='FMS434R'
      AND T1.RPT_NO='0001') 
WHEN NOT MATCHED THEN 
  INSERT(T1.ACCYR, T1.RPT_TYPE, T1.RPT_NO, T1.RPT_NM)
  VALUES(
         (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))
          FROM DUAL), 'FMS434R', '0001',

         (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))
          FROM DUAL)||'年度--未滿一年之短期融資')


MERGE INTO FMS405FA T1 
USING DUAL 
ON(T1.ACCYR =
   (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000')) FROM DUAL)
   AND T1.RPT_TYPE='FMS434R'
   AND T1.RPT_NO='0002') 
WHEN NOT MATCHED THEN 
  INSERT(T1.ACCYR, T1.RPT_TYPE, T1.RPT_NO, T1.RPT_NM)
  VALUES(
         (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))
          FROM DUAL), 'FMS434R', '0002',

         (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))
          FROM DUAL)||'年度--公債及賒借收入')

Reference
[1] http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

No comments: