4.2.4.6. Oceanbase-Oracle

4.2.4.6.1. 插入

4.2.4.6.1.1. 多表插入

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;

-- 转换后OceanBase-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;

-- 转换后OceanBase-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


-- 转换前Oracle SQL:
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;

-- 转换后OceanBase-Oracle SQL:
-- 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;
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;

4.2.4.6.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_;

-- 转换后oceanbase-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          |            |          |

4.2.4.6.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);

-- 转换后oceanbase-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;

-- 转换后oceanbase-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)

4.2.4.6.2. 更新

4.2.4.6.2.1. 更新带表别名

-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;

-- 转换后oceanbase-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;

4.2.4.6.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;

-- 转换后oceanbase-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;

4.2.4.6.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;

-- 转换后oceanbase-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;

4.2.4.6.3. 删除

4.2.4.6.3.1. 条件删除

-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';

-- 转换后oceanbase-Oracle SQL:
DELETE FROM unisql_people_target AS u WHERE u.PERSON_ID=1 AND FIRST_NAME='Bob'

-- 查询表数据
select * from unisql_people_target;

4.2.4.6.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');

-- 转换后oceanbase-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;

4.2.4.6.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);

-- 转换后oceanbase-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;