Oracle中存在更新、不存在插入操作实现

         这里只讲一种写法，merge的用法，核心格式如下

MERGE INTO table_name alias1   
USING (table|view|sub_query) alias2  
ON (join condition)   
WHEN MATCHED THEN   
    UPDATE table_name SET col1 = col_val1
WHEN NOT MATCHED THEN   
    INSERT (column_list) VALUES (column_values);

<insert id="saveOrUpdateReportIndexList">
        MERGE INTO t_report_index T1
        USING (
        <foreach collection="list" index="index" item="item" separator="union all">
            select #{item.cIndexCode} as cIndexCode,
            #{item.cIndexName} as cIndexName,
            #{item.cComputerSql, jdbcType = CLOB} as cComputerSql,
            #{item.nOrder} as nOrder,
            #{item.cComputerType} as cComputerType,
            #{item.cOtherName} as cOtherName,
            #{item.nIsEffect} as nIsEffect,
            #{item.isCirc} as isCirc,
            #{item.decimalsDigits} as decimalsDigits,
            #{item.cDateType} as cDateType,
            #{item.cDataType} as cDataType,
            #{item.isGrading} as isGrading,
            #{item.isSort} as isSort,
            #{item.nReportType} as nReportType,
            #{item.cUpdater} as cUpdater,
            to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') as cUpdatetime
            from dual
        </foreach>
        ) T2 ON (T1.c_Index_Code = T2.cIndexCode and
        T1.n_Is_Effect = T2.nIsEffect and
        T1.c_Data_Type = T2.cDataType)
        WHEN MATCHED THEN
        UPDATE
        SET T1.c_Index_Name = T2.cIndexName,
        T1.c_Computer_Sql = T2.cComputerSql,
        T1.n_Order = T2.nOrder,
        T1.c_Computer_Type = T2.cComputerType,
        T1.c_Other_Name = T2.cOtherName,
        T1.is_Circ = T2.isCirc,
        T1.decimals_Digits = T2.decimalsDigits,
        T1.c_Date_Type = T2.cDateType,
        T1.is_Grading = T2.isGrading,
        T1.is_Sort = T2.isSort,
        T1.n_Report_Type = T2.nReportType,
        T1.c_Updater = T2.cUpdater,
        T1.c_Updatetime = to_char(sysdate,'yyyy-MM-dd HH:mm:ss')
        WHEN NOT MATCHED THEN
        INSERT
        (T1.id,
        T1.c_Index_Code,
        T1.n_Is_Effect,
        T1.c_Data_Type,
        T1.c_Index_Name,
        T1.c_Computer_Sql,
        T1.n_Order,
        T1.c_Computer_Type,
        T1.c_Other_Name,
        T1.is_Circ,
        T1.decimals_Digits,
        T1.c_Date_Type,
        T1.is_Grading,
        T1.is_Sort,
        T1.n_Report_Type,
        T1.c_Updater,
        T1.c_Updatetime)
        VALUES
        (seq_t_report_index.nextval,
        T2.cIndexCode,
        T2.nIsEffect,
        T2.cDataType,
        T2.cIndexName,
        T2.cComputerSql,
        T2.nOrder,
        T2.cComputerType,
        T2.cOtherName,
        T2.isCirc,
        T2.decimalsDigits,
        T2.cDateType,
        T2.isGrading,
        T2.isSort,
        T2.nReportType,
        T2.cUpdater,
        to_char(sysdate,'yyyy-MM-dd HH:mm:ss'))
    </insert>