Built-in functions in OceanBase Database can be used directly in SQL statements. Each function has expected data types for its input parameters. If the actual data type of an input parameter does not match the expected type, OceanBase Database attempts to convert the value before the function is executed.
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.
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.
Notice
When using functions on LOB columns in SQL statements, OceanBase Database creates temporary LOB columns during SQL and PL processing. There are certain limitations on the use of these temporary columns. For more information, see Compatibility with Oracle.
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 clauses such as SELECT, WHERE, START WITH, CONNECT BY, and HAVING of an SQL statement.
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 typically used with the GROUP BY clause in a SELECT statement. When used this way, the database divides the rows of the queried table or view into several groups, applies the aggregate function to each group, and returns a result row for each group.
When using analytic functions, you must use the special keyword OVER to specify the window. For more information about window functions, see Analytic functions.
Numeric functions
The inputs and outputs of numeric functions are both numeric types. The return type of most numeric functions is NUMBER, which can represent numbers with up to 38 decimal digits of precision. Some advanced algebraic functions, such as COS(), COSH(), EXP(), LN(), LOG(), SIN(), SINH(), SQRT(), TAN(), and TANH(), achieve 36 decimal digits of precision. Other algebraic functions, including ACOS(), ASIN(), ATAN(), and ATAN2(), return results with 30 decimal digits of precision.
Function name |
Feature description |
|---|---|
| ABS | Return the absolute value of a specified numeric expression. |
| ACOS | Return an angle in radians whose cosine is the specified numeric expression. |
| ASIN | Returns the angle, in radians, whose sine is the specified numeric expression. |
| ATAN | Returns the angle in radians whose tangent is the specified numeric expression. |
| ATAN2 | Returns an angle in radians whose tangent is the ratio of two specified numeric expressions. |
| BITAND | Perform a bitwise AND operation on two numbers |
| CEIL | Return the smallest integer greater than or equal to a specified value. |
| COS | Return the cosine value of a specified angle. |
| COSH | Return the hyperbolic cosine value at the specified angle. |
| EXP | Return the specified power of e |
| FLOOR | Return the largest integer less than or equal to a specified value. |
| GENERATOR | Generate a random number within the specified range |
| LN | Return the natural logarithm of a specified number |
| LOG | Return the logarithm of a specified value |
| MOD | Return the remainder of dividing two numbers |
| NANVL | If the first parameter is NaN, return the second parameter. |
| NORMAL | Generate a random number from a normal distribution |
| POWER | Return the specified power of a given number |
| RANDOM | Generate a random number between 0 and 1 |
| RANDSTR | Generate a random string of the specified length |
| REMAINDER | Return the remainder of dividing two numbers |
| ROUND | Rounds a number to the specified number of decimal places |
| SIGN | Return the sign of the number (-1, 0, or 1). |
| SIN | Return the sine value at a specified angle. |
| SINH | Return the hyperbolic sine of a specified angle. |
| SQRT | Return the square root of a specified number |
| TAN | Return the tangent value at a specified angle |
| TANH | Return the hyperbolic tangent of a specified angle. |
| TRUNC | Truncates a number to the specified number of decimal places. |
| UNIFORM | Generate uniformly distributed random numbers within a specified range |
| WIDTH_BUCKET | Assign values to a specified number of buckets |
| ZIPF | Generate random numbers following Zipf's distribution |
String functions that return strings
The maximum length of a function's return value is determined by its data type. For example, if the function's return type is VARCHAR2 but the actual value exceeds the maximum limit of this data type, OceanBase Database truncates the result and returns it without notifying the client.
Notice
If the data type of the return value is CLOB, OceanBase Database will not return the data and display an error message when the length of the return value exceeds the maximum limit.
Function name |
Feature description |
|---|---|
| CHR | Convert ASCII values to characters |
| CONCAT | Concatenates two strings |
| INITCAP | Capitalize the first letter of each word in a string |
| LOWER | Convert String to Lowercase |
| MAX_PT | Returns the name of the largest physical partition under the given partitioning key for a partitioned table. |
| LPAD | Pad the left side of a string with specified characters |
| LTRIM | Delete leading spaces or specified characters from a string |
| NLS_LOWER | Converts a string to lowercase using the specified locale. |
| NLS_UPPER | Converts a string to uppercase using the specified locale. |
| NLSSORT | Return the sort key for the string |
| REGEXP_REPLACE | Replace strings using regular expressions |
| REGEXP_SUBSTR | Extract Substring Using Regular Expressions |
| REPLACE | Replace a substring in a string |
| RPAD | Pad the right side of a string with specified characters |
| RTRIM | Delete trailing spaces or specified characters from a string |
| SUBSTR | Extract a substring from a string |
| TRIM | Delete leading and trailing spaces or specified characters from a string |
| TRANSLATE | Replace characters in a string with other characters |
| UPPER | Convert a string to uppercase |
String functions for returning numbers
String functions that return numbers take a numeric value as their argument based on the specified condition, such as an ASCII code value or the position of a character (string).
Function name |
Feature description |
|---|---|
| ASCII | Return the ASCII value of the first character in a string. |
| INSTR | Return the position of a substring within a string |
| LENGTH | Length of the returned string |
| REGEXP_COUNT | Returns the number of times a regular expression matches in a string |
| REGEXP_INSTR | Return the position where the regular expression matches |
Date and time functions
Date and time functions support three types of input data types: datetime (DATE), timestamps (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and intervals (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH).
Functions that support only the DATE data type as an input parameter include: ADD_MONTHS, CURRENT_DATE, LAST_DAY, and NEXT_DAY.
If you attempt to use a TIMESTAMP type value for these functions, OceanBase Database will implicitly convert it to a DATE type before evaluating the function and returning a DATE type result.
Before using date and time functions, it is recommended to execute SELECT * FROM NLS_DATABASE_PARAMETERS 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 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
Notice
MONTHS_BETWEENreturns a single number.ROUNDandTRUNCcannot perform implicit conversion; they must be passed values of theDATEtype. Otherwise, an error will be returned.
The remaining functions support all three parameter data types and return the same data type as the input parameters.
Function name |
Feature description |
|---|---|
| ADD_MONTHS | Add a specified number of months to a date |
| CURRENT_DATE | Return the current date |
| CURRENT_TIMESTAMP | Return the current date and time |
| DBTIMEZONE | Return Database Time Zone |
| EXTRACT | Extract a specified part from a date and time |
| FROM_TZ | Convert Timestamp and Time Zone to Timestamp with Time Zone |
| LAST_DAY | Return the last day of the month in which a specified date falls. |
| LOCALTIMESTAMP | Return the current local timestamp. |
| MONTHS_BETWEEN | Return the number of months between two dates |
| NEXT_DAY | Return the date of the next specified weekday after a given date. |
| NUMTODSINTERVAL | Convert a numeric value to an interval in days, hours, and seconds |
| NUMTOYMINTERVAL | Convert a numeric value to a year-month interval |
| ROUND | Round a date to the specified format |
| SESSIONTIMEZONE | Return the time zone of the current session. |
| SYSDATE | Return the current system date and time. |
| SYSTIMESTAMP | Return the current system timestamp. |
| SYS_EXTRACT_UTC | Extract UTC Time from a Timestamp with a Time Zone |
| TO_CHAR | Convert datetime to string |
| TO_DSINTERVAL | Convert a string to a datetime interval in days, hours, and seconds |
| TO_TIMESTAMP | Convert a string to a timestamp |
| TO_TIMESTAMP_TZ | Convert a string to a timestamp with time zone |
| TO_YMINTERVAL | Convert a string to a year-month interval |
| TRUNC | Truncates a date to the specified format. |
| TZ_OFFSET | Return the offset of the specified timezone. |
General comparison functions
You can use the functions in this category to quickly find the maximum and minimum values in a set.
Function name |
Feature description |
|---|---|
| GREATEST | Return the maximum value in the parameter list. |
| LEAST | Return the minimum value in the parameter list. |
Conversion functions
You can use the functions in this category to convert one data type into another.
Function name |
Feature description |
|---|---|
| ASCIISTR | Convert a string to an ASCII string. |
| CAST | Convert an expression to a specified data type. |
| CHARTOROWID | Convert String to ROWID |
| CONVERT | Converts a string from one character set to another. |
| HEXTORAW | Convert Hexadecimal String to RAW Data |
| RAWTOHEX | Convert RAW data to a hexadecimal string |
| ROWIDTOCHAR | Convert ROWID to string |
| ROWIDTONCHAR | Convert ROWID to NCHAR string |
| SCN_TO_TIMESTAMP | Convert SCN to Timestamp |
| TIMESTAMP_TO_SCN | Convert timestamp to SCN |
| TO_BINARY_DOUBLE | Convert an expression to BINARY_DOUBLE |
| TO_BINARY_FLOAT | Convert an expression to BINARY_FLOAT |
| TO_BLOB | Convert an expression to a BLOB |
| TO_CHAR | Convert an expression to a string |
| TO_CLOB | Convert an expression to CLOB |
| TO_DATE | Convert String to Date |
| TO_MULTI_BYTE | Convert single-byte characters to multibyte characters |
| TO_NCHAR | Convert an expression to an NCHAR string |
| TO_NUMBER | Convert the expression to a number. |
| TO_SINGLE_BYTE | Convert multibyte characters to single-byte characters |
| TO_YMINTERVAL | Convert a string to a year-month interval |
| TO_DSINTERVAL | Convert a string to a datetime interval in days, hours, and seconds |
| TO_TIMESTAMP | Convert a string to a timestamp |
| TO_TIMESTAMP_TZ | Convert a string to a timestamp with time zone |
| UNISTR | Convert a string to a Unicode string |
Encoding and decoding functions
You can use the functions in this category to implement data encoding and decoding requirements in OceanBase Database.
Function name |
Feature description |
|---|---|
| DECODE | Conditional expression, similar to a CASE statement |
| DUMP | Return the internal representation of an expression |
| ORA_HASH | Calculate the hash value of an expression |
| VSIZE | Number of bytes in the internal representation of the return expression |
NULL-related functions
NULL-related functions are used to handle null values in parameters. For the vast majority of functions, when an input parameter is a null value (NULL), the function returns NULL. In such cases, you can use the NVL function to return a non-null value. For example, if the commission column commission_pct in a table that records commissions is a null value (NULL), the expression NVL(commission_pct, 0) returns 0; if the value of commission_pct is not NULL, it returns the actual commission value.
Function name |
Feature description |
|---|---|
| COALESCE | Return the first non-NULL value |
| LNNVL | Check if the condition is NULL or FALSE. |
| NANVL | If the first parameter is NaN, return the second parameter. |
| NULLIF | If two parameters are equal, NULL is returned. |
| NVL | If the first parameter is NULL, return the second parameter. |
| NVL2 | If the first parameter is not NULL, return the second parameter; otherwise, return the third parameter. |
Environment-related functions
Environment-related functions provide environment information related to a session or tenant instance.
Function name |
Feature description |
|---|---|
| OB_VERSION | Return OceanBase Database version |
| SYS_CONTEXT | Return the value of a property for the specified context. |
| SYS_GUID | Generate a globally unique identifier |
| UID | Return the user ID of the current user. |
| USER | Return Current Username |
| USERENV | Return the environment information of the current session. |
Hierarchical functions
Function name |
Feature description |
|---|---|
| SYS_CONNECT_BY_PATH | Returns the path of column values from the root to a node. The values of the column returned by each row under the CONNECT BY condition are separated by a specified delimiter. |
JSON functions
JSON functions perform operations such as creating, manipulating, and searching JSON values.
Function name |
Feature description |
|---|---|
| JSON_ARRAY | Create a JSON array |
| JSON_ARRAYAGG | Aggregate Multiple Rows into a JSON Array |
| JSON_EQUAL | Compare whether two JSON values are equal. |
| JSON_EXISTS | Check if the specified path exists in the JSON document. |
| JSON_MERGEPATCH | Merging Documents Using JSON Merge Patch |
| JSON_OBJECT | Create a JSON object |
| JSON_OBJECTAGG | Aggregate multiple rows into a JSON object |
| JSON_QUERY | Query values from a JSON document |
| JSON_TABLE | Convert JSON data into a table format. |
| JSON_VALUE | Extract a scalar value from a JSON document |
XML functions
XML functions can efficiently query, update, and search XML documents.
Function name |
Feature description |
|---|---|
| DELETEXML | Delete a node from an XML document. |
| EXISTSNODE | Check if a specified node exists in the XML document. |
| EXTRACT | Extract nodes from an XML document |
| EXTRACTVALUE | Extract values from XML documents |
| INSERTCHILDXML | Insert child nodes into an XML document |
| UPDATEXML | Update a node in an XML document |
| XMLAGG | Aggregate multiple XML values into a single XML document |
| XMLATTRIBUTES | Create XML Attribute |
| XMLCAST | Convert an XML value to a specified data type. |
| XMLCONCAT | Concatenates Multiple XML Values |
| XMLELEMENT | Create XML Element |
| XMLFOREST | Create an XML forest |
| XMLPARSE | Parse String to XML Document |
| XMLSEQUENCE | Convert an XML document into a row sequence |
| XMLSERIALIZE | Serialize an XML document to a string |
| XMLTABLE | Convert XML data into a table format. |
TABLE FUNCTION
TABLE FUNCTIONS are used to return a row set result that can be referenced in the FROM clause.
Name |
Feature description |
|---|---|
| JSON_TABLE | Convert JSON data into a table format. |
| XMLTABLE | Convert XML data into a table format. |
| FILES | Read data from an external file and return the table schema |
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 together with the GROUP BY clause in a SELECT statement.
Function name |
Feature description |
|---|---|
| APPROX_COUNT_DISTINCT | Returns the number of approximately unique values |
| AVG | Return the average value of the numbers. |
| CORR | Return the correlation coefficient of two numeric columns |
| COUNT | Number of rows returned or non-NULL values |
| COVAR_POP | Return the overall covariance |
| COVAR_SAMP | Return Sample Covariance |
| CUME_DIST | Return cumulative distribution value |
| DENSE_RANK | Return Dense Ranking |
| GROUP_ID | Return Group ID |
| GROUPING | Return the grouping information from the GROUP BY clause |
| GROUPING_ID | Return the bit vector of group IDs. |
| KEEP | Retain values that meet specified conditions within a group |
| LISTAGG | Concatenate multiple row values into a single string |
| MAX | Return the maximum value |
| MEDIAN | Return the median |
| MIN | Return the minimum value |
| PERCENTILE_CONT | Return Continuous Percentile Values |
| PERCENTILE_DISC | Return discrete percentile values |
| PERCENT_RANK | Return Percentage Ranking |
| RANK | Return Ranking |
| REGR_ | Linear Regression Function Family |
| ROLLUP | Create Group Summary |
| STDDEV | Return Standard Deviation |
| STDDEV_POP | Return the overall standard deviation. |
| STDDEV_SAMP | Return Sample Standard Deviation |
| SUM | Return the sum of the values. |
| VAR_POP | Return the overall variance |
| VAR_SAMP | Return Sample Variance |
| VARIANCE | Return variance |
| WMSYS.WM_CONCAT/WM_CONCAT | Concatenate multiple row values into a single string |
Analytical functions
Analytical functions (also called 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 only one row per group, an analytical function can return multiple rows per group, with each row within the group being the result of a window-based calculation. Analytical functions can significantly optimize queries that require self-joins.
Function name |
Feature description |
|---|---|
| ARG_MAX | Parameter for returning the maximum value |
| ARG_MIN | Parameter for returning the minimum value |
| AVG | Return the average value of the numbers. |
| CORR | Return the correlation coefficient of two numeric columns |
| COUNT | Number of rows returned or non-NULL values |
| COVAR_POP | Return the overall covariance |
| COVAR_SAMP | Return Sample Covariance |
| CUME_DIST | Return cumulative distribution value |
| DENSE_RANK | Return Dense Ranking |
| FIRST_VALUE | Return the first value in the window |
| KEEP | Retain values that meet specified conditions within a group |
| LAG | Return the value of the previous row or the first N rows |
| LAST_VALUE | Return the last value in the window |
| LEAD | Return the value of the next row or the next N rows |
| LISTAGG | Concatenate multiple row values into a single string |
| MAX | Return the maximum value |
| MEDIAN | Return the median |
| MIN | Return the minimum value |
| NTH_VALUE | Return the N-th value in the window |
| NTILE | Split the result set into a specified number of groups. |
| PERCENTILE_CONT | Return Continuous Percentile Values |
| PERCENTILE_DISC | Return discrete percentile values |
| PERCENT_RANK | Return Percentage Ranking |
| RANK | Return Ranking |
| RATIO_TO_REPORT | Proportion of return values in the group |
| REGR_ | Linear Regression Function Family |
| ROW_NUMBER | Return Line Number |
| STDDEV | Return Standard Deviation |
| STDDEV_POP | Return the overall standard deviation. |
| STDDEV_SAMP | Return Sample Standard Deviation |
| SUM | Return the sum of the values. |
| VAR_POP | Return the overall variance |
| VAR_SAMP | Return Sample Variance |
| VARIANCE | Return variance |
| WMSYS.WM_CONCAT/WM_CONCAT | Concatenate multiple row values into a single string |
| WINDOW_FUNNEL | Calculates the maximum number of consecutive steps that meet the specified conditions within a given time window. |
Information functions
Function name |
Feature description |
|---|---|
| OB_TRANSACTION_ID | Return the current transaction ID. |
Spatial functions
Function name |
Feature description |
|---|---|
| SDO_GEOMETRY | Create a spatial geometry object |
| Query Aggregation Functions | Query and calculate attributes of spatial geometric objects |
Audit functions
Function name |
Feature description |
|---|---|
| AUDIT_LOG_ENCRYPTION_PASSWORD_SET | Trigger archiving of the current audit logs and generate a new encryption key. |
| AUDIT_LOG_ENCRYPTION_PASSWORD_GET | Get the encryption password for the corresponding archived log file. |