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. 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.3. 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.4. 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|