Function overview

Functions work in a similar way as operators. After data elements are passed to a function as the input of parameters, the function returns a result. However, functions differ from operators in the format of input parameters. Functions can contain a variable number of parameters. A function can contain one, two, or more parameters.

Function name (parameter, parameter, ...)

A function without parameters is similar to a pseudocolumn. For more information, see Pseudo columns. A pseudocolumn returns a different value for each row in the result set. However, a function without variables returns the same value for all rows.

About functions

Built-in functions of ApsaraDB for OceanBase can be used in SQL statements. An expected data type is specified for each parameter of a function. If the data type of an input argument is expected, ApsaraDB for OceanBase attempts to convert the data type to the expected one and then executes the SQL statement.

Null values in functions

If the input argument is NULL, most functions return NULL as the result. In this case, you can use the NVL function to return a non-null value. For example, assume that a table is created to store commission data and the commission_pct column stores the commission values. If the value in the commission_pct column is NULL, the NVL(commission_pct,0) expression returns 0. If the value in the commission_pct column is not NULL, the expression returns the actual commission value.

Function types and lists

In the following function lists, each parameter or return value of each function has a specific data type. Notice

When you apply a function to a LOB column in an SQL statement, ApsaraDB for OceanBase creates a temporary LOB column during SQL processing or PL/SQL processing. Some limits are set for using the column. For more information, see Compatibility with Oracle databases.

This topic describes two categories of functions:

• Single-row functions : 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, and null-related functions.

• Statistical functions : include aggregate functions and analytic functions.

A single-row function returns a result value for each row of a queried table or view. These functions can be used in SQL clauses such as SELECT, WHERE, START WITH, CONNECT BY, and HAVING.

Aggregate functions and analytic functions perform aggregate calculations on a group of rows. An aggregate function returns only a single row for each group of rows. However, an analytic function returns multiple rows for each group of rows. A group of rows is also called a window. In most cases, aggregate functions are used in combination with the GROUP BY clause in the SELECT statement. When you use an aggregate function, the database divides the rows of a queried table or view into groups and applies the aggregate function to each group of rows. Then, one result row is returned for each group of rows.

When you use analytic functions, you must use the special keyword OVER to specify a window. For more information about window functions, see Window functions.

Numeric functions

Numeric functions accept numeric values as the input and return numeric values. Most numeric functions return values of

the NUMBER data type. The return values can be accurate to 38 decimal places. Some functions are related to advanced algebra. Some of these functions, such as COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH, return values that are accurate to 36 decimal points. Other functions that are related to advanced algebra, such as ACOS, ASIN, ATAN, and ATAN2, return values that are accurate to 30 decimal places.

String functions that return strings

The maximum length of the value returned by a function varies based on the data type of the value. For example, assume that a function returns a VARCHAR2 value whose length exceeds the maximum length of a VARCHAR2 value. In this case, ApsaraDB for OceanBase truncates the value and returns the truncated value without displaying a prompt on the client. Notice

If a function returns a CLOB value whose length exceeds the maximum length of a CLOB value, ApsaraDB for OceanBase returns no data and displays an error message.

String functions that return numbers

Date and time functions

Date and time functions support input parameters of the following data types: date-related data types (DATE), timestamp-related data types (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval-related data types (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH).

The following functions support input parameters of only the DATE data type: ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY.

Assume that you attempt to insert a TIMESTAMP value as an argument into the preceding functions. ApsaraDB for OceanBase implicitly converts the data type, passes the converted data to the function for calculations, and then returns a DATE value. Notice

MONTHS_BETWEEN returns a number.

ROUND and TRUNC cannot implicitly convert data types and support input parameter of only the DATE type. If you pass values of other data types to these functions, errors are reported.

The other date and time functions support input parameters of the three data types and return values of the same data types as the input parameters.

General comparison functions

You can use this type of functions to locate the maximum and minimum values in a set of values in a short time.

Conversion functions

You can convert a value from one data type to another data type by using this type of functions.

Encoding and decoding functions

You can encode and decode data in ApsaraDB for OceanBase by using this type of functions.

Null value-related functions

Environment-related functions

Functions of this type provide the environment information about sessions or tenants.

Aggregate functions

Analytic functions

More information

For more information about the OVER keyword of analytic functions, see Window function description.