统一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到其余信创数据库的转换用法请参考: