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.
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Single-row function | Numeric function | ABS | A mathematical function that returns the absolute (positive) value of the specified numeric expression. |
| Single-row function | Numeric function | ACOS | Returns the angle that is represented in radians, whose cosine is specified by using the NUMBER expression. This angle is also called arccosine. |
| Single-row function | Numeric function | ASIN | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | ATAN | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | ATAN2 | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | BITAND | Performs a bitwise operation by using the bitwise AND operator. Both the input and output values are of the same INT type. |
| Single-row function | Numeric function | CEIL | Returns the smallest integer that is greater than or equal to the value of numeric_expression. |
| Single-row function | Numeric function | COS | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | COSH | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | EXP | Returns e raised to the power of numeric_expression. |
| Single-row function | Numeric function | FLOOR | Returns the largest integer that is equal to or less than the value of numeric_expression. |
| Single-row function | Numeric function | LN | Returns the e-based logarithm of numeric_expression. |
| Single-row function | Numeric function | LOG | Returns the x-based logarithm of y. |
| Single-row function | Numeric function | MOD | Returns the remainder that is produced when x is divided by y. |
| Single-row function | Numeric function | POWER | Returns x raised to the power of y. |
| Single-row function | Numeric function | REMAINDER | Returns the remainder that is produced when x is divided by y. |
| Single-row function | Numeric function | ROUND | Returns the rounded value of numeric. |
| Single-row function | Numeric function | SIGN | Returns the sign of the number n. This function returns 1 if n is greater than 0, returns -1 if n is less than 0, and returns 0 if n is equal to 0. |
| Single-row function | Numeric function | SIN | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | SINH | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | SQRT | Returns the square root of n. |
| Single-row function | Numeric function | TAN | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | TANH | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Numeric function | TRUNC | Truncates numeric to the specified precision and returns the result value. |
| Single-row function | Numeric function | WIDTH_BUCKET | ApsaraDB for OceanBase does not support this function. |
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.
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Single-row function | String functions that return strings | CHR | Converts n to one or more equivalent characters. The return value is related to the current system character set. |
| Single-row function | String functions that return strings | CONCAT | Concatenates two strings. |
| Single-row function | String functions that return strings | INITCAP | Returns a string in which the first letter of each word is uppercase and other letters are lowercase. |
| Single-row function | String functions that return strings | LOWER | Converts all letters in a string to lowercase letters. |
| Single-row function | String functions that return strings | LPAD | Left-pads the c1 string to the given length n with the c2 string. |
| Single-row function | String functions that return strings | LTRIM | Trims the string that appears on the left. |
| Single-row function | String functions that return strings | REGEXP_REPLACE | Replaces an object with a regular expression. |
| Single-row function | String functions that return strings | REGEXP_SUBSTR | ApsaraDB for OceanBase does not support this function. |
| Single-row function | String functions that return strings | REPLACE | Replaces a specified string with a new string in a character expression. |
| Single-row function | String functions that return strings | RPAD | Right-pads the c1 string to the given length n with the c2 string. |
| Single-row function | String functions that return strings | RTRIM | Trims a string that appears on the right. This function is useful for formatting the output of a query. |
| Single-row function | String functions that return strings | SUBSTR | Extracts a substring. A multibyte character, such as a full-width character, is counted as one character. |
| Single-row function | String functions that return strings | TRANSLATE | Replaces a specified character with a new character in a character expression. A multibyte character, such as a full-width character, is counted as one character. |
| Single-row function | String functions that return strings | TRIM | Trims leading characters, trailing characters, or both of them from a string. |
| Single-row function | String functions that return strings | UPPER | Converts all letters in a string to uppercase letters. |
String functions that return numbers
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Single-row function | String functions that return numbers | ASCII | Returns the ASCII value of the leftmost character in a character expression. |
| Single-row function | String functions that return numbers | INSTR | Searches for a specified character in a string, and returns the position where the specified character is found. |
| Single-row function | String functions that return numbers | LENGTH | Returns the length of a string. |
| Single-row function | String functions that return numbers | REGEXP_COUNT | ApsaraDB for OceanBase does not support this function. |
| Single-row function | String functions that return numbers | REGEXP_INSTR | ApsaraDB for OceanBase does not support this function. |
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.
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Single-row function | Date and time function | ADD_MONTHS | Returns the date value that is n months after date. If n is a negative number, this function returns the date value that is n months before date. |
| Single-row function | Date and time function | CURRENT_DATE | Returns the current date in the session time zone. |
| Single-row function | Date and time function | CURRENT_TIMESTAMP | Returns a value of the TIMESTAMP WITH TIME ZONE data type. The return value indicates the current date in the session time zone and contains the information of the current time zone. |
| Single-row function | Date and time function | DBTIMEZONE | Returns the time zone of the current database instance. In ApsaraDB for OceanBase, the database time zone is UTC+0 and cannot be changed. |
| Single-row function | Date and time function | EXTRACT (datetime) | Extracts elements such as the year, month, day, hour, minute, and second values from a specified time field or expression. |
| Single-row function | Date and time function | FROM_TZ | Combines a value of the TIMESTAMP data type with the time zone information into a time value of the TIMESTAMP WITH TIME ZONE data type. |
| Single-row function | Date and time function | LAST_DAY | Returns the date of the last day of the month in which the specified date falls. |
| Single-row function | Date and time function | LOCALTIMESTAMP | Returns a value of the TIMESTAMP data type. The return value indicates the current date in the session time zone. |
| Single-row function | Date and time function | MONTHS_BETWEEN | Returns the number of months between date1 and date2. |
| Single-row function | Date and time function | NEW_TIME | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Date and time function | NEXT_DAY | Returns the date value of the weekday c1 in the week following d1. |
| Single-row function | Date and time function | NUMTODSINTERVAL | Converts the argument n to a value of the INTERVAL DAY TO SECOND data type. The interval_unit parameter specifies the measurement unit. |
| Single-row function | Date and time function | NUMTOYMINTERVAL | Converts the argument n to a value of the INTERVAL YEAR TO MONTH data type. The interval_unit parameter specifies the measurement unit. |
| Single-row function | Date and time function | ROUND (date) | Returns a date and time value that is nearest to the specified date. The fmt parameter specifies the unit that is used to measure the interval between the returned date and the specified date. |
| Single-row function | Date and time function | SESSIONTIMEZONE | Returns the time zone of the current session. |
| Single-row function | Date and time function | SYS_EXTRACT_UTC | Returns the UTC time that corresponds to the specified time. |
| Single-row function | Date and time function | SYSDATE | Returns the current date. |
| Single-row function | Date and time function | SYSTIMESTAMP | Returns the current system date which contains the current time zone information. Six digits appear after the decimal point of the second value. |
| Single-row function | Date and time function | TO_CHAR (datetime) | Converts a value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of the VARCHAR2 data type. The fmt parameter specifies the format of the return value. |
| Single-row function | Date and time function | TO_DSINTERVAL | Converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL DAY TO SECOND data type. You can perform the addition and subtraction arithmetic operations on date and time values by using this function. |
| Single-row function | Date and time function | TO_TIMESTAMP | Converts a string to a value of the TIMESTAMP data type. |
| Single-row function | Date and time function | TO_TIMESTAMP_TZ | Converts a string to a value of the TIMESTAMP WITH TIME ZONE data type, which contains the time zone information. |
| Single-row function | Date and time function | TO_YMINTERVAL | Converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL YEAR TO MONTH data type. You can perform the addition and subtraction arithmetic operations on date and time values by using this function. |
| Single-row function | Date and time function | TRUNC (date) | Returns a date and time value that is nearest to the specified date. The fmt parameter specifies the unit in which the interval between the return value and the specified date is measured. The returned date value precedes date. |
| Single-row function | Date and time function | TZ_OFFSET | Returns the offset of the n time zone. The time zone offset is the difference between a time zone and the UTC+0 time zone in hours and minutes. |
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.
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Single-row function | General comparison function | GREATEST | Returns the maximum value in a list of one or more expressions. |
| Single-row function | General comparison function | LEAST | Returns the minimum value in a list of one or more expressions. |
Conversion functions
You can convert a value from one data type to another data type by using this type of functions.
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Function category | Function subcategory | Function name | Description |
| Single-row function | Conversion function | ASCIISTR | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Conversion function | BIN_TO_NUM | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Conversion function | CHARTOROWID | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Conversion function | HEXTORAW | Converts a character that contains hexadecimal numbers and is of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the RAW data type. |
| Single-row function | Conversion function | RAWTOHEX | Converts a binary number to a hexadecimal format string. |
| Single-row function | Conversion function | TO_BINARY_DOUBLE | Returns a 64-bit double-precision floating-point number. |
| Single-row function | Conversion function | TO_BINARY_FLOAT | Returns a 32-bit single-precision floating-point number. |
| Single-row function | Conversion function | TO_CHAR (character) | Converts an NCHAR, NVARCHAR2, or CLOB character to a database character set. |
| Single-row function | Conversion function | TO_CHAR (datetime) | Converts a value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of the VARCHAR2 data type. The fmt parameter specifies the format of the return value. |
| Single-row function | Conversion function | TO_CHAR (number) | Converts the value n of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type to a value of the VARCHAR2 data type in the format specified by fmt. |
| Single-row function | Conversion function | TO_DATE | Converts a character of the CHAR, VARCHAR, NCHAR, or NVARCHAR2 data type to a value of the DATE data type. |
| Single-row function | Conversion function | TO_DSINTERVAL | Converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL DAY TO SECOND data type. You can perform the addition and subtraction arithmetic operations on a date and time value by using this function. |
| Single-row function | Conversion function | TO_NUMBER | Converts expr to a numeric value. |
| Single-row function | Conversion function | TO_TIMESTAMP | Converts a string to a value of the TIMESTAMP data type. |
| Single-row function | Conversion function | TO_TIMESTAMP_TZ | Converts a string to a value of the TIMESTAMP WITH TIME ZONE data type, which contains the time zone information. |
| Single-row function | Conversion function | TO_YMINTERVAL | Converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL YEAR TO MONTH data type. You can perform the addition and subtraction arithmetic operations on a date and time value by using this function. |
Encoding and decoding functions
You can encode and decode data in ApsaraDB for OceanBase by using this type of functions.
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Single-row function | Encoding and decoding function | DECODE | Returns a value that matches the specified conditions. |
| Single-row function | Encoding and decoding function | ORA_HASH | Retrieves the hash value of a corresponding expression. |
| Single-row function | Encoding and decoding function | VSIZE | Returns the size of X in bytes. |
Null value-related functions
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Single-row function | Null value-related function | COALESCE | Returns the first non-null expression in a parameter list. You must specify at least two parameters. |
| Single-row function | Null value-related function | LNNVL | Determines whether one or two operands in a condition are NULL. |
| Single-row function | Null value-related function | NULLIF | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Null value-related function | NVL | Returns a non-NULL value from two expressions. If expr1 and expr2 return NULL, the NVL function returns NULL. |
| Single-row function | Null value-related function | NVL2 | Returns different values based on whether an expression is null. If expr1 is not null, this function returns the value of expr2. If expr1 is null, this function returns the value of expr3. If expr2 and expr3 are of different data types, this function converts the data type of expr3 to that of expr1. |
Environment-related functions
Functions of this type provide the environment information about sessions or tenants.
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Single-row function | Environment-related function | SYS_CONTEXT | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Environment-related function | UID | ApsaraDB for OceanBase does not support this function. |
| Single-row function | Environment-related function | USER | ApsaraDB for OceanBase does not support this function. |
Aggregate functions
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Statistical function | Aggregate function | AVG | Returns the average value of all values in a numeric column. |
| Statistical function | Aggregate function | COUNT | Queries the number of rows for the expr parameter. |
| Statistical function | Aggregate function | SUM | Returns the sum of all values in a specified column. |
| Statistical function | Aggregate function | GROUPING | ApsaraDB for OceanBase does not support this function. |
| Statistical function | Aggregate function | MAX | Returns the maximum value of a specified column. |
| Statistical function | Aggregate function | MIN | Returns the minimum value of a specified column. |
| Statistical function | Aggregate function | LISTAGG | Converts a column to a row. The LISTAGG function sorts the data within each group that is specified in the ORDER BY clause and merges the values in the measure column. |
| Statistical function | Aggregate function | ROLLUP | Returns a subtotal for each group and a grand total for all groups during data statistical analysis and report generation. |
| Statistical function | Aggregate function | STDDEV | Calculates the population standard deviation. |
| Statistical function | Aggregate function | STDDEV_POP | Calculates the population standard deviation. |
| Statistical function | Aggregate function | STDDEV_SAMP | Calculates the sample standard deviation. |
| Statistical function | Aggregate function | VARIANCE | Returns the variance of a specified column. |
| Statistical function | Aggregate function | APPROX_COUNT_DISTINCT | Calculates the number of rows in a column where duplicates are removed, and can return only an approximate value. You can use this function to further calculate the selectivity of the referenced column. |
Analytic functions
| Function category | Function subcategory | Function name | Description |
|---|---|---|---|
| Statistical function | Analytic function | AVG | Returns the average value of all values in a numeric column. |
| Statistical function | Analytic function | COUNT | Queries the number of rows for the expr parameter. |
| Statistical function | Analytic function | CUME_DIST | Calculates the cumulative distribution of a value in a group of values. |
| Statistical function | Analytic function | DENSE_RANK | Calculates the rank of a row in an ordered group of rows and returns the rank as NUMBER. |
| Statistical function | Analytic function | MAX | Returns the maximum value of a specified column. |
| Statistical function | Analytic function | MIN | Returns the minimum value of a specified column. |
| Statistical function | Analytic function | SUM | Returns the sum of all values in a specified column. |
| Statistical function | Analytic function | FIRST_VALUE | Returns the first value in a set of ordered values. |
| Statistical function | Analytic function | LAG | Provides access to a multi-row table without a self join. |
| Statistical function | Analytic function | LAST_VALUE | Returns the last value in a set of ordered values. |
| Statistical function | Analytic function | LEAD | Provides access to multiple rows of a table without a self join. Given a set of rows returned from a query and the position of the cursor, LEAD provides access to a row at a given physical offset beyond that position. |
| Statistical function | Analytic function | LISTAGG | Converts a column to a row. |
| Statistical function | Analytic function | NTH_VALUE | Returns the value of measure_expr in the nth row of the window defined by analytic_clause. |
| Statistical function | Analytic function | NTILE | Divides an ordered dataset into several buckets and assigns an appropriate bucket number to each row. expr specifies the number of buckets. |
| Statistical function | Analytic function | PERCENT_RANK | This function is similar to the CUME_DIST function that calculates the cumulative distribution. The return value ranges from 0 to 1. The PERCENT_RANK function of the first row in a set is 0. The return value is NUMBER. |
| Statistical function | Analytic function | SUM | Returns the sum of all values in a specified column. |
| Statistical function | Analytic function | RANK | Determines the rank of a group of values based on the ORDER BY expression in the OVER clause. |
| Statistical function | Analytic function | RATIO_TO_REPORT | Calculates the ratio of a value to the sum of a group of values. |
| Statistical function | Analytic function | ROW_NUMBER | Assigns a unique number to each row to which the function is applied. |
| Statistical function | Analytic function | STDDEV | Calculates the population standard deviation. |
| Statistical function | Analytic function | STDDEV_POP | Calculates the population standard deviation. |
| Statistical function | Analytic function | STDDEV_SAMP | Calculates the sample standard deviation. |
| Statistical function | Analytic function | VARIANCE | Returns the variance of a specified column. |
More information
For more information about the OVER keyword of analytic functions, see Window function description.