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

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