3.2.4.1. LightDB-Oracle
3.2.4.1.1. 插入
3.2.4.1.1.1. 多表插入
备注
LightDB-Oracle暂不支持在INSERT ALL 的条件句中使用子查询
CREATE TABLE unisql_multi_table_small_orders (
order_id INT,
customer_id INT,
order_total INT,
sales_rep_id INT
);
CREATE TABLE unisql_multi_table_medium_orders (
order_id INT,
customer_id INT,
order_total INT,
sales_rep_id INT
);
CREATE TABLE unisql_multi_table_large_orders (
order_id INT,
customer_id INT,
order_total INT,
sales_rep_id INT
);
-- 转换前Oracle SQL:
INSERT ALL
-- 插入到 unisql_multi_table_small_orders 表中
INTO unisql_multi_table_small_orders (order_id,customer_id,order_total,sales_rep_id )
VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,credit_limit)
-- 插入到 unisql_multi_table_medium_orders 表中
INTO unisql_multi_table_medium_orders(order_id,customer_id,order_total,sales_rep_id )
VALUES(order_id,customer_id,(order_total + customer_id + 1)*2+2,credit_limit)
-- 插入到 unisql_multi_table_large_orders 表中
INTO unisql_multi_table_large_orders(order_id,customer_id,order_total,sales_rep_id )
VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;
-- 转换后LightDB-Oracle SQL:
-- INSERT ALL INTO unisql_multi_table_small_orders (order_id,customer_id,order_total,sales_rep_id) VALUES ((order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit) INTO unisql_multi_table_medium_orders (order_id,customer_id,order_total,sales_rep_id) VALUES (order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit) INTO unisql_multi_table_large_orders (order_id,customer_id,order_total,sales_rep_id) VALUES (order_id,customer_id,order_total+3,credit_limit) SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders
INSERT ALL
INTO unisql_multi_table_small_orders (order_id,customer_id,order_total,sales_rep_id) VALUES ((order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit)
INTO unisql_multi_table_medium_orders (order_id,customer_id,order_total,sales_rep_id) VALUES (order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit)
INTO unisql_multi_table_large_orders (order_id,customer_id,order_total,sales_rep_id) VALUES (order_id,customer_id,order_total+3,credit_limit)
SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders
-- 转换前Oracle SQL:
INSERT ALL
WHEN order_id = 4 THEN
INTO unisql_multi_table_small_orders VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,credit_limit)
WHEN order_id = 6 THEN
INTO unisql_multi_table_medium_orders VALUES(order_id,customer_id,(order_total + customer_id + 1)*2+2,credit_limit)
ELSE
INTO unisql_multi_table_large_orders VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;
-- 转换后LightDB-Oracle SQL:
-- INSERT ALL WHEN order_id=4 THEN INTO unisql_multi_table_small_orders VALUES ((order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit) WHEN order_id=6 THEN INTO unisql_multi_table_medium_orders VALUES (order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit) ELSE INTO unisql_multi_table_large_orders VALUES (order_id,customer_id,order_total+3,credit_limit) SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders
INSERT ALL
WHEN order_id=4 THEN
INTO unisql_multi_table_small_orders VALUES ((order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit)
WHEN order_id=6 THEN
INTO unisql_multi_table_medium_orders VALUES (order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit)
ELSE
INTO unisql_multi_table_large_orders VALUES (order_id,customer_id,order_total+3,credit_limit)
SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders
-- 说明，LightDB-Oracle暂不支持在WHEN条件中使用子查询，统一SQL支持转换，但是在数据库执行会报错提示:
-- SQL 错误 [XX000]: ERROR: cannot handle unplanned sub-select
INSERT ALL
WHEN order_id = 4 and order_id in (select order_id FROM unisql_multi_table_small_orders WHERE order_id>1) THEN
INTO unisql_multi_table_small_orders VALUES ((order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit)
ELSE
INTO unisql_multi_table_large_orders VALUES(order_id,customer_id,order_total+3,credit_limit)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,credit_limit FROM unisql_special_orders;
3.2.4.1.1.2. WITH临时表查询插入
-- 创建表
drop table unisql_special_orders;
CREATE TABLE unisql_special_orders(order_ INT DEFAULT 0,
customer_ INT,
order_total INT,
sales_rep_id VARCHAR(64),
credit_limit INT,
cust_email INT);
-- 插入数据
INSERT INTO unisql_special_orders VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders VALUES(2,2,2,'a2',2,2);
INSERT INTO unisql_special_orders VALUES(3,3,3,'a3',3,3);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id)
WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders)
SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders t LEFT JOIN tmp y ON t.order_ = y.order_;
-- 转换后LightDB-Oracle SQL:
-- INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id) WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders) SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders AS t LEFT JOIN tmp AS y ON t.order_=y.order_
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id)
WITH tmp AS (SELECT order_,customer_,order_total,sales_rep_id FROM unisql_special_orders)
SELECT t.order_,t.customer_,y.order_total,y.sales_rep_id FROM unisql_special_orders AS t LEFT JOIN tmp AS y ON t.order_=y.order_
-- 查询表数据
SELECT * FROM unisql_special_orders;
ORDER_|CUSTOMER_|ORDER_TOTAL|SALES_REP_ID|CREDIT_LIMIT|CUST_EMAIL|
------+---------+-----------+------------+------------+----------+
1| 1| 1|a1 | 1| 1|
2| 2| 2|a2 | 2| 2|
3| 3| 3|a3 | 3| 3|
1| 1| 1|a1 | | |
2| 2| 2|a2 | | |
3| 3| 3|a3 | | |
3.2.4.1.1.3. 合并插入
备注
支持 merge_insert_clause 在 merge_update_clause 顺序之前用法。还原后 merge_update_clause 还是在 merge_insert_clause 顺序之前。
DROP TABLE unisql_people_source;
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)
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)
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)
3.2.4.1.2. 更新
3.2.4.1.2.1. 更新带表别名
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;
-- 转换后LightDB-Oracle SQL:
UPDATE unisql_people_target AS u SET u.FIRST_NAME='Ashley', u.LAST_NAME='Jessica' WHERE u.person_id=2
-- 查询表数据
select * from unisql_people_target;
3.2.4.1.2.2. 更新带子查询
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = (SELECT b.FIRST_NAME FROM unisql_people_target b WHERE b.person_id = 2), u.LAST_NAME = 'Nicholas' WHERE u.person_id=3;
-- 转换后LightDB-Oracle SQL:
UPDATE unisql_people_target AS u SET u.FIRST_NAME=(SELECT b.FIRST_NAME FROM unisql_people_target AS b WHERE b.person_id=2), u.LAST_NAME='Nicholas' WHERE u.person_id=3
-- 查询表数据
select * from unisql_people_target;
3.2.4.1.2.3. 更新带函数
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = UPPER(u.FIRST_NAME),u.LAST_NAME = LOWER(u.LAST_NAME) WHERE u.person_id=3;
-- 转换后LightDB-Oracle SQL:
UPDATE unisql_people_target AS u SET u.FIRST_NAME=UPPER(u.FIRST_NAME), u.LAST_NAME=LOWER(u.LAST_NAME) WHERE u.person_id=3
-- 查询表数据
select * from unisql_people_target;
3.2.4.1.3. 删除
3.2.4.1.3.1. 条件删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';
-- 转换后LightDB-Oracle SQL:
DELETE FROM unisql_people_target AS u WHERE u.PERSON_ID=1 AND FIRST_NAME='Bob'
-- 查询表数据
select * from unisql_people_target;
3.2.4.1.3.2. 条件子查询删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID > (SELECT PERSON_ID FROM unisql_people_target WHERE FIRST_NAME = 'ASHLEY');
-- 转换后LightDB-Oracle SQL:
DELETE FROM unisql_people_target AS u WHERE u.PERSON_ID>(SELECT PERSON_ID FROM unisql_people_target WHERE FIRST_NAME='ASHLEY')
-- 查询表数据
select * from unisql_people_target;
3.2.4.1.3.3. exists函数删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_source
WHERE EXISTS(SELECT 1 FROM unisql_people_target WHERE unisql_people_target.PERSON_ID = unisql_people_source.PERSON_ID);
-- 转换后LightDB-Oracle SQL:
DELETE FROM unisql_people_source WHERE EXISTS (SELECT 1 FROM unisql_people_target WHERE unisql_people_target.PERSON_ID=unisql_people_source.PERSON_ID)
-- 查询表数据
select * from unisql_people_source;