Function overview

You can use OceanBase built-in functions directly in SQL statements. The values passed to an SQL function must be of the supported data types. If the values passed to an SQL function are not of a data type expected by the SQL function, OceanBase Database converts the values to the supported data type before it executes the SQL function.

A function is similar to an operator. You can pass some data elements to a function as the input parameters, and then the function returns the results. However, a function differs greatly from an operator in terms of input parameters. Unlike an operator that has a fixed number of parameters, a function can have a variable number of parameters, which can be one, two, or more.

Function name (parameter, parameter...)

Functions may also have no parameters, similar to pseudocolumns. However, unlike pseudocolumns that usually return a different value for each row in the result set, functions without variables return the same value for each row.

Null values in a function

For most functions, the value returned for a null input parameter value is null. In this case, you can use the NVL function to return a non-null value. For example, in a commission records table, if the commission column commitsion_pct is NULL, the NVL(commission_pct,0) expression returns 0. If commitsion_pct is not NULL, the expression returns the actual commission value.

Function categories and lists

The following lists describe the data types of parameters and return values for each type of function.

Notice

When you apply SQL functions to LOB columns, OceanBase Database creates temporary LOB columns when it processes the SQL or PL/SQL statements and some restrictions apply. For more information, see Compatibility with Oracle.

In this chapter, functions are divided into two categories:

• Single-row functions, including numeric functions, character functions that return characters, character functions that return numbers, datetime functions, general comparison functions, conversion functions, encoding and decoding functions, NULL-related functions, environment and identifier functions, and hierarchical functions.

• Statistical functions, including aggregate functions and analytic functions

A single-row function returns a result value for each row in a queried table or view. A single-row function can be used in the SELECT, WHERE, START WITH, CONNECT BY, or HAVING clause in an SQL statement.

Analytic and aggregate functions calculate an aggregate value based on a group of rows. The difference is that aggregate functions return only one value (one row) for each group, whereas analytic functions return multiple rows for each group. A rowset group is also called a window. An aggregate function is usually used together with the GROUP BY clause in a SELECT statement. In this case, the database divides the rows in a queried table or view into groups, applies an aggregate function to each group of rows, and returns one result row for each group.

For an analytic function, you need to use the OVER keyword to specify the window. For more information about window functions, see Window functions.

Numeric functions

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

Character function that returns characters

The maximum length of the return value of a function is subject to the data type. Assume that the return value of a function is of the VARCHAR2 data type. If the return value exceeds the maximum limit of the VARCHAR2 data type, OceanBase Database truncates the result and returns the truncated value. No error is returned to the client.

Notice

If the data type of the return value is CLOB, when the length of the return value exceeds the maximum limit, OceanBase Database does not return the value but returns an error.

Character functions that return numbers

Datetime functions

Datetime functions operate on three input data types: 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).

Functions that support only the DATE data type include ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY.

For these functions, if you give a TIMESTAMP value, OceanBase Database implicitly converts the value before it passes the value to the function, and returns a value of the DATE data type.

We recommend that you execute SELECT * FROM NLS_DATABASE_PARAMETERS to check the current NLS format before you use a datetime function:

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 format is inconsistent with that in the previous example, you can execute 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 
/* Specify 9 decimal places for fractional seconds 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

Notice

• MONTHS_BETWEEN returns a number.
• ROUND and TRUNC cannot implicitly convert one data type to another. Therefore, you must provide a value of the DATE data type for these two functions. Otherwise, an error is returned.

Other functions accept all three types of parameters and return a value of the same data type as the input parameters.

General comparison functions

General comparison functions allow you to quickly locate the maximum and minimum values in a data set.

Conversion functions

Conversion functions allow you to convert a source data type into another data type.

Encoding and decoding functions

You can use encoding and decoding functions in OceanBase Database to encode and decode data.

Null value-related functions

Environment functions

Environment functions mainly provide environment information of sessions or tenant instances.

Hierarchical functions

Aggregate functions

Analytic function

More information

For more information about the keyword OVER in analytic functions, see Window functions.