Oracle兼容函数_实时数仓 Hologres(Hologres)-阿里云帮助中心

Hologres从V1.3版本开始，通过增加orafce扩展包，引入Oracle兼容函数。本文为您介绍Oracle内置函数在Hologres中的使用方法及支持的函数详情。

安装Oracle

在使用Oracle之前，需要Superuser在数据库内执行以下语句安装扩展包才可以正常使用。一个数据库只需执行一次即可，如果创建新的数据库，还需要再次执行如下语句。

-- 加载orafce插件
CREATE extension IF NOT EXISTS orafce; 

-- 卸载orafce插件
DROP EXTENSION orafce;

说明

加载orafce插件时会自动创建多个Schema来代替Oracle中的Package，分别为dbms_alert、dbms_assert、dbms_output、dbms_pipe、dbms_random、dbms_utility、oracle、plunit、plvchr、plvdate、plvlex、plvstr、plvsubst、utl_file。在您安装Oracle前，请确保您的数据库中没有同名Schema，否则会加载失败。

Oracle函数

ADD_MONTHS

描述：为指定日期增加月份。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
ADD_MONTHS(<d> DATE, <month> INT)
```
参数说明
- d：必填，原始日期。
- month：必填，要添加到原始日期的整数。
返回值说明
返回DATE类型。

示例

SELECT ADD_MONTHS(current_date, 2);

返回结果如下。

 add_months
------------
 2024-10-05

MONTHS_BETWEEN

描述：计算两个日期之间相差的月数。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
MONTHS_BETWEEN(DATE, DATE)
```
返回值说明
返回INT类型。

示例

示例1

--返回结果：2
SELECT MONTHS_BETWEEN('2022-01-01', '2021-11-01');

示例2

--返回结果：-2
SELECT MONTHS_BETWEEN('2021-11-01', '2022-01-01');

NEXT_DAY

描述：计算指定日期后的下一个特定星期几所在的日期。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
NEXT_DAY(<d> DATE, <str> TEXT|INT)
```
参数说明
- d：必填，指定日期。
- str：必填，表示星期几的英文字符串，如Friday。也可以是表示星期几的数字，取值范围为1~7，其中1代表星期日，2代表星期一，以此类推。
返回值说明
返回DATE类型。

示例

示例1

--返回结果：2022-05-06
SELECT NEXT_DAY('2022-05-01', 'FRIDAY');

示例2

--返回结果：2022-05-06
SELECT NEXT_DAY('2022-05-01', 5);

ORACLE_LAST_DAY

描述：获取指定日期所在月份的最后一天日期，默认支持的时间范围为1925~2282年。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
ORACLE_LAST_DAY(DATE)
```
返回值说明
返回DATE类型。

示例

SELECT ORACLE_LAST_DAY('2022-05-01');

返回结果如下。

 oracle_last_day
-----------------
 2022-05-31

ROUND

描述：把日期时间圆整到最近的时间单位。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
ROUND(<d> DATE|TIMESTAMPTZ [, <str> TEXT])
```
参数说明
- d：必填，要被圆整的日期时间，如果传入的是TIMESTAMPTZ，剩余部分会置为0。
- str：可选，时间单位，如果不输入，则圆整到最近的一天。取值包含：Y表示圆整到最近的年份第一天，更多选项请参见Oracle文档。
返回值说明
返回DATE、TIMESTAMPTZ类型。

使用示例：

示例1

SELECT ROUND('2022-05-22'::date,'Y');

返回结果如下。

   round
------------
 2022-01-01

示例2

SELECT ROUND('2022-07-22'::date,'Y');

返回结果如下。

   round
------------
 2023-01-01

示例3

SELECT ROUND('2022-07-22 13:11:22'::timestamp,'Y');

返回结果如下。

        round
---------------------
 2023-01-01 00:00:00

示例4

SELECT ROUND('2022-02-22 13:11:22'::timestamp);

返回结果如下。

        round
---------------------
 2022-02-23 00:00:00

TRUNC

描述：将日期或时间戳截断到指定的时间单位。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
TRUNC(<d> DATE|TIMESTAMP [, <str> TEXT])
```
参数说明
- d：必填，要被截断的日期时间。如果传入的是TIMESTAMPTZ，剩余部分会置为0。
- str：可选，要截断到的时间单位，如果不填写，则返回原日期。取值包含：Y表示返回所属年份的第一天，Q表示返回所属季度的第一天，更多选项详情，请参见Oracle文档。
返回值说明
返回DATE、TIMESTAMPTZ类型。

示例

示例1

SELECT TRUNC('2022-05-22'::date,'Y');

返回结果如下。

   trunc
------------
 2022-01-01

示例2

SELECT TRUNC('2022-05-22 13:11:22'::timestamp,'Y');

返回结果如下。

        trunc
---------------------
 2022-01-01 00:00:00

示例3

SELECT TRUNC('2022-05-22 13:11:22'::timestamp,'Q');

返回结果如下。

        trunc
---------------------
 2022-04-01 00:00:00

示例4

SELECT TRUNC('2022-05-22 13:11:22'::timestamp);

返回结果如下。

        trunc
---------------------
 2022-05-22 00:00:00

LISTAGG

描述：将多行的同一列聚合为一个字符串。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
LISTAGG(<str1> TEXT [, <str2> TEXT])
```
参数说明
- str1：必填，要聚合的字符串表达式或列名。
- str2：可选，分割符。
返回值说明
返回TEXT类型。

示例

示例1

--返回结果：hologres
SELECT LISTAGG(t) FROM (VALUES('holo'), ('gres')) AS l(t);

示例2

--返回结果：holo.gres
SELECT LISTAGG(t, '.') FROM (VALUES('holo'), ('gres')) AS l(t);

INSTR

描述：支持在目标字符串指定区间获取子字符串（substring）的位置，若获取到则返回子字符串（substring）在字符串中位置，若没有获取到，则返回0。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
INSTR(<str> TEXT, <patt> TEXT [, <start> INTEGER, <nth> INTEGER])
```
参数说明
- str：必填，目标字符串。
- patt ：必填，待匹配的子字符串。
- start：可选，匹配的起始位置，若未配置该参数，默认从头开始匹配。
- nth：可选，匹配第几次出现的位置，若未配置该参数，默认是第一次出现的位置。
返回值说明
返回INT类型。

示例

示例1

--返回结果：4
SELECT INSTR('Hologres', 'o',1,2);

示例2

--返回结果：2
SELECT INSTR('Hologres', 'o',1,1);

示例3

--返回结果：4
SELECT INSTR('Hologres', 'o',4);

示例4

--返回结果：2
SELECT INSTR('Hologres', 'o');

PLVSTR.RVRS

描述：对字符串进行逆序。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
- 将字符串从指定开始到结束位置的字符串进行逆序。
```
PLVSTR.RVRS(<str> TEXT, <start> INTEGER, <end> INTEGER)
```
- 将字符串从指定位置开始到字符串结束进行逆序。
```
PLVSTR.RVRS(<str> TEXT, <start> INTEGER)
```
- 将整个字符串逆序。
```
PLVSTR.RVRS(<str> TEXT)
```
返回值说明
返回TEXT类型。
示例
- 示例1：将字符串从指定开始到结束位置的字符串进行逆序。
```
-- 返回结果：rg
SELECT PLVSTR.RVRS('Hologres', 5,6);
```
- 示例2：将字符串从指定位置开始到字符串结束进行逆序。
```
-- 返回结果：sergo
SELECT PLVSTR.RVRS('Hologres', 4);
```
- 示例3：将整个字符串逆序。
```
-- 返回结果：sergoloH
SELECT PLVSTR.RVRS('Hologres');
```

SUBSTR

描述
- 支持获取字符串中指定位置开始到字符串结束的子字符串。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
SUBSTR(<str> TEXT, <num_start> INTEGER)
```
  参数说明：
  - str：必填，目标字符串。
  - num_start：指定开始获取子字符串的位置。
- 支持从字符串的指定位置开始，获取指定长度的子字符串。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。
```
SUBSTR(<str> TEXT, <num_start> INTEGER, <len> INTEGER)
```
  参数说明：
  - str：必填，目标字符串。
  - num_start：指定开始获取子字符串的位置。
  - len：获取子字符串的长度。
返回值说明
返回TEXT类型。
示例
- 示例1：获取从第4位开始到字符串结束的字符串。
```
--返回结果：ogres
SELECT SUBSTR('Hologres', 4);
```
- 示例2：从第2位开始获取5位字符。
```
--返回结果：ologr
SELECT SUBSTR('Hologres', 2, 5);
```

CONCAT

描述：连接两个或更多字符串值并返回一个连续字符串。
```
CONCAT(<str1> TEXT, <str2> TEXT, ..., <strN> TEXT)
```
参数说明
str1,...,strN：这些参数代表想要连接的字符串。
说明
若参数中存在NULL，将被忽略。
返回值说明
返回TEXT类型。

示例

SELEC CONCAT('abcde', 2, NULL, 22);

返回结果如下。

concat
---------
abcde222