Overview

You can directly use built-in functions of OceanBase Database in 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 some data elements to a function as the input parameters, and then the function returns the results. However, a function differs greatly from an operator in terms of input parameters. Unlike an operator that has a fixed number of parameters, a function can have a variable number of parameters, which can be one, two, or more.

Function name (parameter, parameter...)

Functions may also have no parameters, similar to pseudocolumns. However, unlike pseudocolumns that usually return a different value for each row in the result set, functions without variables return the same value for each row.

This topic divides functions into two categories and describes the data types of parameters and return values for each type of function.

• Single-row functions, including numeric functions, character functions that return characters, character 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 result value for each row in a queried table or view. A single-row function can be used in the SELECT, WHERE, START WITH, CONNECT BY, or HAVING clause in an SQL statement.

Analytic functions and aggregate functions both calculate an aggregate value based on a group of rows. The difference is that aggregate functions return only one value (one row) for each group, whereas analytic functions return multiple rows for each group. A rowset group is also called a window. An aggregate function is usually used together with the GROUP BY clause in a SELECT statement. In this case, the database divides the rows in a query table or view into groups, applies an aggregate function to each group of rows, and returns one result row for each group.

For an analytic function, 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.

Character functions that return characters

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.

Character 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, NEW_TIME, 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 parameters and return a value of the same data type as the input parameters.

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 null values in parameters. For most functions, the value returned for the NULL input parameter value 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 commitsion_pct is NULL, the NVL(commission_pct,0) expression returns 0. If commitsion_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.

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 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.