3.4. 不支持特性
本节主要介绍统一SQL Oracle2PostgreSQL 不支持转换的Oracle特性。
3.4.1. 数据类型
3.4.1.1. NUMBER
- 当
p、s是以下情况时，不支持： p >= 39
p == 0
s > 127
p==0 && s == 0
s < 0
例如：
create table t(col NUMBER(39));
create table t(col NUMBER(0));
create table t(col NUMBER(1,128));
create table t(col NUMBER(0,0));
3.4.2. 数字函数
3.4.2.1. SQRT
涉及浮点数加后缀的情况，不支持转换。
例如：
select SQRT(3.0d),SQRT(-3.0d),SQRT(3.0f),SQRT(-3.0f) from dual;
3.4.2.2. TRUNC
涉及浮点数加后缀的情况，不支持转换。
例如：
select TRUNC(1.5d),trunc(15.79d, 1),trunc(15.79f, -1) from dual;
3.4.2.6. LPAD
不支持字符类型以外的类型，且有些用法上语义不一致，不推荐使用。
例如：
select LPAD(12345,6),LPAD(12345,6,'^') from dual;
3.4.2.7. RPAD
不支持字符类型以外的类型，且有些用法上语义不一致，不推荐使用。
例如：
select RPAD(12345,6),RPAD(12345,6,'^') from dual;
3.4.2.10. REGEXP_REPLACE
不支持3个以上的参数
例如：
SELECT REGEXP_REPLACE('there are 100 apples', '[0-9]', '*', 1, 1) from dual;
3.4.3. 字符串函数
3.4.3.1. INSTR
有些用法上语义不一致，不推荐使用。
例如：
SELECT INSTR('Unisql','U') i1,INSTR('Unisql','u') i2,INSTR('Unisql','n') i3,INSTR('Unisql','a') i4,INSTR('Unisql','') i5,INSTR('Unisql',NULL) i6,INSTR('foobarbar', 'bar') i7, INSTR('xbar', 'foobar') i8,INSTR('北京欢迎你','北') i9 FROM DUAL;
3.4.3.3. REGEXP_INSTR
不支持2个以上的参数，有些用法上语义不一致，不推荐使用。
例如：
select REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) "REGEXP_INSTR" from dual;
SELECT
REGEXP_INSTR('http://www.example.com/products','http://([[:alnum:]]+\.?){3,4}/?') "regexp_str",
REGEXP_INSTR('1234567890','(123)(4(56)(78))'),
REGEXP_INSTR('Anderson', 'a|e|i|o|u')
FROM DUAL;
3.4.4. 时间日期函数
3.4.4.1. TO_DSINTERVAL
不支持 DEFAULT 用法。
例如：
select TO_DSINTERVAL('1o 1:02:10' DEFAULT '10 8:00:00' ON CONVERSION ERROR) "Value" from dual;
3.4.4.3. SCN_TO_TIMESTAMP
不支持。
例如：
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees WHERE employee_id = 188;
3.4.5. 通用比较函数
3.4.6. 转换函数
3.4.6.2. TO_NUMBER
不支持2个及2个以上参数的用法。
例如：
select TO_NUMBER('-AusDollars100','L9G999D99',' NLS_NUMERIC_CHARACTERS = '',.''NLS_CURRENCY = ''AusDollars''') "Amount" from dual;
3.4.6.4. TO_DATE
TO_DATE用于以下情况时不支持DEFAULT语法
例如：
SELECT TO_DATE('Febuary 15, 2016, 11:00 A.M.'
DEFAULT 'January 01, 2016 12:00 A.M.' ON CONVERSION ERROR,
'Month dd, YYYY, HH:MI A.M.') "Value"
FROM DUAL;
3.4.6.5. TO_NCHAR (datetime)
最多只支持一个参数，有些用法上语义不一致，不推荐使用。
例如：
SELECT TO_NCHAR(SYSDATE),
TO_NCHAR(TO_TIMESTAMP('2023-04-20 17:31:12.66', 'YYYY-MM-DD HH24:MI:SS.FF')) AS TIMESTAMP_NCHAR
FROM DUAL;
3.4.6.6. TO_NCHAR (number)
最多只支持一个参数，涉及浮点数加后缀的情况，不支持转换。
例如：
SELECT TO_NCHAR(123456789.123d) FROM DUAL;
3.4.6.7. ROWIDTOCHAR
不支持ROWIDTOCHAR的转换， 下面参考示例的转换将失败：
SELECT rowidtochar(rowid) AS char_rowid FROM rowid_example_table;
------------------+
CHAR_ROWID |
------------------+
AAA1I1AAMAAIku/AAA|
AAA1I1AAMAAIku/AAB|
3.4.7. 环境和标识符函数
3.4.8. 分析函数
3.4.8.1. CORR、CORR_K、CORR_S
不支持。
例如：
SELECT weight_class, CORR(list_price, min_price) FROM product_information GROUP BY weight_class;
SELECT CORR_S(salary, commission_pct) commission, CORR_S(salary, employee_id) empid FROM employees;
3.4.8.2. COVAR_POP
不支持。
例如：
SELECT job_id,COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop FROM employees WHERE department_id in (50, 80) GROUP BY job_id;
3.4.8.3. COVAR_SAMP
不支持。
例如：
SELECT job_id,COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp FROM employees WHERE department_id in (50, 80) GROUP BY job_id;
3.4.8.4. MEDIAN
不支持。 例如：
SELECT department_id, MEDIAN(department_id) OVER (PARTITION BY department_id) FROM unisql_employee ke GROUP BY department_id;
3.4.8.5. PERCENTILE_CONT
不支持
3.4.8.6. PERCENTILE_DISC
不支持
3.4.9. 聚合函数
3.4.9.1. CORR、CORR_K、CORR_S
不支持。
例如：
SELECT weight_class, CORR(list_price, min_price) FROM product_information GROUP BY weight_class;
SELECT CORR_S(salary, commission_pct) commission, CORR_S(salary, employee_id) empid FROM employees;
3.4.9.2. COVAR_POP
不支持。
例如：
SELECT job_id,COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop FROM employees WHERE department_id in (50, 80) GROUP BY job_id;
3.4.9.3. COVAR_SAMP
不支持。
例如：
SELECT job_id,COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp FROM employees WHERE department_id in (50, 80) GROUP BY job_id;
3.4.9.4. PERCENT_RANK
不支持 WITHIN GROUP 用法。
例如：
SELECT PERCENT_RANK(15000, .05) WITHIN GROUP (ORDER BY salary, commission_pct) FROM employees;
3.4.10. 空值相关函数
3.4.10.1. 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;
3.4.11. 其他
3.4.11.1. TIMESTAMP_TO_SCN
不支持。
例如：
SELECT TIMESTAMP_TO_SCN(order_date) FROM orders WHERE order_id = 5000;
3.4.12. 伪列
3.4.12.1. ROWNUM
ROWNUM用于WHERE条件中时，以下情况不支持：WHERE ROWNUM <> 1
WHERE ROWNUM != 1
WHERE ROWNUM > x AND ROWNUM <= y
WHERE ROWNUM > x OR ROWNUM <= y
3.4.13. 查询
3.4.13.1. PIVOT
- 以下情况不支持：
SELECT *
SELECT fields 中经过pivot for、in分配后剩余用于group by的字段为空
PIVOT … IN 中的表达式没有别名
FROM后面的表带别名
FROM后面是子查询
例如：
select * from t_test a pivot(min(score) for subject in('CHINESE' as c90, 'MATH' as m80, 'ENGLISH' as e70)) where id in (1, 2, 3) order by id;
select id,name,c90,m80,e70 from (select * from a) pivot(min(score) for subject in('CHINESE' as c90, 'MATH' as m80, 'ENGLISH' as e70)) where id in (1, 2, 3) order by id;
select c90 from t_test pivot(min(score) for subject in('CHINESE' as c90, 'MATH' as m80, 'ENGLISH' as e70)) where id in (1, 2, 3) order by id;
3.4.13.2. UNPIVOT
- 以下情况不支持：
FROM后面的表带别名
FROM后面是子查询
例如：
select id, name, score, subject from t_test a unpivot(score for subject in(chinese, math, english)) where id in (1, 2, 3) order by id;
select id, name, score, subject from (select * from a) unpivot(score for subject in(chinese, math, english)) where id in (1, 2, 3) order by id;
3.4.13.3. CONNECT BY
CONNECT BY中以下情况不支持connect_by_iscycle 不支持
connect_by_root(xxx) 虚列中xxx只支持column，不支持表达式和计算
sys_connect_by_path(xxx,text)虚列中xxx只支持column，不支持表达式和计算
层次查询特有表达式不支持嵌套使用：如: sys_connect_by_root(prior column)
不支持和oracle(+)一起使用
不支持和pivot, unpivot函数一起使用
列名不支持带schema名和数据库名，只支持表名和字段名
不支持和cte一起使用
不支持*，需要列明字段
不支持函数
不支持ROWNUM
3.4.14. 外连接符号(+)
3.4.14.1. (+)
- 以下情况不支持：
a.key1(+) =b.key1(+) 全连接不支持
a.key1 = b.key1(+) and a.key2(+) = b.key2 不能彼此关联
a.key1 = b.key1(+) and b.key2(+) = c.key1 一个表不能外关联多于一个表
a.key1+c.key1 = b.key1(+) 一个表不能外关联多于一个表
a.key1 = b.key1(+) or a.key2 = b.key2(+) 不支持OR
select * from t1 a where exists (select * from t2 b where a.key1(+)=b.key1) 不能引用非本queryblock的表
not (a.key1(+)= b.key1 and a.key2(+)=b.key2) 不能用于嵌套表达式
a.key1(+) =123 不支持非列名表达式
30 * a.key1(+) = b.key1 不支持运算表达式
例如：
select * from t1 a where exists (select * from t2 b,t3,c where a.key1(+)=b.key1);
SELECT * FROM table1 t1, table2 t2, table3 t3 WHERE NOT (t1.column1 = t2.column1(+) AND t1.column1 = t3.column1(+)) AND t1.column2 = t2.column2(+);
select * from table1 t1, table2 t2 where t1.c1(+) = t2.c1(+);
SELECT * FROM table1 t1, table2 t2, table3 t3 WHERE t1.column1 = t2.column1(+) AND t1.column1 * 300 = t3.column1(+);
SELECT * FROM table1 t1, table2 t2, table3 t3 WHERE NOT (t1.column1 = t2.column1(+) AND t1.column1 = t3.column1(+)) AND t1.column2 = t2.column2(+);
SELECT * FROM table1 t1, table2 t2, table3 t3 WHERE t1.column1 = t2.column1(+) OR t1.column1 = t3.column1(+);
3.4.15. DDL
3.4.15.2. ALTER TABLE table_name ADD PARTITION partition_definition
新增分区不支持。
例如：
ALTER TABLE sales_data ADD PARTITION q1_2024 VALUES LESS THAN (TO_DATE('2024/04/01', 'yyyy/mm/dd'));
3.4.15.3. ALTER TABLE tbl_name (MERGE|COALESCE) PARTITIONS merge_table_partitions::=
合并分区不支持。
例如：
ALTER TABLE sales_data MERGE PARTITIONS q4_2023, q1_2024 INTO PARTITION q1_2024;
3.4.16. DML
3.4.16.1. INSERT FIRST
不支持
例如：
INSERT FIRST
WHEN sal > 10000 THEN
INTO high_sal_emp (empno, ename, job, sal)
VALUES (empno, ename, job, sal)
WHEN sal > 5000 THEN
INTO mid_sal_emp (empno, ename, job, sal)
VALUES (empno, ename, job, sal)
ELSE
INTO low_sal_emp (empno, ename, job, sal)
VALUES (empno, ename, job, sal)
SELECT * FROM table1
3.4.16.2. INSERT INTO TABLE PARTITION
插入分区数据不支持
例如：
INSERT INTO t PARTITION (p1) (col,col2,col3) VALUES (1,2,3)
3.4.16.5. MERGE INTO
MERGE INTO用于以下情况不支持MERGE INTO … DELETE
例如：
MERGE INTO table2 t2
USING table1 t1
ON (t1.id = t2.id)
WHEN MATCHED THEN
UPDATE SET t2.name = t1.name,t2.age = t1.age
DELETE WHERE t2.age > 30
WHEN NOT MATCHED THEN
INSERT (id, name, age) VALUES (t1.id, t1.name, t1.age)
3.4.16.6. 创建分区表
- 在Oracle中创建Hash分区表时,有一些数据类型不能作为分区键。主要原因是这些数据类型无法在分区之间很好地分布数据。以下是不能用作Hash分区键的数据类型:
LONG 和 LONG RAW:这两种数据类型已被弃用,Oracle建议使用CLOB和BLOB代替。
ROWID 和 UROWID:这些伪列类型不能作为分区键。
对象类型(Object Types) 和 集合类型(Collection Types):包括嵌套表和可变长数组等。
REF类型 (REF Types):引用对象类型的类型。
空间数据类型(Spatial Types):如SDO_GEOMETRY等。
URI类型
TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE:TIMESTAMP和DATE类型可以用作分区键,但带时区的时间戳类型不可以。
所有LOB类型(CLOB,BLOB,NCLOB):LOB数据类型不能直接作为分区键,但可以使用函数获取其部分值作为分区键。
嵌套表(Nested Table) 和 可变长数组(Varray):集合类型不能作为分区键。
转化到PostgreSQL数据库中不支持的作为Hash分区键的类型：XML，JSON
除上述类型外,大多数Oracle标量数据类型都可以用作Hash分区键,如NUMBER、VARCHAR2、CHAR等。选择具有适当数值分布和基数的列作为分区键非常重要,这样可以更好地在分区之间分布数据。官方文档建议使用整数类型NUMBER作为分区键通常是更好的选择。