阅读(1.7k) 书签赞(0) 我要纠错

OceanBase 函数概述

2021-06-18 13:59 更新

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

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

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

关于函数

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` 表达式，也称为反余弦。
单行函数	数字函数	ASIN	OceanBase 暂不支持。
单行函数	数字函数	ATAN	OceanBase 暂不支持。
单行函数	数字函数	ATAN2	OceanBase 暂不支持。
单行函数	数字函数	BITAND	运算符按位进行“与”操作。输入和输出类型均为 `INT` 整型，且类型一致。
单行函数	数字函数	CEIL	返回值大于等于数值 `numeric_expression` 的最小整数。
单行函数	数字函数	COS	OceanBase 暂不支持。
单行函数	数字函数	COSH	OceanBase 暂不支持。
单行函数	数字函数	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。
单行函数	数字函数	SIN	OceanBase 暂不支持。
单行函数	数字函数	SINH	OceanBase 暂不支持。
单行函数	数字函数	SQRT	返回 `n` 的平方根。
单行函数	数字函数	TAN	OceanBase 暂不支持。
单行函数	数字函数	TANH	OceanBase 暂不支持。
单行函数	数字函数	TRUNC	返回 `numeric` 按精度 `precision` 截取后的值。
单行函数	数字函数	WIDTH_BUCKET	OceanBase 暂不支持。

返回字符串的字符串函数

函数的返回值的最大长度受数据类型的影响，比如：函数的返回值的数据类型是 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	OceanBase 暂不支持。

时间日期函数

时间日期函数支持的入参数据类型有三类：日期时间 (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 类型的返回值。

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

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 (0.05 sec)

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

obclient>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected (0.00 sec)

obclient>ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Query OK, 0 rows affected (0.00 sec)
/*将返回值中秒的小数位设置为 9 位*/

obclient> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
Query OK, 0 rows affected (0.00 sec)

注意

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_TIME	OceanBase 暂不支持。
单行函数	时间日期函数	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	返回一个或多个表达式列表中的最小值。

转换函数

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

函数分类	函数子分类	函数名	功能描述
函数分类	函数子分类	函数名	功能描述
单行函数	转换函数	ASCIISTR	OceanBase 暂不支持。
单行函数	转换函数	BIN_TO_NUM	OceanBase 暂不支持。
单行函数	转换函数	CHARTOROWID	OceanBase 暂不支持。
单行函数	转换函数	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`。
单行函数	空值相关函数	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` 的行数。
统计函数	聚合函数	SUM	返回参数中指定列的和。
统计函数	聚合函数	GROUPING	OceanBase 暂不支持。
统计函数	聚合函数	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	返回参数指定列的方差。