函数与运算符相似，即给定一些数据元素作为参数的输入并返回结果。然而函数在入参形式上与运算符有比较大的区别。函数允许包含的参数数量不定，一个函数里可以有一到两个，甚至更多的参数。

  1. 函数名（参数，参数，...）

没有任何参数的函数类似于 伪列。但是，伪列通常为结果集中的每一行返回不同的值，而没有任何变量的函数通常为每一行返回相同的值。

关于函数

OceanBase 内嵌的函数可以直接在 SQL 语句中使用。每个函数的入参传入值均有期望的数据类型，如果传入的数据类型不是期望的数据类型，则 OceanBase 会在实际执行 SQL 函数之前尝试将参数传入的数值转换为期望的数据类型。

函数中的空值

对于绝大多数的函数当入参为空值 NULL 的时候，其返回的结果也为 NULL。这种情况下，您可以使用 NVL 函数返回一个非空值。例如，一张记录佣金的表的佣金列 commission_pct 为空值 NULL ，则表达式NVL(commission_pct，0) 返回 0；如果 commission_pct 的值不为 NULL，则返回实际的佣金值。

函数分类与列表

在如下的函数分类列表中，每一类函数的参数和最终的函数返回值都有其特定的数据类型。

注意：

在 SQL 语句中对 LOB 列使用函数时，OceanBase 数据库将在 SQL 和 PL/SQL 处理期间创建临时 LOB 列，并有一定的使用限制，详情信息请参考文档 与Oracle兼容性对比。

本章中函数分成了两大类：

  • 单行函数：包括 数字函数、返回字符串的字符串函数、返回数字的字符串函数、 时间日期函数、通用比较函数、转换函数、编码解码函数和空值相关函数。
  • 统计函数：包括聚合函数和分析函数。

单行函数对于被查询的表或者视图每一行均返回一个结果值，这些函数可以使用在 SQL 语句的 SELECT、WHERE、START WITH、CONNECT BY、HAVING 等子句当中。

分析函数与聚合函数，都是对行集组（一组行的集合）进行聚合计算，不同的是，聚合函数每组只能返回一个值（一行），而分析函数每组可以返回多个值（多行）。行集组又称为窗口（Window）。聚合函数通常和 SELECT 语句中的 GROUP BY 子句一起使用，使用时数据库将查询表或视图的行分为几组，并将聚合函数应用于每组行，同时为每组返回一个结果行。

使用分析函数时需要用特殊的关键字 OVER 来指定窗口。更多关于窗后函数的信息，请参阅文档窗口函数说明。

数字函数

数字函数的变量输入与函数输出结果均为数字类型，绝大部分的数字函数的返回值的数据类型为 NUMBER，可以精确到小数点后 38 位。一些高等代数相关函数 COS，COSH，EXP，LN，LOG，SIN，SINH，SQRT，TAN，TANH 等函数的结果精确到小数点后 36 位，其他代数相关函数 ACOS，ASIN，ATAN 和 ATAN2

其结果返回值精确到小数点后 30 位。

函数分类函数子分类函数名功能描述
单行函数数字函数ABS返回指定数值表达式的绝对值（正值）的数学函数。
单行函数数字函数ACOS返回以弧度表示的角，其余弦为指定的 NUMBER 表达式，也称为反余弦。
单行函数数字函数ASINOceanBase 暂不支持。
单行函数数字函数ATANOceanBase 暂不支持。
单行函数数字函数ATAN2OceanBase 暂不支持。
单行函数数字函数BITAND运算符按位进行“与”操作。输入和输出类型均为 INT 整型，且类型一致。
单行函数数字函数CEIL返回值大于等于数值 numeric_expression 的最小整数。
单行函数数字函数COSOceanBase 暂不支持。
单行函数数字函数COSHOceanBase 暂不支持。
单行函数数字函数EXP返回 e 的 numeric_expression 次幂。
单行函数数字函数FLOOR返回小于等于数值 numeric_expression 的最大整数。
单行函数数字函数LN返回以 e 为底的 numeric_expression 的对数。
单行函数数字函数LOG返回以 x 为底的 y 的对数。
单行函数数字函数MOD返回 x 除以 y 的余数。
单行函数数字函数POWER返回 x 的 y 次幂。
单行函数数字函数REMAINDER返回 x 除以 y 的余数。
单行函数数字函数ROUND返回 numberic 四舍五入后的值。
单行函数数字函数SIGN返回数字 n 的符号，大于 0 返回 1，小于 0 返回 -1 ，等于 0 返回 0。
单行函数数字函数SINOceanBase 暂不支持。
单行函数数字函数SINHOceanBase 暂不支持。
单行函数数字函数SQRT返回 n 的平方根。
单行函数数字函数TANOceanBase 暂不支持。
单行函数数字函数TANHOceanBase 暂不支持。
单行函数数字函数TRUNC返回 numeric 按精度 precision 截取后的值。
单行函数数字函数WIDTH_BUCKETOceanBase 暂不支持。

返回字符串的字符串函数

函数的返回值的最大长度受数据类型的影响，比如：函数的返回值的数据类型是 VARCHAR2，但是返回值实际的大小超过了 VARCHAR2 数据类型的最大限制，此时 OceanBase 数据库会对结果进行截断处理并返回，但是在客户端上并不会显示提示。

注意：

如果返回值的数据类型是 CLOB，当返回值长度超过了最大限制时，OceanBase 不会返回数据且显示错误提示。

函数分类函数子分类函数名功能描述
单行函数返回字符串的字符串函数CHR将 n 转换为等价的一个或多个字符返回，且返回值与当前系统的字符集相关。
单行函数返回字符串的字符串函数CONCAT连接两个字符串。
单行函数返回字符串的字符串函数INITCAP返回字符串并将字符串中每个单词的首字母大写，其他字母小写。
单行函数返回字符串的字符串函数LOWER将字符串全部转为小写。
单行函数返回字符串的字符串函数LPAD在字符串 c1 的左边用字符串 c2 填充，直到长度为 n 时为止。
单行函数返回字符串的字符串函数LTRIM删除左边出现的字符串。
单行函数返回字符串的字符串函数REGEXP_REPLACE用于正则表达式替换。
单行函数返回字符串的字符串函数REGEXP_SUBSTROceanBase 暂不支持。
单行函数返回字符串的字符串函数REPLACE将字符表达式值中，部分相同字符串，替换成新的字符串。
单行函数返回字符串的字符串函数RPAD在字符串 c1 的右边用字符串 c2 填充，直到长度为 n 时为止。
单行函数返回字符串的字符串函数RTRIM删除右边出现的字符串，此函数对于格式化查询的输出非常有用。
单行函数返回字符串的字符串函数SUBSTR截取子字符串。其中多字节符（汉字、全角符等）按 1 个字符计算。
单行函数返回字符串的字符串函数TRANSLATE将字符表达式值中，指定字符替换为新字符。多字节符（汉字、全角符等），按 1 个字符计算。
单行函数返回字符串的字符串函数TRIM删除一个字符串的开头或结尾（或两者）的字符。
单行函数返回字符串的字符串函数UPPER将字符串全部转为大写。

返回数字的字符串函数

函数分类函数子分类函数名功能描述
单行函数返回数字的字符串函数ASCII返回字符表达式最左端字符的 ASCII 码值。
单行函数返回数字的字符串函数INSTR在一个字符串中搜索指定的字符，返回发现指定的字符的位置。
单行函数返回数字的字符串函数LENGTH返回字符串的长度。
单行函数返回数字的字符串函数REGEXP_COUNTOceanBase 暂不支持。
单行函数返回数字的字符串函数REGEXP_INSTROceanBase 暂不支持。

时间日期函数

时间日期函数支持的入参数据类型有三类：日期时间 (DATE)，时间戳 (TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE)，以及间隔 (INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH)

仅支持 DATE 数据类型入参的函数列表：ADD_MONTHS、CURRENT_DATE, LAST_DAY、NEW_TIME 和 NEXT_DAY。

如果对于以上函数，您尝试给入的是 TIMESTAMP 类型的数据，OceanBase 内部会先进行隐式数据类型转换后带入函数进行运算，并返回 DATE 类型的返回值。

注意：

MONTHS_BETWEEN 返回值为 1 个数字。

ROUND 和 TRUNC 并不能进行隐式转换，必须传输 DATE 类型的数值，否则会报错。

剩余的函数对于三种参数数据类型均可以支持，且返回和入参同样的数据类型。

函数分类函数子分类函数名功能描述
单行函数时间日期函数ADD_MONTHS返回在日期 date 基础上 n 个月后的日期值，如果 n 的值为负数则返回日期 date 基础上 n 个月前的日期值（date 减去 n 个月）。
单行函数时间日期函数CURRENT_DATE返回当前会话时区中的当前日期。
单行函数时间日期函数CURRENT_TIMESTAMP返回 TIMESTAMP WITH TIME ZONE 数据类型的当前会话时区中的当前日期，返回值中包含当前的时区信息。
单行函数时间日期函数DBTIMEZONE返回当前数据库实例的时区，在 OceanBase 中数据库时区恒为+00:00，且不支持修改。
单行函数时间日期函数EXTRACT (datetime)从指定的时间字段或表达式中抽取年、月、日、时、分、秒等元素。
单行函数时间日期函数FROM_TZ将一个 TIMSTAMP 数据类型的值和时区信息拼成一个 TIMESTAMP WITH TIME ZONE 数据类型的时间值。
单行函数时间日期函数LAST_DAY返回日期 date 所在月份的最后一天的日期。
单行函数时间日期函数LOCALTIMESTAMP返回当前会话时区中的当前日期，返回 TIMESTAMP 数据类型的值。
单行函数时间日期函数MONTHS_BETWEEN返回返回参数 date1 到 date2 之间的月数。
单行函数时间日期函数NEW_TIMEOceanBase 暂不支持。
单行函数时间日期函数NEXT_DAY返回日期 d1 的下一周中 c1（星期值）所在的日期值。
单行函数时间日期函数NUMTODSINTERVAL把参数 n 转为以参数 interval_unit 为单位的 INTERVAL DAY TO SECOND 数据类型的值。
单行函数时间日期函数NUMTOYMINTERVAL把参数 n 转为以 interval_unit 为单位的 INTERVAL YEAR TO MONTH 数据类型的值。
单行函数时间日期函数ROUND (date)返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值。
单行函数时间日期函数SESSIONTIMEZONE返回当前会话时区。
单行函数时间日期函数SYS_EXTRACT_UTC返回与指定时间相对应的的标准 UTC 时间。
单行函数时间日期函数SYSDATE返回当前日期。
单行函数时间日期函数SYSTIMESTAMP返回系统当前日期，返回值的秒的小数位包含 6 位精度，且包含当前时区信息。
单行函数时间日期函数TO_CHAR (datetime)将 DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH 等数据类型的值按照参数 fmt 指定的格式转换为 VARCHAR2 数据类型的值。
单行函数时间日期函数TO_DSINTERVAL将一个 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串转换为一个 INTERVAL DAY TO SECOND 数据类型的值，该函数可以用来对一个日期时间值进行加减计算。
单行函数时间日期函数TO_TIMESTAMP将字符串转换为 TIMESTAMP 数据类型。
单行函数时间日期函数TO_TIMESTAMP_TZ将字符串转换为 TIMESTAMP WITH TIME ZONE 数据类型，包含时区信息。
单行函数时间日期函数TO_YMINTERVAL将一个 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串转换为一个 INTERVAL YEAR TO MONTH 数据类型的值，该函数可以用来对一个日期时间值进行加减计算。
单行函数时间日期函数TRUNC (date)返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值，并且返回的日期值在 date 之前。
单行函数时间日期函数TZ_OFFSET返回时区 n 的时区偏移量。时区偏移量是指与格林尼治标准时间 GMT 的差（小时和分钟）。

通用比较函数

可以通过本类别函数快速的在集合中寻找到最大值和最小值。

函数分类函数子分类函数名功能描述
单行函数通用比较函数GREATEST返回一个或多个表达式列表中的最大值。
单行函数通用比较函数LEAST返回一个或多个表达式列表中的最小值。

转换函数

可以通过本类型的函数将原本的数据类型转换为另外一种数据类型。

函数分类函数子分类函数名功能描述
单行函数转换函数ASCIISTROceanBase 暂不支持。
单行函数转换函数BIN_TO_NUMOceanBase 暂不支持。
单行函数转换函数CAST用于将源数据类型的表达式显式转换为另一种数据类型。
单行函数转换函数CHARTOROWIDOceanBase 暂不支持。
单行函数转换函数HEXTORAW将 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型中包含十六进制数字的字符转换为 RAW 数据类型。
单行函数转换函数RAWTOHEX将二进制数转换为一个相应的十六进制表示的字符串。
单行函数转换函数TO_BINARY_DOUBLE返回一个双精度的 64 位浮点数.
单行函数转换函数TO_BINARY_FLOAT返回一个单精度的 32 位浮点数。
单行函数转换函数TO_CHAR (character)将 NCHAR、NVARCHAR2 或 CLOB 数据转换为数据库字符集。
单行函数转换函数TO_CHAR (datetime)将 DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH 等数据类型的值按照参数 fmt 指定的格式转换为 VARCHAR2 数据类型的值。
单行函数转换函数TO_CHAR (number)将 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 类型的数值 n 按照指定数值格式 fmt 转换为 varchar2 数据类型的值。
单行函数转换函数TO_DATE将 CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的字符转换为日期数据类型的值。
单行函数转换函数TO_DSINTERVAL将一个 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串转换为一个 INTERVAL DAY TO SECOND 数据类型的值，该函数可以用来对一个日期时间值进行加减计算。
单行函数转换函数TO_NUMBER将 expr 转换为数值数据类型的值。
单行函数转换函数TO_TIMESTAMP将字符串转换为 TIMESTAMP 数据类型。
单行函数转换函数TO_TIMESTAMP_TZ将字符串转换为 TIMESTAMP WITH TIME ZONE 数据类型，包含时区信息。
单行函数转换函数TO_YMINTERVAL将一个 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串转换为一个 INTERVAL YEAR TO MONTH 数据类型的值，该函数可以用来对一个日期时间值进行加减计算。

编码解码函数

可以通过本类型的函数在 OceanBase 数据库中实现数据的编码解密需求。

函数分类函数子分类函数名功能描述
单行函数编码解码函数DECODE会根据条件返回相应值。
单行函数编码解码函数ORA_HASH  获取对应表达式的 HASH 值。
单行函数编码解码函数VSIZE返回 X 的字节大小数。

空值相关函数

函数分类函数子分类函数名功能描述
单行函数空值相关函数COALESCE返回参数列表中第一个非空表达式，必须指定最少两个参数。
单行函数空值相关函数LNNVL判断条件中的一个或者两个操作数是否为 NULL。
单行函数空值相关函数NULLIFOceanBase 暂不支持。
单行函数空值相关函数NVL从两个表达式返回一个非 NULL 值。如果 expr1 与 expr2 的结果皆为 NULL 值，则 NVL 函数返回 NULL。
单行函数空值相关函数NVL2根据表达式是否为空，返回不同的值。如果 expr1 不为空，则返回 expr2 的值，如果 expr1 为空，则返回 expr3 的值。expr2 和 expr3 类型不同的话，expr3 会转换为 expr1 的类型。

环境相关函数

本分类的函数主要提供会话或者租户实例相关的环境信息。

函数分类函数子分类函数名功能描述
单行函数环境相关函数SYS_CONTEXTOceanBase 暂不支持。
单行函数环境相关函数UIDOceanBase 暂不支持。
单行函数环境相关函数USEROceanBase 暂不支持。

聚合函数

函数分类函数子分类函数名功能描述
统计函数聚合函数AVG返回数值列的平均值。
统计函数聚合函数COUNT用于查询参数 expr 的行数。
统计函数聚合函数SUM返回参数中指定列的和。
统计函数聚合函数GROUPINGOceanBase 暂不支持。
统计函数聚合函数MAX返回参数中指定的列中的最大值。
统计函数聚合函数MIN返回参数中指定列的最小值。
统计函数聚合函数LISTAGG用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值。
统计函数聚合函数ROLLUP在数据统计和报表生成过程中，它可以为每个分组返回一个小计，同时为所有分组返回总计。
统计函数聚合函数STDDEV用于计算总体标准差。
统计函数聚合函数STDDEV_POP计算总体标准差。
统计函数聚合函数STDDEV_SAMP计算样本标准差。
统计函数聚合函数VARIANCE返回参数指定列的方差。
统计函数聚合函数APPROX_COUNT_DISTINCT计算某一列去重后的行数，返回的值是一个近似值，该函数可以进一步用于计算被引用的列的选择性。

分析函数

函数分类函数子分类函数名功能描述
统计函数分析函数AVG返回数值列的平均值。
统计函数分析函数COUNT用于查询参数 expr 的行数。
统计函数分析函数CUME_DIST计算一个值在一组值中的累积分布。
统计函数分析函数DENSE_RANK计算有序行组中行的秩，并将秩作为 NUMBER 返回。
统计函数分析函数MAX返回参数中指定的列中的最大值。
统计函数分析函数MIN返回参数中指定列的最小值。
统计函数分析函数SUM返回参数中指定列的和。
统计函数分析函数FIRST_VALUE返回有序值中的第一个值。
统计函数分析函数LAG提供对多行表的访问，而不需要自连接。
统计函数分析函数LAST_VALUE返回一组有序值中的最后一个值。
统计函数分析函数LEAD它提供了对表多行的访问，而无需进行自我连接。给定从查询返回的一些列行和光标的位置，LEAD 提供超出该位置的物理偏移量的行的访问。
统计函数分析函数LISTAGG用于列转行。
统计函数分析函数NTH_VALUE返回 analytic_clause 定义的窗口中第 n 行的 measure_expr 值。
统计函数分析函数NTILE  将有序数据集划分为 expr 指示的若干桶，并为每一行分配适当的桶号。
统计函数分析函数PERCENT_RANK类似于 CUME_DIST（累积分布）函数。 它的返回值范围为 0~1。任何集合中的第一行的 PERCENT_RANK 函数为 0，返回值为 NUMBER。
统计函数分析函数SUM返回参数中指定列的和。
统计函数分析函数RANK基于 OVER 子句中的 ORDER BY 表达式确定一组值的排名。
统计函数分析函数RATIO_TO_REPORT计算一个值与一组值之和的比率。
统计函数分析函数ROW_NUMBER为应用它的每一行分配一个唯一的数字。
统计函数分析函数STDDEV用于计算总体标准差。
统计函数分析函数STDDEV_POP计算总体标准差。
统计函数分析函数STDDEV_SAMP计算样本标准差。
统计函数分析函数VARIANCE返回参数指定列的方差。

更多信息

分析函数中的关键字 OVER，请参阅 窗口函数说明。