Function overview

Built-in functions in OceanBase Database can be used directly in SQL statements. Each function has expected data types for its input parameters. If the actual data type of an input parameter does not match the expected type, OceanBase Database attempts to convert the value before the function is executed.

Functions are similar to operators in that they take data elements as input parameters and return a result. However, functions differ significantly from operators in terms of parameter input. Functions can accept an arbitrary number of parameters, ranging from one or two to many more.

function name (parameter, parameter, ... )

A function can also have no parameters, similar to a pseudo-column. However, while a pseudo-column typically returns a different value for each row in the result set, a function with no variables usually returns the same value for every row.

In this chapter, functions are divided into two main categories. Each category of functions has specific data types for its parameters and return values:

• Single-row functions: Include numeric functions, string functions that return strings, string functions that return numbers, date and time functions, general comparison functions, conversion functions, encoding and decoding functions, null-related functions, environment and identity functions, hierarchical functions, and large object functions.

• Statistical functions: Include aggregate functions and analytic functions.

Single-row functions return a result value for each row in the queried table or view. These functions can be used in clauses such as SELECT, WHERE, START WITH, CONNECT BY, and HAVING of an SQL statement.

Analysis functions and aggregate functions both perform aggregate calculations on row sets (collections of rows). The difference is that an aggregate function returns only one value per group (one row), while an analysis function can return multiple values per group (multiple rows). A row set is also called a window. Aggregate functions are typically used with the GROUP BY clause in a SELECT statement. When used this way, the database divides the rows of the queried table or view into several groups, applies the aggregate function to each group, and returns a result row for each group.

When using analytic functions, you must use the special keyword OVER to specify the window. For more information about window functions, see Analytic functions.

Numeric functions

The inputs and outputs of numeric functions are both numeric types. The return type of most numeric functions is NUMBER, which can represent numbers with up to 38 decimal digits of precision. Some advanced algebraic functions, such as COS(), COSH(), EXP(), LN(), LOG(), SIN(), SINH(), SQRT(), TAN(), and TANH(), achieve 36 decimal digits of precision. Other algebraic functions, including ACOS(), ASIN(), ATAN(), and ATAN2(), return results with 30 decimal digits of precision.

String functions that return strings

The maximum length of a function's return value is determined by its data type. For example, if the function's return type is VARCHAR2 but the actual value exceeds the maximum limit of this data type, OceanBase Database truncates the result and returns it without notifying the client.

String functions for returning numbers

String functions that return numbers take a numeric value as their argument based on the specified condition, such as an ASCII code value or the position of a character (string).

Date and time functions

Date and time functions support three types of input data types: datetime (DATE), timestamps (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and intervals (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH).

Functions that support only the DATE data type as an input parameter include: ADD_MONTHS, CURRENT_DATE, LAST_DAY, and NEXT_DAY.

If you attempt to use a TIMESTAMP type value for these functions, OceanBase Database will implicitly convert it to a DATE type before evaluating the function and returning a DATE type result.

Before using date and time functions, it is recommended to execute SELECT * FROM NLS_DATABASE_PARAMETERS to view the current NLS format, as shown below:

obclient> SELECT * FROM NLS_DATABASE_PARAMETERS;
+-------------------------+------------------------------+
| PARAMETER               | VALUE                        |
+-------------------------+------------------------------+
| NLS_DATE_FORMAT         | DD-MON-RR                    |
| NLS_TIMESTAMP_FORMAT    | YYYY-MM-DD HH24:MI:SS        |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_TERRITORY           | AMERICA                      |
| NLS_SORT                | BINARY                       |
| NLS_COMP                | BINARY                       |
| NLS_CHARACTERSET        | AL32UTF8                     |
| NLS_NCHAR_CHARACTERSET  | AL16UTF16                    |
| NLS_DATE_LANGUAGE       | AMERICAN                     |
| NLS_LENGTH_SEMANTICS    | BYTE                         |
| NLS_NCHAR_CONV_EXCP     | FALSE                        |
| NLS_CALENDAR            | GREGORIAN                    |
| NLS_NUMERIC_CHARACTERS  | .,                           |
+-------------------------+------------------------------+
13 rows in set

If the format does not match the examples in the text, you can run the following command to change the format:

obclient>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected

obclient>ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Query OK, 0 rows affected
/*Set the decimal places of seconds in the return value to 9.*/

obclient> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
Query OK, 0 rows affected

The remaining functions support all three parameter data types and return the same data type as the input parameters.

General comparison functions

You can use the functions in this category to quickly find the maximum and minimum values in a set.

Conversion functions

You can use the functions in this category to convert one data type into another.

Encoding and decoding functions

You can use the functions in this category to implement data encoding and decoding requirements in OceanBase Database.

NULL-related functions

NULL-related functions are used to handle null values in parameters. For the vast majority of functions, when an input parameter is a null value (NULL), the function returns NULL. In such cases, you can use the NVL function to return a non-null value. For example, if the commission column commission_pct in a table that records commissions is a null value (NULL), the expression NVL(commission_pct, 0) returns 0; if the value of commission_pct is not NULL, it returns the actual commission value.

Environment-related functions

Environment-related functions provide environment information related to a session or tenant instance.

Hierarchical functions

Large object functions

Large object functions construct large object data.

JSON functions

JSON functions perform operations such as creating, manipulating, and searching JSON values.

XML functions

XML functions can efficiently query, update, and search XML documents.

TABLE FUNCTION

TABLE FUNCTIONS are used to return a row set result that can be referenced in the FROM clause.

For more information, see Overview of TABLE FUNCTIONS.

Aggregate functions

Aggregate functions perform calculations on a set of values and return a single value. Aggregate functions ignore null values. Aggregate functions are often used together with the GROUP BY clause in a SELECT statement.

Analytical functions

Analytical functions (also called window functions in some databases) are similar to aggregate functions in that they always operate on a set of rows. However, whereas an aggregate function returns only one row per group, an analytical function can return multiple rows per group, with each row within the group being the result of a window-based calculation. Analytical functions can significantly optimize queries that require self-joins.

Information functions

Spatial functions

Audit functions