Overview

Built-in functions in OceanBase Database can be used directly 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 attempts to convert the parameter 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 way they accept parameters. Functions can have a variable number of parameters, ranging from one to several.

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

This chapter categorizes functions into two main types, each with specific data types for their 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-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 group of rows (a set 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 group 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, the special keyword OVER is required to specify the window. 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 precise to 38 decimal places. Some high-level algebraic functions such as COS(), COSH(), EXP(), LN(), LOG(), SIN(), SINH(), SQRT(), TAN(), and TANH() return values precise to 36 decimal places, while 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 is affected by the data type. For example, if the return value of a function is of the VARCHAR2 type, but the actual size exceeds the maximum limit of the VARCHAR2 type, OceanBase Database will truncate the result and return it. However, no prompt will be displayed on the client.

String functions that return numbers

String functions that return numbers return a number based on the specified conditions, such as ASCII values and the positions of characters (strings).

Date and time functions

Date and time functions support three types of input data: 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 an input parameter: ADD_MONTHS, CURRENT_DATE, LAST_DAY, and NEXT_DAY.

If you pass a TIMESTAMP value to any of the preceding functions, OceanBase Database will implicitly convert the value to the DATE type before passing it to the function for calculation and will return a value of the DATE type.

Before using 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 in the preceding example is different from the one in the sample, you can execute the following statements 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 the seconds to 9 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

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 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 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, the actual commission value is returned.

Environment-related functions

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

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 in SELECT statements.

Analytic functions

Analytic functions (also known as window functions in some databases) are similar to aggregate functions. Both calculate values based on a set of rows. However, an aggregate function returns only one row per group, while an analytic function can return multiple rows per group. Each row in the group is the result of a window-based calculation. Analytic functions can significantly optimize queries that require self-joins.

Table functions

A table function generates a set of rows, which can be queried like a database table. You can use a table function in the FROM clause of a query. Starting from OceanBase Database V4.2.5 BP5, table functions support complex data types.