5.4. 不支持特性

本节主要介绍统一SQL oracle2tdsql-mysql 不支持转换的Oracle特性

5.4.1. 多表插入

INSERT ALL 用于以下情况不支持

INSERT ALL 多表
INSERT ALL … SELECT … FROM TABLE

INSERT ALL
INTO small_orders (order_id,customer_id,order_total,sales_rep_id )
    VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,sales_rep_id)
INTO 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 large_orders(order_id,customer_id,order_total,sales_rep_id )
    VALUES(order_id,customer_id,order_total+3,sales_rep_id)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,sales_rep_id,credit_limit FROM special_orders;

INSERT ALL WHEN … THEN … ELSE …

INSERT ALL
WHEN order_id = 4 and order_id in (select order_id FROM small_orders WHERE order_id>1) THEN
INTO small_orders VALUES((order_id + customer_id + 1)*2,customer_id,order_total+1,sales_rep_id)
WHEN order_id = 6 THEN
INTO medium_orders VALUES(order_id,customer_id,(order_total + customer_id + 1)*2+2,credit_limit)
ELSE
INTO large_orders VALUES(order_id,customer_id,order_total+3,sales_rep_id)
SELECT (order_ +1 )*2 order_id ,(customer_ +1 )*2 customer_id,order_total,sales_rep_id,credit_limit FROM special_orders

5.4.2. 合并插入

MERGE INTO 用于以下情况不支持

MERGE INTO USE [DELETE]
插入和更新同时存在时，更新不支持带where条件

5.4.3. 注释列

语法

COMMENT ON COLUMN table_name.column_name IS 'Your comment here';

说明: 统一SQL暂不支持注释列的转换

5.4.4. ORDER BY 子句

语法

ORDER BY expr
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, expr
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]

说明: 统一SQL暂不支持ORDER BY子句中的NULLS FIRST、NULLS LAST语法词的转换

5.4.5. EMPTY_BLOB, EMPTY_CLOB

不支持下面示例中使用到的empty_blob,empty_clob函数的转换。

SELECT empty_clob() FROM dual;
SELECT empty_blob() FROM dual;

DROP TABLE unisql_blob_test;
CREATE TABLE unisql_blob_test (id number,blob_data blob)

-- 在insert语句中的使用
INSERT INTO unisql_blob_test (id,blob_data) VALUES (1,EMPTY_BLOB());
INSERT INTO unisql_blob_test (id,blob_data) VALUES (2,EMPTY_BLOB());
INSERT INTO unisql_blob_test (id,blob_data) VALUES (3,EMPTY_BLOB());
INSERT INTO unisql_blob_test (id,blob_data) VALUES (3,'blob data');

SELECT * FROM unisql_blob_test;

-- 在update语句中的使用
UPDATE unisql_blob_test SET blob_data = EMPTY_BLOB() WHERE id = 4;

5.4.6. ROWIDTOCHAR

不支持ROWIDTOCHAR的转换，下面参考示例的转换将失败：

SELECT rowidtochar(rowid) AS char_rowid FROM rowid_example_table;
------------------+
CHAR_ROWID        |
------------------+
AAA1I1AAMAAIku/AAA|
AAA1I1AAMAAIku/AAB|

5.4.7. 分析函数

不支持AVG、COUNT、MAX、MIN、SUM、LISTAGG作为分析函数的用法，不支持over子句。

SELECT department_id, name, salary,MAX(salary) OVER (PARTITION BY department_id) AS mgr_max FROM unisql_employee ORDER BY department_id, name, salary;
SELECT department_id, name, salary,MIN(salary) OVER (PARTITION BY department_id) AS mgr_max FROM unisql_employee ORDER BY department_id, name, salary;
SELECT employee_id,salary,department_id,AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS avgRes FROM unisql_employee ke GROUP BY department_id,salary ,employee_id;
SELECT id,value,count(id) OVER (PARTITION BY value) FROM unisql_test_data;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW ERROR) over(partition by department_id) rk FROM listagg_test_employees;

5.4.8. 聚合函数

COVAR_POP、COVAR_SAMP、CUME_DIST、MEDIAN、DENSE_RANK、PERCENT_RANK、RANK、STDDEV、STDDEV_POP、STDDEV_SAMP、VAR_POP、VAR_SAMP、VARIANCE不支持。

5.4.9. INTERSECT 交集

SELECT * FROM unisql_collect_1
 INTERSECT
SELECT * FROM unisql_collect_2;

5.4.10. MINUS 差集

SELECT * FROM unisql_collect_1
 MINUS
SELECT * FROM unisql_collect_2;

5.4.11. 删除索引

DROP INDEX idx;

5.4.12. 创建表中列的DEFAULT表达式

不支持SYSTIMESTAMP、TO_DATE、MAX等函数，序列等。即使统一SQL中可支持转化，但是实际数据库不支持使用。

CREATE TABLE unisql_default_expr_table (
    col_systimestamp DATE DEFAULT SYSTIMESTAMP,
    col_nextval NUMBER DEFAULT unisql_default_expr_seq.NEXTVAL,
    col_currval NUMBER DEFAULT unisql_default_expr_seq.CURRVAL,
    col_to_date DATE DEFAULT TO_DATE('2000-01-01', 'YYYY-MM-DD')
)

5.4.13. 创建分区

统一SQL中可支持转化，但是实际数据库不支持使用。

RANGE分区字段类型不支持

varchar2/char/nvarchar2/nchar/clob/blob/raw/long/longraw

timestamp/timestamp with time zone/timestamp with local time zone

interval year to month/interval day to second

number/decimal/dec/numeric/float

HASH分区字段类型不支持

varchar2/char/nvarchar2/nchar/clob/blob/raw/long/longraw

date/timestamp/timestamp with time zone/timestamp with local time zone

interval year to month/interval day to second

number/decimal/dec/numeric/float

LIST分区字段不支持

number/decimal/dec/numeric/float

date/timestamp/timestamp with time zone/timestamp with local time zone

interval year to month/interval day to second

在oracle中timestamp with time zone也不可以作为分区字段的类型，在实施时不要选择此类型。

举例

 -- oracle sql，使用number类型作为(RANGE)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 NUMBER,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 )PARTITION BY RANGE (col1)
 (
 PARTITION part1 VALUES LESS THAN (10),
 PARTITION part2 VALUES LESS THAN (20),
 PARTITION part3 VALUES LESS THAN (MAXVALUE));

 -- oracle sql，使用varchar2类型作为(RANGE)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 varchar2,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 )PARTITION BY RANGE (col1)
 (
 PARTITION part1 VALUES LESS THAN (10),
 PARTITION part2 VALUES LESS THAN (20),
 PARTITION part3 VALUES LESS THAN (MAXVALUE));

 -- oracle sql，使用timestamp类型作为(RANGE)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 timestamp,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 )PARTITION BY RANGE (col1)
 (
       PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('2024/04/01','yyyy/mm/dd')),
       PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('2024/07/01','yyyy/mm/dd')),
       PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('2024/10/01','yyyy/mm/dd'))
);
 -- oracle sql，当前不支持以下语句的解析
 CREATE TABLE unisql_partition_test (
 col1 timestamp with local time zone,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 )PARTITION BY RANGE (col1)
 (
       PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_TIMESTAMP_TZ('2022-04-13 12:34:56.789 +00:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')),
       PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_TIMESTAMP_TZ('2022-04-16 12:34:56.789 +00:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')),
       PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_TIMESTAMP_TZ('2022-04-19 12:34:56.789 +00:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'))
);

-- oracle sql，当前不支持interval year to month的转换
CREATE TABLE unisql_partition_test (
 col1 interval year to month,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 )PARTITION BY RANGE (col1)
 (
       PARTITION SALES_Q1_1998 VALUES LESS THAN (INTERVAL '3-4' YEAR TO MONTH),
       PARTITION SALES_Q2_1998 VALUES LESS THAN (INTERVAL '5-6' YEAR TO MONTH),
       PARTITION SALES_Q3_1998 VALUES LESS THAN (INTERVAL '7-8' YEAR TO MONTH)
);

 -- oracle sql，使用number类型作为(HASH)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 number,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 ) partition by HASH (col1) PARTITIONS 4;

 -- oracle sql，使用varchar2类型作为(HASH)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 varchar2(10),
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 ) partition by HASH (col1) PARTITIONS 4;

 -- oracle sql，使用date类型作为(HASH)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 date,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 ) partition by HASH (col1) PARTITIONS 4;

 -- oracle sql，使用number类型作为(LIST)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 number,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 ) partition by list (col1) (
   partition p1 values (1,2,3),
       partition p2 values (10,11,12),
       partition p3 values (20,21,22)
 );

 -- oracle sql，使用date类型作为(LIST)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 date,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 ) partition by list (col1) (
   partition p1 values (TO_DATE('1998/04/01','yyyy/mm/dd')),
       partition p2 values (TO_DATE('1998/04/02','yyyy/mm/dd')),
       partition p3 values (TO_DATE('1998/04/03','yyyy/mm/dd'))
 );

 -- oracle sql，使用timestamp类型作为(LIST)分区字段，转换后的语句在tdmysql-mysql中执行报错
 CREATE TABLE unisql_partition_test (
 col1 timestamp,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 ) partition by list (col1) (
   partition p1 values (TO_DATE('1998/04/01','yyyy/mm/dd')),
       partition p2 values (TO_DATE('1998/04/02','yyyy/mm/dd')),
       partition p3 values (TO_DATE('1998/04/03','yyyy/mm/dd'))
 );

-- oracle sql，当前不支持以下语句的解析
CREATE TABLE unisql_partition_test (
 col1 timestamp with local time zone,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 ) partition by list (col1) (
   partition p1 values (TO_TIMESTAMP_TZ('2022-04-13 12:34:56.789 +00:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')),
       partition p2 values (TO_TIMESTAMP_TZ('2022-04-16 12:34:56.789 +00:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')),
       partition p3 values (TO_TIMESTAMP_TZ('2022-04-19 12:34:56.789 +00:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'))
 );

-- oracle sql，当前不支持interval year to month的转换
CREATE TABLE unisql_partition_test (
 col1 interval year to month,
 col2 NUMBER,
 col3 NUMBER,
 col4 NUMBER
 ) partition by list (col1) (
   partition p1 values (INTERVAL '3-4' YEAR TO MONTH),
       partition p2 values (INTERVAL '5-6' YEAR TO MONTH),
       partition p3 values (INTERVAL '7-8' YEAR TO MONTH)
 );