AI 助理
备案控制台
文档
输入文档关键字查找
首页

Oracle兼容函数

更新时间：
产品详情
相关技术圈
我的收藏

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函数

Hologres已支持的Oracle函数列表如下。

  • Date函数

    函数名称

    描述

    用例

    结果

    add_months(day date, value int)

    返回第一个月份参数加上第二个月份参数，返回类型为DATE。

    SELECT add_months(current_date, 2);

    2022-05-21

    oracle_last_day(value date)

    返回该日期所在月份的最后一天，返回类型为DATE。

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

    2022-05-31

    next_day(value date, weekday text)

    返回开始日期后的下一个星期几所在的日期。参数一：开始的日期。参数二：包含星期几的英文字符串，如Friday。

    SELECT next_day('2022-05-01', 'FRIDAY');

    2022-05-06

    next_day(value date, weekday integer)

    返回开始日期后的下一个星期几所在的日期。参数一：开始的日期。参数二：星期几的数字，取值为1到7，1为星期日，2为星期一，以此类推。

    • SELECT next_day('2022-05-01', 1);
    • SELECT next_day('2022-05-01', 2);

    • 2022-05-08

    • 2022-05-02

    months_between(date1 date, date2 date)

    返回date1和date2之间的月数。如果date1晚于date2，结果为正。如果date1早于date2，结果为负。

    • SELECT months_between('2022-01-01', '2021-11-01');
    • SELECT months_between('2021-11-01', '2022-01-01');

    • 2

    • -2

    trunc(value timestamp with time zone, fmt text)

    返回被截取的时间。参数一：要被截断的timestamp。参数二：应用于截断的度量单位。Y：截断成日期年份的第一天。Q：返回季度的第一天。

    • SELECT TRUNC(current_date,'Y');
    • SELECT TRUNC(current_date,'Q');

    • 2022-01-01

    • 2022-01-01

    trunc(value timestamp with time zone)

    返回被截取的时间，默认截断时分秒。

    SELECT TRUNC('2022-03-01'::timestamp);

    2022-03-01 00:00:00

    round(value timestamp with time zone, fmt text)

    将timestamp圆整到最近的unit_of_measure（日，周等）。

    SELECT round('2022-02-22 13:11:22'::timestamp, 'YEAR');

    2022-01-01 00:00:00

    round(value timestamp with time zone)

    默认圆整到天。

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

    2022-02-23 00:00:00

    round(value date, fmt text)

    参数类型为date。

    • SELECT round(TO_DATE('27-OCT-00','DD-MON-YY'), 'YEAR');
    • SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'), 'YEAR');

    • 2001-01-01

    • 2000-01-01

    round(value date)

    参数类型为date。

    SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'));

    2000-02-27

  • String函数

    函数名称

    描述

    用例

    结果

    instr(str text, patt text, start integer, nth integer)

    在一个字符串中搜索一个substring，若搜索到则返回substring在字符串中位置，若没有搜索到，则返回0。start：搜索的起始位置。nth：搜索第几次出现的位置。

    • SELECT instr('Hologres', 'o',1,2);
    • SELECT instr('Hologres', 'o',1,1);

    • 4

    • 2

    instr(str text, patt text, start integer)

    未提供nth参数，默认是第一次出现的位置。

    SELECT instr('Hologres', 'o',4);

    4

    instr(str text, patt text)

    未提供start参数，默认从头开始搜索。

    SELECT instr('Hologres', 'o');

    2

    plvstr.rvrs(str text, start integer, end integer)

    对输入字符串str从start位置到end位置进行逆序。

    SELECT plvstr.rvrs('Hologres', 5,6);

    rg

    plvstr.rvrs(str text, start integer)

    对输入字符串str从start位置到字符串结束进行逆序。

    SELECT plvstr.rvrs('Hologres', 4);

    sergo

    plvstr.rvrs(str text)

    逆序整个字符串。

    SELECT plvstr.rvrs('Hologres');

    sergoloH

    substr(str text, start integer)

    从start字符开始到字符串结束，获取输入字符串str的子串。

    • SELECT substr('Hologres', 1);
    • SELECT substr('Hologres', 4);

    • Hologres

    • ogres

    substr(str text, start integer, len integer)

    从start字符开始，获取长度为len字符的输入字符串str的子串。

    • SELECT substr('Hologres', 5, 5);
    • SELECT substr('Oracle函数', 5, 5);

    • gres

    • le函数

  • 其他函数

    函数名称

    描述

    用例

    结果

    listagg(str text)

    将文本值聚合为一个字符串。

    SELECT listagg(t) FROM (VALUES('holo'), ('gres')) as l(t);

    hologres

    listagg(str text, str text)

    将文本值聚集成一个串，第二个参数执行了分割符。

    SELECT listagg(t, '.') FROM (VALUES('holo'), ('gres')) as l(t);

    holo.gres

    concat(str text, str text)

    将两个字符串拼接在一起。

    SELECT concat('holo','gres');

    hologres

    • concat(str text, anyarray)

    • concat(anyarray, str text)

    • concat(anyarray, anyarray)

    拼接任意类型的数据。

    • SELECT concat('hologres', 123);
    • SELECT concat(123, 123);
    • SELECT concat(current_date, 123);

    • hologres123

    • 123123

    • 2022-03-21123

    • nanvl(num real, num real)

    • nanvl(num decimal, num decimal)

    如果第一个参数为数值类型，则返回第一个参数；如果不为数值类型，则返回第二参数。

    • SELECT nanvl('NaN', 1.1);
    • SELECT nanvl('1.2', 1.1);

    • 1.1

    • 1.2

    bitand(num bigint, num bigint)

    将两个整型的二进制进行与操作，并返回与之后的结果，只输出一行。

    • SELECT bitand(1,3);
    • SELECT bitand(2,6);
    • SELECT bitand(4,6);

    • 1

    • 2

    • 4

    nvl2(anyelement, anyelement, anyelement)

    如果第一个参数不为null，那么返回第二个参数；如果第一个参数为null，则返回第三个参数。

    • SELECT nvl2(null, 1, 2);
    • SELECT nvl2(0, 1, 2);

    • 2

    • 1

    lnnvl(boolean)

    如果参数为null或者false，则返回true；如果为true，则返回false。

    • SELECT lnnvl(null);
    • SELECT lnnvl(false);
    • SELECT lnnvl(true);

    • t

    • t

    • f

    sinh(num double precision)

    双曲正弦值。

    SELECT sinh(0.1);

    0.100166750019844

    tanh(num double precision)

    双曲正切值。

    SELECT tanh(3);

    0.995054753686731

    cosh(num double precision)

    双曲余弦值。

    SELECT cosh(0.2);

    1.02006675561908

    decode(expression, value, return [,value,return]... [, default])

    在表达式中寻找一个搜索值，搜索到则返回指定的值；如果没有搜索到，返回默认值。

    CREATE TABLE t1(id int, name varchar(20));
INSERT INTO t1 values(1,'alibaba');
INSERT INTO t1 values(2,'hologres');
    • SELECT decode(id, 1, 'alibaba', 2, 'hologres', 'not found') from t1;
    • SELECT decode(id, 3, 'alibaba', 4, 'hologres', 'not found') from t1;
    • hologres
alibaba
    • not found
not found

    dump(anyelement)

    返回一个文本值，该文本中包含第一个参数的数据类型代码、以字节计的长度和内部表示。

    SELECT dump('hologres');

    Typ=25 Len=12: 48,0,0,0,104,111,108,111,103,114,101,115

    dump(anyelement, num integer)

    第二个参数表示返回文本值内部表示的进位制。

    • SELECT dump('hologres', 10);
    • SELECT dump('hologres', 16);

    • Typ=25 Len=12: 48,0,0,0,104,111,108,111,103,114,101,115

    • Typ=25 Len=12: 30,0,0,0,68,6f,6c,6f,67,72,65,73

    nlssort(str text, str text)

    指定排序规则的排序数据函数。

    创建表格并插入数据。

    CREATE TABLE t1 (name text); INSERT INTO t1 VALUES('Anne'), ('anne'), ('Bob'), ('bob');
    • SELECT * FROM t1 ORDER BY nlssort(name, 'en_US.UTF-8');
    • SELECT*FROM t1 ORDER BY nlssort(name, 'C');
    • anne
Anne
bob
Bob
    • Anne
Bob
anne
bob