3.2.4.7. GaussDB-Oracle
3.2.4.7.1. 插入
3.2.4.7.1.1. 插入单条记录
指定所有字段
-- 创建表
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);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);
-- 转换后GaussDB-Oracle SQL:
INSERT INTO unisql_special_orders (order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES (1,1,1,'a1',1,1)
-- 查询表数据
SELECT * FROM unisql_special_orders;
指定某些字段，不指的有默认值的插入时带上默认值。
-- 创建表
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);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');
-- 转换后GaussDB-Oracle SQL:
INSERT INTO unisql_special_orders (sales_rep_id) VALUES ('a1')
-- 查询表数据
SELECT * FROM unisql_special_orders;
不指定字段，则默认为所有字段与建表时一致。
-- 创建表
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);
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);
-- 转换后GaussDB-Oracle SQL:
INSERT INTO unisql_special_orders VALUES (3,1,1,'a1',1,1)
-- 查询表数据
SELECT * FROM unisql_special_orders;
支持IGNORE_ROW_ON_DUPKEY_INDEX优化器提示
-- 创建表
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);
create unique index uidx_sales_rep_id on unisql_special_orders(sales_rep_id);
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);
-- 转换前Oracle SQL:
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(unisql_special_orders,uidx_sales_rep_id) */ INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);
-- 转换后GaussDB-Oracle SQL:
INSERT INTO unisql_special_orders VALUES (3,1,1,'a1',1,1) ON DUPLICATE KEY UPDATE NOTHING
-- 查询表数据
SELECT * FROM unisql_special_orders;
3.2.4.7.1.2. 插入多行数据
语法
{ INSERT | REPLACE } [INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list), (value_list) ... }
MariaDB语法。
说明
在GaussDB-Oracle中自增主键如果插入多行null值会报错无法执行，但是插入单行null值是可以的。所以统一sql转换时将插入多行数据的Insert、Replace语句拆分成多个插入单行数据的语句。
示例
-- 创建表
CREATE TABLE unisql_tb_cdm_class_attrib_test (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT 'xxx',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入多行数据（转换前）
INSERT INTO unisql_tb_cdm_class_attrib_test (id) VALUES (null), (null);
-- 多条插入单行数据（转换后）
INSERT INTO unisql_tb_cdm_class_attrib_test (id) VALUES (NULL);
INSERT INTO unisql_tb_cdm_class_attrib_test (id) VALUES (NULL);
-- 创建表
CREATE TABLE unisql_sales_insert_test (
id INT AUTO_INCREMENT,
product_name VARCHAR(50),
quantity INT,
sale_date DATE,
PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE COLUMNS (sale_date) (
PARTITION sales_p0 VALUES LESS THAN ('2020-01-01'),
PARTITION sales_p1 VALUES LESS THAN ('2021-01-01'),
PARTITION sales_p2 VALUES LESS THAN ('2022-01-01'),
PARTITION sales_p3 VALUES LESS THAN ('2023-01-01'),
PARTITION sales_p4 VALUES LESS THAN ('2024-01-01'),
PARTITION sales_p5 VALUES LESS THAN MAXVALUE
);
-- INSERT插入多行数据（转换前）
INSERT INTO unisql_sales_insert_test (product_name, quantity, sale_date) VALUES
('Product A', 10, '2022-01-01'),
('Product B', 5, '2023-02-15'),
('Product C', 8, '2024-05-10');
-- 多条INSERT插入单行数据（转换后）
INSERT INTO unisql_sales_insert_test (product_name,quantity,sale_date) VALUES ('Product A',10,'2022-01-01');
INSERT INTO unisql_sales_insert_test (product_name,quantity,sale_date) VALUES ('Product B',5,'2023-02-15');
INSERT INTO unisql_sales_insert_test (product_name,quantity,sale_date) VALUES ('Product C',8,'2024-05-10');
-- REPLACE插入多行数据（转换前）
REPLACE INTO unisql_sales_insert_test (product_name, quantity, sale_date) VALUES
('Product A', 10, '2022-01-01'),
('Product B', 5, '2023-02-15'),
('Product C', 8, '2024-05-10');
-- 多条REPLACE插入单行数据（转换后）
REPLACE INTO unisql_sales_insert_test (product_name,quantity,sale_date) VALUES ('Product A',10,'2022-01-01');
REPLACE INTO unisql_sales_insert_test (product_name,quantity,sale_date) VALUES ('Product B',5,'2023-02-15');
REPLACE INTO unisql_sales_insert_test (product_name,quantity,sale_date) VALUES ('Product C',8,'2024-05-10');
3.2.4.7.1.3. 从另外一个表取数据进行批量插入
-- 创建表
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(order_,customer_,order_total,sales_rep_id,credit_limit,cust_email) VALUES(1,1,1,'a1',1,1);
INSERT INTO unisql_special_orders(sales_rep_id) VALUES('a1');
INSERT INTO unisql_special_orders VALUES(3,1,1,'a1',1,1);
-- 从另外一个表读取数据进行数据插入
-- 转换前Oracle SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders;
-- 转换后GaussDB-Oracle SQL:
INSERT INTO unisql_special_orders SELECT order_,customer_,order_total,sales_rep_id,credit_limit,cust_email FROM unisql_special_orders
-- 查询表数据
SELECT * FROM unisql_special_orders;
order_|customer_|order_total|sales_rep_id|credit_limit|cust_email|
------+---------+-----------+------------+------------+----------+
1| 1| 1|a1 | 1| 1|
0| | |a1 | | |
3| 1| 1|a1 | 1| 1|
1| 1| 1|a1 | 1| 1|
0| | |a1 | | |
3| 1| 1|a1 | 1| 1|
3.2.4.7.1.4. 批量插入
-- 创建表
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);
-- 转换前Oracle SQL:
INSERT ALL
INTO unisql_special_orders VALUES(1,1,1,'a1',1,1)
INTO unisql_special_orders VALUES(2,2,2,'a2',2,2)
INTO unisql_special_orders VALUES(3,3,3,'a3',3,3)
INTO unisql_special_orders VALUES(4,4,4,'a4',4,4)
INTO unisql_special_orders VALUES(5,5,5,'a5',5,5)
SELECT * FROM dual;
-- 查询表数据
select * from unisql_special_orders ORDER BY order_ ;
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|
4| 4| 4|a4 | 4| 4|
5| 5| 5|a5 | 5| 5|
-- 转换后GaussDB-Oracle SQL:
WITH tmp AS (SELECT * FROM sys_dummy),
unisql_special_orders_tmp_0 AS (INSERT INTO unisql_special_orders SELECT 1,1,1,'a1',1,1 FROM tmp),
unisql_special_orders_tmp_1 AS (INSERT INTO unisql_special_orders SELECT 2,2,2,'a2',2,2 FROM tmp),
unisql_special_orders_tmp_2 AS (INSERT INTO unisql_special_orders SELECT 3,3,3,'a3',3,3 FROM tmp),
unisql_special_orders_tmp_3 AS (INSERT INTO unisql_special_orders SELECT 4,4,4,'a4',4,4 FROM tmp),
unisql_special_orders_tmp_4 AS (INSERT INTO unisql_special_orders SELECT 5,5,5,'a5',5,5 FROM tmp)
SELECT 1 FROM sys_dummy
-- 查询表数据
select * from unisql_special_orders ORDER BY order_ ;
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|
4| 4| 4|a4 | 4| 4|
5| 5| 5|a5 | 5| 5|
3.2.4.7.1.5. 多表插入
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;
-- 转换后GaussDB-Oracle SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0 AS (INSERT INTO unisql_multi_table_small_orders (order_id,customer_id,order_total,sales_rep_id)
SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp),
unisql_multi_table_medium_orders_tmp_1 AS (INSERT INTO unisql_multi_table_medium_orders (order_id,customer_id,order_total,sales_rep_id)
SELECT order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit FROM tmp),
unisql_multi_table_large_orders_tmp_2 AS (INSERT INTO unisql_multi_table_large_orders (order_id,customer_id,order_total,sales_rep_id)
SELECT order_id,customer_id,order_total+3,credit_limit FROM tmp)
SELECT 1 FROM sys_dummy
-- 转换前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;
-- 转换后GaussDB-Oracle SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0_0 AS (INSERT INTO unisql_multi_table_small_orders SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp WHERE order_id=4),
unisql_multi_table_medium_orders_tmp_1_0 AS (INSERT INTO unisql_multi_table_medium_orders SELECT order_id,customer_id,(order_total+customer_id+1)*2+2,credit_limit FROM tmp WHERE order_id=6),
unisql_multi_table_large_orders_tmp_2_0 AS (INSERT INTO unisql_multi_table_large_orders SELECT order_id,customer_id,order_total+3,credit_limit FROM
(SELECT * FROM tmp EXCEPT ALL (SELECT * FROM tmp WHERE order_id=4 UNION ALL SELECT * FROM tmp WHERE order_id=6)) AS tmp_except_0)
SELECT 1 FROM sys_dummy
-- 转换前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;
-- 转换后GaussDB-Oracle SQL:
WITH tmp AS (SELECT (order_+1)*2 AS order_id,(customer_+1)*2 AS customer_id,order_total,credit_limit FROM unisql_special_orders),
unisql_multi_table_small_orders_tmp_0_0 AS (INSERT INTO unisql_multi_table_small_orders SELECT (order_id+customer_id+1)*2,customer_id,order_total+1,credit_limit FROM tmp WHERE order_id=4 AND order_id IN (SELECT order_id FROM unisql_multi_table_small_orders WHERE order_id>1)),
unisql_multi_table_large_orders_tmp_1_0 AS (INSERT INTO unisql_multi_table_large_orders SELECT order_id,customer_id,order_total+3,credit_limit FROM (SELECT * FROM tmp EXCEPT ALL (SELECT * FROM tmp WHERE order_id=4 AND order_id IN (SELECT order_id FROM unisql_multi_table_small_orders WHERE order_id>1))) AS tmp_except_0)
SELECT 1 FROM sys_dummy
3.2.4.7.1.6. 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_;
-- 转换后GaussDB-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_
-- 查询表数据
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.7.1.7. 合并插入
语法
MERGE INTO table_name [ [ AS ] alias ]
USING { { table_name | view_name } | subquery | ( VALUES ( expression [, ...] ) ) } [ [ AS ] alias ] [ ( column[, ...] ) ]
ON ( condition )
[
WHEN MATCHED THEN
UPDATE SET { column_name = { expression } [, ...] }
[ WHERE condition ]
]
[
WHEN NOT MATCHED THEN
INSERT {
[ ( column_name [, ...] ) ] VALUES ( { expression } [, ...] )
[ WHERE condition ] }
];
警告
merge into的on条件中不能有绑定变量占位，涉及到占位相关的条件写到外层
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);
-- 转换后GaussDB-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 table_name [ [ AS ] alias ] USING ( VALUES ( expression [, ...] ) ) [ AS ] alias ( column[, ...] ) ON ( condition ) ... oracle不支持改语法，仅针对GaussDB-Oracle进行语法解析,转换前 SQL:
MERGE INTO test_table t1
USING (VALUES
(1, 'John Doe', 'Description 11', 'Transaction 1', 'Value 1', 'ValueL 1', 'ValueDGBS 1'),
(2, 'Jane Doe', 'Description 2', 'Transaction 2', 'Value 2', 'ValueL 2', 'ValueDGBS 2'),
(3, 'Alice', 'Description 3', 'Transaction 3', 'Value 3', 'ValueL 3', 'ValueDGBS 3'),
(4, 'Bob', 'Description 4', 'Transaction 4', 'Value 4', 'ValueL 4', 'ValueDGBS 4'),
(5, 'Charlie', 'Description 5', 'Transaction 5', 'Value 5', 'ValueL 5', 'ValueDGBS 5'),
(6, 'Dave', 'Description 6', 'Transaction 6', 'Value 6', 'ValueL 6', 'ValueDGBS 6'),
(7, 'Eve', 'Description 7', 'Transaction 7', 'Value 7', 'ValueL 7', 'ValueDGBS 7')
) AS t2 (id, name, name_desc, trans_name, test_values, values1, values_dgbs)
ON (t1.id = t2.id)
WHEN MATCHED THEN
UPDATE SET
t1.name = t2.name,
t1.name_desc = t2.name_desc,
t1.trans_name = t2.trans_name,
t1.test_values = t2.test_values,
t1.values1 = t2.values1,
t1.values_dgbs = t2.values_dgbs
WHEN NOT MATCHED THEN
INSERT (id, name, name_desc, trans_name, test_values, values1, values_dgbs)
VALUES (t2.id, t2.name, t2.name_desc, t2.trans_name, t2.test_values, t2.values1, t2.values_dgbs);
-- 转换后GaussDB-Oracle SQL:
MERGE INTO test_table AS t1 USING ( VALUES (1,'John Doe','Description 11','Transaction 1','Value 1','ValueL 1','ValueDGBS 1'),(2,'Jane Doe','Description 2','Transaction 2','Value 2','ValueL 2','ValueDGBS 2'),(3,'Alice','Description 3','Transaction 3','Value 3','ValueL 3','ValueDGBS 3'),(4,'Bob','Description 4','Transaction 4','Value 4','ValueL 4','ValueDGBS 4'),(5,'Charlie','Description 5','Transaction 5','Value 5','ValueL 5','ValueDGBS 5'),(6,'Dave','Description 6','Transaction 6','Value 6','ValueL 6','ValueDGBS 6'),(7,'Eve','Description 7','Transaction 7','Value 7','ValueL 7','ValueDGBS 7')) AS t2 (id,name,name_desc,trans_name,test_values,values1,values_dgbs) ON (t1.id=t2.id) WHEN MATCHED THEN UPDATE SET t1.name=t2.name,t1.name_desc=t2.name_desc,t1.trans_name=t2.trans_name,t1.test_values=t2.test_values,t1.values1=t2.values1,t1.values_dgbs=t2.values_dgbs WHEN NOT MATCHED THEN INSERT (id,name,name_desc,trans_name,test_values,values1,values_dgbs) VALUES (t2.id,t2.name,t2.name_desc,t2.trans_name,t2.test_values,t2.values1,t2.values_dgbs)
3.2.4.7.2. 更新
3.2.4.7.2.1. 更新带表别名
-- 转换前Oracle SQL:
UPDATE unisql_people_target u SET u.FIRST_NAME = 'Ashley', u.LAST_NAME = 'Jessica' WHERE u.person_id=2;
-- 转换后GaussDB-Oracle SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME='Ashley', LAST_NAME='Jessica' WHERE u.person_id=2
-- 查询表数据
select * from unisql_people_target;
3.2.4.7.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;
-- 转换后GaussDB-Oracle SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME=(SELECT b.FIRST_NAME FROM unisql_people_target AS b WHERE b.person_id=2), LAST_NAME='Nicholas' WHERE u.person_id=3
-- 查询表数据
select * from unisql_people_target;
3.2.4.7.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;
-- 转换后GaussDB-Oracle SQL:
UPDATE unisql_people_target AS u SET FIRST_NAME=UPPER(u.FIRST_NAME), LAST_NAME=LOWER(u.LAST_NAME) WHERE u.person_id=3
-- 查询表数据
select * from unisql_people_target;
3.2.4.7.2.4. 更新多列
-- 转换前Oracle SQL:
UPDATE unisql_people_target SET
(FIRST_NAME, LAST_NAME)=( SELECT FIRST_NAME, LAST_NAME FROM unisql_people_target WHERE person_id= 1 )
WHERE person_id=2;
-- 转换后GaussDB-Oracle SQL:
UPDATE unisql_people_target SET (FIRST_NAME,LAST_NAME)=(SELECT FIRST_NAME,LAST_NAME FROM unisql_people_target WHERE person_id=1) WHERE person_id=2
-- 查询表数据
select * from unisql_people_target;
3.2.4.7.3. 删除
3.2.4.7.3.1. 条件删除
-- 转换前Oracle SQL:
DELETE FROM unisql_people_target u WHERE u.PERSON_ID =1 AND FIRST_NAME = 'Bob';
-- 转换后GaussDB-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.7.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');
-- 转换后GaussDB-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.7.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);
-- 转换后GaussDB-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;