Overview

Built-in functions in OceanBase Database can be directly used in SQL statements. Each function expects specific data types for its input parameters. If the provided data type does not match the expected type, OceanBase Database will attempt to convert the parameter value to the expected data type before executing the SQL function.

Like operators, functions take some data elements as input parameters and return a result. However, functions differ from operators in terms of parameter form. Functions allow for an unspecified number of parameters, ranging from one to multiple parameters.

function_name (parameter, parameter, ...)

Functions can also have no parameters, similar to pseudo-columns. However, pseudo-columns typically return different values for each row in the result set, while functions without any parameters usually return the same value for each row.

This chapter categorizes functions into two main types, each with specific data types for parameters and return values:

• Single-row functions: These include numeric functions, string functions that return strings, string functions that return numeric values, date and time functions, general comparison functions, conversion functions, encoding and decoding functions, null value-related functions, environment and identity functions, and hierarchical functions.

• Statistical functions: These include aggregate functions and analytical functions.

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

Analytical functions, like aggregate functions, perform aggregate calculations on a set of rows (a group of rows). The difference is that aggregate functions return one value (one row) per group, while analytical functions can return multiple values (multiple rows) per group. A set of rows is also known as a window. Aggregate functions are typically used with the GROUP BY clause in SELECT statements. When used, the database divides the rows of the queried table or view into groups and applies the aggregate function to each group, returning one result row per group.

When using analytical functions, you need to specify the window using the special keyword OVER. For more information about window functions, see Analytical functions.

Numeric functions

The input and output of numeric functions are both numeric types. Most numeric functions return a value of the NUMBER type, which can be accurate to 38 decimal places. Some higher algebraic functions such as COS(), COSH(), EXP(), LN(), LOG(), SIN(), SINH(), SQRT(), TAN(), and TANH() return values accurate to 36 decimal places. Other algebraic functions such as ACOS(), ASIN(), ATAN(), and ATAN2() return values accurate to 30 decimal places.

String functions that return strings

The maximum length of the return value of a string function is affected by the data type. For example, the return value of a string function is of the VARCHAR2 type, but the actual size of the return value exceeds the maximum limit of the VARCHAR2 type. In this case, OceanBase Database truncates the result and returns it. However, no prompt is displayed on the client.

String functions that return numbers

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

Date and time functions

Date and time functions support three types of input parameters: date and time (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH).

The following functions support only the DATE data type as an input parameter: ADD_MONTHS, CURRENT_DATE, LAST_DAY, and NEXT_DAY.

If you provide a TIMESTAMP value as an input parameter to the preceding functions, OceanBase Database implicitly converts the value to the DATE data type and returns a value of the DATE type.

Before you use date and time functions, we recommend that you execute the SELECT * FROM NLS_DATABASE_PARAMETERS statement to view the current NLS format, as shown in the following example:

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 display format does not match the example, you can execute the following commands 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
/*The seconds in the return value are set to 9 decimal places.*/

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 data types as input parameters and return values of the same data types.

General comparison functions

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

Conversion functions

You can use the functions in this category to convert a value from one data type to another.

Encoding and decoding functions

You can use the functions in this category to encode and decode data in OceanBase Database.

Null value-related functions

Null value-related functions are used to handle null values in parameters. For most functions, if the input parameter is a null value NULL, the return value is also NULL. In this case, you can use the NVL function to return a non-null value. For example, if the commission_pct column of a table that records commissions is a null value NULL, the expression NVL(commission_pct, 0) returns 0. If the commission_pct column is not a null value, the actual commission value is returned.

Environment-related functions

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

Hierarchical functions

JSON functions

JSON functions are used to create, operate on, and search for JSON values.

XML functions

XML functions are used to efficiently query, update, and search for XML documents.

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 with the GROUP BY clause of a SELECT statement.

Analytic functions

Analytic functions (also called window functions in some databases) are similar to aggregate functions. Both types of functions perform calculations based on a set of rows. However, an aggregate function returns only one row for a set, whereas an analytic function can return multiple rows for a set. Each row in the set is based on the logical calculation of the window. Analytic functions can significantly optimize queries that require self-joins.