Oracle 19中基于EnrollmentTransaction同步EnrollmentDetail的单SQL实现

现有数据库表结构及数据 #

1. EnrollmentTransaction表 #

主键为EnrollmentId与TransactionId，数据如下：

EnrollmentId | TransactionId
-------------+--------------
5            | 1
5            | 2
6            | 3
7            | 2
7            | 3
8            | 3
8            | 2
8            | 1
9            | 1

2. EnrollmentDetail表 #

新增TransactionId列（默认值为1且非空），主键为EnrollmentId、ParameterId、SVCId、TransactionId，数据如下：

EnrollmentId | ParameterId| SVCId| SVCValueId| TransactionId 
-------------+------------+------+-----------+--------------
5            | 1          | 57   | 21        | 1
6            | 8          | 58   | 24        | 1
7            | 9          | 57   | 21        | 1
7            | 6          | 58   | 29        | 1
8            | 8          | 57   | 21        | 1

需求逻辑 #

参照EnrollmentTransaction表，对EnrollmentDetail表执行基于EnrollmentId的批量增改操作（无对应(EnrollmentId, TransactionId)组合则插入，已有则更新），具体规则：

• 当EnrollmentTransaction中某EnrollmentId仅1条记录时：
  • 若EnrollmentDetail中无该EnrollmentId记录，不操作；
  • 若有记录，将对应行的TransactionId更新为EnrollmentTransaction中的值。
• 当EnrollmentTransaction中某EnrollmentId有多条记录时：
  • 若EnrollmentDetail中无该EnrollmentId记录，不操作；
  • 若仅有1条记录，复制该记录并生成对应所有(EnrollmentId, TransactionId)组合的行（保留原记录的ParameterId、SVCId、SVCValueId，替换TransactionId）；
  • 若有多条记录，将每条记录与EnrollmentTransaction中该EnrollmentId下的所有TransactionId匹配，生成对应组合的行（原记录的TransactionId替换为目标值，无对应组合则插入）。

示例场景 #

• EnrollmentId=5在EnrollmentTransaction中有2条记录，EnrollmentDetail仅1条，需插入(5,1,57,21,2)，保留原(5,1,57,21,1)。
• EnrollmentId=6在两表各1条记录但TransactionId不同，需将EnrollmentDetail对应行的TransactionId更新为3。
• EnrollmentId=9在EnrollmentTransaction中有1条但EnrollmentDetail无记录，不操作。

最终目标表数据 #

EnrollmentId | ParameterId| SVCId| SVCValueId| TransactionId  
-------------+------------+------+-----------+--------------
5            | 1          | 57   | 21        | 1
5            | 1          | 57   | 21        | 2
6            | 8          | 58   | 24        | 3
7            | 9          | 57   | 21        | 2
7            | 6          | 58   | 29        | 2
7            | 9          | 57   | 21        | 3
7            | 6          | 58   | 29        | 3
8            | 8          | 57   | 21        | 1
8            | 8          | 57   | 21        | 2
8            | 8          | 57   | 21        | 3

可行性及实现方案 #

可以通过单条MERGE语句实现上述批量增改操作，利用Oracle的MERGE语法结合子查询生成目标数据集，匹配主键完成更新或插入。

实现SQL语句 #

MERGE INTO EnrollmentDetail ed
USING (
    SELECT 
        et.EnrollmentId,
        ed_src.ParameterId,
        ed_src.SVCId,
        ed_src.SVCValueId,
        et.TransactionId
    FROM EnrollmentTransaction et
    JOIN EnrollmentDetail ed_src 
        ON et.EnrollmentId = ed_src.EnrollmentId
    WHERE (
        (SELECT COUNT(*) FROM EnrollmentTransaction WHERE EnrollmentId = et.EnrollmentId) = 1
        AND ed_src.TransactionId = 1
    ) OR (
        (SELECT COUNT(*) FROM EnrollmentTransaction WHERE EnrollmentId = et.EnrollmentId) > 1
    )
) src
ON (
    ed.EnrollmentId = src.EnrollmentId
    AND ed.ParameterId = src.ParameterId
    AND ed.SVCId = src.SVCId
    AND ed.TransactionId = src.TransactionId
)
WHEN MATCHED THEN 
    UPDATE SET ed.TransactionId = src.TransactionId
WHEN NOT MATCHED THEN 
    INSERT (EnrollmentId, ParameterId, SVCId, SVCValueId, TransactionId)
    VALUES (src.EnrollmentId, src.ParameterId, src.SVCId, src.SVCValueId, src.TransactionId);

逻辑说明 #

1. 源数据集生成：通过JOIN操作仅关联EnrollmentDetail中存在的EnrollmentId，自动过滤无对应记录的情况（如EnrollmentId=9）。
2. 单条TransactionId处理：当EnrollmentTransaction中EnrollmentId仅1条记录时，仅匹配EnrollmentDetail中原TransactionId=1的记录，生成目标TransactionId的匹配项，触发更新。
3. 多条TransactionId处理：当EnrollmentTransaction中EnrollmentId有多条记录时，将EnrollmentDetail中该EnrollmentId下的所有记录与每一个TransactionId组合，生成所有需要的行，匹配则更新、不匹配则插入。

内容的提问来源于stack exchange，提问作者Seegel