3.2.2.3. 转换为 PostgreSQL
3.2.2.3.1. 数值函数
3.2.2.3.1.1. BITAND
- 语法
BITAND (expr1,expr2)- 描述
- 该函数将其输入参数进行二进制按位与操作
参数解释
参数 |
说明 |
|
|---|---|---|
expr1 |
参数1 |
NUMBER 类型表达式 |
expr2 |
参数2 |
NUMBER 类型表达式 |
警告
参数2为负的情况需要包一层括号，例如：BITAND(0,(-1))。
示例
-- 转换前Oracle SQL：
SELECT BITAND(0,0),BITAND(0,(-1)),BITAND(0,NULL),BITAND(-1,2) FROM DUAL;
BITAND(0,0)|BITAND(0,(-1))|BITAND(0,NULL)|BITAND(-1,2)|
-----------+--------------+--------------+------------+
0| 0| | 2|
-- 转换后PostgreSQL SQL：
SELECT 0&0,0&(-1),0&NULL,-1&2
?column?|?column?|?column?|?column?|
--------+--------+--------+--------+
0| 0| | 2|
3.2.2.3.1.2. DBMS_RANDOM.VALUE
- 语法
DBMS_RANDOM.VALUE- 描述
- 随机生成 [0,1) 范围内的数字，精度为 38 位
示例
-- 转换前Oracle SQL：
SELECT DBMS_RANDOM.VALUE from dual;
VALUE |
----------------------------------------+
0.90603062118926722027812535155007101231|
-- 转换后PostgreSQL SQL：
SELECT random()
random |
------------------+
0.6059267559984995|
3.2.2.3.1.3. DBMS_RANDOM.RANDOM
- 语法
DBMS_RANDOM.RANDOM- 描述
- 随机生成 [-2^31，2^31）范围内的整数。
-- 转换前Oracle SQL：
SELECT DBMS_RANDOM.RANDOM from dual;
RANDOM |
----------+
-886930169|
-- 转换后PostgreSQL SQL：
SELECT CAST(CAST(random() AS numeric)*power(2, 32)-power(2, 31) AS numeric(38,0))
numeric |
-----------+
-1109521446|
3.2.2.3.1.4. LOG
- 语法
LOG (x,y)- 描述
- 该函数返回以 x 为底的 y 的自然对数
参数解释
参数 |
说明 |
|---|---|
x |
表示对数的底，数值型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。 x 大于 0 且不等于 1。 |
y |
表示真数，数值型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。 y 大于 0。 |
示例
警告
Oracle和 targetDB 对数据类型默认值(比如数字类型的精度)存在一定的差异。
数据库函数在处理传入的参数时也存在隐式转换的情况。
如果对数据完全一致要求较高，建议根据函数参数类型进行更精确的控制。
-- 转换前Oracle SQL：
SELECT LOG(10,100),LOG(3,27) AS "LOG3",LOG(2,1024) AS "LOG(2,1024)" from dual;
LOG(10,100)|LOG3 |LOG(2,1024) |
-----------+----------------------------------------+----------------------------------------+
2|2.99999999999999999999999999999999999998|9.99999999999999999999999999999999999995|
-- 转换后PostgreSQL SQL：
SELECT LOG(10, 100),LOG(3, 27) AS "LOG3",LOG(2, 1024) AS "LOG(2,1024)"
log |LOG3 |LOG(2,1024) |
------------------+------------------+-------------------+
2.0000000000000000|3.0000000000000000|10.0000000000000000|
3.2.2.3.2. 字符串函数
3.2.2.3.2.1. SUBSTR
语法
SUBSTR(str, pos)
SUBSTR(str, pos, len)
- 描述
- 返回 str 的子字符串，起始位置为 pos，长度为 len。参数中包含 NULL 时，返回 NULL
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
pos |
子字符串的起始位置 |
len |
子字符串的长度 |
示例
-- 转换前Oracle SQL：
SELECT SUBSTR('abcdefg', 3),SUBSTR('abcdefg', 3, 2),SUBSTR('abcdefg', -3),SUBSTR('abcdefg', 3, -2) FROM DUAL;
-------------------+---------------------+--------------------+----------------------+
SUBSTR('ABCDEFG',3)|SUBSTR('ABCDEFG',3,2)|SUBSTR('ABCDEFG',-3)|SUBSTR('ABCDEFG',3,-2)|
-------------------+---------------------+--------------------+----------------------+
cdefg |cd |efg | |
-- 转换后PostgreSQL SQL：
SELECT unisql.substr('abcdefg', 3),unisql.substr('abcdefg', 3, 2),unisql.substr('abcdefg', -3),unisql.substr('abcdefg', 3, -2)
substr|substr|substr|substr|
------+------+------+------+
cdefg |cd |efg | |
3.2.2.3.2.2. REGEXP_INSTR
- 语法
REGEXP_INSTR(source_char, pattern [, position, occurrence, return_option ])- 描述
- 该函数作用是返回正则表达式匹配值在源字符串中的位置。
参数 |
说明 |
|---|---|
source_char |
指定用作搜索值的字符表达式，数据类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB |
pattern |
指定正则表达式截取规则。它通常是一个文本文字，字符类型可为 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 |
position |
可选，指定从 source_char 哪个位置开始搜索字符串，是正整数，默认值是 1 |
occurrence |
可选，指定搜索第几次匹配 pattern。是正整数，默认值是 1 |
return_option |
可选，指定返回结果的情况，0，表示返回匹配的起始位置；1，表示返回匹配的结束位置，默认值为0，其余数值不支持 |
注意
由于PostgreSQL中不存在regexp_instr函数,所以在使用统一SQL转换涉及到regexp_instr函数的sql时,需要先导入创建自定义函数的脚本(参考快速开始-》导入统一SQL脚本到目标库中一节)
1. 当前仅支持两到五个参数的转换
2. 当第三、四、五参数是''时,Oracle返回null,转换后的sql在PostgreSQL中报错,使用时请注意。
3. 正则匹配默认大小写敏感,多行被视为一行匹配,支持的正则规则参考后续列表
4. 由于Oracle和PostgreSQL关于正则的实现存在差异,会出现部分正则写法不支持的情况,例如:
在Oracle中:
select regexp_instr('IL3nbfhzGl','*') FROM dual;
REGEXP_INSTR('IL3NBFHZGL','*')|
------------------------------+
1|
转换之后在PostgreSQL模式数据库执行:
SELECT unisql.regexp_instr(CAST('IL3nbfhzGl' AS text), CAST('*' AS text))
SQL 错误 [2201B]: ERROR: invalid regular expression: quantifier operand invalid
在Oracle中:
SELECT REGEXP_INSTR('foofoo', '(foo)\1') FROM DUAL;
REGEXP_INSTR('FOOFOO','(FOO)\1')|
--------------------------------+
1|
转换之后在PostgreSQL模式数据库执行:
SELECT unisql.REGEXP_INSTR(CAST('foofoo' AS text), CAST('(foo)1' AS text))
SQL 错误 [2201B]: ERROR: invalid regular expression: invalid backreference number
示例
-- 转换前Oracle SQL:
SELECT REGEXP_INSTR(' unisql1 unisql12 unisql128', 'unisql\d+') FROM dual;
REGEXP_INSTR('UNISQL1UNISQL12UNISQL128','UNISQL\D+')|
----------------------------------------------------+
2|
SELECT REGEXP_INSTR(' unisql1 unisql12 unisql128', 'unisql\d+',4) FROM dual;
REGEXP_INSTR('UNISQL1UNISQL12UNISQL128','UNISQL\D+',4)|
------------------------------------------------------+
10|
SELECT REGEXP_INSTR(' unisql1 unisql12 unisql128', 'unisql\d+',4,2) FROM dual;
REGEXP_INSTR('UNISQL1UNISQL12UNISQL128','UNISQL\D+',4,2)|
--------------------------------------------------------+
19|
SELECT REGEXP_INSTR(' unisql1 unisql12 unisql128', 'unisql\d+',4,2,1) FROM dual;
REGEXP_INSTR('UNISQL1UNISQL12UNISQL128','UNISQL\D+',4,2,1)|
----------------------------------------------------------+
28|
-- 转换后PostgreSQL模式 SQL:
SELECT unisql.REGEXP_INSTR(CAST(' unisql1 unisql12 unisql128' AS text), CAST('unisql\d+' AS text));
regexp_instr|
------------+
2|
SELECT unisql.REGEXP_INSTR(CAST(' unisql1 unisql12 unisql128' AS text), CAST('unisql\d+' AS text), CAST(4 AS int));
regexp_instr|
------------+
10|
SELECT unisql.REGEXP_INSTR(CAST(' unisql1 unisql12 unisql128' AS text), CAST('unisql\d+' AS text), CAST(4 AS int), CAST(2 AS int));
regexp_instr|
------------+
19|
SELECT unisql.REGEXP_INSTR(CAST(' unisql1 unisql12 unisql128' AS text), CAST('unisql\d+' AS text), CAST(4 AS int), CAST(2 AS int), CAST(1 AS int))
regexp_instr|
------------+
28|
正则规则
规则 |
描述 |
备注 |
|---|---|---|
\ |
反斜杠(1.可代表自己;2.引用下一个字符;3.引入一个操作符;4.什么都不做) |
单独使用目标库执行会报错 |
* |
匹配零或更多 |
单独使用目标库执行会报错 |
+ |
匹配一次或多次 |
单独使用目标库执行会报错 |
? |
匹配零或一次 |
单独使用目标库执行会报错 |
| |
选择匹配,比如:aa`|`bb（匹配 aa 或 bb ） |
单独使用目标库执行不支持 |
^ |
默认情况下匹配字符串的开头。在多行模式下，它匹配源字符串中任何地方的任何行的开头。 |
单独使用目标库执行不支持 |
$ |
默认情况下匹配字符串的末端。在多行模式下，它匹配源字符串中任何地方的任何线路的末端。 |
单独使用目标库执行不支持 |
. |
匹配受支持的字符集中的任何字符,null除外 |
|
[ ] |
匹配列表中表示的任何表达式, 比如: [abc](匹配 a、b 或 c 中的一个字母),[a-z](匹配 a 到 z 中的一个字母)。如果使用^则表示匹配除列表外的表达式[^abc](匹配除了 a、b 或 c 中的其他字母) |
|
() |
分组表达 |
|
{m} |
完全匹配M次 |
|
{m,} |
匹配至少M次 |
|
{m,n} |
匹配至少M次，但不超过n次 |
|
\n |
反向表示表达式（n是1和9之间的数字）与n之前的“（’and”）’之间的第N个子表达相匹配 | |
|
[..] |
指定一个collation元素 |
|
[::] |
指定字符类（例如，[：alpha：]）。它匹配字符类中的任何字符 |
|
[==] |
指定等价类。例如，[= a =]匹配所有具有基本字母’a’的字符。 |
|
\d |
一个数字字符 |
|
\D |
一个非数字字符 |
|
\w |
一个单词字符 |
|
\W |
一个非单词字符 |
|
\s |
一个空格字符 |
|
\S |
一个非空格字符 |
|
\A |
仅在字符串的开头或字符串末端的newline字符之前进行匹配 |
单独使用目标库执行不支持 |
\Z |
仅在字符串结束时匹配 |
单独使用目标库执行不支持 |
*? |
匹配前面的模式元素0或更多次(nongreedy) |
单独使用目标库执行会报错 |
+? |
匹配前面的模式元素元素1或更多次(nongreedy) |
单独使用目标库执行会报错 |
?? |
匹配前面的模式元素0或1次(nongreedy) |
单独使用目标库执行会报错 |
{n}? |
完全匹配前面的模式元素n次(nongreedy) |
|
{n,}? |
与前面的模式元素匹配至少n次(nongreedy) |
|
{n,m}? |
匹配前面的模式元素，至少n，但不超过M次(nongreedy) |
|
()\n |
分组和后向引用 |
不支持 |
(?:expr) |
忽略捕获的子模式 |
不支持 |
(?=expr) |
正向先行断言（Positive Lookahead),它断言从当前位置开始，后面必须匹配表达式 |
不支持 |
(?!expr) |
负向先行断言（Negative Lookahead）,断言从当前位置开始，后面不能匹配表达式 |
不支持 |
\m |
只在一个词的开头匹配 |
不支持 |
\M |
只在一个词的末尾匹配 |
不支持 |
\y |
只在一个词的开头或末尾匹配 |
不支持 |
\Y |
只在一个词的不是开头或末尾的点上匹配 |
不支持 |
3.2.2.3.2.3. TRIM
- 语法
TRIM([[{BOTH | LEADING | TRAILING}] FROM] str- 描述
- 删除字符串所有前缀和/或后缀，默认为 BOTH。参数中包含 NULL 时，返回 NULL
参数解释
参数 |
说明 |
|---|---|
BOTH |
删除字符串所有前缀和后缀 |
LEADING |
删除字符串前缀 |
TRAILING |
删除字符串后缀 |
str |
要操作的字符串 |
示例
-- 转换前Oracle SQL：
SELECT TRIM(' bar '),TRIM(LEADING 'x' FROM 'xxxbarxxx'), TRIM(BOTH 'x' FROM 'xxxbarxxx'),TRIM(TRAILING 'x' FROM 'xxxbarxxx'),TRIM(BOTH 'x' FROM NULL),TRIM(NULL) FROM DUAL;
TRIM('BAR')|TRIM(LEADING'X'FROM'XXXBARXXX')|TRIM(BOTH'X'FROM'XXXBARXXX')|TRIM(TRAILING'X'FROM'XXXBARXXX')|TRIM(BOTH'X'FROMNULL)|TRIM(NULL)|
-----------+-------------------------------+----------------------------+--------------------------------+---------------------+----------+
bar |barxxx |bar |xxxbar | | |
-- 转换后PostgreSQL SQL：
SELECT trim(CAST(' bar ' AS text)),ltrim(CAST('xxxbarxxx' AS text), CAST('x' AS text)),trim(CAST('x' AS text) FROM CAST('xxxbarxxx' AS text)),rtrim(CAST('xxxbarxxx' AS text), CAST('x' AS text)),trim(CAST('x' AS text) FROM CAST(NULL AS text)),trim(CAST(NULL AS text))
btrim|ltrim |btrim|rtrim |btrim|btrim|
-----+------+-----+------+-----+-----+
bar |barxxx|bar |xxxbar| | |
3.2.2.3.2.4. LENGTH
- 语法
LENGTH(str)- 描述
- 该函数返回 str 的字符长度
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
示例
-- 转换前Oracle SQL：
SELECT LENGTH('中国'), LENGTH('hello') FROM DUAL;
LENGTH('中国')|LENGTH('HELLO')|
------------+---------------+
2| 5|
-- 转换后PostgreSQL SQL：
SELECT LENGTH(CAST('中国' AS text)),LENGTH(CAST('hello' AS text))
length|length|
------+------+
2| 5|
3.2.2.3.2.5. LENGTHB
- 语法
LENGTHB(str)- 描述
- 该函数返回 str 的字节长度，与字符集有关
参数解释
参数 |
说明 |
|---|---|
str |
要操作的字符串 |
示例
-- 转换前Oracle SQL：
SELECT LENGTHB('中国'), LENGTHB('hello') FROM DUAL;
LENGTHB('中国')|LENGTHB('HELLO')|
-------------+----------------+
6| 5|
-- 转换后PostgreSQL SQL：
SELECT octet_length(CAST('中国' AS text)),octet_length(CAST('hello' AS text))
octet_length|octet_length|
------------+------------+
6| 5|
3.2.2.3.3. 时间日期函数
3.2.2.3.3.1. ADD_MONTHS
- 语法
ADD_MONTHS(date, n)- 描述
- 该函数功能是返回日期 date 加上 n 个月后的日期值。
参数解释
参数 |
说明 |
|---|---|
date |
指定日期。该参数为 DATE 数据类型。 |
n |
整数或可以转换为一个整数的任意值。NUMBER 数据类型。 |
示例
-- 转换前Oracle SQL：
SELECT
ADD_MONTHS(CURRENT_DATE,-3) AS a1,
ADD_MONTHS(CURRENT_DATE, 3)AS a2,
ADD_MONTHS(CURRENT_DATE, 1.1)AS a3,
ADD_MONTHS(CURRENT_DATE, 1.5)AS a4,
ADD_MONTHS(CURRENT_DATE, 1.8)AS a5,
ADD_MONTHS(CURRENT_DATE, 0)AS a6
FROM DUAL;
A1 |A2 |A3 |A4 |A5 |A6 |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-22 15:25:08.000|2024-03-22 15:25:08.000|2024-01-22 15:25:08.000|2024-01-22 15:25:08.000|2024-01-22 15:25:08.000|2023-12-22 15:25:08.000|
-- 转换后PostgreSQL SQL：
SELECT localtimestamp(0)+make_interval(0, CAST(trunc(-3) AS int), 0, 0, 0, 0, 0) AS a1,localtimestamp(0)+make_interval(0, CAST(trunc(3) AS int), 0, 0, 0, 0, 0) AS a2,localtimestamp(0)+make_interval(0, CAST(trunc(1.1) AS int), 0, 0, 0, 0, 0) AS a3,localtimestamp(0)+make_interval(0, CAST(trunc(1.5) AS int), 0, 0, 0, 0, 0) AS a4,localtimestamp(0)+make_interval(0, CAST(trunc(1.8) AS int), 0, 0, 0, 0, 0) AS a5,localtimestamp(0)+make_interval(0, CAST(trunc(0) AS int), 0, 0, 0, 0, 0) AS a6
a1 |a2 |a3 |a4 |a5 |a6 |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-22 15:26:16.000|2024-03-22 15:26:16.000|2024-01-22 15:26:16.000|2024-01-22 15:26:16.000|2024-01-22 15:26:16.000|2023-12-22 15:26:16.000|
3.2.2.3.3.2. CURRENT_DATE
- 语法
CURRENT_DATE- 描述
- 该函数返回当前会话时区的当前日期
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT CURRENT_DATE FROM DUAL;
CURRENT_DATE |
-----------------------+
2023-12-22 15:27:15.000|
-- 转换后PostgreSQL SQL：
SELECT localtimestamp(0)
localtimestamp |
-----------------------+
2023-12-22 15:28:04.000|
3.2.2.3.3.3. LAST_DAY
- 语法
LAST_DAY(date)- 描述
- 返回 date 当月最后一天的日期值
参数解释
参数 |
说明 |
|---|---|
date |
日期类型参数 |
示例
-- 转换前Oracle SQL：
SELECT LAST_DAY(SYSDATE),LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD')) FROM DUAL;
LAST_DAY(SYSDATE) |LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD'))|
-----------------------+--------------------------------------------+
2023-12-31 14:05:54.000| 2023-09-30 00:00:00.000|
-- 转换后PostgreSQL SQL：
SELECT unisql.last_day(statement_timestamp()),unisql.last_day(CAST(to_timestamp('2023/09/21', 'YYYY/MM/DD') AS timestamp))
last_day |last_day |
-----------------------+-----------------------+
2023-12-31 09:44:53.000|2023-09-30 00:00:00.000|
3.2.2.3.3.4. MONTHS_BETWEEN
- 语法
MONTHS_BETWEEN (date1，date2)- 描述
- 该函数是返回参数 date1 到 date2 之间的月数
参数解释
参数 |
说明 |
|---|---|
date1 |
DATE 数据类型的值。 |
date2 |
DATE 数据类型的值。 |
示例
-- 转换前Oracle SQL：
SELECT MONTHS_BETWEEN (TO_DATE('05-02-2023 13:24:52','MM-DD-YYYY HH24:MI:SS'), TO_DATE('03-04-2023 19:24:52','MM-DD-YYYY HH24:MI:SS') ) FROM dual;
MONTHS_BETWEEN(TO_DATE('05-02-202313:24:52','MM-DD-YYYYHH24:MI:SS'),TO_DATE('03-04-202319:24:52','MM-DD-YYYYHH24:MI:SS'))|
-------------------------------------------------------------------------------------------------------------------------+
1.92741935483870967741935483870967741935|
-- 转换后PostgreSQL SQL：
SELECT unisql.months_between(CAST(to_timestamp('05-02-2023 13:24:52', 'MM-DD-YYYY HH24:MI:SS') AS timestamp), CAST(to_timestamp('03-04-2023 19:24:52', 'MM-DD-YYYY HH24:MI:SS') AS timestamp))
months_between |
------------------+
1.9274193548387097|
3.2.2.3.3.5. NUMTODSINTERVAL
- 语法
NUMTODSINTERVAL (n,interval_unit)- 描述
- 该函数是把参数 n 转为以参数 interval_unit 为单位的 INTERVAL DAY TO SECOND 数据类型的值
参数解释
参数 |
说明 |
|---|---|
n |
NUMBER 数据类型或可以转换为 NUMBER 数据类型的表达式。 |
interval_unit |
单位值。取值为 DAY（天）、HOUR（小时）、MINUTE（分钟）、SECOND（秒），不区分大小写。 |
示例
警告
结果格式或表现存在不一致的情况。如果对数据完全一致要求较高，建议不使用该特性或对结果进一步处理。
-- 转换前Oracle SQL：
SELECT SYSDATE+NUMTODSINTERVAL(3,'DAY'),NUMTODSINTERVAL(2,'day') FROM dual;
SYSDATE+NUMTODSINTERVAL(3,'DAY')|NUMTODSINTERVAL(2,'DAY')|
--------------------------------+------------------------+
2023-12-24 09:54:11.000|2 0:0:0.0 |
-- 转换后PostgreSQL SQL：
?column? |make_interval |
-----------------------+---------------------------------------------+
2023-12-24 09:54:11.000|0 years 0 mons 2 days 0 hours 0 mins 0.0 secs|
3.2.2.3.3.6. SYSDATE
- 语法
SYSDATE- 描述
- 返回当前日期和时间
- 参数解释
- 无
示例
-- 转换前Oracle SQL：
SELECT SYSDATE FROM DUAL;
SYSDATE |
-----------------------+
2023-12-21 09:56:26.240|
-- 转换后PostgreSQL SQL：
SELECT statement_timestamp()
statement_timestamp |
-----------------------+
2023-12-21 09:56:26.240|
3.2.2.3.3.7. SYSTIMESTAMP
- 语法
SYSTIMESTAMP- 描述
- 该函数返回系统当前日期和时间，返回值的秒的小数位包含 6 位精度，包含当前时区信息,依赖于当前数据库服务器所在操作系统的时区
- 参数解释
- 无
示例
-- 转换前Oracle SQL：
SELECT SYSTIMESTAMP,SYSTIMESTAMP(0),SYSTIMESTAMP(1),SYSTIMESTAMP(2),SYSTIMESTAMP(3),SYSTIMESTAMP(4),SYSTIMESTAMP(5),SYSTIMESTAMP(6),SYSTIMESTAMP(7),SYSTIMESTAMP(8),SYSTIMESTAMP(9) FROM DUAL;
SYSTIMESTAMP |SYSTIMESTAMP(0) |SYSTIMESTAMP(1) |SYSTIMESTAMP(2) |SYSTIMESTAMP(3) |SYSTIMESTAMP(4) |SYSTIMESTAMP(5) |SYSTIMESTAMP(6) |SYSTIMESTAMP(7) |SYSTIMESTAMP(8) |SYSTIMESTAMP(9) |
-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:31.000 +0800|2023-12-21 10:04:30.500 +0800|2023-12-21 10:04:30.540 +0800|2023-12-21 10:04:30.544 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|
-- 转换后PostgreSQL SQL：
SELECT statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp()
statement_timestamp |statement_timestamp |statement_timestamp |statement_timestamp |statement_timestamp |statement_timestamp |statement_timestamp |statement_timestamp |statement_timestamp |statement_timestamp |statement_timestamp |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|
3.2.2.3.3.8. TRUNC
- 语法
TRUNC(date,[fmt])- 描述
- 该函数返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值，并且返回的日期值在 date 之前
参数解释
参数 |
说明 |
|---|---|
date |
DATE 数据类型 |
fmt |
指定了函数返回值与 date 的距离单位 |
fmt的取值如下：
fmt 参数的取值 |
说明 |
|
|---|---|---|
j |
默认值，最近 0 点日期。 |
|
day、dy、d |
返回离指定日期最近的星期日。 |
|
iw |
返回离指定日期最近的星期一。 |
|
q |
返回离指定日期最近的季的日期。 |
|
yyyy、yyy、yy、y |
多个 y 表示不同的精度，返回离指定日期最近的年的第一个日期。 |
警告
经统一sql转化会将Oracle的Date类型转为timestamp类型,时分秒为0。
示例
-- 转换前Oracle SQL：
SELECT TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'YEAR'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'MONTH'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'DDD'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'IW'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'D') FROM DUAL;
TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'YEAR')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'MONTH')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'DDD')|TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'IW')|TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'D')|
------------------------------------------------------------------+-------------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+-----------------------------------------------------------------+
2022-01-01 00:00:00.000| 2022-04-01 00:00:00.000| 2022-04-19 00:00:00.000| 2022-04-17 00:00:00.000| 2022-04-18 00:00:00.000|
-- 转换后PostgreSQL SQL：
SELECT date_trunc('YEAR', CAST(to_timestamp('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS') AS timestamp)),date_trunc('MONTH', CAST(to_timestamp('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS') AS timestamp)),date_trunc('DAY', CAST(to_timestamp('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS') AS timestamp)),date_trunc('WEEK', CAST(to_timestamp('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS') AS timestamp)),date_trunc('WEEK', CAST(to_timestamp('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS') AS timestamp))-interval '1 DAY'
date_trunc |date_trunc |date_trunc |date_trunc |date_trunc |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2022-01-01 00:00:00.000|2022-04-01 00:00:00.000|2022-04-19 00:00:00.000|2022-04-18 00:00:00.000|2022-04-17 00:00:00.000|
3.2.2.3.3.9. TO_DSINTERVAL
语法
/*SQL 日期格式*/
TO_DSINTERVAL ('[+ | -] days hours:minutes:seconds[.frac_secs]')
/*ISO 日期格式*/
TO_DSINTERVAL ('[-] P[days D]
[T[hours H][minutes M][seconds[.frac_secs]S]]')
- 描述
- 该函数将一个 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串转换为一个 INTERVAL DAY TO SECOND 数据类型的值
参数解释
参数 |
说明 |
|
|---|---|---|
[+ | -]days hours：minutes：seconds[.frac_secs] |
符合该参数格式的 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串。 |
|
[-] P[days D] [T[hours H][minutes M][seconds[.frac_secs]S] |
符合该参数格式的 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串。注意 值中不允许有空格。 |
|
frac_secs |
表示秒的小数部分，取整数值，范围为 [0 999999999]。 | |
|
days 表示天，取整数值，范围为 [0,999999999]。
hours 表示小时，取整数值，范围为 [0,23]。
minutes 表示分钟，取整数值，范围为 [0,59]。
seconds 表示秒，取整数值，范围为 [0,59]。
示例
-- 转换前Oracle SQL：
SELECT TO_DSINTERVAL('100 00:00:00'),TO_DSINTERVAL('P100DT05H') FROM DUAL;
TO_DSINTERVAL('10000:00:00')|TO_DSINTERVAL('P100DT05H')|
----------------------------+--------------------------+
100 0:0:0.0 |100 5:0:0.0 |
-- 转换后PostgreSQL SQL：
SELECT CAST('100 00:00:00' AS INTERVAL),CAST('P100DT05H' AS INTERVAL)
interval |interval |
-----------------------------------------------+-----------------------------------------------+
0 years 0 mons 100 days 0 hours 0 mins 0.0 secs|0 years 0 mons 100 days 5 hours 0 mins 0.0 secs|
3.2.2.3.3.10. TO_YMINTERVAL
语法
/*SQL 日期格式*/
TO_YMINTERVAL([+|-] years-months)
/*ISO 日期格式*/
TO_YMINTERVAL([-]P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]])
- 描述
- 该函数将一个 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串转换为一个 INTERVAL YEAR TO MONTH 数据类型的值
参数解释
参数 |
说明 |
||
|---|---|---|---|
[+|-] years-months |
符合该参数格式的 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串。years 表示年，取整数值，范围为 [0 999999999]。months 表示月，取整数值，范围为 [0 | 11]。 | |
||
[-]P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]] |
符合该参数格式的 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串。 frac_secs 表示秒的小数部分，取整数值，范围是[0 999999999]。注意 值中不允许有空格。 | |
||
示例
-- 转换前Oracle SQL：
SELECT SYSDATE,SYSDATE+TO_YMINTERVAL('01-02') FROM DUAL;
SYSDATE |SYSDATE+TO_YMINTERVAL('01-02')|
-----------------------+------------------------------+
2023-12-21 10:15:15.000| 2025-02-21 10:15:15.000|
-- 转换后PostgreSQL SQL：
SELECT statement_timestamp(),statement_timestamp()+CAST('01-02' AS INTERVAL)
statement_timestamp |?column? |
-----------------------+-----------------------+
2023-12-21 10:09:45.154|2025-02-21 10:09:45.154|
3.2.2.3.4. 转换函数
3.2.2.3.4.1. CAST
- 语法
CAST (expr AS type_name )- 描述
- 该函数用于将一种内置数据类型转换为另一种内置数据类型
参数解释
参数 |
说明 |
|---|---|
expr |
列名或者表达式。 |
AS |
用于分隔两个参数，在 AS 之前的是要处理的数据，在 AS 之后是要转换的数据类型。 |
type_name |
数据库的内建数据类型名称。 |
下表显示了哪些数据类型可以转换为其他内置数据类型
from BINARY_FLOAT / BINARY_DOUBLE |
from CHAR / VARCHAR2 |
from NUMBER |
from DATETIME/INTERVAL |
from RAW |
from NCHAR/ NVARCHAR2 |
|
|---|---|---|---|---|---|---|
to BINARY_FLOAT/ BINARY_DOUBLE |
yes |
yes |
yes |
no |
no |
yes |
to CHAR/ VARCHAR2 |
yes |
yes |
yes |
yes |
yes |
no |
to NUMBER |
yes |
yes |
yes |
no |
no |
yes |
to DATETIME/INTERVAL |
no |
yes |
no |
yes |
no |
no |
to RAW |
yes |
yes |
yes |
no |
yes |
no |
to NCHAR/NVARCHAR2 |
yes |
no |
yes |
yes |
yes |
yes |
警告
使用 CAST 函数转换数据类型时，需要注意以下内容：
对于以上列表中的转换，如果目标库对转换后的目标数据类型不支持，则会在目标库中执行时报错。
对于类似cast(‘6983140795139873811265107033372908879759234567’ AS number)这种超过38位精度的数字，转换后的SQL在目标库执行会存在报错或者精度错误的情况。
示例
-- 转换前Oracle SQL：
select
cast(123 as BINARY_FLOAT),
cast(123 as BINARY_DOUBLE),
cast(1 as CHAR),
cast(123 as VARCHAR2(10)),
cast('123' as NUMBER),
cast('2023-12-20' as DATE),
cast('2023-12-20 10:07:55.222' as TIMESTAMP),
cast('2-6' AS INTERVAL YEAR TO MONTH),
cast('1 10:22:22' AS INTERVAL DAY TO SECOND),
cast('313233' AS RAW(10)),
cast(123 as NCHAR(3)),
cast(123 as NVARCHAR2(3))
from dual;
CAST(123ASBINARY_FLOAT)|CAST(123ASBINARY_DOUBLE)|CAST(1ASCHAR)|CAST(123ASVARCHAR2(10))|CAST('123'ASNUMBER)|CAST('2023-12-20'ASDATE)|CAST('2023-12-2010:07:55.222'ASTIMESTAMP)|CAST('2-6'ASINTERVALYEARTOMONTH)|CAST('110:22:22'ASINTERVALDAYTOSECOND)|CAST('313233'ASRAW(10))|CAST(123ASNCHAR(3))|CAST(123ASNVARCHAR2(3))|
-----------------------+------------------------+-------------+-----------------------+-------------------+------------------------+-----------------------------------------+--------------------------------+--------------------------------------+-----------------------+-------------------+-----------------------+
123.0| 123.0|1 |123 | 123| 2023-12-20 00:00:00.000| 2023-12-20 10:07:55.222|2-6 |1 10:22:22.0 |123 |123 |123 |
-- 转换后PostgreSQL SQL：
float4|float8|bpchar|varchar|numeric|timestamp |timestamp |interval |interval |bytea |bpchar|varchar|
------+------+------+-------+-------+-----------------------+-----------------------+---------------------------------------------+------------------------------------------------+------+------+-------+
123.0| 123.0|1 |123 | 123|2023-12-20 00:00:00.000|2023-12-20 10:07:55.222|2 years 6 mons 0 days 0 hours 0 mins 0.0 secs|0 years 0 mons 1 days 10 hours 22 mins 22.0 secs|313233|123 |123 |
-- 转换前Oracle SQL:
SELECT
CAST('100.2345' AS NUMBER) AS to_nu,
CAST('100.2345' AS NUMBER(*)) AS to_nu,
CAST('100.2345' AS NUMBER(*,0)) AS to_nu,
CAST('100.2345' AS NUMBER(*,2)) AS to_nu,
CAST('100.2345' AS NUMBER(4)) AS to_nu,
CAST('100.2345' AS NUMBER(4,0)) AS to_nu,
CAST('100.2345' AS NUMBER(9)) AS to_nu,
CAST('100.2345' AS NUMBER(9,0)) AS to_nu,
CAST('100.2345' AS NUMBER(18)) AS to_nu,
CAST('100.2345' AS NUMBER(18,0)) AS to_nu,
CAST('100.2345' AS NUMBER(38)) AS to_nu,
CAST('100.2345' AS NUMBER(38,0)) AS to_nu,
CAST('100.2345' AS NUMBER(10,2)) AS to_nu,
cast('100.2345' AS decimal) AS to_dec,
cast('100.2345' AS decimal(*)) AS to_dec,
cast('100.2345' AS decimal(*,0)) AS to_dec,
cast('100.2345' AS decimal(*,2)) AS to_dec,
cast('100.2345' AS decimal(4)) AS to_dec,
cast('100.2345' AS decimal(4,0)) AS to_dec,
cast('100.2345' AS decimal(9)) AS to_dec,
cast('100.2345' AS decimal(9,0)) AS to_dec,
CAST('100.2345' AS decimal(18)) AS to_dec,
CAST('100.2345' AS decimal(18,0)) AS to_dec,
CAST('100.2345' AS decimal(38)) AS to_dec,
CAST('100.2345' AS decimal(38,0)) AS to_dec,
CAST('100.2345' AS decimal(10,2)) AS to_dec,
cast('100.2345' AS dec) AS to_dec,
cast('100.2345' AS dec(*)) AS to_dec,
cast('100.2345' AS dec(*,0)) AS to_dec,
cast('100.2345' AS dec(*,2)) AS to_dec,
cast('100.2345' AS dec(4)) AS to_dec,
cast('100.2345' AS dec(4,0)) AS to_dec,
cast('100.2345' AS dec(9)) AS to_dec,
cast('100.2345' AS dec(9,0)) AS to_dec,
CAST('100.2345' AS dec(18)) AS to_dec,
CAST('100.2345' AS dec(18,0)) AS to_dec,
CAST('100.2345' AS dec(38)) AS to_dec,
CAST('100.2345' AS dec(38,0)) AS to_dec,
CAST('100.2345' AS dec(10,2)) AS to_dec,
cast('100.2345' AS numeric) AS to_numr,
cast('100.2345' AS numeric(*)) AS to_numr,
cast('100.2345' AS numeric(*,0)) AS to_numr,
cast('100.2345' AS numeric(*,2)) AS to_numr,
cast('100.2345' AS numeric(4)) AS to_numr,
cast('100.2345' AS numeric(4,0)) AS to_numr,
cast('100.2345' AS numeric(9)) AS to_numr,
cast('100.2345' AS numeric(9,0)) AS to_numr,
CAST('100.2345' AS numeric(18)) AS to_numr,
CAST('100.2345' AS numeric(18,0)) AS to_numr,
CAST('100.2345' AS numeric(38)) AS to_numr,
CAST('100.2345' AS numeric(38,0)) AS to_numr,
CAST('100.2345' AS numeric(10,2)) AS to_numr
FROM dual
TO_NU |TO_NU |TO_NU|TO_NU |TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU |TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|
--------+--------+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100.2345|100.2345| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100| 100.23| 100| 100| 100| 100| 100| 100| 100| 100| 100.23|
-- 转换后PostgreSQL SQL：
SELECT CAST('100.2345' AS decimal) AS to_nu,CAST('100.2345' AS decimal) AS to_nu,CAST('100.2345' AS decimal(38,0)) AS to_nu,CAST('100.2345' AS decimal(38,2)) AS to_nu,CAST('100.2345' AS decimal(4)) AS to_nu,CAST('100.2345' AS decimal(4)) AS to_nu,CAST('100.2345' AS decimal(9)) AS to_nu,CAST('100.2345' AS decimal(9)) AS to_nu,CAST('100.2345' AS decimal(18)) AS to_nu,CAST('100.2345' AS decimal(18)) AS to_nu,CAST('100.2345' AS decimal(38)) AS to_nu,CAST('100.2345' AS decimal(38)) AS to_nu,CAST('100.2345' AS decimal(10,2)) AS to_nu,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,2)) AS to_dec,CAST('100.2345' AS decimal(4)) AS to_dec,CAST('100.2345' AS decimal(4)) AS to_dec,CAST('100.2345' AS decimal(9)) AS to_dec,CAST('100.2345' AS decimal(9)) AS to_dec,CAST('100.2345' AS decimal(18)) AS to_dec,CAST('100.2345' AS decimal(18)) AS to_dec,CAST('100.2345' AS decimal(38)) AS to_dec,CAST('100.2345' AS decimal(38)) AS to_dec,CAST('100.2345' AS decimal(10,2)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,2)) AS to_dec,CAST('100.2345' AS dec(4)) AS to_dec,CAST('100.2345' AS dec(4)) AS to_dec,CAST('100.2345' AS dec(9)) AS to_dec,CAST('100.2345' AS dec(9)) AS to_dec,CAST('100.2345' AS dec(18)) AS to_dec,CAST('100.2345' AS dec(18)) AS to_dec,CAST('100.2345' AS dec(38)) AS to_dec,CAST('100.2345' AS dec(38)) AS to_dec,CAST('100.2345' AS dec(10,2)) AS to_dec,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,2)) AS to_numr,CAST('100.2345' AS numeric(4)) AS to_numr,CAST('100.2345' AS numeric(4)) AS to_numr,CAST('100.2345' AS numeric(9)) AS to_numr,CAST('100.2345' AS numeric(9)) AS to_numr,CAST('100.2345' AS numeric(18)) AS to_numr,CAST('100.2345' AS numeric(18)) AS to_numr,CAST('100.2345' AS numeric(38)) AS to_numr,CAST('100.2345' AS numeric(38)) AS to_numr,CAST('100.2345' AS numeric(10,2)) AS to_numr
to_nu |to_nu |to_nu|to_nu |to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu |to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|
--------+--------+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100.2345|100.2345| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100|100.23| 100| 100| 100| 100| 100| 100| 100| 100|100.23| 100| 100| 100| 100.23| 100| 100| 100| 100| 100| 100| 100| 100| 100.23|
3.2.2.3.4.2. TO_CHAR (character)
- 语法
TO_CHAR(character)- 描述
- 该函数将 NCHAR、NVARCHAR2 或 CLOB 类型的数据转换为 VARCHAR2 数据类型。
参数解释
参数 |
说明 |
|---|---|
character |
指定要转换为 VARCHAR2 数据类型表达式，数据类型可以是 NCHAR、NVARCHAR2 或 CLOB |
示例
-- 转换前Oracle SQL：
SELECT TO_CHAR('010101') FROM DUAL;
TO_CHAR('010101')|
-----------------+
010101 |
-- 转换后PostgreSQL SQL：
SELECT CAST('010101' AS text)
text |
------+
010101|
3.2.2.3.4.3. TO_CLOB
- 语法
TO_CLOB(lob_column | char)- 描述
- 该函数将 LOB 列或其他字符串中的 NCLOB 值转换为 CLOB 值。
参数解释
参数 |
说明 |
|---|---|
lob_column |
属于 LOB 列或其他字符串中的 NCLOB 值。 |
char |
属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB 类型的值。 |
警告
不支持2个及2个以上参数的用法。
示例
-- 转换前Oracle SQL：
CREATE TABLE unisql_test_clob (c1 clob,c2 varchar2(10));
INSERT INTO unisql_test_clob VALUES (TO_CLOB('1'),'orale');
SELECT TO_CLOB(c1),TO_CLOB(c2),TO_CLOB(10) FROM unisql_test_clob;
TO_CLOB(C1)|TO_CLOB(C2)|TO_CLOB(10)|
-----------+-----------+-----------+
1 |orale |10 |
-- 转换后PostgreSQL SQL：
SELECT CAST(c1 AS text),CAST(c2 AS text),CAST(10 AS text) FROM unisql_test_clob
c1|c2 |text|
--+-----+----+
1 |orale|10 |
3.2.2.3.4.4. TO_DATE
- 语法
TO_DATE(char [, fmt])- 描述
- 该函数将 CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的字符转换为日期数据类型的值
参数解释
参数 |
说明 |
|---|---|
char |
CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的值 |
fmt |
指定 char 的时间格式 |
示例
-- 转换前Oracle SQL：
select to_date('2012/12/12', 'yyyy-MM-dd'),
to_date('2023-12-02', 'yyyy-mm-dd'),
to_date('2023-12-02 11:23:33', 'yyyy-mm-dd HH24:MI:SS')
FROM DUAL;
TO_DATE('2012/12/12','YYYY-MM-DD')|TO_DATE('2023-12-02','YYYY-MM-DD')|TO_DATE('2023-12-0211:23:33','YYYY-MM-DDHH24:MI:SS')|
----------------------------------+----------------------------------+----------------------------------------------------+
2012-12-12 00:00:00.000| 2023-12-02 00:00:00.000| 2023-12-02 11:23:33.000|
-- 转换后PostgreSQL SQL：
SELECT CAST(to_timestamp('2012/12/12', 'yyyy-MM-dd') AS timestamp),CAST(to_timestamp('2023-12-02', 'yyyy-mm-dd') AS timestamp),CAST(to_timestamp('2023-12-02 11:23:33', 'yyyy-mm-dd HH24:MI:SS') AS timestamp)
to_timestamp |to_timestamp |to_timestamp |
-----------------------+-----------------------+-----------------------+
2012-12-12 00:00:00.000|2023-12-02 00:00:00.000|2023-12-02 11:23:33.000|
3.2.2.3.4.5. TO_NUMBER
- 语法
TO_NUMBER(expr)- 描述
- 该函数功能是将 CHAR、VARCHAR2等类型的字符串转换为 NUMBER 数值数据类型的值
参数解释
参数 |
说明 |
|---|---|
expr |
属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。 |
示例
-- 转换前Oracle SQL：
SELECT TO_NUMBER('0123456'),TO_NUMBER('1.2'),TO_NUMBER('0'),TO_NUMBER(1.2),TO_NUMBER('-1'),TO_NUMBER(-1) FROM DUAL;
TO_NUMBER('0123456')|TO_NUMBER('1.2')|TO_NUMBER('0')|TO_NUMBER(1.2)|TO_NUMBER('-1')|TO_NUMBER(-1)|
--------------------+----------------+--------------+--------------+---------------+-------------+
123456| 1.2| 0| 1.2| -1| -1|
-- 转换后PostgreSQL SQL：
SELECT CAST('0123456' AS numeric),CAST('1.2' AS numeric),CAST('0' AS numeric),CAST(1.2 AS numeric),CAST('-1' AS numeric),CAST(-1 AS numeric)
numeric|numeric|numeric|numeric|numeric|numeric|
-------+-------+-------+-------+-------+-------+
123456| 1.2| 0| 1.2| -1| -1|
3.2.2.3.4.6. TO_NCHAR (character)
- 语法
TO_NCHAR(character)- 描述
- 该函数将 CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据转换为国家字符集，返回 NVARCHAR2 数据类型。
参数解释
参数 |
说明 |
|---|---|
character |
CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据 |
示例
-- 转换前Oracle SQL：
CREATE TABLE unisql_test_nchar(col1 INT,col2 VARCHAR2(20));
INSERT INTO unisql_test_nchar VALUES(1,'unisql tool');
SELECT TO_NCHAR(col1),TO_NCHAR(col2) FROM unisql_test_nchar;
TO_NCHAR(COL1)|TO_NCHAR(COL2)|
--------------+--------------+
1 |unisql tool |
-- 转换后PostgreSQL SQL：
SELECT CAST(col1 AS text),CAST(col2 AS text) FROM unisql_test_nchar
col1|col2 |
----+-----------+
1 |unisql tool|
3.2.2.3.5. 编码解码函数
3.2.2.3.5.1. DECODE
- 语法
DECODE (condition, search, result [, search, result ...][, default])- 描述
- 该函数功能是将 condition 与每个 search 依次做比较，并返回对比结果
参数解释
参数 |
说明 |
|---|---|
condition |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
search |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
result |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
default |
数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式 |
示例
-- 转换前Oracle SQL：
SELECT DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大'),
DECODE(INSTR('CLARK','S'), 0, '不含有 S', '含有 S') "CLARK",
DECODE(INSTR('KING','S'), 0, '不含有 S', '含有 S') "KING",
DECODE(INSTR('MILLER','S'), 0, '不含有 S', '含有 S') "MILLER",
DECODE(INSTR('ADAMS','S'), 0, '不含有 S', '含有 S') "ADAMS",
DECODE(INSTR('FORD','S'), 0, '不含有 S', '含有 S') "FORD",
DECODE(INSTR('JONES','S'), 0, '不含有 S', '含有 S') "JONES"
FROM DUAL;
DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大')|CLARK|KING |MILLER|ADAMS|FORD |JONES|
------------------------------------------------------------+-----+-----+------+-----+-----+-----+
(5*3-2)大 |不含有 S|不含有 S|不含有 S |含有 S |不含有 S|含有 S |
-- 转换后PostgreSQL SQL：
SELECT CASE WHEN SIGN((5*3-2)-(3*4-1))=0 THEN '相等' WHEN SIGN((5*3-2)-(3*4-1))=1 THEN '(5*3-2)大' ELSE '(3*4-1)大' END,CASE WHEN strpos('CLARK', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "CLARK",CASE WHEN strpos('KING', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "KING",CASE WHEN strpos('MILLER', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "MILLER",CASE WHEN strpos('ADAMS', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "ADAMS",CASE WHEN strpos('FORD', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "FORD",CASE WHEN strpos('JONES', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "JONES"
case |CLARK|KING |MILLER|ADAMS|FORD |JONES|
--------+-----+-----+------+-----+-----+-----+
(5*3-2)大|不含有 S|不含有 S|不含有 S |含有 S |不含有 S|含有 S |
3.2.2.3.6. 空值函数
3.2.2.3.6.1. NVL
- 语法
NVL(expr1, expr2)- 描述
- 该函数从两个表达式返回一个非空值。如果 expr1 与 expr2 的结果都为空值，则 NVL 函数返回 NULL
参数解释
参数 |
说明 |
|---|---|
expr1 |
指定第一个参数，数据类型可以是数据库内建数据类型中的任何数据类型。 |
expr2 |
指定第二个参数，数据类型可以是数据库内建数据类型中的任何数据类型。 |
示例
-- 转换前Oracle SQL：
SELECT NVL(10,'1'),NVL(NULL,1),NVL(0/1,1) FROM DUAL;
NVL(10,'1')|NVL(NULL,1)|NVL(0/1,1)|
-----------+-----------+----------+
10| 1| 0|
-- 转换后PostgreSQL SQL：
SELECT coalesce(10, '1'),coalesce(NULL, 1),coalesce(0/1, 1)
coalesce|coalesce|coalesce|
--------+--------+--------+
10| 1| 0|
3.2.2.3.7. 环境和标识符函数
3.2.2.3.7.1. SYS_GUID
- 语法
SYS_GUID()- 描述
- 该函数生成并返回由 16 个字节组成的全局唯一标识符，即生成一个全局唯一序列号
参数解释
无
示例
警告
结果格式或表现存在不一致的情况。如果对数据完全一致要求较高，建议不使用该特性或对结果进一步处理。
-- 转换前Oracle SQL：
SELECT SYS_GUID() FROM DUAL;
SYS_GUID() |
----------------+
íx OH xàc) çt|
-- 转换后PostgreSQL SQL：
SELECT gen_random_uuid()
gen_random_uuid |
------------------------------------+
6b846107-4402-46da-832f-c9738a6e029e|
3.2.2.3.8. 聚合函数
3.2.2.3.8.1. LISTAGG
- 语法
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)- 描述
- 该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值
参数解释
参数 |
说明 |
|---|---|
measure_expr |
可以是任何表达式。度量列中的空值将被忽略。 |
delimiter |
指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。 |
示例
-- 转换前Oracle SQL：
SELECT LISTAGG(name, '; ') WITHIN GROUP (ORDER BY employee_id,name) AS rk FROM unisql_employee WHERE department_id=1;
RK |
----------------+
JACK; TOM; LINDA|
-- 转换后PostgreSQL SQL：
SELECT STRING_AGG(name, '; ' ORDER BY employee_id,name) AS rk FROM unisql_employee WHERE department_id=1
rk |
----------------+
JACK; TOM; LINDA|
-- 使用语法词ON OVERFLOW TRUNCATE/ON OVERFLOW ERROR
CREATE TABLE listagg_test_employees (
emp_id int,
emp_name VARCHAR(100),
department_id int
);
-- 插入数据
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (1, 'John Doe', 1);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (2, 'Jane Smith', 1);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (3, 'Mark Johnson', 2);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (4, 'Emily Davis', 2);
SELECT LISTAGG(emp_name ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW TRUNCATE) over(partition by department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name ON OVERFLOW ERROR) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW ERROR) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW ERROR) over(partition by department_id) rk FROM listagg_test_employees;
3.2.2.3.8.2. MEDIAN
- 语法
MEDIAN(expr) [ OVER (query_partition_clause) ]- 描述
- 该函数用于返回一组数值的中值，即将一组数值排序后返回居于中间的数值。如果参数集合中包含偶数个数值，该函数将返回位于中间的两个数的平均值。可以将其用作聚合或分析函数。
说明
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。
参数解释
参数 |
说明 |
|---|---|
expr |
指定要求中值的数组名称，参数类型属于数值数据类型或可以隐式转换为数字数据类型。 |
OVER |
使用 OVER 子句定义窗口进行计算。详细信息请参见 分析函数说明。 |
示例
-- 转换前Oracle SQL：
SELECT department_id, MEDIAN(salary) FROM unisql_employee ke GROUP BY department_id;
DEPARTMENT_ID|MEDIAN(SALARY)|
-------------+--------------+
1| 10000|
2| 25000|
3| 50000|
-- 转换后PostgreSQL SQL：
SELECT department_id,percentile_cont(5e-01) WITHIN GROUP (ORDER BY salary) FROM unisql_employee AS ke GROUP BY department_id
department_id|percentile_cont|
-------------+---------------+
1| 10000.0|
2| 25000.0|
3| 50000.0|