7.2. 内置函数
本节主要介绍统一SQL支持原生Oracle数据库中的函数
7.2.3. 时间日期函数
警告
在 Oracle 中，DATE 类型包含日期和时间信息，精确到秒。虽然它具有时间部分，但通常在显示和处理时可能会截断为整数秒。但实际上，DATE 类型存储了时分秒的信息。
当你从 DATE 类型中检索数据时，如果没有特殊设置，可能只会看到日期和部分时间信息（如 HH:MM:SS）被格式化为整数或特定的格式。
你可以使用适当的函数或转换方法来显示或提取 DATE 类型的时间信息。
在达梦中，DATE 类型只包含日期而不包含时间信息，所以在检索数据时，看到的数据格式可能会不一致。
7.2.3.1. ADD_MONTHS
- 语法
ADD_MONTHS(date, n)- 描述
- 该函数功能是返回日期 date 加上 n 个月后的日期值。
参数解释
参数 |
说明 |
|---|---|
date |
指定日期。该参数为 DATE 数据类型。 |
n |
整数或可以转换为一个整数的任意值。NUMBER 数据类型。 |
示例
-- 转换前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|
-- 转换后OceanBase-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 |
+-----------+-----------+-----------+-----------+-----------+-----------+
| 21-SEP-23 | 21-MAR-24 | 21-JAN-24 | 21-JAN-24 | 21-JAN-24 | 21-DEC-23 |
+-----------+-----------+-----------+-----------+-----------+-----------+
7.2.3.2. CURRENT_DATE
- 语法
CURRENT_DATE- 描述
- 该函数返回当前会话时区的当前日期
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT CURRENT_DATE FROM DUAL;
CURRENT_DATE |
-----------------------+
2023-12-27 14:18:21.000|
-- 转换后OceanBase-Oracle SQL：
SELECT CURRENT_DATE FROM DUAL;
+--------------+
CURRENT_DATE |
--------------+
27-DEC-23 |
--------------+
7.2.3.3. CURRENT_TIMESTAMP
- 语法
CURRENT_TIMESTAMP- 描述
- 该函数返回当前会话时区中的当前日期
参数解释
无
示例
-- 转换前Oracle SQL：
SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP |
-----------------------------+
2023-12-27 14:18:42.605 +0800|
-- 转换后OceanBase-Oracle SQL：
SELECT CURRENT_TIMESTAMP FROM DUAL
+-------------------------------------+
| CURRENT_TIMESTAMP |
+-------------------------------------+
| 27-DEC-23 01.27.33.635647 PM +08:00 |
+-------------------------------------+
7.2.3.4. TRUNC(date)
- 语法
TRUNC(date,[fmt])- 描述
- 该函数返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值，并且返回的日期值在 date 之前
参数解释
参数 |
说明 |
|---|---|
date |
DATE 数据类型 |
fmt |
指定了函数返回值与 date 的距离单位 |
fmt的取值如下：
fmt 参数的取值 |
说明 |
|---|---|
J |
默认值，最近 0 点日期。 |
DAY、DY、D、DDD、DD |
返回离指定日期最近的星期日。 |
MONTH、MON、MM、RM |
返回离指定日期最近的月的第一天日期。 |
Q |
返回离指定日期最近的季的日期。 |
YYYY、YYY、YY、Y |
多个 y 表示不同的精度，返回离指定日期最近的年的第一个日期。 |
CC、SCC |
返回离指定日期最近的世纪的初日期。 |
示例
-- 转换前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')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')|
------------------------------------------------------------------+-------------------------------------------------------------------+-----------------------------------------------------------------+
2022-01-01 00:00:00.000| 2022-04-01 00:00:00.000| 2022-04-19 00:00:00.000|
-- 转换后OceanBase-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')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') |
+--------------------------------------------------------------------+---------------------------------------------------------------------+-------------------------------------------------------------------+
| 01-JAN-22 | 01-APR-22 | 19-APR-22 |
+--------------------------------------------------------------------+---------------------------------------------------------------------+-------------------------------------------------------------------+
7.2.3.5. EXTRACT
- 语法
EXTRACT(unit FROM date)- 描述
- 以整数类型返回 date 的指定部分值
参数解释
参数 |
说明 |
|---|---|
date |
日期类型参数 |
示例
-- 转换前Oracle SQL：
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-09-06 12:38:40') "HOUR",
EXTRACT(MINUTE FROM TIMESTAMP '2023-09-06 12:38:40') "MINUTE",
EXTRACT(SECOND FROM TIMESTAMP '2023-09-06 12:38:40') "SECOND",
EXTRACT(DAY FROM TIMESTAMP '2023-09-06 12:38:40') "DAY",
EXTRACT(MONTH FROM TIMESTAMP '2023-09-06 12:38:40') "MONTH",
EXTRACT(YEAR FROM TIMESTAMP '2023-09-06 12:38:40') "YEAR"
FROM DUAL;
HOUR|MINUTE|SECOND|DAY|MONTH|YEAR|
----+------+------+---+-----+----+
12| 38| 40| 6| 9|2023|
-- 转换后OceanBase-Oracle SQL：
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-09-06 12:38:40') AS "HOUR",EXTRACT(MINUTE FROM TIMESTAMP '2023-09-06 12:38:40') AS "MINUTE",EXTRACT(SECOND FROM TIMESTAMP '2023-09-06 12:38:40') AS "SECOND",EXTRACT(DAY FROM TIMESTAMP '2023-09-06 12:38:40') AS "DAY",EXTRACT(MONTH FROM TIMESTAMP '2023-09-06 12:38:40') AS "MONTH",EXTRACT(YEAR FROM TIMESTAMP '2023-09-06 12:38:40') AS "YEAR" FROM DUAL
+------+--------+--------+------+-------+------+
| HOUR | MINUTE | SECOND | DAY | MONTH | YEAR |
+------+--------+--------+------+-------+------+
| 12 | 38 | 40 | 6 | 9 | 2023 |
+------+--------+--------+------+-------+------+
7.2.3.6. 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|
-- 转换后OceanBase-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')) |
+-------------------+----------------------------------------------+
| 31-DEC-23 | 30-SEP-23 |
+-------------------+----------------------------------------------+
7.2.3.7. SYSDATE
- 语法
SYSDATE- 描述
- 返回当前日期和时间
- 参数解释
- 无
示例
-- 转换前Oracle SQL：
SELECT SYSDATE FROM DUAL;
SYSDATE |
-----------------------+
2023-12-27 01:16:00.000|
-- 转换后OceanBase-Oracle SQL：
SELECT SYSDATE FROM DUAL;
+-----------+
| SYSDATE |
+-----------+
| 27-DEC-23 |
+-----------+
7.2.3.8. ROUND(date)
ROUND(date,[fmt])该函数以参数 fmt 为单位距离返回离指定日期 date 最近的时间值，根据公历的规则运算。
-- 转换前Oracle SQL：
SELECT ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'YYYY') AS yyyy,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'Q') AS q,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'MONTH') AS mont,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'DDD') AS ddd,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'HH') AS hh,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'MI') AS mi from dual;
YYYY |Q |MONT |DDD |HH |MI |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-01-01 00:00:00.000|2023-04-01 00:00:00.000|2023-04-01 00:00:00.000|2023-03-21 00:00:00.000|2023-03-20 12:00:00.000|2023-03-20 12:12:00.000|
-- 转换后OceanBase-Oracle SQL：
SELECT ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'YYYY') AS yyyy,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'Q') AS q,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'MONTH') AS mont,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'DDD') AS ddd,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'HH') AS hh,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'MI') AS mi from dual;
+-----------+-----------+-----------+-----------+-----------+-----------+
| YYYY | Q | MONT | DDD | HH | MI |
+-----------+-----------+-----------+-----------+-----------+-----------+
| 01-JAN-23 | 01-APR-23 | 01-APR-23 | 21-MAR-23 | 20-MAR-23 | 20-MAR-23 |
+-----------+-----------+-----------+-----------+-----------+-----------+
7.2.3.9. SYSTIMESTAMP
- 语法
SYSTIMESTAMP- 描述
- 该函数返回系统当前日期和时间，返回值的秒的小数位包含 3 位精度，包含当前时区信息,依赖于当前数据库服务器所在操作系统的时区
- 参数解释
- 无
示例
-- 转换前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-27 01:16:47.002 -0500|2023-12-27 01:16:47.000 -0500|2023-12-27 01:16:47.000 -0500|2023-12-27 01:16:47.000 -0500|2023-12-27 01:16:47.003 -0500|2023-12-27 01:16:47.002 -0500|2023-12-27 01:16:47.002 -0500|2023-12-27 01:16:47.002 -0500|2023-12-27 01:16:47.002 -0500|2023-12-27 01:16:47.002 -0500|2023-12-27 01:16:47.002 -0500|
-- 转换后OceanBase-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) |
+-------------------------------------+-------------------------------+--------------------------------+---------------------------------+----------------------------------+-----------------------------------+------------------------------------+-------------------------------------+--------------------------------------+---------------------------------------+----------------------------------------+
| 27-DEC-23 02.19.03.859202 PM +08:00 | 27-DEC-23 02.19.04. PM +08:00 | 27-DEC-23 02.19.03.9 PM +08:00 | 27-DEC-23 02.19.03.86 PM +08:00 | 27-DEC-23 02.19.03.859 PM +08:00 | 27-DEC-23 02.19.03.8592 PM +08:00 | 27-DEC-23 02.19.03.85920 PM +08:00 | 27-DEC-23 02.19.03.859202 PM +08:00 | 27-DEC-23 02.19.03.8592020 PM +08:00 | 27-DEC-23 02.19.03.85920200 PM +08:00 | 27-DEC-23 02.19.03.859202000 PM +08:00 |
+-------------------------------------+-------------------------------+--------------------------------+---------------------------------+----------------------------------+-----------------------------------+------------------------------------+-------------------------------------+--------------------------------------+---------------------------------------+----------------------------------------+
7.2.5. 转换函数
请参考Oracle2LightDB-Oracle：
以下是和Oracle2LightDB-Oracle有差异的部分函数：
7.2.5.1. 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 |
-- 转换后OceanBase-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') |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| +000000010 00:00:00.000000000 | +000000000 10:00:00.000000000 | +000000000 00:10:00.000000000 | +000000000 00:00:10.000000000 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
7.2.5.2. 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|
-- 转换后OceanBase-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') |
+---------------------------+----------------------------+-----------------------------------+------------------------------------+
| +000000001-00 | +000000000-01 | 27-DEC-24 | 27-JAN-24 |
+---------------------------+----------------------------+-----------------------------------+------------------------------------+
7.2.5.3. TO_BLOB
- 语法
TO_BLOB( raw_value )- 描述
- 该函数将 LONG RAW 值和 RAW 值转换为 BLOB 值
参数解释
参数 |
说明 |
|---|---|
raw_value |
LONG RAW 或 RAW 类型的值 |
示例
-- create table
DROP TABLE unisql_test_blob;
CREATE TABLE unisql_test_blob (c1 RAW(100));
INSERT INTO unisql_test_blob (c1) VALUES (HEXTORAW('FFAABB'));
INSERT INTO unisql_test_blob (c1) VALUES (HEXTORAW('112233'));
-- 转换前Oracle SQL：
SELECT LENGTH(c1) "LENGTH_RAW",LENGTH(TO_BLOB(c1)) "LENGTH_BLOB" FROM unisql_test_blob
LENGTH_RAW|LENGTH_BLOB|
----------+-----------+
6| 3|
6| 3|
-- 转换后OceanBase-Oracle SQL:
SELECT LENGTH(c1) "LENGTH_RAW",LENGTH(TO_BLOB(c1)) "LENGTH_BLOB" FROM unisql_test_blob
+------------+-------------+
| LENGTH_RAW | LENGTH_BLOB |
+------------+-------------+
| 6 | 3 |
| 6 | 3 |
+------------+-------------+
7.2.5.4. TO_CLOB
- 语法
TO_CLOB(lob_column | char)- 描述
- 该函数将 LOB 列或其他字符串中的 NCLOB 值转换为 CLOB 值。
参数解释
参数 |
说明 |
|---|---|
lob_column |
属于 LOB 列或其他字符串中的 NCLOB 值。 |
char |
属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB 类型的值。 |
示例
CREATE TABLE unisql_test_clob (c1 clob,c2 varchar2(10));
INSERT INTO unisql_test_clob VALUES (TO_CLOB('1'),'orale');
-- 转换前Oracle SQL：
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 |
-- 转换后OceanBase-Oracle SQL:
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 |
+-------------+-------------+-------------+
7.2.5.5. TO_NCHAR (character)
- 语法
TO_NCHAR(character)- 描述
- 该函数将 CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据转换为国家字符集，返回 NVARCHAR2 数据类型。
参数解释
参数 |
说明 |
|---|---|
character |
CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据 |
示例
CREATE TABLE unisql_test_nchar(col1 INT,col2 VARCHAR2(20));
INSERT INTO unisql_test_nchar VALUES(1,'unisql tool');
-- 转换前Oracle SQL：
SELECT TO_NCHAR(col1),TO_NCHAR(col2) FROM unisql_test_nchar;
TO_NCHAR(COL1)|TO_NCHAR(COL2)|
--------------+--------------+
1 |unisql tool |
-- 转换后OceanBase-Oracle SQL:
SELECT TO_NCHAR(col1),TO_NCHAR(col2) FROM unisql_test_nchar;
+----------------+----------------+
| TO_NCHAR(COL1) | TO_NCHAR(COL2) |
+----------------+----------------+
| 1 | unisql tool |
+----------------+----------------+
7.2.5.6. TO_NCHAR (datetime)
- 语法
TO_NCHAR({ datetime | interval }[, fmt [, 'nlsparam' ] ])- 描述
- 该函数将 DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL YEAR TO MONTH 或 INTERVAL DAY TO SECOND 等数据类型的值从数据库字符集转换为国家字符集的数据类型
参数解释
参数 |
说明 |
|---|---|
datetime |
DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH 等数据类型的值。 |
interval |
INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH 数据类型的值 |
fmt |
指定输出格式 |
nlsparam |
用来控制返回的月份和日份所使用的语言，为可选项。 |
interval取值 |
说明 |
|---|---|
YEAR |
表示年。 |
MONTH |
表示月。 |
DAY |
表示天。 |
HOUR |
表示小时。 |
MINUTE |
表示分钟。 |
SECOND |
表示秒数。 |
示例
-- 转换前Oracle SQL:
SELECT TO_NCHAR(SYSDATE,'yyyy/mm/dd'),TO_NCHAR(SYSDATE,'DSDL') FROM DUAL;
TO_NCHAR(SYSDATE,'YYYY/MM/DD')|TO_NCHAR(SYSDATE,'DSDL') |
------------------------------+-------------------------+
2023/12/27 |2023-12-272023年12月27日 星期三|
-- 转换后OceanBase-Oracle SQL:
SELECT TO_NCHAR(SYSDATE,'yyyy/mm/dd'),TO_NCHAR(SYSDATE,'DSDL') FROM DUAL;
+--------------------------------+----------------------------------------+
| TO_NCHAR(SYSDATE,'YYYY/MM/DD') | TO_NCHAR(SYSDATE,'DSDL') |
+--------------------------------+----------------------------------------+
| 2023/12/27 | 12/27/2023Wednesday, December 27, 2023 |
+--------------------------------+----------------------------------------+
7.2.5.7. TO_NCHAR (number)
- 语法
TO_NCHAR(n)- 描述
- 该函数将 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 类型的数值转换为国家字符集中的字符串。
参数解释
参数 |
说明 |
|---|---|
n |
属于 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。 |
示例
-- 转换前Oracle SQL：
SELECT TO_NCHAR(123456789.123) FROM DUAL;
TO_NCHAR(123456789.123)|
-----------------------+
123456789.123 |
-- 转换后OceanBase-Oracle SQL:
SELECT TO_NCHAR(123456789.123) FROM DUAL;
+-------------------------+
| TO_NCHAR(123456789.123) |
+-------------------------+
| 123456789.123 |
+-------------------------+
7.2.8. 环境和标识符函数
请参考Oracle2LightDB-Oracle：
以下是和Oracle2LightDB-Oracle有差异的部分函数：
警告
Oracle和OceanBase-Oracle返回的结果不一致，Oracle返回的是raw类型，可以使用RAWTOHEX函数转换成具有可读性的结果。
7.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|
-- 转换后OceanBase-Oracle SQL:
SELECT SYS_GUID(),RAWTOHEX(SYS_GUID()) FROM DUAL;
+----------------------------------+----------------------------------+
| SYS_GUID() | RAWTOHEX(SYS_GUID()) |
+----------------------------------+----------------------------------+
| 4FCAB411A48711EEB238005056A15AB3 | 4FCAB41AA48711EEB238005056A15AB3 |
+----------------------------------+----------------------------------+
7.2.10. 聚合函数
请参考Oracle2LightDB-Oracle：
以下是和Oracle2LightDB-Oracle有差异的部分函数：
7.2.10.1. LISTAGG
- 语法
LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)- 描述
- 该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值
参数解释
参数 |
说明 |
|---|---|
measure_expr |
可以是任何表达式。度量列中的空值将被忽略。 |
delimiter |
指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。 |
警告
下面示例中的ON OVERFLOW TRUNCATE/ON OVERFLOW ERROR语法词在OceanBase-Oracle中不支持。统一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|
-- 转换后OceanBase-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|
-- ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees' at line 1
-- 使用语法词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;
7.2.11. 分析函数
请参考Oracle2LightDB-Oracle：
以下是和Oracle2LightDB-Oracle有差异的部分函数：
7.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语法词在OceanBase-Oracle中不支持。统一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|
-- 转换后OceanBase-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|
-- ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees' at line 1
-- 使用语法词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;
7.2.12. 序列、伪列
请参考Oracle2LightDB-Oracle：
以下是和Oracle2LightDB-Oracle有差异的部分函数：
7.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|
-- 转换后OceanBase-Oracle SQL:
SELECT ROWID,ke.* FROM unisql_employee ke;
rowid|employee_id|name |salary|department_id|hire_date |commission_pct|
+-------------------+-------------+-------+--------+---------------+------------+----------------+
| ROWID | EMPLOYEE_ID | NAME | SALARY | DEPARTMENT_ID | HIRE_DATE | COMMISSION_PCT |
+-------------------+-------------+-------+--------+---------------+------------+----------------+
| *AAIKAQAAAAAAAAA= | 1 | JACK | 5000 | 1 | 2023-01-01 | .05 |
| *AAIKAgAAAAAAAAA= | 2 | TOM | 10000 | 1 | 2023-02-01 | .15 |
| *AAIKAwAAAAAAAAA= | 3 | LINDA | 15000 | 1 | 2023-03-01 | .2 |
| *AAIKBAAAAAAAAAA= | 4 | ADA | 20000 | 2 | 2023-04-01 | .1 |
| *AAIKBQAAAAAAAAA= | 5 | TINA | 30000 | 2 | 2023-05-01 | .2 |
| *AAIKBgAAAAAAAAA= | 6 | KATE | 50000 | 3 | 2023-06-01 | .3 |
+-------------------+-------------+-------+--------+---------------+------------+----------------+