oracle MERGE INTO

怼烎@ 2023-09-25 23:37 135阅读 0赞

mapper 接口：  
void insertGenerateData5521(List list);  
xml-sql:  
<update id=“insertGenerateData5521” parameterType=“java.util.List”»  
MERGE INTO tb ci 5521 a using (  
¿foreach collection-“list” index-“index” item-“item” open-“” close-“” separ  
select  
\#\{item- JLXH,jdbCT yPe=DECIMAL) AS JLXH,f\{itemZSDM, jdbCType=VARCHARI AS ZSD4,f(item- ZQDM, jdbCT yPe=VARCHARJ AS ZRDM,\#\{ite- ZQJC, jabCI ype=VARCHAR) AS ZQJC.  
decode (\# (item. SSDD, jdbcIype=VARCHAR),  
深圳•口，‘上海”1，•香港，,2) 25 5SDD，  
\#tite\_NELX,jdbCIyPe=VA RCHAR\} AS WELXftite- JSBZ, I abCIyPE=DECIMAL) AS ISRZ  
\#(item.CZIX, jdbcType-VARCHAR) AS CZIX,  
(item LIGQS, jabcIype-DECIMAL\] AS LIGQ5,  
(item. QSSJ, jdbcIype=DECIMAL) AS QSSJ, ‡(item. XGTJ, jdbcType-VARCHAR) AS XGIJ,  
(item. CZFW, jdbcIype=VARCHAR) AS CZFW,  
(item YWMC, jdbcIype=VARCHAR\} AS YWMC,  
\#(item.RIQI, jdbcType=DATE) AS RIQI,  
(item.IZYZ, jdbeType=DECIMAL\} AS IZYZ,  
‡(item\_WHLB, jdbcT ype=VARCHARI AS WHLB, \#(item OB\_ROLE\_ ID, jdbcIype=DECIMAL\\ AS OB\_ROLE\_ID,  
(item. DRBZ, jdbcIype-VARCHAR) AS DRBZ,  
(item MARKETMARK, jdbcIype-VARCHAR\] AS MARKEIMARK,

E1item OE\_ ROLE NANE S CbCI VPe VARCHAR AS OP\_ROLE\_ NANE.  
ftitem-EZ, jabCI YPe-VARCHARI AS EZ  
from dual  
I  
) b  
on to  
char (a\_RIQI, vyyy-mm-dd’) = to\_char (b.RIQI, ‘yyyy-man-dd’) and  
a\_OB\_ROLE\_ID = b\_OB ROLE\_ID and  
a.DRBZ = b. DRBZ and  
a\_CZIX = b. CZLX and  
a\_ZSDM = b\_ZSDM and  
a\_ZQDM =b\_ZQDM  
)  
when matched then update set  
a. SSDD = b. SSDD,  
E-WELX - b-WELX,  
a.JsB2 = b.JsBz,  
a\_LIGQS = b. LIGQS,  
a.QsSJ = b-QSSJ,  
a.XGIJ = b.XGIJ,  
a. CZEW = b. CZFW,  
a. YWMC = b. YWMC,  
a.TZYZ = b.IZYZ,  
a .MARKETMARK = b. MARKETMARK,  
a.ob role name = b.ob  
role name,  
a\_BZ = b.BZ  
when not matched then insert  
(E-JLXE, =-ZSDM. =-ZQDM, a- ZQJC,a-SSDD, E-WELx, e- JSBZ,a-C2Ix, a-LIGQS.a-QsSJ, a-xGTJ,a-CzET  
values  
(SEQLCI\_ID-NEXIVAL,D-2SDM, 5-29D4,5-29JC,b-SSDD.b-MELx,5-JSB2,b-C2Ix,D-LIGQS,b-QSSJ,b.