Net Dapper 基于 Oracle Merge 覆盖数据

SQL

MERGE INTO TableName TARGET 
USING (
SELECT :USERID0 AS USERID, :DISPLAY_NAME0 AS DISPLAY_NAME, :EMAIL0 AS EMAIL, :ROWUID0 AS ROWUID, :ROWSTATE0 AS ROWSTATE, :CTIME0 AS CTIME, :MTIME0 AS MTIME, :MODIFIER0 AS MODIFIER FROM DUAL
UNION
SELECT :USERID1 AS USERID, :DISPLAY_NAME1 AS DISPLAY_NAME, :EMAIL1 AS EMAIL, :ROWUID1 AS ROWUID, :ROWSTATE1 AS ROWSTATE, :CTIME1 AS CTIME, :MTIME1 AS MTIME, :MODIFIER1 AS MODIFIER FROM DUAL
UNION
SELECT :USERID2 AS USERID, :DISPLAY_NAME2 AS DISPLAY_NAME, :EMAIL2 AS EMAIL, :ROWUID2 AS ROWUID, :ROWSTATE2 AS ROWSTATE, :CTIME2 AS CTIME, :MTIME2 AS MTIME, :MODIFIER2 AS MODIFIER FROM DUAL
UNION
SELECT :USERID3 AS USERID, :DISPLAY_NAME3 AS DISPLAY_NAME, :EMAIL3 AS EMAIL, :ROWUID3 AS ROWUID, :ROWSTATE3 AS ROWSTATE, :CTIME3 AS CTIME, :MTIME3 AS MTIME, :MODIFIER3 AS MODIFIER FROM DUAL
UNION
SELECT :USERID4 AS USERID, :DISPLAY_NAME4 AS DISPLAY_NAME, :EMAIL4 AS EMAIL, :ROWUID4 AS ROWUID, :ROWSTATE4 AS ROWSTATE, :CTIME4 AS CTIME, :MTIME4 AS MTIME, :MODIFIER4 AS MODIFIER FROM DUAL
) SOURCE
ON (SOURCE.USERID = TARGET.USERID)
WHEN MATCHED THEN
    UPDATE SET TARGET.MTIME = SOURCE.MTIME, TARGET.MODIFIER = SOURCE.MODIFIER, TARGET.DISPLAY_NAME = SOURCE.DISPLAY_NAME, TARGET.EMAIL = SOURCE.EMAIL
WHEN NOT MATCHED THEN
    INSERT (USERID, DISPLAY_NAME, EMAIL, ROWUID, ROWSTATE, CTIME, MTIME, MODIFIER)
    VALUES (SOURCE.USERID, SOURCE.DISPLAY_NAME, SOURCE.EMAIL, SOURCE.ROWUID, SOURCE.ROWSTATE, SOURCE.CTIME, SOURCE.MTIME, SOURCE.MODIFIER)

 

Reference:

https://rogertroller.com/2017/12/29/merge-delete/