8.2. 内置函数

本节主要介绍统一SQL Oracle2DM 函数转换前后的情况

8.2.1. 数字函数

请参考Oracle2LightDB-Oracle：

数字函数

8.2.2. 字符串函数

请参考Oracle2LightDB-Oracle：

字符串函数

以下是和Oracle2LightDB-Oracle有差异的部分函数：

8.2.2.1. INSTR

语法
INSTR(str,substr)
描述
返回字符串 str 中子字符串的第一个出现位置

参数解释

参数

说明

str

要操作的字符串

substr

子字符串

警告

对于该函数，在Oracle中字符串匹配区分大小写，达梦中不区分大小写，在使用时请特别注意。

示例

-- 转换前Oracle SQL：
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;
--+--+--+--+--+--+--+--+--+
I1|I2|I3|I4|I5|I6|I7|I8|I9|
--+--+--+--+--+--+--+--+--+
1| 0| 2| 0|  |  | 4| 0| 1|


-- 转换后达梦 SQL：
SELECT INSTR('Unisql', 'U') AS i1,INSTR('Unisql', 'u') AS i2,INSTR('Unisql', 'n') AS i3,INSTR('Unisql', 'a') AS i4,INSTR('Unisql', '') AS i5,INSTR('Unisql', NULL) AS i6,INSTR('foobarbar', 'bar') AS i7,INSTR('xbar', 'foobar') AS i8,INSTR('▒▒ЙЕ╗ХМГ─с', '▒▒') AS i9 FROM DUAL
i1|i2|i3|i4|i5|i6|i7|i8|i9|
--+--+--+--+--+--+--+--+--+
1| 1| 2| 0|  |  | 4| 0| 1|

8.2.3. 时间日期函数

请参考Oracle2LightDB-Oracle：

时间日期函数

以下是和Oracle2LightDB-Oracle有差异的部分函数：

8.2.3.1. ADD_MONTHS

语法
ADD_MONTHS(date, n)
描述
该函数功能是返回日期 date 加上 n 个月后的日期值。

参数解释

参数

说明

date

指定日期。该参数为 DATE 数据类型。

n

整数或可以转换为一个整数的任意值。NUMBER 数据类型。

警告

在 Oracle 中，DATE 类型包含日期和时间信息，精确到秒。虽然它具有时间部分，但通常在显示和处理时可能会截断为整数秒。但实际上，DATE 类型存储了时分秒的信息。

当你从 DATE 类型中检索数据时，如果没有特殊设置，可能只会看到日期和部分时间信息（如 HH:MM:SS）被格式化为整数或特定的格式。

你可以使用适当的函数或转换方法来显示或提取 DATE 类型的时间信息。

在达梦中，DATE 类型只包含日期而不包含时间信息，所以在检索数据时，看到的数据格式可能会不一致。

示例

-- 转换前Oracle SQL：
SELECT
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD') ,-3) AS a1,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 3)AS a2,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.1)AS a3,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.5)AS a4,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.8)AS a5,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 0)AS a6
FROM DUAL;
A1                     |A2                     |A3                     |A4                     |A5                     |A6                     |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-21 00:00:00.000|2024-03-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2023-12-21 00:00:00.000|

-- 转换后达梦 SQL：
SELECT ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), -3) AS a1,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 3) AS a2,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 1.1) AS a3,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 1.5) AS a4,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 1.8) AS a5,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 0) AS a6 FROM DUAL
a1        |a2        |a3        |a4        |a5        |a6        |
----------+----------+----------+----------+----------+----------+
2023-09-21|2024-03-21|2024-01-21|2024-02-21|2024-02-21|2023-12-21|

8.2.3.2. CURRENT_DATE

语法
CURRENT_DATE
描述
该函数返回当前会话时区的当前日期

参数解释

无

示例

-- 转换前Oracle SQL：
SELECT CURRENT_DATE FROM DUAL;
CURRENT_DATE           |
-----------------------+
2023-12-20 09:34:47.000|

-- 转换后达梦 SQL：
SELECT CURRENT_DATE FROM DUAL;
CURRENT_DATE|
------------+
  2023-12-28|

8.2.3.3. CURRENT_TIMESTAMP

语法
CURRENT_TIMESTAMP
描述
该函数返回当前会话时区中的当前日期

参数解释

无

示例

-- 转换前Oracle SQL：
SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP            |
-----------------------------+
2023-12-20 09:36:55.612 +0800|


-- 转换后达梦 SQL：
SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP                |
---------------------------------+
2023-12-28 10:49:45.719664 +08:00|

8.2.3.4. 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|


-- 转换后达梦 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|                                  2023-09-30|

8.2.4. 通用比较函数

请参考Oracle2LightDB-Oracle：

通用比较函数

8.2.5. 转换函数

请参考Oracle2LightDB-Oracle：

转换函数

以下是和Oracle2LightDB-Oracle有差异的部分函数：

8.2.5.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 NUMBER

yes

yes

yes

no

no

yes

警告

使用 CAST 函数转换数据类型时，需要注意以下内容：

  • 对于以上列表中的转换，如果目标库对转换后的目标数据类型不支持，则会在目标库中执行时报错。

  • 对于类似cast(‘6983140795139873811265107033372908879759234567’ AS number)这种超过38位精度的数字，转换后的SQL在目标库执行会存在报错或者精度错误的情况。

示例

-- 转换前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|

-- 转换后达梦 SQL：
SELECT CAST('100.2345' AS number) AS to_nu,CAST('100.2345' AS number) AS to_nu,CAST('100.2345' AS number(38,0)) AS to_nu,CAST('100.2345' AS number(38,2)) AS to_nu,CAST('100.2345' AS number(4)) AS to_nu,CAST('100.2345' AS number(4)) AS to_nu,CAST('100.2345' AS number(9)) AS to_nu,CAST('100.2345' AS number(9)) AS to_nu,CAST('100.2345' AS number(18)) AS to_nu,CAST('100.2345' AS number(18)) AS to_nu,CAST('100.2345' AS number(38)) AS to_nu,CAST('100.2345' AS number(38)) AS to_nu,CAST('100.2345' AS number(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 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|

8.2.5.2. NUMTODSINTERVAL

语法
NUMTODSINTERVAL(number, 'interval_unit')
描述
该函数作用是将一个数值表达式加时间间隔单位转换为一个 INTERVAL DAY TO SECOND 数据类型的值。可以用来对一个日期时间值进行加减计算。

参数解释

参数

说明

number

指定间隔数量，是一个 NUMBER 值或可以隐式转换为 NUMBER 值的表达式。

interval_unit

指定间隔单位，可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的值，不区分大小写。默认情况下，返回的精度为 9

interval_unit的取值如下：

  • DAY, 表示天。

  • HOUR, 表示小时。

  • MINUTE ,表示分钟。

  • SECOND, 表示秒。

示例

-- 转换前Oracle SQL：
SELECT NUMTODSINTERVAL(10,'day'),
NUMTODSINTERVAL(10,'HOUR'),
NUMTODSINTERVAL(10,'MINUTE'),
NUMTODSINTERVAL(10,'SECOND')
FROM DUAL;
NUMTODSINTERVAL(10,'DAY')|NUMTODSINTERVAL(10,'HOUR')|NUMTODSINTERVAL(10,'MINUTE')|NUMTODSINTERVAL(10,'SECOND')|
-------------------------+--------------------------+----------------------------+----------------------------+
10 0:0:0.0               |0 10:0:0.0                |0 0:10:0.0                  |0 0:0:10.0                  |


-- 转换后达梦 SQL：
SELECT NUMTODSINTERVAL(10, 'day'),NUMTODSINTERVAL(10, 'HOUR'),NUMTODSINTERVAL(10, 'MINUTE'),NUMTODSINTERVAL(10, 'SECOND') FROM DUAL
NUMTODSINTERVAL(10,'day')                               |NUMTODSINTERVAL(10,'HOUR')                              |NUMTODSINTERVAL(10,'MINUTE')                            |NUMTODSINTERVAL(10,'SECOND')                            |
--------------------------------------------------------+--------------------------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
INTERVAL '000000010 00:00:00.000000' DAY(9) TO SECOND(6)|INTERVAL '000000000 10:00:00.000000' DAY(9) TO SECOND(6)|INTERVAL '000000000 00:10:00.000000' DAY(9) TO SECOND(6)|INTERVAL '000000000 00:00:10.000000' DAY(9) TO SECOND(6)|

8.2.5.3. NUMTOYMINTERVAL

语法
NUMTOYMINTERVAL(number, 'interval_unit')
描述
该函数作用是将一个数值表达式加时间间隔单位转换为一个 INTERVAL DAY TO SECOND 数据类型的值。可以用来对一个日期时间值进行加减计算。

参数解释

参数

说明

number

指定间隔数量，是一个 NUMBER 值或可以隐式转换为 NUMBER 值的表达式。

interval_unit

指定间隔单位，可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的值，不区分大小写。默认情况下，返回的精度为 9。 间隔单位：YEAR 表示年。MONTH 表示月。

示例

-- 转换前Oracle SQL：
SELECT NUMTOYMINTERVAL(1,'YEAR'),
NUMTOYMINTERVAL(1,'MONTH'),
SYSDATE+NUMTOYMINTERVAL(1,'YEAR'),
SYSDATE+NUMTOYMINTERVAL(1,'MONTH')
FROM DUAL;
NUMTOYMINTERVAL(1,'YEAR')|NUMTOYMINTERVAL(1,'MONTH')|SYSDATE+NUMTOYMINTERVAL(1,'YEAR')|SYSDATE+NUMTOYMINTERVAL(1,'MONTH')|
-------------------------+--------------------------+---------------------------------+----------------------------------+
1-0                      |0-1                       |          2024-12-20 13:40:28.000|           2024-01-20 13:40:28.000|


-- 转换后达梦 SQL：
SELECT NUMTOYMINTERVAL(1, 'YEAR'),NUMTOYMINTERVAL(1, 'MONTH'),sysdate+NUMTOYMINTERVAL(1, 'YEAR'),sysdate+NUMTOYMINTERVAL(1, 'MONTH') FROM DUAL
NUMTOYMINTERVAL(1,'YEAR')               |NUMTOYMINTERVAL(1,'MONTH')              |SYSDATE+NUMTOYMINTERVAL(1,'YEAR')|SYSDATE+NUMTOYMINTERVAL(1,'MONTH')|
----------------------------------------+----------------------------------------+---------------------------------+----------------------------------+
INTERVAL '000000001-00' YEAR(9) TO MONTH|INTERVAL '000000000-01' YEAR(9) TO MONTH|          2024-12-28 10:58:52.000|           2024-01-28 10:58:52.000|

8.2.5.4. TO_BLOB

语法
TO_BLOB( raw_value )
描述
该函数将 LONG RAW 值和 RAW 值转换为 BLOB 值

参数解释

参数

说明

raw_value

LONG RAW 或 RAW 类型的值

示例

-- 转换前Oracle SQL：
SELECT LENGTH('FFAABB'),LENGTHB(to_blob('FFAABB')) FROM dual
LENGTH('FFAABB')|LENGTHB(to_blob('FFAABB'))|
----------------+--------------------------+
               6|                         3|

-- 转换后达梦 SQL：
SELECT LENGTH('FFAABB'),LENGTHB(to_blob('FFAABB')) FROM dual
LENGTH('FFAABB')|LENGTHB(to_blob('FFAABB'))|
----------------+--------------------------+
               6|                         3|

8.2.5.5. TO_NCHAR

语法
TO_NCHAR(x)
描述
该函数将 CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据转换为国家字符集，返回 NVARCHAR2 数据类型。

参数解释

参数

说明

character

CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据

示例

-- 转换前Oracle SQL:
select to_nchar('abc') from dual;
select to_nchar('1234567') from dual;
select to_nchar(col1) from to_nchar_test_1;
select to_nchar(1) from dual;
select to_nchar(to_timestamp('2023-10-31 16:38:10','YYYY-MM-DD HH24:MI:SS')) from dual;

-- 转换后达梦 SQL:
SELECT CAST('abc' AS NVARCHAR2) FROM dual
SELECT CAST('1234567' AS NVARCHAR2) FROM dual
SELECT CAST(col1 AS NVARCHAR2) FROM to_nchar_test_1
SELECT CAST(1 AS NVARCHAR2) FROM dual
SELECT CAST(to_timestamp('2023-10-31 16:38:10', 'YYYY-MM-DD HH24:MI:SS') AS NVARCHAR2) FROM dual

8.2.5.6. RAWTOHEX

该函数将一个字节数组转换为十六进制字符串。

-- 转换前Oracle SQL：
SELECT RAWTOHEX(SYS_GUID()) FROM DUAL;
RAWTOHEX(SYS_GUID())            |
--------------------------------+
0D003A97D52E7EB7E0632989140A93CC|

-- 转换后达梦 SQL：
SELECT RAWTOHEX(SYS_GUID()) FROM DUAL;
RAWTOHEX(SYS_GUID())            |
--------------------------------+
32DA0CACC022B211DE93D9514F390543|

8.2.5.7. JSON_OBJECT

描述
对于 SQL 输入的一系列 key-value 对，JSON_OBJECT() 函数将其转换成一个 JSON 格式的对象，包含了前面输入的所有 key-value 对。

语法

JSON_OBJECT([KEY]'key_name': value_expr,
            [KEY]'key_name' VALUE value_expr,
            column_name_x);

示例

-- 转换前Oracle SQL:
SELECT JSON_OBJECT(
      'first_name' : a.first_name,
      'first_namet' : 'first_name',
      KEY 'full_name' VALUE a.first_name || ' ' || b.last_name,
      'last_name' VALUE a.last_name,
      KEY 'full_name_x' VALUE 'full_name_x',
      'last_name_x' VALUE 'last_name_x',
      b.employee_id,
      a.EMPLOYEE_ID,
      'salary' : 99999999
      ) FROM employee_json a,employee_json b;


-- 转换后达梦 SQL:
-- 返回的 object 对象自动对 key/value 中的 key 进行去重排序，去重时仅保留输入的最后一对 key/value
-- value为空字符串，oracle处理为null，DM处理为空字符串
SELECT json_object(
      'first_name', a.first_name,
      'first_namet', 'first_name',
      'full_name', a.first_name||' '||b.last_name,
      'last_name', a.last_name,
      'full_name_x', 'full_name_x',
      'last_name_x', 'last_name_x',
      'employee_id', b.employee_id,
      'EMPLOYEE_ID', a.EMPLOYEE_ID,
      'salary', 99999999)
      FROM employee_json a , employee_json b

8.2.6. 编码解码函数

请参考Oracle2LightDB-Oracle：

编码解码函数

以下是和Oracle2LightDB-Oracle有差异的部分函数：

8.2.7. 空值函数

请参考Oracle2LightDB-Oracle：

空值函数

8.2.8. 环境和标识符函数

请参考Oracle2LightDB-Oracle：

环境和标识符函数

8.2.8.1. SYS_GUID

语法
SYS_GUID()
描述
该函数生成并返回由 16 个字节组成的全局唯一标识符，即生成一个全局唯一序列号

参数解释

无

示例

-- 转换前Oracle SQL：
SELECT SYS_GUID(),RAWTOHEX(SYS_GUID()) FROM DUAL;
SYS_GUID()      |RAWTOHEX(SYS_GUID())            |
----------------+--------------------------------+
 yv  Û¹úàe      |0D7976950CDCB9FAE065000000000001|


-- 转换后达梦 SQL：
SELECT SYS_GUID(),RAWTOHEX(SYS_GUID()) FROM DUAL;
SYS_GUID()      |RAWTOHEX(SYS_GUID())            |
----------------+--------------------------------+
\ái,Å"² Û²ëpq  ?|71E1692CC522B2115E8B7CA6C586F50B|

8.2.8.2. USERENV

描述
该函数返回有关当前会话的信息。该函数不能在 CHECK 约束的条件下使用。

语法

USERENV('parameter')

参数解释

参数

说明

达梦是否支持

CLIENT_INFO

返回用户会话信息（最多 64 字节），应用程序可以使用 DBMS_APPLICATION_INFO 系统包存储这些信息

不支持

INSTANCE

当前实例的标识号

不支持

LANG

语言的缩写名称，是比 LANGUAGE 参数更短的格式

支持

LANGUAGE

当前会话使用的语言和区域，以及数据库字符集。格式为 language_territory.characterset

支持

SCHEMAID

Schema ID

支持

SESSIONID

审计会话标识符

不支持

SID

会话 ID

支持

ENTRYID

每个会话分配的一个唯一标识符

不支持

示例

-- 转换前Oracle SQL:
SELECT USERENV('LANGUAGE') "Language" FROM DUAL;

-- 转换后达梦 SQL:
SELECT sys_context('USERENV', 'LANGUAGE') AS "Language" FROM DUAL;

8.2.9. 层次函数

请参考Oracle2LightDB-Oracle：

层次函数

8.2.10. 聚合函数

请参考Oracle2LightDB-Oracle：

聚合函数

以下是和Oracle2LightDB-Oracle有差异的部分函数：

8.2.10.1. CUME_DIST

警告

达梦8不支持该函数用法,建议使用其他方式实现

语法

/*聚合语法*/
CUME_DIST( expr[,expr]...)
WITHIN GROUP (ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...)

/*分析语法*/
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)
描述
该函数用于计算一组值中的某个值的累积分布，返回值的范围为 (0,1]。可以将此函数用作聚合或分析函数。

8.2.10.2. DENSE_RANK

警告

达梦8不支持该函数用法,建议使用其他方式实现

语法

/*聚合语法*/
DENSE_RANK(expr [, expr ...]) WITHIN GROUP ( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]... )

/*分析语法*/
DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)
描述
该函数功能是计算有序行组中行的秩。秩的值是从 1 开始的连续整数，最大的秩值是符合查询结果的数值。具有相同值的排序标准的行接收相同的秩，相同的行数不会被记录到下个排名中。可以将其用作聚合或分析函数。

8.2.10.3. LISTAGG

语法
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)
描述
该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值

参数解释

参数

说明

measure_expr

可以是任何表达式。度量列中的空值将被忽略。

delimiter

指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。

警告

下面示例中的ON OVERFLOW TRUNCATE/ON OVERFLOW ERROR语法词在达梦中不支持。统一SQL支持转换，但是在数据库执行是会报错，请在使用时注意。

示例

-- 转换前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|

-- 转换后达梦 SQL:
SELECT LISTAGG(name, '; ') WITHIN GROUP (ORDER BY employee_id,name) AS rk FROM unisql_employee WHERE department_id=1
   rk              |
----------------+
JACK; TOM; LINDA|



-- ORA-00900: SQL 错误 [42000]: 第 1 行, 第 24 列[ON]附近出现错误:
-- 使用语法词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;

8.2.10.4. PERCENTILE_CONT

警告

达梦8不支持该函数用法,建议使用其他方式实现

语法

PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY expr [ DESC | ASC ])
[ OVER (query_partition_clause) ]
描述
该函数用于计算给定百分位数，它根据指定的百分比插值计算出连续型列的值。

8.2.10.5. PERCENTILE_DISC

警告

达梦8不支持该函数用法,建议使用其他方式实现

语法

PERCENTILE_DISC(expr1) WITHIN GROUP (ORDER BY expr2 [ DESC | ASC ])
[ OVER (query_partition_clause) ]
描述
该函数用于计算给定百分位数，它返回在排序后的数据集中，指定百分位数位置的实际值。

8.2.10.6. RANK

警告

达梦8不支持聚合用法,建议使用其他方式实现

语法

/*聚合语法*/
RANK(expr [, expr ]...) WITHIN GROUP( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [, expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...)

/*分析语法*/
RANK() OVER ([ query_partition_clause ] order_by_clause)
描述
该函数用于计算一个值在一组值的排名。当有相同排序值时，将会有相同的排名，并且值相同的行数会被记录到下个排名中。可以将此函数用作聚合或分析函数

8.2.11. 分析函数

请参考Oracle2LightDB-Oracle：

分析函数

以下是和Oracle2LightDB-Oracle有差异的部分函数：

8.2.11.1. LISTAGG

语法
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)
描述
该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值

参数解释

参数

说明

measure_expr

可以是任何表达式。度量列中的空值将被忽略。

delimiter

指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。

警告

下面示例中的ON OVERFLOW TRUNCATE/ON OVERFLOW ERROR语法词在达梦中不支持。统一SQL支持转换，但是在数据库执行是会报错，请在使用时注意。

示例

-- 转换前Oracle SQL：
SELECT LISTAGG(name, '; ') over(partition by department_id) rk FROM unisql_employee ke;
RK              |
----------------+
JACK; TOM; LINDA|
JACK; TOM; LINDA|
JACK; TOM; LINDA|
ADA; TINA       |
ADA; TINA       |
KATE            |

-- 转换后达梦 SQL:
SELECT LISTAGG(name, '; ') OVER (PARTITION BY department_id) AS rk FROM unisql_employee AS ke
rk              |
----------------+
JACK; TOM; LINDA|
JACK; TOM; LINDA|
JACK; TOM; LINDA|
ADA; TINA       |
ADA; TINA       |
KATE            |


-- 使用语法词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;

8.2.11.2. PERCENTILE_CONT

警告

达梦8不支持该函数用法,建议使用其他方式实现

语法

PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY expr [ DESC | ASC ])
[ OVER (query_partition_clause) ]
描述
该函数用于计算给定百分位数，它根据指定的百分比插值计算出连续型列的值。

8.2.11.3. PERCENTILE_DISC

警告

达梦8不支持该函数用法,建议使用其他方式实现

语法

PERCENTILE_DISC(expr1) WITHIN GROUP (ORDER BY expr2 [ DESC | ASC ])
[ OVER (query_partition_clause) ]
描述
该函数用于计算给定百分位数，它返回在排序后的数据集中，指定百分位数位置的实际值。

8.2.12. 序列、伪列

请参考Oracle2LightDB-Oracle：

序列、伪列

以下是和Oracle2LightDB-Oracle有差异的部分函数：

8.2.12.1. ROWID

伪列

-- 转换前Oracle SQL：
SELECT ROWID,ke.* FROM unisql_employee ke;
ROWID             |EMPLOYEE_ID|NAME |SALARY|DEPARTMENT_ID|HIRE_DATE |COMMISSION_PCT|
------------------+-----------+-----+------+-------------+----------+--------------+
AAA2fQAAMAAIbD0AAA|          1|JACK |  5000|            1|2023-01-01|          0.05|
AAA2fQAAMAAIbD0AAB|          2|TOM  | 10000|            1|2023-02-01|          0.15|
AAA2fQAAMAAIbD0AAC|          3|LINDA| 15000|            1|2023-03-01|           0.2|
AAA2fQAAMAAIbD0AAD|          4|ADA  | 20000|            2|2023-04-01|           0.1|
AAA2fQAAMAAIbD0AAE|          5|TINA | 30000|            2|2023-05-01|           0.2|
AAA2fQAAMAAIbD0AAF|          6|KATE | 50000|            3|2023-06-01|           0.3|


-- 转换后达梦 SQL:
SELECT ROWID,ke.* FROM unisql_employee AS ke
ROWID|employee_id|name |salary|department_id|hire_date |commission_pct|
-----+-----------+-----+------+-------------+----------+--------------+
   1|          1|JACK |  5000|            1|2023-01-01|          0.05|
   2|          2|TOM  | 10000|            1|2023-02-01|          0.15|
   3|          3|LINDA| 15000|            1|2023-03-01|           0.2|
   4|          4|ADA  | 20000|            2|2023-04-01|           0.1|
   5|          5|TINA | 30000|            2|2023-05-01|           0.2|
   6|          6|KATE | 50000|            3|2023-06-01|           0.3|