全部产品
Search

搜索本产品

    ：Oracle兼容函数

    文档中心

    ：Oracle兼容函数

    更新时间：Nov 29, 2023

    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