function name (parameter, parameter, ... )

function name (parameter, parameter, ... )

Functions are similar to operators in that they take data elements as input parameters and return a result. However, functions differ significantly from operators in terms of parameter input. Functions can accept an arbitrary number of parameters, ranging from one or two to many more.

```sql
function name (parameter, parameter, ...)

A function can also have no parameters, similar to a pseudo-column. However, while a pseudo-column typically returns a different value for each row in the result set, a function with no variables usually returns the same value for every row.

In this chapter, functions are divided into two main categories. Each category of functions has specific data types for its parameters and return values:

• 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, null-related functions, environment and identity functions, and hierarchical functions.

• Statistical functions: 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 to perform operations such as:SELECT、WHERE、START WITH、CONNECT BY、HAVINGin the WHERE clause.

Analysis functions and aggregate functions both perform aggregate calculations on row sets (collections of rows). The difference is that an aggregate function returns only one value per group (one row), while an analysis function can return multiple values per group (multiple rows). A row set is also called a window. Aggregate functions are often used withSELECTIn the statement,GROUP BYThe GROUP BY clause is used together with aggregate functions. When used, the database divides the rows of the queried table or view into several groups, applies the aggregate function to each group, and returns one result row for each group.

Special keywords are required when using analytic functions.OVERto specify the window. For more information about window functions, see Analysis functions.

Numeric Functions

The variable inputs and function output results of numeric functions are both of the numeric data type. The data types of the return values of most numeric functions are as follows:NUMBER, which can be accurate to 38 decimal places. Some advanced algebra-related functionsCOS()、COSH()、EXP()、LN()、LOG()、SIN()、SINH()、SQRT()、TAN()、TANH()The results of functions such as are accurate to 36 decimal places. Other algebraic-related functionsACOS(),ASIN(),ATAN()andATAN2(). The result is returned with an accuracy of up to 30 decimal places.

String functions that return strings

The maximum length of a function's return value is determined by its data type. For example, if the data type of the function's return value isVARCHAR2, but the actual size of the return value exceedsVARCHAR2The maximum limit of the data type. In this case, OceanBase Database truncates the result and returns it, but no warning is prompted on the client.

String functions for returning numbers

String functions that return numbers produce a numerical value based on the specified condition, such as an ASCII code or the position of a character (or string).

Time and date functions

Time and date functions support three types of input data types, including datetime (DATE), and timestamp (TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE), and the interval (INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH).

Supported only inDATEFunctions that accept data type parameters include:ADD_MONTHS、CURRENT_DATE, LAST_DAYandNEXT_DAY.

If you try to pass the following values to the preceding functions:TIMESTAMPFor data of different types, OceanBase Database performs implicit data type conversion internally before passing the data to the function for computation and returning the result.DATEThe return value of the type.

Before using time and date functions, it is recommended to executeSELECT * FROM NLS_DATABASE_PARAMETERSView 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 the text, you can run the following command 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 seconds in the return value 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 the same data type as their 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

Functions of this type can be used to convert data from one type to another.

Encoding and Decoding Functions

You can use functions of this type to implement data encryption and decryption requirements in OceanBase Database.

NULL-related functions

NULL-related functions are used to handle NULL values in parameters. For the vast majority of these functions, if an input parameter is NULL,NULLWhen it returns the value, the result it returns is alsoNULLIn this case, you can useNVLThe function returns a non-null value. For example, the commission column of a table that records commissions.commission_pctNULL valueNULL, then the expressionNVL(commission_pct，0)Returns 0; ifcommission_pctThe value of is notNULL, the actual commission value is returned.

Environment-related functions

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

Hierarchical Functions

JSON Functions

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

XML Functions

XML functions enable efficient querying, updating, and searching of XML documents.

TABLE FUNCTION

A TABLE FUNCTION returns a value that can be used inFROMThe rowset result referenced in the clause.

For more information, see Overview of TABLE FUNCTIONS.

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 withSELECTThe statement'sGROUP BYcan be used together.

Analytical Functions

Analysis functions (also referred to as window functions in some databases) are similar to aggregate functions in that they always operate on a set of rows. However, whereas an aggregate function returns a single row for each group, an analysis function can return multiple rows per group, with each row within the group being the result of a window-based computation. Analysis functions can significantly optimize queries that require self-joins.

Information Functions

Spatial functions