1.3.2.4.7. GaussDB-Oracle
1.3.2.4.7.1. 插入
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)
指定某些字段，不指的有默认值的插入时带上默认值。
-- 创建表
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;
支持return/returning子句(一般用于PLSQL中静态SQL)
-- 转换前Oracle SQL:
INSERT INTO students VALUES ('John Doe', 20) RETURNING employee_id, salary INTO @v_employee_ids, @v_salaries;
INSERT INTO students (name, age) VALUES ('John Doe', 20) RETURNING employee_id, salary INTO v_employee_ids, v_salaries;
INSERT INTO students (name, age) VALUES ('John Doe', 20) RETURN employee_id, salary INTO @v_employee_ids, @v_salaries;
INSERT INTO students (name, age) VALUES ('John Doe', 20) RETURN employee_id, salary INTO v_employee_ids, v_salaries;
-- 转换后GaussDB-Oracle SQL:
INSERT INTO students VALUES ('John Doe',20) RETURNING employee_id,salary INTO @v_employee_ids,@v_salaries;
INSERT INTO students (name,age) VALUES ('John Doe',20) RETURNING employee_id,salary INTO v_employee_ids,v_salaries;
INSERT INTO students (name,age) VALUES ('John Doe',20) RETURNING employee_id,salary INTO @v_employee_ids,@v_salaries;
INSERT INTO students (name,age) VALUES ('John Doe',20) RETURNING employee_id,salary INTO v_employee_ids,v_salaries
警告
占位符@转换后结果受到配置项 unisql.bind.variable.mode；为 0 的时候转换为 :, 为 1 的时候返回为 @。
1.3.2.4.7.1.2. 从另外一个表取数据进行批量插入
-- 创建表
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|
1.3.2.4.7.1.3. 从DUAL表取数据进行批量插入
语法
INSERT INTO table_name(column1, column2, ...) SELECT * FROM (
SELECT expr alias, [, expr alias] FROM DUAL
UNION ALL
SELECT expr alias, [, expr alias] FROM DUAL
...
) alias;
警告
第一层select 必须是 select *
第二层select 必须是 from dual, 必须包含 union/union all, 每个select的字段个数相同
示例
-- 转换前Oracle SQL:
insert into t1(c1, c2) select * from (select 1 a, 2 b from dual union all select 3 a, 4 b from dual union all select 5 a, 6 b from dual) aaa;
-- 转换后GaussDB-Oracle SQL:
INSERT INTO t1 (c1,c2) VALUES (1,2),(3,4),(5,6);
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|
1.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
1.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 | | |
1.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
)
1.3.2.4.7.2. 更新
1.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 u.FIRST_NAME='Ashley', u.LAST_NAME='Jessica' WHERE u.person_id=2;
-- 查询表数据
select * from unisql_people_target;
1.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 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;
1.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 u.FIRST_NAME=UPPER(u.FIRST_NAME), u.LAST_NAME=LOWER(u.LAST_NAME) WHERE u.person_id=3;
-- 查询表数据
select * from unisql_people_target;
1.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;
1.3.2.4.7.2.5. 支持return/returning子句(一般用于PLSQL中静态SQL)
-- 转换前Oracle SQL:
UPDATE employees SET salary = v_new_salary WHERE employee_id = 101 RETURNING salary, eeee INTO @qw, :er;
UPDATE employees SET salary = v_new_salary WHERE employee_id = 101 RETURNING salary, eeee INTO qw, er;
UPDATE employees SET salary = v_new_salary WHERE employee_id = 101 RETURN salary, eeee INTO @qw, @er;
UPDATE employees SET salary = v_new_salary WHERE employee_id = 101 RETURN salary, eeee INTO qw, er;
-- 转换后GaussDB-Oracle SQL:
UPDATE employees SET salary=v_new_salary WHERE employee_id=101 RETURNING salary,eeee INTO :qw,:er;
UPDATE employees SET salary=v_new_salary WHERE employee_id=101 RETURNING salary,eeee INTO qw,er;
UPDATE employees SET salary=v_new_salary WHERE employee_id=101 RETURNING salary,eeee INTO :qw,:er;
UPDATE employees SET salary=v_new_salary WHERE employee_id=101 RETURNING salary,eeee INTO qw,er;
警告
占位符@转换后结果受到配置项 unisql.bind.variable.mode；为 0 的时候转换为 :, 为 1 的时候返回为 @。
1.3.2.4.7.3. 删除
1.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;
1.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;
1.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;
1.3.2.4.7.3.4. 支持return/returning子句(一般用于PLSQL中静态SQL)
-- 转换前Oracle SQL:
DELETE FROM employees WHERE salary < 3000 RETURNING employee_id, salary INTO @v_employee_ids, @v_salaries;
DELETE FROM employees WHERE salary < 3000 RETURNING employee_id, salary INTO v_employee_ids, v_salaries;
DELETE FROM employees WHERE salary < 3000 RETURN employee_id, salary INTO @v_employee_ids, @v_salaries;
DELETE FROM employees WHERE salary < 3000 RETURN employee_id, salary INTO v_employee_ids, v_salaries;
-- 转换后GaussDB-Oracle SQL:
DELETE FROM employees WHERE salary<3000 RETURNING employee_id,salary INTO :v_employee_ids,:v_salaries;
DELETE FROM employees WHERE salary<3000 RETURNING employee_id,salary INTO v_employee_ids,v_salaries;
DELETE FROM employees WHERE salary<3000 RETURNING employee_id,salary INTO :v_employee_ids,:v_salaries;
DELETE FROM employees WHERE salary<3000 RETURNING employee_id,salary INTO v_employee_ids,v_salaries;
警告
占位符@转换后结果受到配置项 unisql.bind.variable.mode；为 0 的时候转换为 :, 为 1 的时候返回为 @。