全部產品
Search

搜尋本產品

    ：Oracle相容函數

    文件中心

    ：Oracle相容函數

    更新時間：Jun 30, 2024

    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