Overview

Built-in functions of OceanBase Database can be directly used in SQL statements. Each function has expected data types for its input parameters. If the input data type is not the expected one, OceanBase Database will attempt to convert the input value to the expected data type before executing the SQL function.

Functions are similar to operators in that they take some data elements as input parameters and return a result. However, functions differ from operators in the form of their input parameters. Functions can have a variable number of parameters, ranging from one to many.

FunctionName (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 any parameters usually return the same value for each row.

This chapter categorizes functions into two main types. Each type of function has specific data types for its parameters and return values:

• Single-row functions: These 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 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 aggregation calculations on a set of rows (a group of rows). The difference is that an aggregate function returns one value (one row) per group, while an analytical function can return multiple values (multiple rows) per group. A set of rows is also referred to 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 must use the special keyword OVER to specify the window. For more information about window functions, see Analytical functions.

Numeric functions

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

String functions that return strings

The maximum length of the return value of a string function depends on the data type. For example, if the return value of a string function is of the VARCHAR2 data type, but the actual size exceeds the maximum limit of the VARCHAR2 data type, 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 numbers based on specified conditions, such as ASCII values and the positions of characters (strings).

Date and time functions

The 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 only support the DATE data type as input parameters: ADD_MONTHS, CURRENT_DATE, LAST_DAY, and NEXT_DAY.

If you provide a TIMESTAMP type parameter to any of the above functions, OceanBase Database will implicitly convert it to the DATE type before performing the operation and will return 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 display format does not match the examples in this section, 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
/*Set the number of decimal places for seconds to 9 in the result.*/

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 values of 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 data 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-related functions

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

Environment-related functions

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

Hierarchical functions

JSON functions

JSON functions create, operate on, and search JSON values.

XML functions

XML functions 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 null values. Aggregate functions are often used with the GROUP BY clause in a SELECT statement.

Analytic functions

Analytic functions (also known as window functions in some databases) are similar to aggregate functions in that they compute values based on a set of rows. However, while aggregate functions return only one row per group, analytic functions can return multiple rows per group, with each row in the group being the result of a window-based calculation. Analytic functions can significantly optimize queries that require self-joins.

Information functions

Spatial functions