1.3.2.4.10. Goldendb-mysql

1.3.2.4.10.1. 插入

1.3.2.4.10.1.1. 支持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;

-- 转换后Goldendb-Mysql 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`;

1.3.2.4.10.1.2. INSERT ALL

备注

因为原生 Goldendb-mysql 实现了 insert all 语句，因此统一SQL 采用透传方案。但，经过测试发现，Goldendb-mysql 只支持 insert all 的简单用法，如：

• 末尾的 select 语句不支持 where 子句；

• 不支持在 values 子句中使用 select 语句输出列的别名

• 多表插入还会存在一些异常

目前在 Oracle 中使用场景为批量插入，因为 Oracle 不支持多 values 子句。

-- 转换前Oracle SQL:
INSERT ALL
  INTO t1 values (1, 2, 3)
  INTO t1 values ('1', '2', '3')
SELECT 1 from dual;

-- 转换后Goldendb-Mysql SQL, 直接透传 insert all:
INSERT ALL
  INTO t1 VALUES (1,2,3)
  INTO t1 VALUES ('1','2','3')
SELECT 1 FROM dual;

1.3.2.4.10.2. 更新

1.3.2.4.10.2.1. 支持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;

-- 转换后Goldendb-Mysql 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`;

1.3.2.4.10.2.2. 支持set (col1, col2, ...) = (subquery)

-- 转换前Oracle SQL:
UPDATE usps_authority a
set (
a.init_date,
a.exchange_type,
a.stock_code,
a.business_type,
a.authority_code,
a.register_date,
a.distribute_rate,
a.pay_begin_date,
a.pay_end_date,
a.authority_type,
a.onetax_rate,
a.orgtax_rate,
a.arp_onetax_rate,
a.pretax_balance,
a.hkdc_corpbehavior_code,
a.placard_id,
a.info_kind) = (
SELECT
    b.init_date,
    b.exchange_type,
    b.stock_code,
    b.business_type,
    b.authority_code,
    b.register_date,
    b.distribute_rate,
    b.pay_begin_date,
    b.pay_end_date,
    b.authority_type,
    b.onetax_rate,
    b.orgtax_rate,
    b.arp_onetax_rate,
    b.pretax_balance,
    b.hkdc_corpbehavior_code,
    b.placard_id,
    b.info_kind
    FROM
    settredo_usps_authority b
    WHERE
    b.position_str = a.position_str
    and b.sett_dml_type = 'U'
    AND b.sett_batch_no = 1
)
WHERE EXISTS (
SELECT 1
FROM settredo_usps_authority b
WHERE b.position_str = a.position_str
and b.sett_dml_type = 'U'
AND b.sett_batch_no = 1)


-- 转换后Goldendb-Mysql SQL:
UPDATE usps_authority AS a
SET
(a.init_date,
a.exchange_type,
a.stock_code,
a.business_type,
a.authority_code,
a.register_date,
a.distribute_rate,
a.pay_begin_date,
a.pay_end_date,
a.authority_type,
a.onetax_rate,
a.orgtax_rate,
a.arp_onetax_rate,
a.pretax_balance,
a.hkdc_corpbehavior_code,
a.placard_id,
a.info_kind)=
(SELECT
    b.init_date,
    b.exchange_type,
    b.stock_code,
    b.business_type,
    b.authority_code,
    b.register_date,
    b.distribute_rate,
    b.pay_begin_date,
    b.pay_end_date,
    b.authority_type,
    b.onetax_rate,
    b.orgtax_rate,
    b.arp_onetax_rate,
    b.pretax_balance,
    b.hkdc_corpbehavior_code,
    b.placard_id,
    b.info_kind
    FROM settredo_usps_authority AS b
    WHERE b.position_str=a.position_str AND b.sett_dml_type='U' AND b.sett_batch_no=1
)
WHERE EXISTS
(SELECT 1 FROM settredo_usps_authority AS b
WHERE b.position_str=a.position_str AND b.sett_dml_type='U' AND b.sett_batch_no=1)

1.3.2.4.10.3. 删除

1.3.2.4.10.3.1. 支持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;

-- 转换后Goldendb-Mysql 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`;