Overview

Built-in functions in OceanBase Database can be used directly within SQL statements. The values passed to an SQL function must be of the supported data types. If the values passed to an SQL function are not of a data type expected by the SQL function, OceanBase Database converts the values to the supported data type before it executes the SQL function.

A function is similar to an operator: you can pass data elements as input parameters, and the function returns a result. However, functions differ significantly from operators in terms of input parameters. Unlike operators, which have a fixed number of parameters, functions can accept a variable number—one, two, or more.

function_name(parameter, parameter, ...)

Functions can also have no parameters, similar to pseudocolumns. However, pseudocolumns typically return different values for each row in the result set, while functions without parameters generally return the same value for each row.

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

• Single-row functions, including numeric functions, string functions that return strings, string functions that return numbers, datetime functions, general comparison functions, conversion functions, encoding and decoding functions, NULL-related functions, environment and identifier functions, and hierarchical functions.

• Statistical functions, including aggregate functions and analytic functions.

A single-row function returns a value for each row in a queried table or view. Single-row functions can be used in the SELECT, WHERE, START WITH, CONNECT BY, or HAVING clause of an SQL statement.

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

For analytic functions, you need to use the OVER keyword to specify the window. For more information about window functions, see Analytic functions.

Numeric functions

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

String functions that return strings

The maximum length of the return value of a function is subject to the data type. Assume that the return value of a function is of the VARCHAR2 data type. If the return value exceeds the maximum limit of the VARCHAR2 data type, OceanBase Database truncates the result and returns the truncated value. No error is returned to the client.

String functions that return numbers

These functions return numbers such as ASCII code values and character (string) locations based on specified conditions.

Datetime functions

Datetime functions operate on three input data types: date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH).

Functions that support only the DATE data type include ADD_MONTHS, CURRENT_DATE, LAST_DAY, and NEXT_DAY.

For these functions, if you give a TIMESTAMP value, OceanBase Database implicitly converts the value before it passes the value to the function, and returns a value of the DATE data type.

We recommend that you execute SELECT * FROM NLS_DATABASE_PARAMETERS to check the current NLS format before you use a datetime function:

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 is inconsistent with that in the previous example, you can execute the following statement 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
/* Specify 9 decimal places for fractional seconds in the return value */

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

Other functions accept all three types of arguments and return a value of the same data type as the input arguments.

General comparison functions

General comparison functions allow you to quickly locate the maximum and minimum values in a data set.

Conversion functions

Conversion functions allow you to convert a source data type into another data type.

Encoding and decoding functions

You can use encoding and decoding functions in OceanBase Database to encode and decode data.

NULL-related functions

NULL-related functions process NULLs in arguments. Most functions return NULL if the input argument is NULL. In this case, you can use the NVL function to return a non-NULL value. For example, in a commission records table, if the commission column commission_pct is NULL, the NVL(commission_pct,0) expression returns 0. If commission_pct is not NULL, the expression returns the actual commission value.

Environment functions

Environment functions provide environment information of sessions or tenant instances.

Hierarchical functions

JSON functions

JSON functions allow you to create, manage, and search for JSON values.

XML functions

XML functions help you efficiently query, update, and search XML documents.

Aggregate functions

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

Analytic functions

Analytic functions, also known as window functions in some databases, are similar to aggregate functions in that the computation is always based on a set of rows. The difference is that an aggregate function returns only one row per group, while an analytic function can return multiple rows per group, and each row in the group is the result of the window-based logical computation. Analytic functions can significantly optimize queries that require self joins.