Oracle functions-Hologres(Hologres)-阿里云帮助中心

Starting with V1.3, Hologres supports Oracle-compatible functions through the orafce extension. This topic covers how to install the extension and describes each supported function with syntax, parameters, and examples.

Supported functions at a glance

All functions below require the orafce extension. Install the extension before use. For instructions, see Install the orafce extension.

Function	Description	Returns
`ADD_MONTHS(d, n)`	Adds n months to date d	DATE
`MONTHS_BETWEEN(d1, d2)`	Returns the number of months between two dates	INT
`NEXT_DAY(d, day)`	Returns the first occurrence of a specified weekday after date d	DATE
`ORACLE_LAST_DAY(d)`	Returns the last day of the month containing d	DATE
`ROUND(d [, fmt])`	Rounds a date or timestamp to the nearest unit	DATE or TIMESTAMPTZ
`TRUNC(d [, fmt])`	Truncates a date or timestamp to a specified unit	DATE or TIMESTAMPTZ
`LISTAGG(str1 [, str2])`	Aggregates column values across rows into a single string	TEXT
`INSTR(str, patt [, start, nth])`	Returns the position of a substring within a string	INT
`PLVSTR.RVRS(str [, start [, end]])`	Reverses all or part of a string	TEXT
`SUBSTR(str, start [, len])`	Extracts a substring from a string	TEXT
`CONCAT(str1, str2, ..., strN)`	Concatenates two or more strings	TEXT

Install the orafce extension

A superuser must run the following statement once per database before any Oracle-compatible function can be used. If you create a new database, run the statement again for that database.

-- Install the orafce extension.
CREATE EXTENSION IF NOT EXISTS orafce;

-- Uninstall the orafce extension.
DROP EXTENSION orafce;

Important

When you install orafce, Hologres automatically creates the following schemas: dbms_alert, dbms_assert, dbms_output, dbms_pipe, dbms_random, dbms_utility, oracle, plunit, plvchr, plvdate, plvlex, plvstr, plvsubst, and utl_file. If your database already has schemas with any of these names, the installation fails. Rename or drop the conflicting schemas before installing.

Oracle functions

ADD_MONTHS

Adds a specified number of months to a date.

Syntax

ADD_MONTHS(<d> DATE, <month> INT)

Parameters

Parameter	Required	Description
`d`	Yes	The original date.
`month`	Yes	The number of months to add. Use a negative value to subtract months.

Return value

Returns a value of the DATE type.

Example

Add 2 months to the current date:

SELECT ADD_MONTHS(current_date, 2);

Result:

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

MONTHS_BETWEEN

Returns the number of months between two dates.

Syntax

MONTHS_BETWEEN(DATE, DATE)

Return value

Returns a value of the INT type.

Examples

Later date minus earlier date (positive result):

SELECT MONTHS_BETWEEN('2022-01-01', '2021-11-01');
-- Result: 2

Earlier date minus later date (negative result):

SELECT MONTHS_BETWEEN('2021-11-01', '2022-01-01');
-- Result: -2

Usage notes

The result is positive if the first date is later than the second, and negative if the first date is earlier.
Swapping the two inputs reverses the sign of the result.

NEXT_DAY

Returns the date of the first specified weekday after a given date.

Syntax

NEXT_DAY(<d> DATE, <str> TEXT|INT)

Parameters

Parameter	Required	Description
`d`	Yes	The reference date.
`str`	Yes	The target weekday, specified as a name (e.g., `FRIDAY`) or as a number from 1 to 7, where 1 = Sunday, 2 = Monday, ..., 7 = Saturday.

Return value

Returns a value of the DATE type.

Examples

Get the next Friday after May 1, 2022 (using a weekday name):

SELECT NEXT_DAY('2022-05-01', 'FRIDAY');
-- Result: 2022-05-06

Get the next Friday after May 1, 2022 (using a number):

SELECT NEXT_DAY('2022-05-01', 5);
-- Result: 2022-05-06

ORACLE_LAST_DAY

Returns the last day of the month in which a date falls.

Syntax

ORACLE_LAST_DAY(DATE)

Return value

Returns a value of the DATE type.

Example

Get the last day of May 2022:

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

Result:

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

Usage notes

Supports only dates in the range 1925 to 2282. Dates outside this range are not supported.

ROUND

Rounds a date or timestamp to the nearest unit.

Syntax

ROUND(<d> DATE|TIMESTAMPTZ [, <str> TEXT])

Parameters

Parameter	Required	Description
`d`	Yes	The date or timestamp to round. If you pass a TIMESTAMPTZ value, the time portion of the result is set to `00:00:00`.
`str`	No	The rounding unit. Defaults to the nearest day if omitted. Use `Y` for the first day of the nearest year. For all supported format strings, see the Oracle documentation.

Return value

Returns a value of the DATE or TIMESTAMPTZ type.

Examples

Round a date to the nearest year (rounds down — May is before July):

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

Result:

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

Round a date to the nearest year (rounds up — July is past the midpoint):

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

Result:

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

Round a timestamp to the nearest year:

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

Result:

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

Round a timestamp to the nearest day (no format string):

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

Result:

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

TRUNC

Truncates a date or timestamp to the beginning of the specified unit.

Syntax

TRUNC(<d> DATE|TIMESTAMP [, <str> TEXT])

Parameters

Parameter	Required	Description
`d`	Yes	The date or timestamp to truncate. If you pass a TIMESTAMPTZ value, the time portion of the result is set to `00:00:00`.
`str`	No	The truncation unit. If omitted, the date is returned with the time zeroed. Use `Y` for the first day of the year and `Q` for the first day of the quarter. For all supported format strings, see the Oracle documentation.

Return value

Returns a value of the DATE or TIMESTAMPTZ type.

Examples

Truncate a date to the beginning of the year:

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

Result:

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

Truncate a timestamp to the beginning of the year:

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

Result:

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

Truncate a timestamp to the beginning of the quarter:

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

Result:

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

Truncate a timestamp with no format string (time zeroed):

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

Result:

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

LISTAGG

Aggregates values from multiple rows of a column into a single string.

Syntax

LISTAGG(<str1> TEXT [, <str2> TEXT])

Parameters

Parameter	Required	Description
`str1`	Yes	The column or string expression whose values to aggregate.
`str2`	No	The delimiter to insert between values. Defaults to no delimiter if omitted.

Return value

Returns a value of the TEXT type.

Examples

Concatenate values with no delimiter:

SELECT LISTAGG(t) FROM (VALUES('holo'), ('gres')) AS l(t);
-- Result: hologres

Concatenate values with a dot delimiter:

SELECT LISTAGG(t, '.') FROM (VALUES('holo'), ('gres')) AS l(t);
-- Result: holo.gres

INSTR

Returns the position of a substring within a string. Returns 0 if the substring is not found.

Syntax

INSTR(<str> TEXT, <patt> TEXT [, <start> INTEGER, <nth> INTEGER])

Parameters

Parameter	Required	Description
`str`	Yes	The string to search.
`patt`	Yes	The substring to find.
`start`	No	The position to start searching from. Defaults to the beginning of the string.
`nth`	No	Which occurrence of the substring to return the position of. Defaults to 1 (the first occurrence).

Return value

Returns a value of the INT type. Returns 0 if the substring is not found.

Examples

Find the position of the second occurrence of o, starting from position 1:

SELECT INSTR('Hologres', 'o', 1, 2);
-- Result: 4

Find the position of the first occurrence of o, starting from position 1:

SELECT INSTR('Hologres', 'o', 1, 1);
-- Result: 2

Find the position of o starting from position 4:

SELECT INSTR('Hologres', 'o', 4);
-- Result: 4

Find the first occurrence of o with no explicit start position:

SELECT INSTR('Hologres', 'o');
-- Result: 2

PLVSTR.RVRS

Reverses all or part of a string.

Syntax

Reverse a substring between specified positions:

PLVSTR.RVRS(<str> TEXT, <start> INTEGER, <end> INTEGER)

Reverse from a specified position to the end of the string:

PLVSTR.RVRS(<str> TEXT, <start> INTEGER)

Reverse the entire string:

PLVSTR.RVRS(<str> TEXT)

Return value

Returns a value of the TEXT type.

Examples

Reverse the substring from position 5 to position 6:

SELECT PLVSTR.RVRS('Hologres', 5, 6);
-- Result: rg

Reverse from position 4 to the end of the string:

SELECT PLVSTR.RVRS('Hologres', 4);
-- Result: sergo

Reverse the entire string:

SELECT PLVSTR.RVRS('Hologres');
-- Result: sergoloH

SUBSTR

Extracts a substring from a string.

Syntax

Extract from a position to the end of the string:

SUBSTR(<str> TEXT, <num_start> INTEGER)

Extract a fixed-length substring starting from a position:

SUBSTR(<str> TEXT, <num_start> INTEGER, <len> INTEGER)

Parameters

Parameter	Required	Description
`str`	Yes	The source string.
`num_start`	Yes	The position to start extracting from.
`len`	No	The number of characters to extract. If omitted, extraction continues to the end of the string.

Return value

Returns a value of the TEXT type.

Examples

Extract from the fourth character to the end:

SELECT SUBSTR('Hologres', 4);
-- Result: ogres

Extract five characters starting from the second character:

SELECT SUBSTR('Hologres', 2, 5);
-- Result: ologr

CONCAT

Concatenates two or more strings into a single string.

Syntax

CONCAT(<str1> TEXT, <str2> TEXT, ..., <strN> TEXT)

Parameters

Parameter	Required	Description
`str1, ..., strN`	Yes	The strings to concatenate. NULL values are ignored.

Return value

Returns a value of the TEXT type.

Example

Concatenate strings and numbers, ignoring a NULL value:

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

Result:

  concat
---------
 abcde222