统一SQL merge into 支持 insert 在 update 顺序之前用法

统一SQL介绍

详见：统一SQL参考手册

背景

Oracle官方语法图中merge into 用法中merge_update_clause是在merge_insert_clause之前，如下图。

oracle官方链接：MERGE

备注：语法图顺序虽如此但是oracle自身支持merge_insert_clause在merge_update_clause之前用法。

统一SQL支持

针对于上述背景统一SQL对merge into 做了语法支持，支持 merge_insert_clause 在  merge_update_clause 顺序之前用法。

备注：经过统一SQL转换后 merge_update_clause 还是在 merge_insert_clause 顺序之前，保持语义的一致性。

• 使用案例 oracle2lightdb_oracle

    
    -- 初始化表结构
    CREATE TABLE unisql_people_source ( 
    person_id  INTEGER NOT NULL PRIMARY KEY, 
    first_name VARCHAR2(20) NOT NULL, 
    last_name  VARCHAR2(20) NOT NULL, 
    title      VARCHAR2(10) NOT NULL 
    );

    DROP TABLE unisql_people_target;
    CREATE TABLE unisql_people_target ( 
    person_id  INTEGER NOT NULL PRIMARY KEY, 
    first_name VARCHAR2(20) NOT NULL, 
    last_name  VARCHAR2(20) NOT NULL, 
    title      VARCHAR2(10) NOT NULL 
    );

    INSERT INTO unisql_people_target VALUES (1, 'John', 'Smith', 'Mr');
    INSERT INTO unisql_people_target VALUES (2, 'alice', 'jones', 'Mrs');
    INSERT INTO unisql_people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
    INSERT INTO unisql_people_source VALUES (3, 'Jane', 'Doe', 'Miss');
    INSERT INTO unisql_people_source VALUES (4, 'Dave', 'Brown', 'Mr');    

    -- 转换前Oracle SQL:
    MERGE INTO unisql_people_target pt 
    USING unisql_people_source ps 
    ON (pt.person_id = ps.person_id) 
    WHEN MATCHED THEN 
    UPDATE SET pt.first_name = ps.first_name, 
        pt.last_name = ps.last_name, 
        pt.title = ps.title
    WHEN NOT MATCHED THEN 
    INSERT (pt.person_id, pt.first_name, pt.last_name, pt.title) 
    VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

    -- 转换后LightDB-Oracle SQL:
    MERGE INTO unisql_people_target AS pt 
    USING unisql_people_source AS ps 
    ON (pt.person_id=ps.person_id) 
    WHEN MATCHED THEN UPDATE SET pt.first_name=ps.first_name,pt.last_name=ps.last_name,pt.title=ps.title 
    WHEN NOT MATCHED THEN INSERT (pt.person_id,pt.first_name,pt.last_name,pt.title) VALUES (ps.person_id,ps.first_name,ps.last_name,ps.title)


    -- 查询表数据
    SELECT * FROM unisql_people_target;
    PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
    ---------+----------+---------+-----+
            1|John      |Smith    |Mr   |
            2|Alice     |Jones    |Mrs. |
            3|Jane      |Doe      |Miss |
            4|Dave      |Brown    |Mr   |




    -- merge_insert_clause 在 merge_update_clause 顺序之前用法
    -- 转换前Oracle SQL:
    MERGE INTO unisql_people_target pt 
    USING unisql_people_source ps 
    ON (pt.person_id = ps.person_id) 
    WHEN NOT MATCHED THEN 
    INSERT (pt.person_id, pt.first_name, pt.last_name, pt.title) 
    VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title)
    WHEN MATCHED THEN 
    UPDATE SET pt.first_name = ps.first_name, 
        pt.last_name = ps.last_name, 
        pt.title = ps.title;

    -- 转换后LightDB-Oracle SQL:
    MERGE INTO unisql_people_target AS pt 
    USING unisql_people_source AS ps 
    ON (pt.person_id=ps.person_id) 
    WHEN MATCHED THEN UPDATE SET pt.first_name=ps.first_name,pt.last_name=ps.last_name,pt.title=ps.title 
    WHEN NOT MATCHED THEN INSERT (pt.person_id,pt.first_name,pt.last_name,pt.title) VALUES (ps.person_id,ps.first_name,ps.last_name,ps.title)

Oracle到其余信创数据库的转换用法请参考：

统一SQL参考手册