Overview of functions

Built-in functions in OceanBase Database can be directly used in SQL statements. Each function has expected data types for its input parameters. If the data type of the input value does not match the expected data type, OceanBase Database attempts to convert the 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 significantly from operators in the number of parameters they can accept. Functions can have a variable number of parameters, ranging from one to many.

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 any variables 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 value-related functions, environment and identifier functions, and hierarchical functions.

• Aggregate functions: These include aggregate functions and analytic 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.

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

When using analytic functions, the special keyword OVER is required to specify the window. For more information about analytic functions, see Analytic functions.

Numeric functions

The input variables and output results of numeric functions are of the numeric type. Most numeric functions return values of the NUMBER data type, which can be precise to 38 decimal places. The results of some advanced algebraic functions, such as COS(), COSH(), EXP(), LN(), LOG(), SIN(), SINH(), SQRT(), TAN(), and TANH(), are precise to 36 decimal places. The results of other algebraic functions, such as ACOS(), ASIN(), ATAN(), and ATAN2(), are 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 is of the VARCHAR2 data type, but its actual length exceeds the maximum limit of the VARCHAR2 data type, OceanBase Database will truncate the result and return it, without prompting on the client side.

String functions that return numbers

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

Date and time functions

The date and time functions support three types of input data: 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).

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

If you provide TIMESTAMP data for the above functions, OceanBase Database will implicitly convert the data type before using it in the function and return a DATE value.

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 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 displayed format is different from 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
/* Set the number of decimal places for seconds to 9. */

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 implement data encoding and decoding requirements in OceanBase Database.

Functions for handling NULL values

Functions for handling NULL values are used to process NULL values in parameters. For most functions, if the input parameter is NULL, the function returns 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 contains NULL, the expression NVL(commission_pct, 0) returns 0; if commission_pct is not NULL, it returns the actual commission value.

Functions for retrieving environment information

Functions for retrieving environment information are used to obtain environment information related to the current session or tenant instance.

Hierarchical functions

JSON functions

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

XML functions

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

Aggregate functions

Aggregate functions are used to 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.

Analytical functions

Analytical functions (also known as window functions in some databases) are similar to aggregate functions. Both calculate results based on a set of rows. The key difference is that aggregate functions return only one row per group, while analytical functions can return multiple rows per group, with each row representing the result of a window-based logical calculation. Analytical functions can significantly optimize queries that involve self-joins.

Table functions

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