函数 - 函数概述 - 《OceanBase v3.1.1 官方教程》

函数分类与列表
更多信息

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

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

对于绝大多数的函数当入参为空值 NULL 的时候，其返回的结果也为 NULL。这种情况下，您可以使用函数返回一个非空值。例如，一张记录佣金的表的佣金列 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 位。

返回字符串的字符串函数

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

注意

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

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

返回数字的字符串函数

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

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

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

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

在使用时间函数前，建议执行 SELECT * FROM NLS_DATABASE_PARAMETERS 查看当前的 NLS 格式：

如果与文中示例的显示格式不一致，可以执行如下命令更改格式：

注意

MONTHS_BETWEEN 返回值为 1 个数字。

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

通用比较函数

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

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

转换函数

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

函数分类	函数子分类	函数名	功能描述
函数分类	函数子分类	函数名	功能描述
单行函数	转换函数	ASCIISTR	OceanBase 暂不支持。
单行函数	转换函数	BIN_TO_NUM	OceanBase 暂不支持。
单行函数	转换函数	CHARTOROWID	OceanBase 暂不支持。
单行函数	转换函数	HEXTORAW	将 `CHAR`、`VARCHAR2`、`NCHAR` 或数据类型中包含十六进制数字的字符转换为 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 数据库中实现数据的编码解密需求。

空值相关函数

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

环境相关函数

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

函数分类	函数子分类	函数名	功能描述
单行函数	环境相关函数	SYS_CONTEXT	OceanBase 暂不支持。
单行函数	环境相关函数	UID	OceanBase 暂不支持。
单行函数	环境相关函数	USER	OceanBase 暂不支持。

分析函数

函数分类	函数子分类	函数名	功能描述
统计函数	分析函数	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	返回参数指定列的方差。